need help with mysql quiry in php

jasondavis


select auto_id from friend_mail_details where auto_id < 1556092 and to_user="1" order by auto_id DESC LIMIT 1

SELECT auto_id FROM friend_mail_setting WHERE mail_id ="1555663" AND user_id="1" AND inbox="Yes" LIMIT 1

I need to combine these into 1 quiry and basicly assign the auto_id from both tables into 2 variables, I would greatly appreciate some help with this,

the problem with the above 2 running seperatley is I use this to get the 2 auto_ids to build a link to the next available message for a user, in the above the result from friend_mail_details is used as the value for mail_id when searching friend_mail_setting table, so in the above 1556092 is actually marked as deleted so it does not return the next available value

jasondavis

ok I got it now!

Just a question my current query returns the whole row of both tables and I only need 1 row from each table, is there a way to get just the auto_id from both tables

my new code


//fetch next mail message link
$sqlnext = 'SELECT * FROM friend_mail_details AS a, friend_mail_setting AS b WHERE a.auto_id = b.mail_id
AND a.auto_id < ' .$id. '
AND a.to_user = "' .$_SESSION['auto_id']. '"
AND b.inbox = "Yes"
ORDER BY a.auto_id DESC
LIMIT 1 ';
$result_photo = executeQuery($sqlnext);
if ($line_photo = mysql_fetch_array($result_photo)) {
	$nextlink = '<a href="?p=mail.read&id=' .$line_photo['0']. '&auto_id=' .$line_photo['8']. '" class="bluelinkbold">Next >></a>';
}else{
	//no next message found so disable next mail link
	$nextlink = '<s>Next >></s>';
}

BlueKai

You don't need to use aliasing for this - do a join. '' selects every column from the table. If you want to select just a few, list what you want instead of using ''.

select friend_mail_details.auto_id as details_id,friend_mail_setting.auto_id as setting_id
from friend_mail_details left join friend_mail_setting on friend_mail_details.auto_id=friend_mail_setting.mail_id
where friend_mail_details.auto_id<{$id} and friend_mail_details.to_user={$_SESSION['auto_id']} and friend_mail_setting.inbox='Yes'
order by friend_mail_details.auto_id desc limit 1

jasondavis

//my query I cam up with
SELECT * FROM friend_mail_details AS a, friend_mail_setting AS b WHERE a.auto_id = b.mail_id AND a.auto_id < 1563682 AND a.to_user = "1" AND b.inbox = "Yes" ORDER BY a.auto_id DESC LIMIT 1






//the one the girl above me provided
select friend_mail_details.auto_id as details_id,friend_mail_setting.auto_id as setting_id from friend_mail_details left join friend_mail_setting on friend_mail_details.auto_id=friend_mail_setting.mail_id where friend_mail_details.auto_id<1563682 and friend_mail_details.to_user="1" and friend_mail_setting.inbox="Yes" order by friend_mail_details.auto_id desc limit 1

So both of these seem to execute in around .0035 seconds when I run them, about the same speed but your query only returns the 2 values I need so I will use it instead, thank you very much for the help =)

bogu

Dont forget to mark this thread as RESOLVED ....