php mysql query based on previous query?

[andy_jacko]

Tweet

I have a 2 databases one containing information, one containing photos.

I have all the information appearing on screen each record in a seperate div,


what i need is to access the photos database and grab 1 photo for each information set.

the photos are all tagged with the sime id the information is?

any ideas if this can be done,

regards andy

p.s. sorry if its not explained well

[morleymouse]

explained terribly

might be way off the mark but something like...

$getinfo= mysql_query("SELECT * FROM information") or die("Database Error"  . mysql_error());
while ($data_info= mysql_fetch_array($getinfo, MYSQL_ASSOC)) {

// here we can call records using $data_info[field-name];

// presuming you have a ID field in your 'information' database...
$ID_for_image = $data_info[ID];
$getimage= mysql_query("SELECT * FROM images WHERE id = $ID_for_image") or die("Database Error"  . mysql_error());
while ($data_img= mysql_fetch_array($getimage, MYSQL_ASSOC)) {

// here we can call records using $data_img[field-name];

}


// here we can call records using $data_info[field-name];  (still)

} 

[simhd]

You can also do this by joining the 2 tables together:

$sql = "select database1.tablename.*,database2.tablename.image from database1.tablename inner join database2.tablename using (id)";

$rowset = mysql_query($sql);
while($row = mysql_fetch_array($rowset))
{
    echo $row['information'];
                echo $row['image '];
} 

This query is joining 2 tables from 2 separate databases - if your tables are in the same database you can just remove the database names from the SQL command. I wasn't sure from your post whether you if your images were in a separate database or a separate table.

This query will show 1 photo per piece of information.

Simon