Get ID from ROW and include in a linkable result - PHP MYSQL

I have this code for a search engine. In the results I should get back the following columns: name, organizer_id,no_pages,publication_date,price

The search itself works fine, but I need to make linkable the results under the the column name, but when I add the code, ALL the colums turns into a link.

Here is how the results looks like now: http://postimg.org/image/59y36mih7/

I need to construct the link for the column name in this way:

http://www.mysite.com/(id),(name of the report)  ///// (coma included)
So, in real would be like this:

http://www.mysite.com/129234,construction_in_germany

Can you help me how to make the query to get the info from id column and make only the results from name column clickable with the mentioned structure? I´m quite lost.

I highlighted the code I consider could have the between //////////////




CODE:

<?php



    $MySQLPassword = "*****";   
    $HostName = "***";  
    $UserName = "***";
    $Database = "****";

    mysql_connect($HostName,$UserName,$MySQLPassword)
    or die("ERROR: Could not connect to database!");
    mysql_select_db($Database) or die("cannot select db");


    $default_sort = 'ID';
    $allowed_order = array ('name','description');

    if (!isset ($_GET['order']) || 
        !in_array ($_GET['order'], $allowed_order)) {
        $order = $default_sort;
    } else {
        $order = $_GET['order'];
    }


    if (isset($_GET['keyword'])) {

            if(!$_GET['keyword']) {
              die('<p>Please enter a search term.</p>');
        }     





/////////////////////////HERE IS THE BEGINING OF CODE WHERE I THINK SHOULD BE THE PROBLEM ////////////////////////////

    $tables = 'reports';
    $return_fields = 'name organizer_id no_pages publication_date price';
    $check_fields = 'name description';


    $query_text = $_GET['keyword'];

    $clean_query_text =cleanQuery($query_text);

    $newquery=bq_simple ($return_fields, $tables, $check_fields, $clean_query_text);
    $newquery = $newquery . " ORDER BY $order;";

    $result = mysql_query($newquery) or die(mysql_error());


    $numrows = mysql_num_rows($result);
    if ($numrows == 0) {
        echo "<H4>No data to display!</H4>";
        exit;
    }
    echo    "<p>Your search '$query_text' returned ".$numrows. " results.</p>\n";
    echo    "<p>Click on the headings to sort.</p>\n";

    $row = mysql_fetch_assoc ($result);
    echo "<TABLE border=1>\n";
    echo "<TR>\n";
    foreach ($row as $heading=>$column) {


        echo "<TD><b>";
        if (in_array ($heading, $allowed_order)) {
            echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading&keyword=$query_text\">$heading</a>";
        } else {
            echo $heading;
        }                
        echo "</b></TD>\n";
    }
    echo "</TR>\n";

    $results = mysql_query("SELECT id, name FROM reports WHERE id = $id") or die(mysql_error());
    while ($row = mysql_fetch_assoc ($result)) {
        echo "<TR>\n";

        echo '<a href="mysite.com/' . $row['id'] . ',">' . $row['name'] . '</a>';
        echo "</TR>\n";
    }
    echo "</TABLE>\n";
    }

////////////////////////FINISH OF THE CODE WITH PROBLEM ////////////////////////////

/* * * * * * * * * * * * * *  F U N C T I O N S * * * * * * * * * * * */

    function cleanQuery($string)
    {
      $string = trim($string);
      $string = strip_tags($string); // remove any html/javascript.

      if(get_magic_quotes_gpc())  // prevents duplicate backslashes
      {
        $string = stripslashes($string);
      }
      if (phpversion() >= '4.3.0')
      {
        $string = mysql_real_escape_string($string);
      }
      else
      {
        $string = mysql_escape_string($string);
      }
      return $string;
    }


function bq_handle_shorthand($text) {
    $text = preg_replace("/ \+/", " and ", $text);
    $text = preg_replace("/ -/", " not ", $text);
    return $text;
}


