SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Resource id #9 errors in query

    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

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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.

  3. #3
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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=>$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<=$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=>$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<=$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?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by sketchgal View Post
    When I do <?php echo "$sql"; ?>
    I get Resource id #9

    Does this help?
    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 Code:
    <?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
    ?>

  5. #5
    @php.net Salathe's Avatar
    Join Date
    Dec 2004
    Location
    Edinburgh
    Posts
    1,397
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Where do $from and $max_results get assigned values?
    Salathe
    Software Developer and PHP Manual Author.

  6. #6
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 '&#37;$subcat%'";
    }

    if ($min != "") {

    $query .= " AND Price=>$min";

    }

    if ($max != "") {

    $query .= " AND Price<=$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

    ?>

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please echo $query just before you execute it, so that we can see the actual SQL statement that caused the error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    please echo $query just before you execute it, so that we can see the actual SQL statement that caused the error
    He already does

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by guido2004 View Post
    He already does
    oh? i must have missed it, which post was it please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    http://www.sitepoint.com/forums/show...26&postcount=6
    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.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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.

  14. #14
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    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

  15. #15
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Price>=

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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.
    aha! okay, sorry, now i understand what you meant

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Price>=
    DOH!!

    we didn't even need to see the query! that was right there in the error message!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    DOH!!

    we didn't even need to see the query! that was right there in the error message!
    Yes
    But it's easier to spot when you see the whole query, and not just a piece

  19. #19
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all the help with this you guys are once again fantastic.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •