How can I properly use a PDO object for a parameterized SELECT query

Question

I ve tried following the PHP net instructions for doing SELECT queries but I am not sure the best way to go about doing this   I would like to use a parameterized SELECT query  if possible  to return the ID in a table where the name field matches the parameter  This should return one ID because it will be unique   I would then like to use that ID for an INSERT into another table  so I will need to determine if it was successful or not   I also read that you can prepare the queries for reuse but I wasn t sure how this helps

User · Answer

You can use the bindParam or bindValue methods to help prepare your statement   It makes things more clear on first sight instead of doing  check- gt execute array   name    gt   name    Especially if you are binding multiple values variables   Check the clear  easy to read example below    q    db- gt prepare  SELECT id FROM table WHERE forename    forename and surname    surname LIMIT 1     q- gt bindValue   forename    Joe     q- gt bindValue   surname     Bloggs     q- gt execute     if   q- gt rowCount    gt  0        check    q- gt fetch PDO  FETCH ASSOC        row id    check  id           do something     If you are expecting multiple rows remove the LIMIT 1 and change the fetch method into fetchAll    q    db- gt prepare  SELECT id FROM table WHERE forename    forename and surname    surname      removed limit 1  q- gt bindValue   forename    Joe     q- gt bindValue   surname     Bloggs     q- gt execute     if   q- gt rowCount    gt  0        check    q- gt fetchAll PDO  FETCH ASSOC          check will now hold an array of returned rows         let s say we need the second result  i e  index of 1      row id    check 1   id            do something

User · Answer

if you are using inline coding in single page and not using oops than go with this full example  it will sure help    connect to the db  dbh   new PDO  mysql host localhost dbname mydb   dbuser  dbpw       build the query  query  SELECT field1  field2 FROM ubertable WHERE field1  gt  6969      execute the query  data    dbh- gt query  query     convert result resource to array  result    data- gt fetchAll PDO  FETCH ASSOC      view the entire array  for testing  print r  result      display array elements foreach  result as  output    echo output field1          output field1      lt br   gt

User · Answer

A litle bit complete answer is here with all ready for use        sql    SELECT  username  FROM  users  WHERE  id     id        q    dbh- gt prepare  sql        q- gt execute array   id    gt   4          done   q- gt fetch      echo  done 0     Here  dbh is PDO db connecter  and based on id from table users we ve get the username using fetch     I hope this help someone  Enjoy

User · Answer

Method 1 USE PDO query method   stmt    db- gt query  SELECT id FROM Employee where name      name        results    stmt- gt fetchAll PDO  FETCH ASSOC     Getting Row Count   stmt    db- gt query  SELECT id FROM Employee where name      name        row count    stmt- gt rowCount    echo  row count   rows selected     Method 2  Statements With Parameters   stmt    db- gt prepare  SELECT id FROM Employee WHERE name       stmt- gt execute array  name     rows    stmt- gt fetchAll PDO  FETCH ASSOC     Method 3 Bind parameters       stmt    db- gt prepare  SELECT id FROM Employee WHERE name       stmt- gt bindValue 1   name  PDO  PARAM STR    stmt- gt execute     rows    stmt- gt fetchAll PDO  FETCH ASSOC      bind with named parameters    stmt    db- gt prepare  SELECT id FROM Employee WHERE name  name     stmt- gt bindValue   name    name  PDO  PARAM STR    stmt- gt execute     rows    stmt- gt fetchAll PDO  FETCH ASSOC    or  stmt    db- gt prepare  SELECT id FROM Employee WHERE name  name     stmt- gt execute array   name    gt   name     rows    stmt- gt fetchAll PDO  FETCH ASSOC     Want to know more look at this link

User · Answer

You select data like this    db   new PDO          statement    db- gt prepare  select id from some table where name    name     statement- gt execute array   name    gt   Jimbo      row    statement- gt fetch       Use fetchAll   if you want all results  or just iterate over the statement  since it implements Iterator   You insert in the same way    statement    db- gt prepare  insert into some other table  some id  values   some id      statement- gt execute array   some id    gt   row  id        I recommend that you configure PDO to throw exceptions upon error  You would then get a PDOException if any of the queries fail - No need to check explicitly  To turn on exceptions  call this just after you ve created the  db object    db   new PDO          db- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION

User · Answer

I ve been working with PDO lately and the answer above is completely right  but I just wanted to document that the following works as well    nametosearch    Tobias    conn   new PDO  server    username    password     conn- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION    sth    conn- gt prepare  SELECT  id  from  tablename  WHERE  name     name     sth- gt bindParam   name    nametosearch      Or sth- gt bindParam   name     POST  namefromform     depending on application  sth- gt execute

[php] How can I properly use a PDO object for a parameterized SELECT query

Examples related to php

Examples related to mysql

Examples related to select

Examples related to pdo