The correct way to do nested SQL queries using SQL server and PHP

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.\n";
         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.

score 4 · Accepted Answer · 2012-03-22 10:55:42Z

4

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

edited Mar 22, 2012 at 10:55

answered Mar 22, 2012 at 9:23

user359040

Sign up to request clarification or add additional context in comments.

5 Comments

deucalion0 Over a year ago

Yes I felt that was the issue, I was actually doing two queries and expecting PHP to handle it as one. I was told some time ago I cannot remember where or when but if it works in SQL management studio it should work anywhere else. I will try and use a JOIN, thanks.

deucalion0 Over a year ago

I have been researching joins but cannot figure out how to do this correctly, as I want to select all from two tables, is this possible? Thanks!

user359040 Over a year ago

@deucalion0: Can you list the columns that you want to return from each table? The reason I ask is that where different tables have the same column names, SQL queries generally require different column aliases (or else they will return an error). Also, it's good practice to only select the required columns, instead of all columns on all queried tables.

deucalion0 Over a year ago

Thats a good idea, only select required columns, I need better habits! The two tables and the columns needed are: Table: product_catalogue Columns: catalogueID, product_name, product_price, description Table: products Columns: productID, picturem, picturew Many thanks for your help!

deucalion0 Over a year ago

Thank you very much, that shows exactly how it works and it does work! I really appreciate your time and efforts!

Collectives™ on Stack Overflow

The correct way to do nested SQL queries using SQL server and PHP

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related