Re: Prepared Statements - Search

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





What is your question?

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux