[Resolved] PHP and Complex SQL help

ATLien

Hello, I am working on a private message script, and I am having some issues with a while statment on some SQL. Here is my code:

		$pminbox = mysql_query("SELECT a.pmid, a.pmtitle, a.sendid, a.date, b.username, c.username FROM pm AS a, users AS b, users AS c WHERE a.recieveid = '$userid' && a.folderid = '0' && b.id = a.recieveid && c.id = a.sendid");
		while ($pminbox = mysql_fetch_array($pminbox)){PHP] 

For some reason I am getting a error with the fetch array. Is this the correct way to do this or is there a better.

bradgrafelman

It's probably failing at your multiple "AS" statements. I'm not a SQL guru; but my bet would be that it's something about that.

Did you try echo'ing mysql_error() to get a little more info?

EDIT: Your PHP tags aren't typed correctly BTW. Here's the debug code you could use, too:

$pminbox = mysql_query("SELECT a.pmid, a.pmtitle, a.sendid, a.date, b.username, c.username FROM pm AS a, users AS b, users AS c WHERE a.recieveid = '$userid' && a.folderid = '0' && b.id = a.recieveid && c.id = a.sendid") or die('Error! MySQL said: ' . mysql_error() . '<br><br>Query: ' . "SELECT a.pmid, a.pmtitle, a.sendid, a.date, b.username, c.username FROM pm AS a, users AS b, users AS c WHERE a.recieveid = '$userid' && a.folderid = '0' && b.id = a.recieveid && c.id = a.sendid");

ATLien

The SQL query works fine when I run it in the database itself it returns fine. It is just when I try and do the fetch array.

Sxooter

What is the actual error you're getting. You do no one any favors when you leave out the actual error. Unless it's something uninformative like "General Error #245 at mysql_fetch_array, which isn't very common in PHP.

ATLien

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

AstroTeg

Eh, this isn't going to work:

while($pminbox = mysql_fetch_array($pminbox))

You're assigning the result set $pminbox the 1 row you pull from the database. When the loop comes around for pass #2, $pminbox is no longer a result set but an array containing the first row. You need to change the $pminbox seen on the left side of the equals sign. Like this:

while($row = mysql_fetch_array($pminbox))

Sxooter

OK, try this, instead of putting your query directly inside the mysql_query, put it in a string, then print that string out on the screen and see if it runs after copying and pasting it into the mysql cli window.

And you probably want AND in the query, not &&. While many databases have an && operator, it's usually a bitwise thing used to do things like

select '255' && '13'

bradgrafelman

Wow, didn't even notice that. Nice catch.

Yeah, change '&&' to 'AND' in your query and also change the variable name as AstrTeg suggested, and if that doesn't fix it, do an echo mysql_error() after your mysql_query() but before your mysql_fetch_array().

ATLien

Hey everyone, thanks for all the help, the actual issue was with the varible name. Thanks again, I feel stupid making such a rookie mistake.

bradgrafelman

If it's all fixed and running fine now... MARK THE THREAD RESOLVED!

Thanks.