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.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