function bq_explode_respect_quotes($line) {
        $quote_level = 0;   #keep track if we are in or out of quote-space
        $buffer = "";

        for ($a = 0; $a < strlen($line); $a++) {
                if ($line[$a] == "\"") {
                        $quote_level++;
                        if ($quote_level == 2) { $quote_level = 0; }
                }
                else {
                        if ($line[$a] == " " and $quote_level == 0) {
                                $buffer = $buffer . "~~~~";   #Hackish magic key
                        }
                        else {
                                $buffer = $buffer . $line[$a];
                        }
                }

        }

    $buffer = str_replace("\\", "", $buffer);

        $array = explode("~~~~", $buffer);
        return $array;
}


function bq_make_subquery($fields, $word, $mode) {

    if ($mode == "not") {
        $back = " LIKE '%$word%'))";
    }
    else {
        $back = " LIKE '%$word%')";
    }

    if ($mode == "not") {
        $front = "(NOT (";
        $glue = " LIKE '%$word%' AND ";
    }
    else {
        $front = "(";
        $glue = " LIKE '%$word%' AND ";
    }

    $text = str_replace(" ", $glue, $fields);
    $text = $front . $text . $back;

    return $text;
}



function bq_make_query($fields, $text) {

    $text = strtolower($text);


    $text = bq_handle_shorthand($text);


    $wordarray = bq_explode_respect_quotes($text);

    $buffer = "";
    $output = "";

    for ($i = 0; $i<count($wordarray); $i++) {
        $word = $wordarray[$i];

        if ($word == "and" or $word == "not" and $i > 0) {
            if ($word == "not") {


                $i++;
                if ($i == 1) {   #invalid sql syntax to prefix the first check with and/or/not
                    $buffer = bq_make_subquery($fields, $wordarray[$i], "not");
                }
                else {
                    $buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "not");
                }
            }
            else {
                if ($word == "and") {
                    $i++;
                    if ($i == 1) {
                        $buffer = bq_make_subquery($fields, $wordarray[$i], "");
                    }
                    else {

                        $buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "");
                    }
                }
                else {
                    if ($word == "and") {
                        $i++;
                        if ($i == 1) {
                            $buffer = bq_make_subquery($fields, $wordarray[$i], "");
                        }
                        else {

                            $buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "");
                        }
                    }
                }
            }
        }
        else {
            if ($i == 0) {  # 0 instead of 1 here because there was no conditional word to skip and no $i++;
                $buffer = bq_make_subquery($fields, $wordarray[$i], "");
            }
            else {
                $buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "");
            }
        }
        $output = $output . $buffer;
    }
    return $output;
}



function bq_simple ($return_fields, $tables, $check_fields, $query_text) {


    $return_fields = str_replace(" ", ", ", $return_fields);
    $tables = str_replace(" ", ", ", $tables);


    $query = "SELECT $return_fields FROM $tables WHERE ";
    $query = $query . bq_make_query($check_fields, $query_text);

    #
    # Uncomment to debug
    #

    return $query;
}



?>

Open in new window

Klaus AndersenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
Lines 77-83 look a little off. You are setting $results to a query but then never using it. You're looping through the results of the previous query, but creating a table row that looks something like:

<tr><a href="mysite.com/id,">Name</a></tr>

...which is clearly wrong.

I don't see how the code you've posted could generate the HTML you've posted!
0
Terry WoodsIT GuruCommented:
Have a look at the generated HTML tags and see whether you've got an <a href...> tag without a closing tag being found, or whether each table cell is being enclosed in such tags. That will help narrow down the problem.
0
Klaus AndersenAuthor Commented:
I've requested that this question be deleted for the following reason:

People didnt come with a real solution
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Chris StanyonWebDevCommented:
Seriously? If you ask a question on EE you should at least engage the experts. Did you really expect us to click our fingers and magically give you a complete answer based solely on the code and image you provided?
0
Julian HansenCommented:
while ($row = mysql_fetch_assoc ($result)) {
        echo "<TR>\n";

        echo '<a href="mysite.com/' . $row['id'] . ',">' . $row['name'] . '</a>';
        echo "</TR>\n";
    }

Open in new window

The above code does not make sense to me in terms of the image posted - there are no <td></td> for the columns.

Is this code complete - do you have a link to the site (not an image)?
0
Klaus AndersenAuthor Commented:
Here is the new code I have:

