Re: Prepared Statements - Search
Jim Giner <[email protected]>
| Newsgroups | gmane.comp.php.database |
|---|---|
| Message-ID | <[email protected]> |
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