Show text instead of row data - PHP /MYSQL

I have a search engine connected to a database that gives the results in a table.

How the table looks

table
The column organizer_id contains a number used as reference. Instead, should be the organizer name.

Ex:

1280132044 should be Corporate Inc

In pseudo code I think should be like this:

If organizer_id is equal to (nÂș xxxxxxxxxx) THEN change it to (name of the organizer)

Can you give me a hand how to make this? I'm new in PHP/MYSQL




// DB Connection //

	$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','publication_date', 'price');
	

/* 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>');
	  	}     

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

// Get the keyword from the search form.
 	$query_text = $_GET['keyword'];


// Sanitize Data Input
	$clean_query_text =cleanQuery($query_text);


// Call the bq_simple function and store the //
// resulting SQL Query in $newquery variable //
	$newquery=bq_simple ($return_fields, $tables, $check_fields, $clean_query_text);
	$newquery = $newquery . " ORDER BY $order;";




// sql data query construction //
	$result = mysql_query($newquery) or die(mysql_error());

/* make sure data was retrieved */
	$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";


/* now grab the first row and start the table */
	$row = mysql_fetch_assoc ($result);
	echo "<TABLE border=1>\n";
	
	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";

/* reset the $result set back to the first row and 
 * display the data */
	
	mysql_data_seek ($result, 0);
	while ($row = mysql_fetch_assoc ($result)) {
   echo "<tr>\n";

   printf("<td><a href='http://embs-group.com/%s,%s'>%s</a></td>", $row['id'], str_replace(" ", "_", $row['name']), $row['name']);
   
   	$nombres_publisher = "SELECT t.nazwa AS nazwa FROM baza_obiektow_inne as t WHERE t.id=u.organizer_id";
	
   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']);
   printf("<td>%s</td>", $row['currency']);
   echo "</tr>\n";
}
	echo "</TABLE>\n";
	}

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:
Where do you plan on getting the organiser name from?
0
Klaus AndersenAuthor Commented:
Hi Chris,

I thought I could create the replacement in the same php file.  
In a table called baza_obiektow_inne I have the  column id for the id of the organiser, and a column nazwa for the name of the organiser.

Should I start declaring the new table here? $tables = 'reports';

I imagine that after I should make a query to get the info from the new table, but I dont know how to make id=nazwa
0
Chris StanyonWebDevCommented:
OK. You could run a new query for each search result within your loop:

while ($row = mysql_fetch_assoc ($result)) {
   //get the publisher name
   $organizerSql = mysql_query(SELECT nazwa FROM baza_obiektow_inne WHERE id=" . $row['organizer_id'] . " LIMIT 1");
   $organizer = mysql_fetch_assoc($organizerSql);

   echo "<tr>\n";
   printf("<td><a href='http://embs-group.com/%s,%s'>%s</a></td>", $row['id'], str_replace(" ", "_", $row['name']), $row['name']);
   printf("<td>%s</td>", $organizer['nazwa']);
   printf("<td>%s</td>", $row['no_pages']);
   printf("<td>%s</td>", $row['publication_date']);
   printf("<td>%s</td>", $row['price']);
   printf("<td>%s</td>", $row['currency']);
   echo "</tr>\n";
}

Open in new window

An alternative would be to use a SQL Join Query to grab the records from the database in the first place. Your code is using some arbitrary functions to query your database, so this wouldn't be the easiest option.

As a heads up, the code you are using is deprecated (out of date). The mysql library has been replaced and you should be switching to PDO or mysqli.
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Klaus AndersenAuthor Commented:
@Chris,

So I changed  the while for your new script, but I get an error:

This is what I have:

	
	mysql_data_seek ($result, 0);
	while ($row = mysql_fetch_assoc ($result)) {
   //get the publisher name
   $organizerSql = mysql_query(SELECT nazwa FROM baza_obiektow_inne WHERE id=" . $row['organizer_id'] . " LIMIT 1");
   $organizer = mysql_fetch_assoc($organizerSql);

   echo "<tr>\n";
   printf("<td><a href='http://embs-group.com/%s,%s'>%s</a></td>", $row['id'], str_replace(" ", "_", $row['name']), $row['name']);
   printf("<td>%s</td>", $organizer['nazwa']);
   printf("<td>%s</td>", $row['no_pages']);
   printf("<td>%s</td>", $row['publication_date']);
   printf("<td>%s</td>", $row['price']);
   printf("<td>%s</td>", $row['currency']);
   echo "</tr>\n";
}

Open in new window


Seems to be a problem with this line
 $organizerSql = mysql_query(SELECT nazwa FROM baza_obiektow_inne WHERE id=" . $row['organizer_id'] . " LIMIT 1");

Open in new window


I also included the new table in the $ tables:

$tables = 'reports baza_obiektow_inne';

Open in new window


I know the code is deprecated. But I plan to make the system works in this way, and then to "update" the fuctions. May be is a longer process, but I see as a way to learn.
0
Chris StanyonWebDevCommented:
No need to change the $tables variable - we're by-passing that completely. The line you're getting a error on is because you have a missing opening quote. It should be:

$organizerSql = mysql_query("SELECT nazwa FROM baza_obiektow_inne WHERE id=" . $row['organizer_id'] . " LIMIT 1");

Open in new window

0
Klaus AndersenAuthor Commented:
It works excellent! Thanks @Chris!
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.