[SOLVED] PHP/MySQL/ Join Query

[jreed2132]

I have the following code:

 

$query = "Select p.ProfileID, p.ProfileName from Profiles as p join ProfileAccess as a on p.ProfileID = a.ProfileID Order By p.ProfileName;";

 

$result=mysql_query($query) or die(mysql_error());

$row = mysql_fetch_array($result) or die(mysql_error());

 

while($row = mysql_fetch_array($result))

{

echo "<a href = viewprofile.php?info=$row[ProfileID]>$row[ProfileName]</a>";

echo "<br />";

}

 

mysql_close();

 

 

When the script runs, I get nothing.  No SQL errors or anything.  I even changed the query so that it would error and I receive one.

 

I take that same query and run it directly in MYSQL and it works.  I also changed the query in my script to a basic, "Select * from Profiles", and the script works.

 

Why won't this run in the PHP script?

[ignace]

$row = mysql_fetch_array($result) or die(mysql_error());
while($row = mysql_fetch_array($result))

 

You are calling mysql_fetch_array() twice.

 

P.S. remove 'or die()' your users don't know what 'SQL syntax error near ..' means.

[jreed2132]

Ahh, Great, thanks, works now,

 

Why did a plain old "Select * from Profiles" but not the join?  If one worked, why wouldn't the other?

[ignace]

Why did a plain old "Select * from Profiles" but not the join?  If one worked, why wouldn't the other?

 

Because select * from profiles select's all profiles while select p.profileid, p.profilename from profiles as p join profileaccess as a on p.profileid = a.profileid order by p.profilename select's only one matching row. Which is retrieved by:

 

$row = mysql_fetch_array($result);

 

$row = array('profileid' => .., 'profilename' => ..);

 

and overwritten by:

 

while ($row = mysql_fetch_array($result))

 

$row = false;