SELECT FROM in MySQLi

Question

My site is rather extensive  and I just recently made the switch to PHP5  call me a late bloomer    All of my MySQL query s before were built as such    SELECT   FROM tablename WHERE field1    value   amp  amp  field2    value2      This made it very easy  simple and friendly   I am now trying to make the switch to mysqli for obvious security reasons  and I am having a hard time figuring out how to implement the same SELECT   FROM queries when the bind param requires specific arguments   Is this statement a thing of the past   If it is  how do I handle a query with tons of columns involved  Do I really need to type them all out every time

User · Answer

Is this statement a thing of the past    Yes  Don t use SELECT    it s a maintenance nightmare  There are tons of other threads on SO about why this construct is bad  and how avoiding it will help you write better queries   See also    What is the reason not to use select    Performance issue in using SELECT    Why is using   39    39  to build a view bad

User · Answer

I was looking for a nice and complete example of how to bind multiple query parameters dynamically to any SELECT  INSERT  UPDATE and DELETE query  Alec mentions in his answer a way of how to bind result  for me the get result   after execute   function for SELECT queries works just fine  and am able to retrieve all the selected results into an array of associative arrays   Anyway  I ended up creating a function where I am able to dynamically bind any amount of parameters to a parametrized query   using call user func array function  and obtain a result of the query execution  Below is the function with its documentation  please read before it before using - especially the  paremetersTypes - Type specification chars parameter is important to understand                   Prepares and executes a parametrized QUERY  SELECT  INSERT  UPDATE  DELETE                 param in   dbConnection mysqli database connection to be used for query execution         param in   dbQuery parametrized query to be bind parameters for and then execute         param in   isDMQ boolean value  should be set to TRUE for  DELETE  INSERT  UPDATE - Data manipulaiton queries   FALSE for SELECT queries         param in   paremetersTypes String representation for input parametrs  types as per http   php net manual en mysqli-stmt bind-param php         param in   errorOut A variable to be passed by reference where a string representation of an error will be present if a FAUILURE occurs         param in   arrayOfParemetersToBind Parameters to be bind to the parametrized query  parameters need to be specified in an array in the correct order          return array of feched records associative arrays for SELECT query on SUCCESS  TRUE for INSERT  UPDATE  DELETE queries on SUCCESS  on FAIL sets the error and returns NULL              function ExecuteMySQLParametrizedQuery  dbConnection   dbQuery   isDMQ   paremetersTypes   amp  errorOut   arrayOfParemetersToBind                 stmt    dbConnection- gt prepare  dbQuery             outValue   NULL           if   stmt     FALSE               errorOut    Failed to prepare statement for query       dbQuery          else if   call user func array array  stmt   bind param    array merge array  paremetersTypes    arrayOfParemetersToBind       FALSE               errorOut    Failed to bind required parameters to query       dbQuery        parameters      json encode  arrayOfParemetersToBind           else if    stmt- gt execute                 errorOut    Failed to execute query   dbQuery    erorr      stmt- gt error          else                       if   isDMQ                  outValue   TRUE              else                                result    stmt- gt get result                     if   result     FALSE                         errorOut    Failed to obtain result from statement for query      dbQuery                  else                      outValue    result- gt fetch all MYSQLI ASSOC                                     stmt- gt close             return  outValue          usage        param1    128989        param2    some passcode          insertQuery    INSERT INTO Cards  Serial  UserPin  VALUES               rowsInserted   ExecuteMySQLParametrizedQuery  dbConnection   insertQuery  TRUE   ss    errorOut  array  amp  param1   amp  param2        Make sure the parameters in an array are passed by reference      if   rowsInserted     NULL          echo  error      errorOut      else         echo  successfully inserted row          selectQuery    SELECT CardID FROM Cards WHERE Serial like   AND UserPin like          arrayOfCardIDs   ExecuteMySQLParametrizedQuery  dbConnection   selectQuery  FALSE   ss    errorOut  array  amp  param1   amp  param2        Make sure the parameters in an array are passed by reference      if   arrayOfCardIDs     NULL           echo  error      errorOut      else               echo  obtained result array of     count  arrayOfCardIDs     selected rows            if  count  arrayOfCardIDs   gt  0               echo  obtained card id        arrayOfCardIDs 0   CardID

User · Answer

You can still use it  mysqli is just another way of communicating with the server  the SQL language itself is expanded  not changed   Prepared statements are safer  though - since you don t need to go through the trouble of properly escaping your values each time  You can leave them as they were  if you want to but the risk of sql piggybacking is reduced if you switch

User · Answer

you can use get result   on the statement   http   php net manual en mysqli-stmt get-result php

User · Answer

This was already a month ago  but oh well   I could be wrong  but for your question I get the feeling that bind  95 param isn t really the problem here  You always need to define some conditions  be it directly in the query string itself  of using bind  95 param to set the   placeholders  That s not really an issue   The problem I had using MySQLi SELECT   queries is the bind result part  That s where it gets interesting  I came across this post from Jeffrey Way  http   jeff-way com 2009 05 27 tricky-prepared-statements  This link is no longer active   The script basically loops through the results and returns them as an array     no need to know how many columns there are  and you can still use prepared statements   In this case it would look something like this    stmt    mysqli- prepare     SELECT   FROM tablename WHERE field1     AND field2         stmt- bind param  ss    value   value2    stmt- execute     Then use the snippet from the site    meta    stmt- result metadata     while   field    meta- fetch field         parameters       row  field- name      call user func array array  stmt   bind result     parameters    while   stmt- fetch        foreach  row as  key     val         x  key     val         results      x     And  results now contains all the info from SELECT    So far I found this to be an ideal solution

User · Answer

SELECT   FROM tablename WHERE field1    value   amp  amp  field2    value2      becomes   SELECT   FROM tablename WHERE field1      amp  amp  field2         which is passed to the  mysqli  prepare    stmt    mysqli- gt prepare     SELECT   FROM tablename WHERE field1      amp  amp  field2         stmt- gt bind param   ss    value   value2        ss  is a format string  each  s  means string  stmt- gt execute      stmt- gt bind result  col1   col2      then fetch and close the statement   OP comments      so if i have 5 parameters  i could potentially have  sssis  or something  depending on the types of inputs      Right  one type specifier per   parameter in the prepared statement  all of them positional  first specifier applies to first   which is replaced by first actual parameter  which is the second parameter to bind param     mysqli will take care of escaping and quoting  I think

User · Answer

While you are switching  switch to PDO instead of mysqli  It helps you write database agnositc code and have better features for prepared statements   http   www php net pdo  Bindparam for PDO  http   se php net manual en pdostatement bindparam php   sth    dbh- gt prepare  SELECT   FROM tablename WHERE field1    value1  amp  amp  field2    value2     sth- gt bindParam   value1    foo     sth- gt bindParam   value2    bar     sth- gt execute      or     sth    dbh- gt prepare  SELECT   FROM tablename WHERE field1      amp  amp  field2         sth- gt bindParam 1   foo     sth- gt bindParam 2   bar     sth- gt execute      or execute with the parameters as an array    sth    dbh- gt prepare  SELECT   FROM tablename WHERE field1    value1  amp  amp  field2    value2     sth- gt execute array   value1    gt   foo      value2    gt   bar       It will be easier for you if you would like your application to be able to run on different databases in the future   I also think you should invest some time in using some of the classes from Zend Framwework whilst working with PDO  Check out their Zend Db and more specifically  Zend Db Factory  2   You do not have to use all of the framework or convert your application to the MVC pattern  but using the framework and reading up on it is time well spent

[php] SELECT * FROM in MySQLi

Examples related to php

Examples related to mysqli