Avatar of Klaus Andersen
Klaus AndersenFlag for Germany asked on

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

PHPMySQL ServerWeb Development

Avatar of undefined
Last Comment
Klaus Andersen

8/22/2022 - Mon
Chris Stanyon

Where do you plan on getting the organiser name from?
ASKER
Klaus Andersen

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
ASKER CERTIFIED SOLUTION
Chris Stanyon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ray Paseur

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
Klaus Andersen

@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.
Chris Stanyon

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

ASKER
Klaus Andersen

It works excellent! Thanks @Chris!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.