<?php


// DB Connection //
/* Change the following 3 values to reflect your MySQL Server */
	$MySQLPassword = "****";	
	$HostName = "***";	
	$UserName = "***";
	$Database = "****";

	mysql_connect($HostName,$UserName,$MySQLPassword)
	or die("ERROR: Could not connect to database!");
 	mysql_select_db($Database) or die("cannot select db");

// Data Sorting Variables //
/* The following are used to set the columns for data sorting                */
/* Change these to the table field names in your MySQL database              */
	$default_sort = 'ID';
	$allowed_order = array ('name','description');
	

/* if order is not set, or it is not in the allowed
 * list, then set it to a default value. Otherwise, 
 * set it to what was passed in. */
	if (!isset ($_GET['order']) || 
	    !in_array ($_GET['order'], $allowed_order)) {
	    $order = $default_sort;
	} else {
	    $order = $_GET['order'];
	}

// The following line stops the undefined index error on initial page load
	if (isset($_GET['keyword'])) {

	        if(!$_GET['keyword']) {
	          die('<p>Please enter a search term.</p>');
	  	}     




/////////////////////////HERE IS THE BEGINING OF CODE WHERE I THINK SHOULD BE THE PROBLEM ////////////////////////////
	$tables = 'reports';
	$return_fields = 'name organizer_id no_pages publication_date price';
	$check_fields = 'name description';

 	$query_text = $_GET['keyword'];

	$clean_query_text =cleanQuery($query_text);
	
	$newquery=bq_simple ($return_fields, $tables, $check_fields, $clean_query_text);
	$newquery = $newquery . " ORDER BY $order;";


	$result = mysql_query($newquery) or die(mysql_error());


	$numrows = mysql_num_rows($result);
	if ($numrows == 0) {
	    echo "<H4>No data to display!</H4>";
	    exit;
	}
	echo 	"<p>Your search '$query_text' returned ".$numrows. " results.</p>\n";
	echo 	"<p>Click on the headings to sort.</p>\n";


	$row = mysql_fetch_assoc ($result);
	echo "<TABLE border=1>\n";
	echo "<TR>\n";
	foreach ($row as $heading=>$column) {

	    echo "<TD><b>";
	    if (in_array ($heading, $allowed_order)) {
	        echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading&keyword=$query_text\">$heading</a>";
	    } else {
	        echo $heading;
	    }                
	    echo "</b></TD>\n";
	}
	echo "</TR>\n";

	mysql_data_seek ($result, 0);
	while ($row = mysql_fetch_assoc ($result)) {
	    echo "<TR>\n";
	    foreach ($row as $column) {
     	  echo "<TD><a href='http://mysite.com/(id)(name),'>$column</TD>\n";
	    }
	    echo "</TR>\n";
	}
	echo "</TABLE>\n";
	}
	
/////////////////////////END OF THE CODE  ////////////////////////////



	function cleanQuery($string)
	{
	  $string = trim($string);
	  $string = strip_tags($string); // remove any html/javascript.

	  if(get_magic_quotes_gpc())  // prevents duplicate backslashes
	  {
	    $string = stripslashes($string);
	  }
	  if (phpversion() >= '4.3.0')
	  {
	    $string = mysql_real_escape_string($string);
	  }
	  else
	  {
	    $string = mysql_escape_string($string);
	  }
	  return $string;
	}


function bq_handle_shorthand($text) {
	$text = preg_replace("/ \+/", " and ", $text);
	$text = preg_replace("/ -/", " not ", $text);
	return $text;
}


function bq_explode_respect_quotes($line) {
        $quote_level = 0;	#keep track if we are in or out of quote-space
        $buffer = "";

        for ($a = 0; $a < strlen($line); $a++) {
                if ($line[$a] == "\"") {
                        $quote_level++;
                        if ($quote_level == 2) { $quote_level = 0; }
                }
                else {
                        if ($line[$a] == " " and $quote_level == 0) {
                                $buffer = $buffer . "~~~~";   #Hackish magic key
                        }
                        else {
                                $buffer = $buffer . $line[$a];
                        }
                }

        }

	$buffer = str_replace("\\", "", $buffer);

        $array = explode("~~~~", $buffer);
        return $array;
}


