Checking for empty result php pdo mysql

Question

Please  can anyone tell me what I m doing wrong here  I m simply retrieving results from a table then adding them to an array  Everything works as expected until I check for an empty result     This gets the match  adds it to my array and echoes the result as expected    today   date  Y-m-d   strtotime  now       sth    db- gt prepare  SELECT id email FROM db WHERE hardcopy    1  AND hardcopy date  lt    today AND hardcopy sent    0  ORDER BY id email ASC      sth- gt bindParam   today   today  PDO  PARAM STR    if   sth- gt execute           db   null       exit       while   row    sth- gt fetch PDO  FETCH ASSOC          this- gt id email      row  id email         echo  row  id email         db   null   return true     When I try to check for an empty result  my code returns  empty   but no longer yields the matching result    today   date  Y-m-d   strtotime  now       sth    db- gt prepare  SELECT id email FROM db WHERE hardcopy    1  AND hardcopy date  lt    today AND hardcopy sent    0  ORDER BY id email ASC      sth- gt bindParam   today   today  PDO  PARAM STR    if   sth- gt execute           db   null       exit       if   sth- gt fetchColumn          echo  not empty       while   row    sth- gt fetch PDO  FETCH ASSOC              this- gt id email      row  id email             echo  row  id email                db   null       return true     echo  empty    db   null   return false     As always  any help is appreciated  Thanks

User · Accepted Answer

You re throwing away a result row when you do  sth- gt fetchColumn    That s not how you check if there s any results  you do  if   sth- gt rowCount    gt  0          got results       else      echo  nothing       relevant docs here  http   php net manual en pdostatement rowcount php

User · Answer

what I m doing wrong here    Almost everything    today   date  Y-m-d       no need for strtotime   sth    db- gt prepare  SELECT id email FROM db WHERE hardcopy    1  AND hardcopy date  lt    today AND hardcopy sent    0  ORDER BY id email ASC      sth- gt bindParam   today   today      no need for PDO  PARAM STR   sth- gt execute       no need for if  this- gt id email    sth- gt fetchAll PDO  FETCH COLUMN      no need for while  return count  this- gt id email      no need for the everything else   effectively  you always have your fetched data  in this case in  this- gt id email variable  to tell whether your query returned anything or not  Read more in my article on PDO

User · Answer

sql    dbh- gt prepare  SELECT   from member WHERE member email     username  AND member password     password       sql- gt execute      fetch    sql- gt fetch PDO  FETCH ASSOC       if not empty result if  is array  fetch            SESSION  userMember      fetch  username          SESSION  password      fetch  password        echo  yes this member is registered     else       echo  empty result

User · Answer

Thanks to Marc B s help  here s what worked for me  note  Marc s rowCount   suggestion could work too  but I wasn t comfortable with the possibility of it not working on a different DB or if something changed in mine    also  his select count    suggestion would work too  but  I figured because I d end up getting the data if it existed anyways  so I went this way    today   date  Y-m-d   strtotime  now       sth    db- gt prepare  SELECT id email FROM db WHERE hardcopy    1  AND hardcopy date  lt    today AND hardcopy sent    0  ORDER BY id email ASC      sth- gt bindParam   today   today  PDO  PARAM STR    if   sth- gt execute           db   null       exit       while   row    sth- gt fetch PDO  FETCH ASSOC          this- gt id email      row  id email         echo  row  id email        db   null    if  count  this- gt id email   gt  0        echo  not empty       return true     echo  empty   return false

User · Answer

I only found one way that worked      quote    pdomodel- gt executeQuery  SELECT   FROM MyTable       if   is array  quote      didn t work   if   isset  quote      didn t work  if  count  quote     0        yep the count worked      echo  Record does not exist        die

User · Answer

Even though this is an old thread  I thought I would weigh in as I had to deal with this lately   You should not use rowCount for SELECT statements as it is not portable   I use the isset function to test if a select statement worked    today   date  Y-m-d   strtotime  now       sth    db- gt prepare  SELECT id email FROM db WHERE hardcopy    1  AND hardcopy date  lt    today AND hardcopy sent    0  ORDER BY id email ASC       I would usually put this all in a try catch block  but kept it the same for continuity if   sth- gt execute array   today   gt  today            db   null       exit        result    sth- gt fetch PDO  FETCH OBJ   if  isset  result- gt id email         echo  empty     else       echo  not empty  value is  result- gt id email       db   null    Of course this is only for a single result  as you might have when looping over a dataset

User · Answer

If you have the option of using fetchAll   then if there are no rows returned it will just be and empty array   count  sql- gt fetchAll PDO  FETCH ASSOC     will return the number of rows returned

User · Answer

One more approach to consider   When I build an HTML table or other database-dependent content  usually via an AJAX call   I like to check if the SELECT query returned any data before working on any markup   If there is no data  I simply return  No data found     or something to that effect   If there is data  then go forward  build the headers and loop through the content  etc   Even though I will likely limit my database to MySQL  I prefer to write portable code  so rowCount   is out   Instead  check the the column count  A query that returns no rows also returns no columns    stmt- gt execute     cols    stmt- gt columnCount       no columns    no result set if   cols  gt  0           non-repetitive markup code here     while   row    stmt- gt fetch PDO  FETCH ASSOC

[php] Checking for empty result (php, pdo, mysql)

Examples related to php

Examples related to mysql

Examples related to pdo