[Resolved] Trouble w/ query in mysql

[rcoursey]

Error

SQL-query :

SELECT *
FROM purchase AS pur, purchase_items AS pi, plays AS pl, picks AS pk
WHERE pur.purchaseid = pi.purchaseid, AND pi.playid = pl.playid AND pl.playid = pk.playid AND pk.active > '0' AND pur.date = '0000-00-00 00:00:00'
LIMIT 0 , 30

MySQL said:

You have an error in your SQL syntax near ' AND pi.playid = pl.playid AND pl.playid = pk.playid AND pk.active > '0' AND p' at line 3

Any ideas on this ? I know it's complicated.

[bad76]

No more.

it's just the , after pi.purchaseid...

p.s: if you don't remember the question look the msg before... 😃

[rcoursey]

This is the new select statement:
echo $user."=Userid<br>";
$result=dbquery("SELECT purit.purchaseid, purit.pkgid, pkgit.playid, pk.gamestart, pur.*, pl.playid FROM purchase_items AS purit, pkg_items AS pkgit, purchase AS pur, plays as pl, picks AS pk WHERE purit.purchaseid=pur.purchaseid and purit.pkgid=pkgit.pkgid and pkgit.playid=pl.playid and pk.playid=pl.playid and pur.userid='$user'");
while($row=mysql_fetch_array($result))
{
echo $row['pk.gamestart']."=Gamestart.<br>";
}

My output is:
11=Userid
=Gamestart.
=Gamestart.
=Gamestart.
=Gamestart.
=Gamestart.
=Gamestart.
=Gamestart.

When I run this directly into phpMyAdmin I substitute the value of $user and get the correct output (I have files I know meet the criteria)

Why can't I echo this within the script?

[rcoursey]

One other note.

When I run the query directly I retrieve exactly 7 records when I manually enter 11 for the $user. This is the exact number of blank gamestart items.

[rcoursey]

I changed the echo to:
while($row=mysql_fetch_array($result))
{
echo $row['gamestart']."=Gamestart.<br>";
}

Now the output is correct!

Why can't I use this code with aliases?

$row['pk.gamestart'];

[kevinsequeira]

because in the row array the key is called gamestart not pk.gamestart.

try this

in the while loop print_r($row);

reg
kevin

[rcoursey]

What does print_r do?

[bad76]

Print recursively the array content...