asked on

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

Adrian Pop

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

Cheers,
za-k/

Arikael

$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

Adrian Pop

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

ASKER CERTIFIED SOLUTION

v2Media

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MariaCM

ASKER

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!