[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 691
  • Last Modified:

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!
0
deucalion0
Asked:
deucalion0
  • 2
  • 2
1 Solution
 
Chris HarteThaumaturgeCommented:
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

0
 
deucalion0Author Commented:
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.
0
 
Chris HarteThaumaturgeCommented:
There is a comma on the end of the first line. My bad.
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

0
 
deucalion0Author Commented:
Thanks for all your help and helping me get my code working, appreciated!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now