asked on

How can I out put multiple/nested queries in SQL using PHP and SQL Server?

Hey guys, I have a query which I have tested in SQL servers management studio, but I cannot get it to work in PHP as it tells me there is an error.

Fisrt of all here is my query which I know works as I wrote it in the management studio:

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

Open in new window

Here is the rest of the query where I feel there is a problem outputting it:

$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'  alt='$prod_name' title='$prod_name' width='200' height='400'/>"; 
							echo "<br/><br/>";

Open in new window

Here is the error I get when running that query:

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 ) )

Open in new window

I have tried solving this by reading up on cursors but I had no luck, I don't even know if it is possible to do this type of query.

I appreciate any help.

Thanks!

Chris Harte

I do not recognise the syntax of your query, perhaps the management studio is more forgiving than I am. Shouldn't you be using a join?

$query3 = "SELECT * FROM pc.product_catalogue AS pc,
INNER JOIN p.products AS p
ON pc.productid =  p.productid 
WHERE pc.catalguied = $productid";

Open in new window

deucalion0

ASKER

Thanks for your reply MunterMan, I tried your suggestion but I am receiving errors, I even tried it in the management studio, this is what I put into the management studio:

SELECT * FROM pc.product_catalogue AS pc,
INNER JOIN p.products AS p
ON pc.productid =  p.productid 
WHERE pc.catalguied = 1

Open in new window

The error I received was:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'INNER'.

Thanks for helping me with this.

ASKER CERTIFIED SOLUTION

Chris Harte

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

deucalion0

ASKER

Thanks for all your help and helping me get my code working, appreciated!