code for display of SQL query

[Anon]

I am asking for help on the PHP scripting needed to display the results of an SQL query to a MySQL database. I can manage the basic display by row in the result array using a 'while' statement but I cannot manage a particular display requirement.

I have two tables : widgets and images
Widgets table has columns for 'widget_ID', 'description', 'price' and
Images table has columns for 'image_ID', 'widget_ID', 'filename', 'size' (for the moment size is either "regular" or "thumbnail")

The SQL query I have written is :

"SELECT widgets.widget_ID, widgets.description, widgets.price, images.filename, image_ID, size FROM widgets, images WHERE widgets.widget_ID = images.widget_ID ORDER BY widgets.price ASC"

and this query produces the correct results. But because the relationship of the widgets table to the images table is 'one to many', I get multiple lines per widget_ID. (each line is distinct)

I want to create an HTML display in TABLE form with two cells per line (this is no problem) but I want only one cell per Widget_ID and I need this cell to contain all the filename references for the images applying to that widget_ID ( I don't want only one image filename per cell nor do I want multiple cells per widget_ID)

I have tried splitting the join SQL query above into two separate queries : one for the widget table and one for the images table but then I cannot get the PHP code right for creating the display I want.

Any ideas please ?

[spaceboy]

Well the first thing you could do is modify your SQL query to match this:

--- CUT HERE ---
SELECT widgets.widget_ID, widgets.description, widgets.price, images.filename, image_ID, size FROM widgets, images WHERE widgets.widget_ID = images.widget_ID ORDER BY widgets.price,widgets.widget_ID ASC
--- CUT HERE ---

Notice I have added another clause to the ORDER BY section of the query (",widgets.widget_ID"). This is to cover any widgets that have the same price and make sure they all are grouped together.

Then for your PHP code you could do something like this ($results = your query results):

<?

print "<table border='1'>\n";

// read the first record (some error checking needs to be done here)
$row = mysql_fetch_array($results);
while ($row)
{
// print the first cell with widget information
print "<tr>\n";
print "<td valign='top'>\n";
print "Widget #$row[widget_ID] - \$$row[price]";
print "</td>\n";

    // start the second cell
    print "<td>\n";

    // save the current widget ID
    $currentID = $row[widget_ID];                

    print "<u>Images:</u><br>\n";

    // keep reading image information until a new widget ID is read
    while ($row[widget_ID] == $currentID)                  
    {
            // print image information
            print "$row[filename]<br>\n";            
            $row = mysql_fetch_array($results);
    }

    // its time to do a new widget, close the cell and row and save the new widget ID
    print "</td>\n";
    print "</tr>\n";

    $currentID = $row[widget_ID];

}

print "</table>\n";

?>

[minervois]

thanks for the prompt help - I've got it completed now