[Resolved] Data from 2 tables

scraff

Hello,

I have a problem i cant work out.
I want to select data from table (pictures) where some data = "m" or "f" in another...i will explain:

I have table (pictures) below is a simple structer of it:

ID Pic lastview

2 name2.jpg 38923749
2 name2b.jpg 38923634
3 name3.jpg 38765773

etc

which when someone visits a page it shows the picture that has the smallest time stamp (lastview)
That bit a have figured out

....BUT..... the ID relates the the table 'members' and the gender is either M or F.

I want to be able to get data where LASTVIEW is smallest AND where ID in MEMBERS=only M

This is what i have:

	$query = "SELECT * FROM pictures WHERE pictureurl != '' ORDER BY lastviewtimestamp LIMIT 1";

this gets the pic that hasnt been shown for the longest

WHAT I WANT IS TO DO THAT ABOVE BUT ALSO ONLY SELECT FROM 'PICTURES' THAT THE CORRISPONDING ID IN MEMBERS IS MALE.

How do i do this???

i dont want to use a loop to select the pic then check members cos this will take up too much CPU.

 
$query = "SELECT * FROM pictures WHERE pictureurl != '' ORDER BY lastviewtimestamp LIMIT 1"; 
$query_result_handle = mysql_query ($query) 
or die ('The query failed2!'); 
$row = mysql_fetch_row ($query_result_handle);
$id = $row[15];

Maybe:

 
$query = "SELECT * WHERE pictureurl != '' ORDER BY lastviewtimestamp FROM pictures LIMIT 1 AND SELECT * WHERE id = '$id' AND gender='M' FROM members"; 
$query_result_handle = mysql_query ($query) 
or die ('The query failed2!'); 
$row = mysql_fetch_row ($query_result_handle);

scraff

Or just maybe i could join the 2 tables together with primary keys (or foreign key) but i dont know how to do this.

thanks

LarsLai

you can join the two tables like this:

$query = 
"SELECT pictures.id, pictures.pic, pictures.whatyouwant 
FROM pictures 
INNER JOIN members 
ON pictures.id = members.id 
WHERE pictures.picture='value' AND members.gender='m' 
ORDER BY lastviewtimestamp LIMIT 1";

scraff

Thanks! thats worked.

Tried 4 ages tryin to get this! One more thing:

how do i then get the pic value from table 'pictures'?

$query_result_handle = mysql_query ($query) 
or die ('The query failed!'); 

$row = mysql_fetch_row ($query_result_handle);
echo $row[0];

what will row 0 display? and from which table? the row[0] displayin the pictures.id as in the query?

Thanks again!

LarsLai

hi!
if you have

$query =  

"SELECT pictures.id, pictures.pic, pictures.whatyouwant 
FROM pictures 
INNER JOIN members 
ON pictures.id = members.id 
WHERE pictures.picture='value' AND members.gender='m' 
ORDER BY lastviewtimestamp LIMIT 1";

$row = mysql_fetch_row ($query_result_handle); 
echo $row[0];
echo $row[1];

then $row is an array with all your results, as to say
$row[0] is pictures.id, $row[1] is pictures.pic and so on