[SOLVED] combining two queries.

[stelthius]

Hello guys after several hours of trying to combine two queries ive had to admit defeat, does anyone know of a way to combine these two queries so i can lessen the load on the pagea and server its self, iany help is greatly appretiated.

 

$result = mysql_query("SELECT unread FROM messages WHERE unread = 'unread' and receiver = '$session->username'");
$num_rows = mysql_num_rows($result);

$view_msg = mysql_query("SELECT received FROM messages WHERE received = '0' and receiver = '$session->username'");
$msg = mysql_num_rows($view_msg);

 

Thanks rick

[Maq]

Can you post the table structure of "unread" and "received"?

[stelthius]

Sorry if its messy im still learning

 

Field  	Type  	        Attributes  	                       Null  	Default
received  	enum('1', '0')  	latin1_swedish_ci  	       Yes  	0  	 	 
unread 	varchar(255) 	latin1_swedish_ci 	       No 	unread 	

[Maq]

Ooops, sorry I thought those were tables when "unread" and "received" were actually fields, my bad.  :-X

[Ken2k7]

I don't think you can interpolate complex PHP statements like that.

<?php
$result = mysql_query("SELECT unread FROM messages WHERE unread = 'unread' and receiver = '".$session->username."'");
$num_rows = mysql_num_rows($result);

$view_msg = mysql_query("SELECT received FROM messages WHERE received = '0' and receiver = '".$session->username."'");
$msg = mysql_num_rows($view_msg);

 

But combining them -

<?php
$sql = 'SELECT received, unread FROM messages WHERE received = "0" AND unread = "unread" AND receiver = "'.$session->username.'";';

[stelthius]

So its best to leave them as they are ? i was just trying to minimize the ammount of queries being used in that page but if i cant join them its all good

[stelthius]

so if i use

 

$sql = 'SELECT received, unread FROM messages WHERE received = "0" AND unread = "unread" AND receiver = "'.$session->username.'";';

 

how would i get the info i require from that as currently im using

 

$result = mysql_query("SELECT unread FROM messages WHERE unread = 'unread' and receiver = '$session->username'");
$num_rows = mysql_num_rows($result);

$view_msg = mysql_query("SELECT received FROM messages WHERE received = '0' and receiver = '$session->username'");
$msg = mysql_num_rows($view_msg);

$consoleOutput = "<font face='Verdana' color='#FFFFFF' size='2'>Welcome back <b>$session->username</b>,</font>
<a href='index.php?page=messages&option=inbox'>Private Messages</a> (Unread " .$msg. ", Total " .$num_rows. ")";

 

Sorry to drag this out as i said im learning so trying to cover all angles

[MadTechie]

While Maq works his magic, i thought i add an idea,

Add two fields to the users table called msg_unread and msg_recieved,

when the user sends a message just add to that field as well, and when the user views his messages run those queries and update the users fields

 

or maybe try (untested)

<?php
$sql = 'SELECT COUNT(received) as numreceived, COUNT(unread) as numunread FROM messages WHERE (received = "0" OR unread = "unread") AND receiver = "'.$session->username.'" GROUP BY receiver  ';
?>

 

[Ken2k7]

Uh... you really should really clarify what you need.

 

Try this -

<?php
$sql = 'SELECT (SELECT COUNT(received) FROM messages WHERE received = "0" AND receiver = "' . $session->username . '") AS received), (SELECT COUNT(unread) FROM messages WHERE unread = "unread" AND receiver = "' . $session->username . '") AS unread;';

[stelthius]

Sorry if i was unclear about it guys, but as i said im learning so i didnt really know how to explain what i was trying to achieve properly, or maybe i asked it the wrong way, but either way im very greatly for any help given.

[Ken2k7]

That SQL didn't work?

[stelthius]

Ok let me try and explain my problem,

 

with what you gave

 

$sql = 'SELECT (SELECT COUNT(received) FROM messages WHERE received = "0" AND receiver = "' . $session->username . '") AS received), (SELECT COUNT(unread) FROM messages WHERE unread = "unread" AND receiver = "' . $session->username . '") AS unread;';

 

Im curious how i would go about getting my results from that as right now im using,

 

$num_rows = mysql_num_rows($result);

 

To get the ammount of pm's the user has in the inbox total and im using,

 

$msg = mysql_num_rows($view_msg);

 

to show the user how many unread pms they have, so using what you showed me how do i get those two results ? sorry if im being confusing but im trying my best to explain something im unsure how to do

 

 

[MadTechie]

I understood the problem..

any luck with the suggestions so far stelthius?

[stelthius]

the queries you and Ken both gave work fine.. im just struggling to understand how to display the results from them, as the code im using now runs two queries to display the results im displaying,

[Ken2k7]

Uh you don't.

 

<?php
$sql = 'SELECT (SELECT COUNT(received) FROM messages WHERE received = "0" AND receiver = "' . $session->username . '") AS received), (SELECT COUNT(unread) FROM messages WHERE unread = "unread" AND receiver = "' . $session->username . '") AS unread;';

$results = mysql_query($sql);
echo $results['received'];

echo $results['unread'];

:-\

[MadTechie]

Try this Example

<?php
$sql = 'SELECT COUNT(received) as numreceived, COUNT(unread) as numunread FROM messages WHERE (received = "0" OR unread = "unread") AND receiver = "'.$session->username.'" GROUP BY receiver  ';

$result = mysql_query($sql);
$row = mysql_fetch_assoc($result)

$consoleOutput = "<font face='Verdana' color='#FFFFFF' size='2'>Welcome back <b>$session->username</b>,</font>
<a href='index.php?page=messages&option=inbox'>Private Messages</a> (Unread {$row['numunread']}, Total {$row['numreceived']})"; 
?>

 

stelthius You may want to try a performace test on both queries.. to see which one suite best,

 

[stelthius]

Ahhh thats what i couldnt work out as i said i was doing it differently so i was looking at it from the wrong view.. thanks for the patience.

[Ken2k7]

MadTechie, your SQL will return the same number for both COUNTs as both columns will have the same number of rows.

[MadTechie]

Ahh true, i was going to do a self join, but it occured to me a simpler and quicker route would be instead of running 2 or 3 selects or joins just add them up!

 

<?php
$sql = 'SELECT SUM( received ) AS numreceived, SUM( if(unread="unread",1,0 )) AS numunread FROM messages WHERE receiver = {$session->username}';

$result = mysql_query($sql);
$row = mysql_fetch_assoc($result)

$consoleOutput = "<font face='Verdana' color='#FFFFFF' size='2'>Welcome back <b>$session->username</b>,</font>
<a href='index.php?page=messages&option=inbox'>Private Messages</a> (Unread {$row['numunread']}, Total {$row['numreceived']})"; 
?>

 

EDIT oops left a limit on it LOL

[Maq]

That will add the values of each field, which I don't think the OP wanted.

 

@OP, to my knowledge you need two queries.  Is there a specific reason you want to combine them into 1, rather then consolidation?

[MadTechie]

This is true BUT the value is being converted into a 1 or 0 1=found 0= not found (kinda) if unread="unread" it get set to 1, which is the same as the record so if 500 records get set to 1 then thats 1x500 = 500

i assumes recieved was always one but you can do the same

if(received > 0,1,0 )

[Ken2k7]

Nice one MadTechie. Two things though, you should check if received is 0 (as said by the OP) and you should put quotes around the username just in case a space is allowed.

[Maq]

This is true BUT the value is being converted into a 1 or 0 1=found 0= not found (kinda) if unread="unread" it get set to 1, which is the same as the record so if 500 records get set to 1 then thats 1x500 = 500

i assumes recieved was always one but you can do the same

if(received > 0,1,0 )

 

Yes I see, excuse me.  It hould work, that's some nifty hackery Mad. 

[MadTechie]

Nice one MadTechie. Two things though, you should check if received is 0 (as said by the OP) and you should put quotes around the username just in case a space is allowed.

 

Oh BALLS

 

update:

$sql = "SELECT SUM( received ) AS numreceived, SUM( if(unread="unread",1,0 )) AS numunread FROM messages WHERE receiver = '{$session->username}' LIMIT 1";

 

 

@Maq

Yeah kinda pleased with that one

 

 

EDIT: i added Limit 1 as it should be okay.. but its 2:40am so i could be wrong!

[Ken2k7]

Wait, wouldn't SUM(received) be the opposite of what the OP wanted? The OP wants the number of received to be 0, not 1. If you SUM() up received, you'll just get the value of the number of received = 1, not 0.