Resource id #9 errors in query

sketchgal · February 26, 2009, 11:46am

I have a query that should return certain rows from a table but its bringing up a Resource id #9 error for some reason.

The query causing the problems is shown below.

$sql = mysql_query(“SELECT * FROM stock_portfolio WHERE PFCDealerId IN (‘1832208’, ‘1986050’, ‘1968553’) ORDER BY Price LIMIT $from, $max_results”);

I don’t really know where to start with this one.

Any help will be very much appreciated.

If you need any more info let me know and I’ll try to explain it better

Thanks

DarthGuido · February 26, 2009, 11:51am

Post the code please.
And echo out $sql to see how the query looks like, and use the ‘or die(mysql_error())’ construction on the mysql_query.

sketchgal · February 26, 2009, 12:04pm

Here’s the full chunk of code that has the problem:

<?php
if($_REQUEST[‘pagesubmitted’] == 1){

     if($subcat=="" && $min=="" && $max==""){
    $sql = mysql_query("SELECT * FROM stock_portfolio WHERE PFCDealerId IN ('1832208', '1986050', '1968553') ORDER BY Price LIMIT $from, $max_results");
}
//1 selected
	elseif($subcat!= "" && $min=="" && $max==""){
        $sql = mysql_query("SELECT * FROM stock_portfolio WHERE VehicleDescription LIKE '&#37;$subcat%' AND PFCDealerId IN ('1832208', '1986050', '1968553') ORDER BY Price LIMIT $from, $max_results");
}
	elseif($subcat== "" && $min!="" && $max==""){
		$sql = mysql_query("SELECT * FROM stock_portfolio WHERE Price=&gt;$min AND PFCDealerId IN ('1832208', '1986050', '1968553')ORDER BY Price LIMIT $from, $max_results");
}
	elseif($subcat== "" && $min=="" && $max!=""){
		$sql = mysql_query("SELECT * FROM stock_portfolio WHERE Price&lt;=$max AND PFCDealerId IN ('1832208', '1986050', '1968553') ORDER BY Price LIMIT $from, $max_results");
}
//2 selected
	elseif($subcat!= "" && $min!="" && $max==""){
        $sql = mysql_query("SELECT *  FROM stock_portfolio WHERE VehicleDescription LIKE '%$subcat%' AND Price=&gt;$min AND PFCDealerId IN ('1832208', '1986050', '1968553') ORDER BY Price LIMIT $from, $max_results");
}
	elseif($subcat!= "" && $min=="" && $max!=""){
		$sql = mysql_query("SELECT * FROM stock_portfolio WHERE VehicleDescription LIKE '%$subcat%' AND Price&lt;=$max AND PFCDealerId IN ('1832208', '1986050', '1968553') ORDER BY Price LIMIT $from, $max_results");
}
	elseif($subcat== "" && $min!="" && $max!=""){
		$sql = mysql_query("SELECT * FROM stock_portfolio WHERE Price BETWEEN $min AND $max AND PFCDealerId IN ('1832208', '1986050', '1968553') ORDER BY Price LIMIT $from, $max_results");
}
//3 selected
     elseif($subcat!="" && $min!="" && $max!=""){
		$sql = mysql_query("SELECT * FROM stock_portfolio WHERE VehicleDescription LIKE '%$subcat%' AND Price BETWEEN $min AND $max AND PFCDealerId IN ('1832208', '1986050', '1968553') ORDER BY Price LIMIT $from, $max_results");
}
   else{
      $sql = mysql_query("SELECT * FROM stock_portfolio WHERE PFCDealerId IN ('1832208', '1986050', '1968553') ORDER BY Price LIMIT $from, $max_results");
    }

}else{
$sql = mysql_query(“SELECT * FROM stock_portfolio WHERE PFCDealerId IN (‘1832208’, ‘1986050’, ‘1968553’) ORDER BY Price LIMIT $from, $max_results”);
}

