2 Recordsets for 1 output

[ldtiw]

I have information in two tables.

I want to use gallery_id within the main table im using to print all records found and need to get the gallery_title using the gallery_id.

I kinda got it to work but when i added the table tags it didnt work any more ive started fresh and gone back down to one (gallery_title resides in the table gallery_categories):

$conn_gal = mysql_connect("$hostname", "$username", "$password");
mysql_select_db("$database", $conn_gal);
$sql_gal= "select * FROM gallery;
$result_gal = mysql_query($sql_gal, $conn_gal);

And the while code to print out all rows:

<table>
<?php 
while ($newarray = mysql_fetch_array($result_gal)) {
$title = $newarray['image_title'];
$cat = $newarray['gallery_id'];
$sold = $newarray['sold'];
$gal = $title = $newarray['gallery_title'];
echo "<tr><td>$title</td><td>$gal</td><td>$sold</td></tr>";
}}
?>
</table>

Any help would be great

Danny

[travelbuff]

You need either a join (http://www.devshed.com/Server_Side/MySQL/) or a union (http://www.mysql.com/doc/en/UNION.html), or you can just use a conditional multi-select:

'SELECT * FROM gallery_categories, gallery WHERE gallery.category_id = gallery_categories.category_id' <- returns one row for each record where the cat id's match in both tables.

One suggestion - play with your select statement in phpmyadmin or by using a plain return in your script before you try to manipulate the data in any way. That way you can see the raw output of the statement and make sure it is what you want. I have on app where my fully normalized DB requires me to retrieve data from 5 or 6 different tables in a single select pretty routinely - and I have learned that testing the raw output of the statements before trying to put them to use can save me a lot of rolaids 🙂

Hope that points you in the right direction.

[ldtiw]

Ok thanks, but im getting an error now and nothing will print!

Im using the edited code you showed me:

$conn_gal = mysql_connect("$hostname", "$username", "$password");
mysql_select_db("$database", $conn_gal);
$sql_gal= "SELECT * FROM gallery_categories, gallery WHERE gallery.category_id = gallery_categories.category_id";
$result_gal = mysql_query($sql_gal, $conn_gal);

The error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\hshome\ldtiw200\myartcentre.com\admin_includes\edit.php on line 94

And output code:

This would be line 94->while ($array_gal = mysql_fetch_array($result_gal)) {
$title = $array_gal['image_title'];
$cat = $array_gal['gallery_id'];
$sold = $array_gal['sold'];
$gal = $array_gal['gallery_title'];
echo "<tr><td>$title</td><td>$gal</td><td>$sold</td></tr>";
}

Thanks,
Danny

[travelbuff]

Then that probably means no record sets were returned.

Did you try the select in phpmyadmin or do a raw output of the select as I suggested?

Please post the structures of both tables.

[ldtiw]

Im still kinda new to PHP and Mysql. Although i have a basic understanding of what things do. What your asking me to do in Mysql admin i wouldnt have an idea!lol

Ok the tables look like this:

gallery

image_id | gallery_id | image_title | image_description | image_main | image_thumb | sold |
---------+------------+-------------+-------------------+------------+-------------+------+

gallery_categories

gallery_id | gallery_title | gallery_description |
----------+------------+--------------------+

Im thinking though, i may create a new table with the images and call that via the id. just make it tidyer!

Thanks,
Danny

[travelbuff]

If you are on a commercial webhost you probably have phpmyadmin installed. Ask your host about it - it is a great tool. More info can be found at http://phpwizard.net/projects/phpMyAdmin/ . If you are running on Windows, you can test your queries from the mysql monitor.

When I suggested doing a return on the select, I meant adding the line:

return($result_gal);

after the query. This will return the raw output ofthe query. It is ugly, but will give you an idea of what your data will look like. More info can be found here: http://us3.php.net/manual/en/function.return.php

The syntax you posted seems correct. This may seem a little silly, but are there data rows in those tables that have matching category id's?

Change your query to:

$result_gal = mysql_query($sql_gal, $conn_gal) OR DIE (could not select gallery . mysql_error())  ;

Add the following line below the query:

$num_rows = mysql_num_rows($result_gal);
  echo "$num_rows were returned";

These two things will tell us whether the query is invalid and therefor not being executed or whether the query is running OK and there are just no results to return.

[tekky]

on an offtopic (slightly)

when using variables in function calls as parameters...

ie:

$conn_gal = mysql_connect("$hostname", "$username", "$password"); 

//enclosing the $vars in " " slows down PHP because it has to run "$username" through its string parser to expand variables

$conn_gal = mysql_connect($hostname, $username, $password); 

//this will get you the same affect but PHP will ONLY have to replace the vars, not attempt to parse strings

$var = 'on the same note... if you are only including TEXT in a string... use single quotes, not double quotes... this';

//this will also negate the string parsing that PHP will try on "strings" as strings enclosed in single quotes are considered static variables

hope this explains some variable related things 😉