SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Resolving errors in my SQL queries

    I'm a newb to OOP and working through some examples in Sitepoints PHP Anthology 2nd Edition. I'm currently looking at resolving errors in my SQL queries. However, I'm not quite getting how it works.

    I can get examples to describe any SQL errors using either the default Silent Mode
    Code:
    PDO::ERRMODE_SILENT
    and with warning mode
    Code:
    PDO::ERRMODE_WARNING
    . When using PDO solutions for catching errors, should they prevent your script from running if no error is caught?

    Using the below example, I can get the script to point errors out, but nothing more when all errors are removed.
    Code:
    <?php
    // make the DSN
    $dsn = 'mysql:host=localhost;dbname=world;';
    $user = 'user';
    $password = 'secret';
    
    $country = 'USA';
    
    $dbh = new PDO($dsn, $user, $password);
    // set the error mode to warning 
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);  
    // intentionally typo the table name
    $sql = 'Select * from cities where CountryCode =:country';
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':country', $country, PDO::PARAM_STR);
    $stmt->execute();
    $code = $stmt->errorCode();
    if (empty($code))
    {   
      while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        print $row['Name'] . "\t";
        print $row['CountryCode'] . "\t";
        print $row['Population'] . "\n";
      }
    
    }
    else
    {
      echo 'Error with the database: <br />';
      echo 'SQL Query: ', $sql;
      echo '<pre>';
      var_dump($stmt->errorInfo());
      echo '</pre>';
    }
    ?>
    Are these methods only used for debugging and should therefore be removed once errors are found, or should the scripts run as normal?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The script will continue to run, but your visitors will see both the SQL query and the error message. This is not something you want to show to visitors as it makes it may reveal information someone can use to compromise your site or database.

    What should happen is up to you. In most cases, if database queries are failing, you'll want to display some kind of generic error message that doesn't reveal the technical details, log the query and error to a file, and send some kind of notification to yourself that a problem has occurred.

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see what you mean here, and I suspected that this is what should happen. However, when I run the sample code, the script does not fully run, it just outputs the error. Or, if I correct all the SQL errors, it just ouputs that error are null? And does not output anything retrieved from the database?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What is the value of $stmt->errorCode() when there are no errors? If it always enters the 'else', then maybe its value isn't empty

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vc08 View Post
    I see what you mean here, and I suspected that this is what should happen. However, when I run the sample code, the script does not fully run, it just outputs the error. Or, if I correct all the SQL errors, it just ouputs that error are null? And does not output anything retrieved from the database?
    Your code specifically says to only output the stuff from the database if there is no error. That's not the doing of PHP or SQL or the error, that's the doing of the code that you wrote. If you had more code in this file after your error handling code, it would run, but you don't.

  6. #6
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, what I want it to do, is output the results from the SQL query if there is no error. I thought this should do it, as that's the exact code from the book. But with the correct SQL query, and in theory not getting any errors, it still wont work. So, is this the case because 'empty' is returning a true value, when there is no error an therefore running the else statement? Though, it runs the else statement anywhay when it does return an error. I'm confused, this is downloaded from the Sitepoint Code archive, no matter how much I re-read the section of the book, I just don't get it. Is there a mistake somewhere?

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Put
    PHP Code:
    echo "code: '$code'<br />"
    before the 'if' in your code, and see what it displays.

  8. #8
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I get the output - code: '00000'. Which is what should be stored in the $code variable when no errors are reported. So, is this not the same a the $code variable set as 'empty'. Therefore returning true and running the script and not the else statement?

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    From the page of the php manual I posted before:

    The following things are considered to be empty:

    * "" (an empty string)
    * 0 (0 as an integer)
    * "0" (0 as a string)
    * NULL
    * FALSE
    * array() (an empty array)
    * var $var; (a variable declared, but without a value in a class)

    So it looks like '00000' won't be considered empty.

  10. #10
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was thinking the same thing. This works a treat;

    Code:
    <?php
    // make the DSN
    $dsn = 'mysql:host=localhost;dbname=world;';
    $user = 'user';
    $password = 'password';
    
    $country = 'USA';
    
    $dbh = new PDO($dsn, $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
    $sql = 'Select * from city where CountryCode =:country';
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':country', $country, PDO::PARAM_STR);
    $stmt->execute();
    $code = $stmt->errorCode();
    if ($code !== '00000')
    {	
    	echo 'Error with the database: <br />';
    	echo 'SQL Query: ', $sql;
    	echo '<pre>';
    	var_dump($stmt->errorInfo());
    	echo '</pre>';
    }
    else
    {
    	while ($row = $stmt->fetchObject()) {
    		print $row->Name . "\t";
    		print $row->CountryCode . "\t";
    		print $row->Population . "\n";
    	}
    	
    }
    ?>
    I also see that as of PHP 5, Objects with no properties are no longer considered empty. Not sure if this is an error in the book, as the section is an introduction to OOP in PHP 5.


Tags for this Thread

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
  •