Re: Prepared Statements - Search

On 12/3/2012 2:30 PM, Ethan Rosenberg, PhD wrote:

Dear List - I am trying to use prepared statements with the following code:

                 $allowed_fields = array
                     ('Cust_Num' => 'i',  'Fname' => 's', 'Lname' =>

's', 'Street' => 's','City'=> 's', 'State' => 's', 'Zip' => 'i',

                     'Phone' => 'i', 'Notes' => 's'
                     );

                 if(empty($allowed_fields))
                 {
                      echo "ouch";
                 }

                 // Configure the query and the acceptable params to put

into the WHERE clause

                 $sql12 = 'SELECT * FROM Customers WHERE 1';

                // Magically put everything together
                 $types = '';
                 $args = array();
                 foreach( $allowed_fields as $k => $type )
                 {
                  if( !array_key_exists( $k, $allowed_fields ) )
                         continue;
                     else
                     {
                         if( ($_POST[$k]) != '')
                         {
                             $args[] = &$_POST[$k]; // Note the addition

of the ampersand here

                             $types .= $type;
                             $sql12 .= " AND ($k = ?)";
                         }
                     }
                 }

                 $stmt = mysqli_stmt_init($cxn);
                 mysqli_stmt_prepare( $stmt, $sql12 );

The search fails. This debug code:

                 echo "For debugging and demonstration #1<br />";
                 echo 'Query: ' . $sql12 . PHP_EOL;
                 echo 'Bind types: ' . $types . PHP_EOL;
                 echo "arguments<br />";
                 print_r($args);

gives the following results: For debugging and demonstration #1 Query: SELECT * FROM Customers WHERE 1 AND (Fname = ?) AND (Lname = ?) AND (Street = ?) AND (City = ?) AND (State = ?) AND (Zip = ?) AND (Phone = ?) Bind types: sssssii arguments Array (

     [0] =>
     [1] =>
     [2] =>
     [3] =>
     [4] =>
     [5] =>
     [6] =>  845745745

) If I search the database from the command line, these are the results - mysql> select * from Customers where Phone=845745745; +----------+---------+--------------+--------+--------+-------+-------+-----------+------------+---------------------+------+------+------+

| Cust_Num | Fname   | Lname        | Street | City   | State | Zip   |
Phone     | Date       | Notes               | P1   | P2   | P3   |

+----------+---------+--------------+--------+--------+-------+-------+-----------+------------+---------------------+------+------+------+

|    10016 | okuibtg | uymkibtvgfrc | p7tvgf | Monsey | NY    |   127 |

845745745 | 2012-12-01 | tvgfuyholkijuhy | NULL | NULL | NULL |

|    10017 | okuibtg | uymkibtvgfrc | p7tvgf | Monsey | NY    | 10952 |

845745745 | 2012-12-01 | tvgfuyholkijuhy | NULL | NULL | NULL |

|    10018 | okuibtg | uymkibtvgfrc | p7tvgf | Monsey | NY    | 32767 |

845745745 | 2012-12-02 | tvgfuyholkijuhy | NULL | NULL | NULL | +----------+---------+--------------+--------+--------+-------+-------+-----------+------------+---------------------+------+------+------+ 3 rows in set (0.00 sec) This is the output routine:

                 if(count($errors_array) == 0)
                 {
     ?>

                         <center><b>Search Results</b></center><br />
                             <center>
                         <table border="4" cellpadding="5"

cellspacing="55" rules="all" frame="box" style="table-layout: fixed;">

                         <tr class="heading">
                         <th>Cust_Num</th>
                         <th>First Name</th>
                         <th>Last Name</th>
                         <th>Street</th>
                         <th>City</th>
                         <th>State</th>
                         <th>Zip</th>
                         <th>Phone</th>
                         <th>Notes</th>

     <?php             $i = 0;
                     do
                     {
                         {

                             $vara2 = array(array($Cust_Num, $Fname,

$Lname, $Street, $City, $State, $Zip, $Phone, $Notes));

                             $vara2[$i][0]    = $Cust_Num;
                             $vara2[$i][1]    = $Fname;
                             $vara2[$i][2]    = $Lname;
                             $vara2[$i][3]    = $Street;
                             $vara2[$i][4]    = $City;
                             $vara2[$i][5]    = $State;
                             $vara2[$i][6]    = $Zip;
                             $vara2[$i][7]    = $Phone;
                             $vara2[$i][8]    = Notes;

                             $_SESSION['exe'] = 2;
     ?>

                             <tr>
                             <td>  <?php echo $vara2[$i][0]?>  </td>
                             <td>  <?php echo $vara2[$i][1]?>  </td>
                             <td>  <?php echo $vara2[$i][2]?>  </td>
                             <td>  <?php echo $vara2[$i][3]?>  </td>
                             <td>  <?php echo $vara2[$i][4]?>  </td>
                             <td>  <?php echo $vara2[$i][5]?>  </td>
                             <td>  <?php echo $vara2[$i][6]?>  </td>
                              <td>  <?php echo $vara2[$i][7]?>  </td>
                             <td class="first-col"><?php echo

$vara2[$i][8] ?></td>

     <?php                     echo "</tr>\n";
                                 $i = $i + 1;
                             }
                     } while (mysqli_stmt_fetch($stmt)); //end do-while
                                 $imax = $i;
                                 echo "</table>";
                                 echo "</center>";
                                 echo "</form>";

Help and advice, please. Ethan Yu say the statement fails. Did you trap the execution of it and do you have an error message to show us?

My only other comment is (not having utilized perp-stmts very much at all yet) - what happens when your args are all ?'s? I mean - if you say "where First=? and Phone=?" what happens when you don't supply all the ? with values? My interpretation of the query would be that it ends up looking like this: "Where First='' and Phone=845745745", which is probably not the query you want to be running. BTW - a phone number is not really easily handled when stored as an integer. I always store them as strings so I can easily break them apart or mask them.