[thelist] php/mysql - two consecutive select statements

[Jay Greenspan]

To tell you the truth, you're naming conventions make this hard for my 
small brain to follow.
The table names are generic, and you're joining tables on columns with 
totally different names.

table2.consultant = table3.id isn't very intuitive.

Have you tacked on a
or die(mysql_error()) to the query? Is it returning an error?
Have you echoed out the value of $client_id to see that it is
what you think should be?

These are usually pretty good places to start.

It also looks like you could get this done with a single query with 
another
join. Maybe something like

select table1.client_id, table2.client_name
        table3.givenname, table3.surname, table3.email
FROM table1, table2, table3
where table1.client_id=table2.id #why not name it table2.client_id?
and  ....

-j


On Friday, November 16, 2001, at 03:44 AM, noah wrote:

> I'm having a problem with select statements (PHP querying MySQL).
>
> There are 2 statements in a row:
>
> $job_query = mysql_query("SELECT client_id FROM table1 WHERE id = 
> $job_id");
> list($client_id) = mysql_fetch_row($job_query);
>
> $client_query = mysql_query("SELECT table2.client_name, 
> table3.givenname, table3.surname, table3.email FROM table2, table3 
> WHERE table2.consultant = table3.id AND table2.id = $client_id");
> list($client_name, $client_consultant_givenname, 
> $client_consultant_surname, $client_consultant_email) = 
> mysql_fetch_row($client_query);
>
> For some reason, if the first select is there, the second doesn't 
> work - as soon as I take the first out (e.g., replace it with just 
> "$client_id = 2;"), the second select works fine.
>
> (In case this is relevant, the two selects above are the second and 
> third of three in a row - the first is:
>
> $candidate_query = mysql_query("SELECT table4.givenname, 
> table4.surname, table3.givenname, table3.surname, table3.email FROM 
> table4, table3 WHERE table4.consultant = table3.id AND table4.id = 
> $candidate_id");
>
> list($candidate_givenname, $candidate_surname, 
> $candidate_consultant_givenname, $candidate_consultant_surname, 
> $candidate_consultant_email) = mysql_fetch_row($candidate_query);
> )
>
> Any ideas why this might be? Is there a better way to write the 
> statements that might work around it?