SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Pdo if results = null run another statement

    I'm trying to get my head around php and pdo statements at the moment so bear with.

    I have the following code
    PHP Code:
    <?php
    require_once('config.php');

    try {
        
    $dbh = new PDO("mysql:host=$hostname;dbname=texts"$username$password);
    /*** The SQL SELECT statement ***/
        
    $sql "SELECT * FROM `texts` WHERE `messagesent` = 0 LIMIT 10";
        foreach (
    $dbh->query($sql) as $row)
            {
            
            echo(
    '&nbsp; '.$row['content'].' &nbsp;');
           
            }

        
    /*** close the database connection ***/
        
    $dbh null;
    }
    catch(
    PDOException $e)
        {
        echo 
    $e->getMessage();
        }
    ?>
    What I want to do is pull down first ten rows and display the contents of the contents row but if there are no results I want it to do another query from a different table and display the contents from the other table.

    I also plan to add another query after the first one so that it updates a col.

    The idea is that it pulls the first 10 displays it and updates the col to set it as displayed and refresh and if no results it pulls in from another table.

  2. #2
    SitePoint Enthusiast Belsnickle's Avatar
    Join Date
    Feb 2012
    Location
    Chico, CA
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you prepare the statement first you can actually run a version of row_count ( in it's PDO rowCount) on it, otherwise you can't because it's an unbuffered query. The reason it worked in mysql and mysqli is that those are by default fetching and then buffering all of the rows. PDO initially is closer to using mysql_unbuffered_query(). What all of that means is you need to buffer the query or manually fetch_all() and then count() all of the rows, simply buffering the query has a smaller performance hit in this case iirc.

    So, basically run your statement through a prepare() and then execute() then all you have to do is use call PDO's rowCount() on the object and you've got the number of result rows.

  3. #3
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks i will have a read up on them now to see what's what

  4. #4
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i have some sort of error i need help fixing
    Parse error: syntax error, unexpected '{' in /var/www/smsdisplay/sms.php on line 17
    i tried to remove it { but then keep getting the issue with the else statement.
    PHP Code:
    <?php
    require_once('config.php');

    try {
        
    $dbh = new PDO("mysql:host=$hostname;dbname=texts"$username$password);
    /*** The SQL SELECT statement ***/
    $sql "SELECT * FROM `texts` WHERE `messagesent` = 0 LIMIT 0,5";
        foreach (
    $dbh->query($sql) as $row)
            {
            echo(
    '&nbsp; '.$row['content'].' &nbsp;');
            }
            
    $sql2 "UPDATE texts SET messagesent = '1' WHERE id = ".$row['id']."";
            foreach (
    $dbh->query($sql2) as $row)
            {
            
    //Updating table to say they have been displayed
            
    }
            if (
    $dbh->query($sql) {
             (
    $dbh->fetchColumn() > 0) {
            
    //Ignore more than 1 results
            
    }
            }
                
    /* No rows matched -- do something else */
      
    else {
          print 
    "No rows matched the query.";
        }
    }
    /*** close the database connection ***/
        
    $dbh null;
    }
    catch(
    PDOException $e)
        {
        echo 
    $e->getMessage();
        }
    ?>

  5. #5
    SitePoint Enthusiast Belsnickle's Avatar
    Join Date
    Feb 2012
    Location
    Chico, CA
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by txt3rob View Post
    i have some sort of error i need help fixing
    Parse error: syntax error, unexpected '{' in /var/www/smsdisplay/sms.php on line 17
    i tried to remove it { but then keep getting the issue with the else statement.
    Code:
    <?php
    require_once('config.php');
    
    try {
        $dbh = new PDO("mysql:host=$hostname;dbname=texts", $username, $password);
    /*** The SQL SELECT statement ***/
    $sql = "SELECT * FROM `texts` WHERE `messagesent` = 0 LIMIT 0,5";
        foreach ($dbh->query($sql) as $row)
            {
            echo('* '.$row['content'].' *');
            }
    		$sql2 = "UPDATE texts SET messagesent = '1' WHERE id = ".$row['id']."";
    		foreach ($dbh->query($sql2) as $row)
    		{
    		//Updating table to say they have been displayed
    		}
    		if ($dbh->query($sql) {
    		if ($dbh->fetchColumn() > 0) {
    		//Ignore more than 1 results
    		}
    		}
    		    /* No rows matched -- do something else */
      else {
          print "No rows matched the query.";
        }
    }
    /*** close the database connection ***/
        $dbh = null;
    }
    catch(PDOException $e)
        {
        echo $e->getMessage();
        }
    ?>
    You seem to be missing at least an if (yellowed where it should be, on line 17) and your indentation method seems to be lending to the confusion. Generally it's a good practice to indent dependencies or items inside brackets but particularly to do so in a way that makes it easy for you to see what's required to reach each step in the process/trigger what response.

  6. #6
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I understood your requirement correctly you just want to list the messages that are Unread (sent?) yet and update/mark them all as Read once they are listed/printed , right? If yes then see the following code:
    PHP Code:
    require_once('config.php');

    try {
        
    $dbh = new PDO("mysql:host=$hostname;dbname=texts"$username$password);
        
    /*** The SQL SELECT statement ***/
        
    $sql "SELECT * FROM `texts` WHERE `messagesent` = 0 LIMIT 0,5";
        
    $result $dbh->query($sql)->fetchObject();
        if(
    $result){
            foreach(
    $dbh->query($sql) as $row){
                echo(
    '&nbsp; ' $row['content'] . ' &nbsp;');
                
                
    $dbh->query("UPDATE texts SET messagesent = '1' WHERE id = ?"$row['id']);
            }
        }
        else{
            echo 
    "No unread messages !";
        }
        
    $dbh null;
    }
    catch(
    PDOException $e){
        echo 
    $e->getMessage();

    Not tested with real data but the idea should be something like above or lets say I do normally like the above
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  7. #7
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you raju,

    currently having issues with hamachi at the moment but will report back so far it looks like it will work

  8. #8
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Warning: PDO::query(): SQLSTATE[HY000]: General error: mode must be an integer in

    It shows the contents but also shows the above message.

  9. #9
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Belsnickle View Post
    You seem to be missing at least an if (yellowed where it should be, on line 17) and your indentation method seems to be lending to the confusion. Generally it's a good practice to indent dependencies or items inside brackets but particularly to do so in a way that makes it easy for you to see what's required to reach each step in the process/trigger what response.
    Parse error: syntax error, unexpected '{' in line 17 with new if in place.

  10. #10
    SitePoint Enthusiast Belsnickle's Avatar
    Join Date
    Feb 2012
    Location
    Chico, CA
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
    require_once('config.php');

    try 
    {
         
    $dbh = new PDO("mysql:host=$hostname;dbname=texts"$username$password);
         
    /*** The SQL SELECT statement ***/
         
    $sql "SELECT * FROM `texts` WHERE `messagesent` = 0 LIMIT 0,5";
         foreach (
    $dbh->query($sql) as $row)
         {
                 echo(
    '&nbsp; '.$row['content'].' &nbsp;');
         }
         
    $sql2 "UPDATE texts SET messagesent = '1' WHERE id = ".$row['id']." ";
         foreach (
    $dbh->query($sql2) as $row)
         {
              
    //Updating table to say they have been displayed
         
    }
         if (
    $dbh->query($sql) )
         {
              if (
    $dbh->fetchColumn() > 0
              {
                   
    //Ignore more than 1 results
              
    }
         }  
    /* No rows matched -- do something else */
        
    else 
        {
             print 
    "No rows matched the query.";
        }
    }
    /*** close the database connection ***/
        
    $dbh null;
    }
    catch(
    PDOException $e)
    {
         echo 
    $e->getMessage();
    }
    ?>
    You were also missing a closing ) on the if conditions on line 16.

    fetchColumn returns false when no rows return instead of an integer, you'd have to convert it to 0 and then an int but you may as well just remove the comparison operator completely in that case, much like in Raju's post #6, as anytime it returns a non-compared integer it's just always true.

  11. #11
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    strange now $dbh = null; is causing a problem - PDO is beating me fast here!

    Parse error: syntax error, unexpected '$dbh' (T_VARIABLE), expecting catch (T_CATCH) in /var/www/smsdisplay/sms.php on line 31

  12. #12
    SitePoint Enthusiast
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    88
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The try/catch construct must be exactly that:
    Code:
    try
    {
    }
    catch
    {
    }
    You placed your $dbh=null in the midst.

  13. #13
    SitePoint Enthusiast Belsnickle's Avatar
    Join Date
    Feb 2012
    Location
    Chico, CA
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup, you forgot to write your Catch, until you have that in you can't actively test the script.

  14. #14
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    <?php
    require_once('config.php');
    
    try 
    {
         $dbh = new PDO("mysql:host=$hostname;dbname=texts", $username, $password);
         /*** The SQL SELECT statement ***/
         $sql = "SELECT * FROM `texts` WHERE `messagesent` = 0 LIMIT 0,5";
         foreach ($dbh->query($sql) as $row)
         {
                 echo('&nbsp; '.$row['content'].' &nbsp;');
         }
         $sql2 = "UPDATE texts SET messagesent = '1' WHERE id = ".$row['id']." ";
         foreach ($dbh->query($sql2) as $row)
         {
              //Updating table to say they have been displayed
         }
         if ($dbh->query($sql) )
         {
              if ($dbh->fetchColumn() > 0) 
              {
                   //Ignore more than 1 results
              }
         }  /* No rows matched -- do something else */
        else 
        {
             print "No rows matched the query.";
        }
    	/*** close the database connection ***/
        $dbh->disconnect();
    }
    catch(PDOException $e)
    {
         echo $e->getMessage();
    }
    ?>
    Now shows the contents of the rows but i get the following error: Fatal error: Call to undefined method PDO::fetchColumn() in /var/www/smsdisplay/sms.php on line 20

    I think i might have to do the check if the results are 0 and then echo no results and then do if results = 1 or more then show the results.

  15. #15
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,441
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    What about doing something like this:

    PHP Code:
    require_once('config.php');

    try 
    {
        
    $sent_ids = array();
        
        
    $dbh = new PDO("mysql:host=$hostname;dbname=texts"$username$password);
        
    $sql "SELECT * FROM `texts` WHERE `messagesent` = 0 LIMIT 0,5";
        
        foreach (
    $dbh->query($sql) as $row)
        {
             echo(
    '&nbsp; '.$row['content'].' &nbsp;');
             
    $sent_ids[] = $row['id'];
        }
        
        if (
    $sent_ids)
        {
            
    $sql2 "UPDATE texts SET messagesent = '1' WHERE id IN ("implode(','$sent_ids) .")";
            
    $dbh->query($sql2);
        }
        else
        {
            print 
    "No rows matched the query.";
        }
        
        
    $dbh->disconnect();
    }
    catch(
    PDOException $e)
    {
        echo 
    $e->getMessage();

    If the first query is successful, $sent_ids will not be empty and so the second query will be run, updating all the matching rows. If $sent_ids is empty, then the first query didn't return any results and so you can output a message (or perform another query or whatever).

  16. #16
    SitePoint Enthusiast Belsnickle's Avatar
    Join Date
    Feb 2012
    Location
    Chico, CA
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That would be what I was going on about in post #2, fretburner's code in #15 essentially counts your rows in a way you can manipulate the IDs in the loop which is another way of getting that same information.


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
  •