function bq_make_subquery($fields, $word, $mode) {

	if ($mode == "not") {
		$back = " LIKE '%$word%'))";
	}
	else {
		$back = " LIKE '%$word%')";
	}

	if ($mode == "not") {
		$front = "(NOT (";
		$glue = " LIKE '%$word%' AND ";
	}
	else {
		$front = "(";
		$glue = " LIKE '%$word%' AND ";
	}

	$text = str_replace(" ", $glue, $fields);
	$text = $front . $text . $back;

	return $text;
}



function bq_make_query($fields, $text) {
	#
	# We can't trust the user to give us a specific case
	#
	#$fields = strtolower($fields);
	$text = strtolower($text);

	#
	# Support +keyword -keyword
	#
	$text = bq_handle_shorthand($text);

	#
	# Split, but respect quotation
	#
	$wordarray = bq_explode_respect_quotes($text);

	$buffer = "";
	$output = "";


	for ($i = 0; $i<count($wordarray); $i++) {
		$word = $wordarray[$i];

		if ($word == "and" or $word == "not" and $i > 0) {
			if ($word == "not") {
				#
				# $i++ kicks us to the actual keyword that the 'not' is working against, etc
				#
				$i++;
				if ($i == 1) {   #invalid sql syntax to prefix the first check with and/or/not
					$buffer = bq_make_subquery($fields, $wordarray[$i], "not");
				}
				else {
					$buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "not");
				}
			}
			else {
				if ($word == "and") {
					$i++;
					if ($i == 1) {
						$buffer = bq_make_subquery($fields, $wordarray[$i], "");
					}
					else {

						$buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "");
					}
				}
				else {
					if ($word == "and") {
						$i++;
						if ($i == 1) {
							$buffer = bq_make_subquery($fields, $wordarray[$i], "");
						}
						else {

							$buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "");
						}
					}
				}
			}
		}
		else {
			if ($i == 0) {  # 0 instead of 1 here because there was no conditional word to skip and no $i++;
				$buffer = bq_make_subquery($fields, $wordarray[$i], "");
			}
			else {
				$buffer = " AND " . bq_make_subquery($fields, $wordarray[$i], "");
			}
		}
		$output = $output . $buffer;
	}
	return $output;
}



function bq_simple ($return_fields, $tables, $check_fields, $query_text) {

	#
	# Convert from space deliniated to comma deliniated for the query
	#
	$return_fields = str_replace(" ", ", ", $return_fields);
	$tables = str_replace(" ", ", ", $tables);

	#
	# build the query itself
	#
	$query = "SELECT $return_fields FROM $tables WHERE ";
	$query = $query . bq_make_query($check_fields, $query_text);

	#
	# Uncomment to debug
	#

	return $query;
}
		
?>
      </div>
   </body>
</html>

Open in new window

0
Chris StanyonWebDevCommented:
You really should just ask one question on a given topic. If something's not clear, then engage the experts - don't just try and delete it and ask the same question again...

Normally, you'd do something like this:

while ($row = mysql_fetch_assoc ($result)) {
   echo "<tr>\n";
   printf("<td><a href='http://mysite.com/(%s),(%s)'>%s</a></td>", $row['organizer_id'], $row['name'], $row['name']);
   printf("<td>%s</td>", $row['organizer_id']);
   printf("<td>%s</td>", $row['no_pages']);
   printf("<td>%s</td>", $row['publication_date']);
   printf("<td>%s</td>", $row['price']);
   echo "</tr>\n";
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
Please see the comment at this URL.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28237458.html#a39486475

Please also see this article which explains why you're setting yourself up for a data base conversion if you use MySQL.  You do not want a data base conversion; you want to use one of the more fully supported and future-proof data base extensions.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
Klaus AndersenAuthor Commented:
Thanks @SouthMod for the explanation. That´s totally understandable.

Please excuse me for that issue that for sure will not happen anymore. I´m here to learn and get advices from the experts.

Have a nice day!

PJ
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.