How to use PDO to fetch results array in PHP

Question

I m just editing my search script after reading up on SQL injection attacks  I m trying to get the same functionality out of my script using PDO instead of a regular mysql connection  So I ve been reading other posts about PDO but am unsure  Will these two scripts give the same functionality   With PDO    pdo   new PDO  mysql host  host  dbname  database     user   pass    stmt    pdo- gt prepare  SELECT   FROM auction WHERE name    name     stmt- gt bindParam   name     GET  searchdivebay      stmt- gt execute array   name    gt   name     With regular mysql    dbhost    mysql connect  host   user   pass  or die  Unable to connect to server      mysql select db  divebay   or die  Unable to select database     search     GET  searchdivebay     query   trim  search     sql    SELECT   FROM auction WHERE name LIKE        query            if  isset  query    echo  Your search was invalid   exit      line 18   result   mysql query  trim    numrows   mysql num rows  result   mysql close  dbhost     I go on with the regular example to use  while  i  lt   numrows    row   mysql fetch array  result     to create an array of matching results from the database  How do I do this with PDO

User · Accepted Answer

Take a look at the PDOStatement fetchAll method  You could also use fetch in an iterator pattern   Code sample for fetchAll  from the PHP documentation    lt  php  sth    dbh- gt prepare  SELECT name  colour FROM fruit     sth- gt execute        Fetch all of the remaining rows in the result set    print  Fetch all of the remaining rows in the result set  n     result    sth- gt fetchAll  PDO  FETCH ASSOC   print r  result     Results   Array        0    gt  Array                        NAME    gt  pear              COLOUR    gt  green                 1    gt  Array                        NAME    gt  watermelon              COLOUR    gt  pink

User · Answer

There are three ways to fetch multiple rows returned by PDO statement  The simplest one is just to iterate over PDOStatement itself   stmt    pdo- gt prepare  quot SELECT   FROM auction WHERE name LIKE   quot    stmt- gt execute array  quot   query  quot        iterating over a statement foreach  stmt as  row        echo  row  name       another one is to fetch rows using fetch   method inside a familiar while statement   stmt    pdo- gt prepare  quot SELECT   FROM auction WHERE name LIKE   quot    stmt- gt execute array  quot   query  quot        using while while  row    stmt- gt fetch          echo  row  name       but for the modern web application we should have our datbase iteractions separated from output and thus the most convenient method would be to fetch all rows at once using fetchAll   method   stmt    pdo- gt prepare  quot SELECT   FROM auction WHERE name LIKE   quot    stmt- gt execute array  quot   query  quot        fetching rows into array  data    stmt- gt fetchAll     or  if you need to preprocess some data first  use the while loop and collect the data into array manually  result        stmt    pdo- gt prepare  quot SELECT   FROM auction WHERE name LIKE   quot    stmt- gt execute array  quot   query  quot        using while while  row    stmt- gt fetch           result                newname    gt   row  oldname               etc           and then output them in a template   lt ul gt   lt  php foreach  data as  row     gt       lt li gt  lt    row  name    gt  lt  li gt   lt  php endforeach   gt   lt  ul gt   Note that PDO supports many sophisticated fetch modes  allowing fetchAll   to return data in many different formats

User · Answer

st    data- gt prepare  SELECT   FROM exampleWHERE example LIKE  search LIMIT 10

[php] How to use PDO to fetch results array in PHP?

Examples related to php

Examples related to mysql

Examples related to pdo