[PHP-DB] Prepared Statements - Search -- SOLVED!!!

[Ethan Rosenberg, PhD]

Dear List -

-->> THANKS TO ALL. See below <--
Here is another problem I am having with prepared statements.  The last 
one was  INSERT, this one is SELECT.

Here is the database:

mysql> describe Intake3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| Site   | varchar(6)  | NO   | PRI |         |       |
| MedRec | int(6)      | NO   | PRI | NULL    |       |
| Fname  | varchar(15) | YES  |     | NULL    |       |
| Lname  | varchar(30) | YES  |     | NULL    |       |
| Phone  | varchar(30) | YES  |     | NULL    |       |
| Height | int(4)      | YES  |     | NULL    |       |
| Sex    | char(7)     | YES  |     | NULL    |       |
| Hx     | text        | YES  |     | NULL    |       |
| Bday   | date        | YES  |     | NULL    |       |
| Age    | int(3)      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

Here is my code:

// Prepare statement
    $stmt = mysqli_stmt_init($cxn);
    $sql11 = "SELECT  'Fname', 'Lname', 'Phone', Height, Hx, Bday, Age 
FROM Intake3 where 1 and (MedRec = ?) and (Site = ?) and (Sex = ?)";
// Allocates and initializes a statement object suitable for 
mysqli_stmt_prepare().
// Prepare statement, bind result variables, execute and place results 
into bound result variables
   mysqli_stmt_prepare($stmt, $sql11);
   mysqli_stmt_execute($stmt);
   mysqli_stmt_bind_result($stmt, $Site, $MedRec, $Fname, $Lname, 
$Phone, $Height, $Sex, $Hx, $Bday, $Age); //The error is in this statement.
    while (mysqli_stmt_fetch($stmt)) {
        printf("%s %s %s %s %s %s %s %s %s %s \n", $Site, $MedRec, 
$Fname, $Lname, $Phone, $Height, $Sex, $Hx, $Bday, $Age);
    }

I get no output from the printf statement.

I receive the following error:

Warning: mysqli_stmt_bind_result(): Number of bind variables doesn't 
match number of fields in prepared statement.

The query, with the values inserted, works on the command line

Help and advice, please.

Ethan

***********


Here is my revised code:

// Prepare statement
    $stmt = mysqli_stmt_init($cxn);
    $sql11 = "SELECT  MedRec, Site, Sex,Fname, Lname, Phone, Height, 
Hx, Bday, Age FROM Intake3 where 1 and (MedRec = ?) and (Site = ?) and 
(Sex = ?)";
/* Allocates and initializes a statement object suitable for 
mysqli_stmt_prepare(). */
/* Prepare statement, bind result variables, execute and place results 
into bound result variables */
    mysqli_stmt_prepare($stmt, $sql11);
    mysqli_stmt_bind_param($stmt, 'iss', $_POST['MedRec'], 
$_POST['Site'], $_POST['Sex']);
      mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $MedRec, $Site, $Sex, $Fname, 
$Lname, $Phone, $Height, $Hx, $Bday, $Age);
    while (mysqli_stmt_fetch($stmt)) {
        printf("%s %s %s %s %s %s %s %s %s %s \n", $Site, $MedRec, 
$Fname, $Lname, $Phone, $Height, $Sex, $Hx, $Bday, $Age);
    }

Added the bind_parameters statement and it worked.  Stupid me - you 
can’t perform a query unless the parameters have been inserted into the 
query.

Live and learn.


Ethan

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