We help IT Professionals succeed at work.

PHP/MYSQL Publish Joined Table Results

MariaCM asked
I have a classifieds ads database and would like to publish the contents of this database on another site.

I have to join the contents of two tables:
(contains the 'ad_id', 'ad_cat_id' and short 'ad_description')
(contains added fields 'sel_option_text' linked to the 'ad_id')

The fields ad_id (within phpclass_ad)  and sel_option_ad (within phpclass_selected) correspond (suppose this is called a key field?)

Multiple recordsets from phpclass_selected match with a single phpclass_ad 'ad_id'

How do I publish the joined results?

Can someone help me with sample MYSQL and php?

I have publised items with mysql and php before, but am struggling with this one.

A sample result should look like follows:

ID 1
Add Catagory 1
BFS Telephone       
BFS Fixed Assets (ZAR)
BFS Nett Asset Value (ZAR)
BFS Sale Price (ZAR)       
BFS Cellular
BFS Location       
BFS Industry
BFS Turnover (ZAR)       
BFS Nett Profit (ZAR)

ID 5
Add Catagory 1
BFS1 Telephone       
BFS1 Fixed Assets (ZAR)
BFS1 Nett Asset Value (ZAR)
BFS1 Sale Price (ZAR)       
BFS1 Cellular
BFS1 Location       
BFS1 Industry
BFS1 Turnover (ZAR)       
BFS1 Nett Profit (ZAR)

and iterate through the results, I need to return results by catagory as well.

I need an example familiarise myself with the syntax of the mysql query and php publishing code.
Watch Question


Try to follow a PHP+MySQL tutorial:


$query = 'SELECT * FROM phpclass_ad AS ad INNER JOIN phpclass_selected AS selected ON ad.ad_id = selected.sel_option_ad ORDER B selected.sel_option_ad';

$rs = mysql_query($query);

while($data = mysql_fetch_row($rs, MYSQL_ASSOC)) {

  //echo your stuff
  //access a field like

You shouldn't use the * in your query like I did ;-)
and if you have fields in the tables which have the same name, you have to give them aliases.

Hope I could help


You could go like this:

// Formulate Query
$query = "SELECT * FROM phpclass_ad order by ad_id";
// Perform Query
$result = mysql_query($query);
// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
// Use result
// Attempting to print $result won't allow access to information in the resource
// One of the mysql result functions must be used
// See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.
echo "<html><title>Bla</title><body>";
while ($row = mysql_fetch_assoc($result)) 
echo "<b>" . $row['ad_id'] . "</b><br>"; // echo ad_id
echo "<b>" . $row['ad_owner'] . "</b><br>"; 
echo "<b>" . $row['ad_cat_id'] . "</b><br>"; 
// you continue here
echo "<hr><br>";
echo "</body></html>";
// Free the resources associated with the result set
// This is done automatically at the end of the script

Open in new window

I'm not sure a join is the best way to go about printing the desired results. Too much redundant info in the results. I'd take a different approach with a secondary query to phpclass_selected while looping out the ads:
//conn to db
$db_host = 'localhost';
$db_name = 'phpclass';
$db_user = 'userName';
$db_pass = 'password';
$db_conn = mysql_pconnect($db_host, $db_user, $db_pass) or trigger_error(mysql_error(),E_USER_ERROR);
$db = mysql_select_db ( $db_name, $db_conn );
//resultset must be ordered by the category, which groups
//these results, first.
$sql = "SELECT ad_id, ad_title, ad_description, ad_cat_id 
		FROM phpclass_ad
		ORDER BY ad_cat_id ASC, ad_id ASC";
$qry = mysql_query($sql) or trigger_error ( mysql_error());
$row = mysql_fetch_assoc($qry);
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Categorized Resultset</title>
<table width="550" border="0" cellspacing="1" cellpadding="3">
	//loop through results, categorized by ad_cat_id
	//vars $this_cat and $next_cat are used to control
	//the output of category rows
	$next_cat = '';
	do {
	$this_cat = $row['ad_cat_id'];
	$ad_id = $row['ad_id'];
	$ad_title = $row['ad_title'];
	$ad_desc = $row['ad_description'];
	//output this cat row if it's different to the cat
	//of the previous iteration of loop
	if($this_cat != $next_cat){
    <td colspan="4">Ad Category <?php echo $this_cat ?></td>
	//end cat row
    <td><?php echo $ad_id ?></td>
    <td><?php echo $ad_title ?></td>
    <td><?php echo $ad_desc ?></td>
	//query for this ad_id select options
	//in phpclass_selected
	$opt_sql = "SELECT sel_option_field, sel_option_text
				FROM phpclass_selected 
				WHERE sel_option_ad = $ad_id";
	$opt_qry = mysql_query($opt_sql) or trigger_error ( mysql_error());
	$opt_row = mysql_fetch_assoc($opt_qry);
    <td><select name="ad_select">
      <option selected="selected">Select...</option>
	//loop out select options
	do {
	$val = $opt_row['sel_option_field'];
	$text = $opt_row['sel_option_text'];
	<option value="<?php echo $val ?>"><?php echo $text ?></option>
	//copy this cat to next cat
	$next_cat = $this_cat;
	//end loop
	} while($opt_row = mysql_fetch_assoc($opt_qry));
	//end loop
	} while($row = mysql_fetch_assoc($qry));

Open in new window


Thanks for this, very good code, clean and simple.  I'm not to familiar with coding in php, but am definitely going to use his example to further my skill.
Thanks a mill!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.