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
