Prepared Statements - Search

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






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