使用SQL Server和PHP进行嵌套SQL查询的正确方法

Question

I have a nested query which when I execute in SQL server 2008 management studio, it works perfectly, it outputs two results but when I try and do this in PHP I am receiving an error and I think it is to do with the PHP output.

So here is the query in SQL server which works, but I think this is not right:

select * from product_catalogue where catalogueid =(select catalogueid from products where productid = 1)  (select * from products where productid = 1)

Here is the complete query in PHP:

        $query3 = "select * from product_catalogue where catalogueid =(select catalogueid from products where productid = '" . $productid . "')  (select * from products where productid = '" . $productid . "')";                      

    $result3 = sqlsrv_query( $conn, $query3, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));

    if( $result3 === false)
    {
         echo "Error in query preparation/execution.
";
         die( print_r( sqlsrv_errors(), true));
    }


        while( $obj = sqlsrv_fetch_object( $result3)) 
                    {

                        $prod_image = $obj->picturem;
                        $prod_id = $obj->catalogueID;
                        $prod_description = $obj->description;
                        $prod_price = $obj->product_price;
                        echo "<p>$prod_id" ;
                        echo "<br/>$prod_description" ;
                        echo "<br/>&pound;$prod_price"; 
                        echo "<br/><br/><img src='$prod_image'  width='200' height='400'/>"; 
                        echo "<br/><br/>";
                        }

The error I am receiving when running this query is this:

Error in query preparation/execution. Array ( [0] => Array ( [0] => 01000 [SQLSTATE] => 01000 [1] => 16954 [code] => 16954 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor. ) [1] => Array ( [0] => 01S02 [SQLSTATE] => 01S02 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Server Native Client 10.0]Cursor type changed [message] => [Microsoft][SQL Server Native Client 10.0]Cursor type changed ) ) 

I have been researching nested queries as well as looking through the PHP manual trying to learn more about how to output results, I just feel like because it works in SQL server management studio, doesn't mean the syntax is correct. Any advice is appreciated.

Answer 1

This is actually two completely separate queries:

select * from product_catalogue where catalogueid =(select catalogueid from products where productid = 1)  

and

(select * from products where productid = 1)

The simplest way to do what you want is to join the tables in a single query - something like:

select c.catalogueID, 
       c.product_name, 
       c.product_price, 
       c.description, 
       p.productID, 
       p.picturem, 
       p.picturew
from product p
join product_catalogue c on c.catalogueid = p.catalogueid
where p.productid = 1