PHP/MYSQL Publish Joined Table Results

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:
---------------------------------------------------------------
phpclass_ad
(contains the 'ad_id', 'ad_cat_id' and short 'ad_description')
---------------------------------------------------------------
phpclass_selected
(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
Description
Title
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
Description
Title
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.
bizbuyer-dump.txt
MariaCMAsked:
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.

adrpoCommented:

Try to follow a PHP+MySQL tutorial:
http://www.freewebmasterhelp.com/tutorials/phpmysql/4

Cheers,
za-k/
0
ArikaelCommented:
$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
 //$data['fieldname']
}

----------------------------------
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
0
adrpoCommented:

You could go like this:

<?php
// 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;
    die($message);
}
// 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
mysql_free_result($result);
?> 

Open in new window

0
v2MediaCommented:
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:
<?php
//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">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Categorized Resultset</title>
</head>
<body>
<table width="550" border="0" cellspacing="1" cellpadding="3">
<?php
 
	//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){
?>
  <tr>
    <td colspan="4">Ad Category <?php echo $this_cat ?></td>
  </tr>
  <tr>
    <td>Id</td>
    <td>Title</td>
    <td>Description</td>
    <td>Selection</td>
  </tr>
<?php
	//end cat row
	}
?>
  <tr>
    <td><?php echo $ad_id ?></td>
    <td><?php echo $ad_title ?></td>
    <td><?php echo $ad_desc ?></td>
<?php
	//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>
<?php
	//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>
<?php
	//copy this cat to next cat
	$next_cat = $this_cat;
	//end loop
	} while($opt_row = mysql_fetch_assoc($opt_qry));
?>
    </select></td>
  </tr>
<?php
	//end loop
	} while($row = mysql_fetch_assoc($qry));
?>
</table>
</body>
</html>

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
MariaCMAuthor Commented:
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!
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
MySQL Server

From novice to tech pro — start learning today.