//$sql = mysql_query(“SELECT * FROM stock WHERE man_id=‘$make’ LIMIT $from, $max_results”);
///$stock_result = mysql_query($sql) or die(mysql_error());
///$stock_query = mysql_num_rows($stock_result);
while($row_stock = mysql_fetch_array($sql)){
// Build your formatted results here.
echo
?>

When I do <?php echo “$sql”; ?>
I get Resource id #9

Does this help?

DarthGuido · February 26, 2009, 1:13pm

Sorry, my bad.
You should put the query in a variable and echo that.

It looks like your code would be a lot more readable if you’d construct your query in pieces, instead of rewriting it completely for each possible combination:


<?php
$query = "SELECT * FROM stock_portfolio WHERE 1 = 1";

if ($_REQUEST['pagesubmitted'] == 1) {

   if ($subcat != "") {
      $query .= " AND VehicleDescription LIKE '%$subcat%'";
   }
   if ($min != "") {
      $query .= " AND Price=>$min";
   }
   if ($max != "") {
      $query .= " AND Price<=$max";
   }

}

$query .= " AND PFCDealerId IN ('1832208', '1986050', '1968553') ORDER BY Price LIMIT $from, $max_results");

// echo $query to see if it's been constructed ok
echo "query : $query<br />";

// execute query:

$sql = mysql_query($query) or die(mysql_error());

while($row_stock = mysql_fetch_array($sql)){
// Build your formatted results here.
echo
?>

salathe · February 26, 2009, 2:08pm

Where do $from and $max_results get assigned values?

sketchgal · February 26, 2009, 2:27pm

Thanks for sorting out my query that’s so much easier to go through now.
I’m still having a few problems though.
When I initially load the page now everything seems fine but when I run the search/ filter query its throwing up the following error:

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 ‘=>0 AND Price<=25000 ORDER BY Price LIMIT 0, 10’ at line 1

Here’s how the code looks now:

<?php

$query = “SELECT * FROM stock_portfolio WHERE PFCDealerId IN (‘1832208’, ‘1986050’, ‘1968553’)”;

if ($_REQUEST[‘pagesubmitted’] == 1) {

if ($subcat != “”) {
$query .= " AND VehicleDescription LIKE ‘%$subcat%’";
}

if ($min != “”) {

  $query .= " AND Price=&gt;$min";

}

if ($max != “”) {

  $query .= " AND Price&lt;=$max";

}

$query .= " ORDER BY Price LIMIT $from, $max_results";

// echo $query to see if it’s been constructed ok

echo “query : $query<br />”;

// execute query:

$sql = mysql_query($query) or die(mysql_error());

while($row_stock = mysql_fetch_array($sql)){

// Build your formatted results here.

echo

?>

DarthGuido · February 26, 2009, 2:42pm

And what does the echo command show you?

Btw, please put your code between PHP code tags (there’s a button for it in the advanced reply editor). It makes it easier to read.

r937 · February 26, 2009, 2:42pm

please echo $query just before you execute it, so that we can see the actual SQL statement that caused the error

DarthGuido · February 26, 2009, 2:45pm

He already does

r937 · February 26, 2009, 2:56pm

oh? i must have missed it, which post was it please?

DarthGuido · February 26, 2009, 3:03pm

At the end of the script, just before the query is executed

But of course, it’s of little use to us until he posts the result.

r937 · February 26, 2009, 3:06pm

no, post #6 does not show the generated sql statement, it shows a whole bunch of php stuff, and i have no way of confirming what all the php stuff actually produces without seeing the final, generated sql statement

DarthGuido · February 26, 2009, 3:08pm

Ok, let me rephrase my comment to your post:
He already does echo the sql statement in his code, but he didn’t post the result here.

sketchgal · February 26, 2009, 3:13pm

Here’s the results from the queries. Is this what you are meaning?

query : SELECT * FROM stock_portfolio WHERE PFCDealerId IN (‘1832208’, ‘1986050’, ‘1968553’) AND Price=>0 AND Price<=25000 ORDER BY Price LIMIT 0, 10

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 ‘=>0 AND Price<=25000 ORDER BY Price LIMIT 0, 10’ at line 1

DarthGuido · February 26, 2009, 3:16pm

Price>=

r937 · February 26, 2009, 3:17pm

aha! okay, sorry, now i understand what you meant

r937 · February 26, 2009, 3:18pm

DOH!!

we didn’t even need to see the query! that was right there in the error message!

DarthGuido · February 26, 2009, 3:20pm

Yes
But it’s easier to spot when you see the whole query, and not just a piece

sketchgal · February 26, 2009, 4:07pm

Thanks for all the help with this you guys are once again fantastic.