Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Aug 2012
    Location
    France
    Posts
    58
    Thanks
    15
    Thanked 0 Times in 0 Posts

    should fail?, but doesn't

    I am using this code to find the number of lines in particular orders stored in an order Items table, works just fine except that it doesn't fail but returns 0 when I query with a non existent order number. Can I get round this in SQL or should I filter for the 0 in php after the query?
    [CODE]
    $sql = "SELECT MAX(`lineNumber`) FROM OrderItems WHERE `orderNumber` = $_POST[orderNumber]";
    $resultS1 = mysql_query( $sql ) or die( "fail line number" . mysql_error() );
    $row = mysql_fetch_array($resultS1);
    $lineNumber = $row[0];
    $lineNumber = $lineNumber + 1;
    [CODE]
    A talent for speaking differently, rather than for arguing well, is the chief instrument of cultural change. Richard Rorty

  • #2
    New Coder
    Join Date
    Oct 2013
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Queries only fail if they can't be executed properly. After all, the search worked, it just didn't yield any results.

    So yes, if an empty resultset is an error in your application then you must check for that manually.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    In other words:
    Code:
    $sql = "SELECT MAX(lineNumber) FROM OrderItems WHERE orderNumber = $_POST[orderNumber]";
    $resultS1 = mysql_query( $sql ) or die( "fail line number" . mysql_error() );
    if ( $row = mysql_fetch_array($resultS1) )
    {
        $lineNumber	= $row[0] + 1;
    } else {
        $lineNumber = 1;
    }
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,080
    Thanks
    2
    Thanked 321 Times in 313 Posts
    There will be a row to fetch, due to the use of the aggregate function.

    max(lineNumber) is a mysql null when the query matches no rows. $row is - array(2) { [0]=> NULL ["max(lineNumber)"]=> NULL }

    I'm more concerned about what the code is trying to use the value for. Maintaining a line number, per order, in an order table, getting the maximum value, and incrementing it can only lead to problems.

    Why not just increment a php variable when displaying the items to serve as a line number? If you need to reference any particular item in an order, you would use the table's auto-increment id field.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Oh, DOH on me. Of course CFM is right!

    But then there is a TRIVIAL answer, if you want to use the same basic code:[code]
    Code:
    $sql = "SELECT IFNULL(MAX(lineNumber),0) FROM OrderItems WHERE orderNumber = $_POST[orderNumber]";
    $resultS1 = mysql_query( $sql ) or die( "fail line number" . mysql_error() );
    $row = mysql_fetch_array($resultS1);
    $lineNumber = $row[0] + 1;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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