[RESOLVED] Combine two queries

[mattm591]

I have some code written which first checks to see if there is any messages in the database to output and then if there are it outputs them.

I want to make it simpler so there is just the one query being used, but with my current method I have to use two because the second one is selecting the message based on a counter. Is there a way to get rid of the counter and display each of the messages.

They have to be shown in order though, so message 1 then 2 etc.

My current code is this:

//check for any messages avaliable for output

if (!$die) {

$q_getmsgs = "SELECT * FROM convs WHERE chatid = '$v_chatid'";

if (!@$qr_getmsgs = mysql_query($q_getmsgs,$dbconnect)) {

	$log .= (date("d/m/y H:i:s",time()) . ": " . mysql_error() . " | | on file: " . $_SERVER['PHP_SELF'] . "<br />");

	$die = true;

}

if (!$die) {

	$n_getmsgs = @mysql_num_rows($qr_getmsgs);

	@mysql_free_result($qr_getmsgs);

}

} else {

$n_getmsgs = 0;

}

//output messages

if (!$die) {

if ($n_getmsgs != 0) {

	$counter = 0;

	while ($counter < $n_getmsgs && !$die) {

		$counter++;

		$q_readmsg = "SELECT msg,sender,UNIX_TIMESTAMP(date) AS date FROM convs WHERE chatid = '$v_chatid' AND msgid = '$counter'";

		if (!@$qr_readmsg = mysql_query($q_readmsg,$dbconnect)) {

			$log .= (date("d/m/y H:i:s",time()) . ": " . mysql_error() . " | | on file: " . $_SERVER['PHP_SELF'] . "<br />");

			$die = true;

		}

		$var = @mysql_fetch_assoc($qr_readmsg);

		@mysql_free_result($qr_readmsg );

		$output .= $var['sender'];

		$output .= ": ";

		$output .= $var['msg'];

		if ($counter != $n_getmsgs) {

			$output .= "\n";

		}

	}

}

}

Thanks 🙂

[dougal85]

Could you tun the queries together and use a UNION?

http://dev.mysql.com/doc/refman/5.0/en/union.html

[mattm591]

Thanks for the reply, that looks like a useful function for something else I'm doing.

I did however manage to solve this problem a different way. If anyone is interested in what I did here's my new code:

//check for any messages avaliable for output

if (!$die) {

$q_getmsgs = "SELECT msg,sender,UNIX_TIMESTAMP(date) AS date FROM convs WHERE chatid = '$v_chatid' ORDER BY msgid ASC";

if (!@$qr_getmsgs = mysql_query($q_getmsgs,$dbconnect)) {

	$log .= (date("d/m/y H:i:s",time()) . ": " . mysql_error() . " | | on file: " . $_SERVER['PHP_SELF'] . "<br />");

	$die = true;

}

if (!$die) {

	$n_getmsgs = @mysql_num_rows($qr_getmsgs);

}

} else {

$n_getmsgs = 0;

}

//output messages

if (!$die) {

if ($n_getmsgs != 0) {

	$counter = 0;

	while ($v_getmsgs = @mysql_fetch_assoc($qr_getmsgs)) {

		$counter++;

		$output .= $v_getmsgs['sender'];

		$output .= ": ";

		$output .= $v_getmsgs['msg'];

		if ($counter != $n_getmsgs) {

			$output .= "\n";

		} else {

			$v_lastdate = $v_getmsgs['date'];

		}

	}

	$v_lastmsg = date("d F Y H:i:s",$v_lastdate);

}

@mysql_free_result($qr_getmsgs);

}

[dougal85]

Good stuff.

Well mark the thread resolved if it is 🙂