PHP - MYSQL: Get ID from each row and include as part of a link

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 //////////////////  

<?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

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:
You already have this question open. Although you know seem to have posted the correct. You should delete this question and engage with the experts in the other one.

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28237373.html
0
Chris StanyonWebDevCommented:
Normally, you would 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
Klaus AndersenAuthor Commented:
Thanks Chris! This is quite close to what I need.

But in the construction of the link, Instead the organizer_id I need the id
I make the replacement but doesnt seems to be working.

Sorry for the double posting, I'm new here and I really need help to solve this problem
Cheers!
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:
No worries PJ. Live and learn :)

Where does the id come from. As far as I can tell, you have this:

      $return_fields = 'name organizer_id no_pages publication_date price';

so they are the only fields that are available. There is no ID field being returned from your query.
0
Klaus AndersenAuthor Commented:
tableChris,

id is the column from the table that has the unique number of each row.

I didnt put it in the $return_fields because in that way the information will appear in the search results.

All I need is to get the unique number to add it to the link in the names column

I'm attaching you a image of the table.

http://www.mysite.com/(id),(name of the report)  ///// (coma included)

Will be for example this:
http://www.mysite.com/129234,construction_in_germany

Thanks in advance!
0
Chris StanyonWebDevCommented:
If you need to use the ID in the output, then you need to include it in your query:

$return_fields = 'id name organizer_id no_pages publication_date price';

Open in new window

And then you can use:

printf("<td><a href='http://mysite.com/(%s),(%s)'>%s</a></td>", $row['id'], $row['name'], $row['name']);

Open in new window

0
Ray PaseurCommented:
The general term for this kind of work is "table maintenance" and it's one of the first things that PHP programmers have to learn.  You might want to get one of the books shown in this article.  They teach exactly how to do this, with good examples.  It's usually a chapter or two, so asking questions in a forum is kind of the long way of getting what should be a fairly succinct answer / lesson.  Don't go on Google and look for PHP code samples -- there are too many bad examples (including the one you copied here) and your chances of finding something good at random are very small.  If you stick to the learning resources identified in the article, you will be on much firmer ground, I promise!
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html
0
Klaus AndersenAuthor Commented:
Thanks you both guys @Chris. I included the id in the query and now the link works properly. The only one thing is that appears visible the id of each cell in the search results, but I think I can hide it with CSS. By the way, do you know how to replace the spaces between the words in the link, for this character _

@Ray, I'm gonna take your advice for sure. I'm really new in PHP/SQL but I'm keen on learning and be a good develpper. Your article will be included in my read for the weekend :)

Cheers!
0
Chris StanyonWebDevCommented:
Hey PJ. Not sure what you mean by the ID being visible in each cell. Can you explain what you mean.

As for replacing the space with an underscore:

str_replace(" ", "_", $row['name'])

Open in new window

So you would end up with:

printf("<td><a href='http://mysite.com/%s,%s'>%s</a></td>", $row['id'], str_replace(" ", "_", $row['name']), $row['name']);

Open in new window

0
Klaus AndersenAuthor Commented:
@Chris, Here is what I mean about the id on the results page
 tablePS:Space replacement code works perfect;)
0
Chris StanyonWebDevCommented:
OK.

The code I've posted wouldn't generate that. I think what's happening is that you are looping through the returned columns to automatically create the table headings (and possible the table data).

You could drop in an IF statement to exclude the ID column:

echo "<TR>\n";
   foreach ($row as $heading=>$column) {
      if ($heading != 'id') { //don't create a column for ID!
         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";

Open in new window

0
Klaus AndersenAuthor Commented:
@ Chris, You just turned into a kind of Guru for me :)  it works great!
The points are for you

Thanks for teaching and the patience!
0
Klaus AndersenAuthor Commented:
Chris recognized the problem in my code quickly and helped me to find a solution after many tries and searching from my side
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.