Crud, forgot to include list.
---------- Forwarded message ----------
From: tamouse mailing lists <tamouse.lists@xxxxxxxxx>
Date: Thu, Sep 13, 2012 at 10:31 PM
Subject: Re: [PHP-DB] Prepared Statements - Search
To: erosenberg@xxxxxxxxxxxxxxxxxxxx
On Thu, Sep 13, 2012 at 7:09 PM, Ethan Rosenberg, PhD
<erosenberg@xxxxxxxxxxxxxxxxxxxx> wrote:
> Dear List -
>
> 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.
Pay attention to the code you write.
Your SQL SELECT Statement pulls in these fields, in this order:
'Fname'
'Lname'
'Phone'
Height
Hx
Bday
Age
Got that?
Your bind statement binds the following variables:
$Site
$MedRec
$Fname
$Lname
$Phone
$Height
$Sex
$Hx
$Bday
$Age
So, pairing those up, this is the relationship the bind statement sees:
Column -> PHP variable
----------------------
'Fname' -> $Site
'Lname' -> $MedRec
'Phone' -> $Fname
Height -> $Lname
Hx -> $Phone
Bday -> $Height
Age -> $Sex
and oops, out of columns to bind to the remaining items:
??? -> $Hx
??? -> $Bday
??? -> $Age
So, lesson is: the bind results statement must match exactly
one-for-one with each column returned in the SELECT statement.
Additionally, I'm really uncertain the effect of the surrounding
single quotes on 'Fname', 'Lname' and 'Phone' in your SELECT
statement; perhaps they don't matter, but I've never seen that. Column
names such as you show do not need to be quoted, as none of them
represent MySQL key/reserved words, and when you do quote field names
in MySQL, you use the backtick, i.e. "`", not a single quote.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
