selecting a name from table 2 but get no returns

[jeff5656]

I have a table called users with a fieldname called service_id.  In a table called services I have id and name.

 

I want to query the users table and, based on the service_id, display the name of the service (which is stored in the services table).

 

However, when I try this code I get

No records returned.

 

I later echo out under a while statement $row['name']; or $row['id']

 

$query = "SELECT users.username, users.lname, users.fname, users.service_id, services.name, services.id 
FROM users, services 
WHERE users.inst_id = '".$userarray['inst_id']."' and users.id !='".$userarray['id']."' and users.service_id = services.id ";

[systemick]

What I always do with SQL is print it out in the browser then paste it into my mysql client. I use a shell but i believe you can do this with PHPMyAdmin too. Doing this usually gives an error message which can then be used to diagnose the problem.

[mikosiko]

this could be the culprit

 

 users.fname,, users.service_id

  // 2 , between fields

 

EDIT :.... I posted this just when you were editing your post....

 

probably after the select you have something like this

 

$return = mysql_query($query);

 

I will write that line

$return = mysql_query($query) or die("Select : " . $query . "<br />" . mysql_error());

 

 

[jeff5656]

yea I saw the two commas and removed that.  It got rid of the error but doesnt return any records.  In general let me ask a question regarding a query of two tables.

 

Lets say I want to echo out a value from a record and I use the above query.

then:

$return = mysql_query($query);
while ($row=mysql_fetch_assoc($return ) 

echo $row['fieldname'];

 

Now my question is, in that echo statement, how do I write it so it knows which table the field is from.  for instance

echo $row['users.name']??

 

[mikosiko]

the simple and more clear way is assigning row alias directly in your query, in that way you don't have to worry about fieldname differentiation later in your code... per example..

 

$query = "SELECT a.name AS Aname, b.name AS Bname FROM table1 a JOIN table2 b ON a.id = b.id";
$return = mysql_query($query);
while ($row=mysql_fetch_assoc($return ) 

echo $row['Aname'] . " - " . $row['Bname'];