Problem with MySQL query in PHP - nested queries

[mrsbean]

I have a query which has an additional select statement inside it. The query is a valid query. It works when I dump the results of the variable $ListA into a MySQL query browser. PHP seems to be hanging up on this query though. What can I change to make it work:

//The following is a valid query 
//works when I dump it into MySQL query browser

$ListA = "SELECT phReqDocs.*, Query7.TypeChecklist, Query7.TransSubList, Query7.TransSubOrder
FROM phReqDocs LEFT JOIN (SELECT phTransDefaultDocs.*, phDocuments.DocName, phTransactionType.TypeChecklist
FROM (phTransDefaultDocs INNER JOIN phDocuments ON phTransDefaultDocs.AssignDoc = phDocuments.DocID) INNER JOIN phTransactionType ON phTransDefaultDocs.TransDocList = phTransactionType.TypeID  
     ) AS Query7 ON phReqDocs.TransDoc = Query7.DocName
WHERE (((Query7.TypeChecklist)='Residential') AND ((Query7.TransSubList)='A'))
ORDER BY Query7.TransSubList, Query7.TransSubOrder;
";

//I'm using the following echo 
//statement to retrieve the query and test it elsewhere
//(dump into MySQL query browser)

echo "<p>$ListA</p>";

//PHP doesn't like $resultA
//and it displays the following error
//related to $resultA
//Warning: mysql_num_rows(): supplied argument is not a 
//valid MySQL result resource in 
// /home/[URL unfurl="true"]www/procomusa/phoenician/checklist_top.php[/URL] 
//on line 198
//Sorry. No records found !! 

$resultA = MYSQL_QUERY($ListA);
echo "<p>$resultA</p>";
$numberA = MYSQL_NUM_ROWS($resultA);

MrsBean

 

[sleipnir214]

Try getting rid of the semicolon at the end of the query.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[sleipnir214]

If that doesn't work, let PHP help you figure out what's wrong. Change:

$resultA = MYSQL_QUERY($ListA);

for

$resultA = MYSQL_QUERY($ListA) OR die (mysql_error());





Want the best answers? Ask the best questions! TANSTAAFL!

 

[mrsbean]

Okay. Thanks. I was able to retrieve the following error by incorporating the mysql_error() into the code (removing the semicolon from the end didn't make any difference).


You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT phTransDefaultDocs.*, phDocuments.DocName, phTransaction


The portion that is specified is the nested query. Can I use a nested query from within PHP?

MrsBean

 

[mrsbean]

I've come closer to figuring out my own problem. The MySQL query browser that I used to dump the resulting SQL allowed the nested query. The MySQL server on my local machine (which has a copy of the database hosted on a remote server) is a later version of MySQL. The host application is version 4. something, and it appears that it will now permit the nested query.

Any advice how to get around this? Maybe I need to change my table structure and allow some redundancy so I can get the set of data that I need?

MrsBean

 

[Tarwn]

I don't understand why you need the subselect, couldn't you just reformat your where clause into a single series of joins? If you absolutely must have the inner joins done first, make sure tey are in the deepest layer of parans.

SELECT phReqDocs.*, phTransDefaultDocs.*, phDocuments.DocName, phTransactionType.TypeChecklist
FROM phReqDocs LEFT JOIN ((phTransDefaultDocs INNER JOIN phDocuments ON phTransDefaultDocs.AssignDoc = phDocuments.DocID) INNER JOIN phTransactionType ON phTransDefaultDocs.TransDocList = phTransactionType.TypeID) ON phReqDocs.TransDoc = phDocuments.DocName
WHERE (((TABLENAME.TypeChecklist)='Residential') AND ((TABLENAME.TransSubList)='A'))
ORDER BY TABLENAME.TransSubList, TABLENAME.TransSubOrder;

I don't know anything about your table structure, or I might be able to provide a more suitable example.