I’m trying to get my head around php and pdo statements at the moment so bear with.
I have the following 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(' '.$row['content'].' ');
}
/*** 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.
If you prepare the statement first you can actually run [URL=“http://www.php.net/manual/en/pdostatement.rowcount.php”]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.
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
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) {
($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.
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:
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(' ' . $row['content'] . ' ');
$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
<?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 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.
<?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->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.
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(' '.$row['content'].' ');
$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).
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.
