[PHP-DB] Re: Prepared Statements - Search
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 #1br /;
echo 'Query: ' . $sql12 . PHP_EOL;
echo 'Bind types: ' . $types . PHP_EOL;
echo argumentsbr /;
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: sii
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)
{
?
centerbSearch Results/b/centerbr /
center
table border=4 cellpadding=5
cellspacing=55 rules=all frame=box style=table-layout: fixed;
tr class=heading
thCust_Num/th
thFirst Name/th
thLast Name/th
thStreet/th
thCity/th
thState/th
thZip/th
thPhone/th
thNotes/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
[PHP-DB] Re: Prepared Statements - Search
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 #1br /;
echo 'Query: ' . $sql12 . PHP_EOL;
echo 'Bind types: ' . $types . PHP_EOL;
echo argumentsbr /;
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: sii
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)
{
?
centerbSearch Results/b/centerbr /
center
table border=4 cellpadding=5
cellspacing=55 rules=all frame=box style=table-layout: fixed;
tr class=heading
thCust_Num/th
thFirst Name/th
thLast Name/th
thStreet/th
thCity/th
thState/th
thZip/th
thPhone/th
thNotes/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
Re: [PHP-DB] Re: Prepared Statements - Search
On Mon, Dec 3, 2012 at 3:34 PM, Jim Giner jim.gi...@albanyhandball.com wrote: On 12/3/2012 2:30 PM, Ethan Rosenberg, PhD wrote: $stmt = mysqli_stmt_init($cxn); mysqli_stmt_prepare( $stmt, $sql12 ); The search fails. So, you got us down to where you prepare the sql statement in your code. But where do you actually perform the bind, execute it, and then bind the results? Hard to say anything about what's happening yet. Here's thing. Skip all the constructing of intput. Skip all the formatting code for now. Your *first* step is to make your queries work. *Then* you can make it pretty. Make sure your queries work, your results from the queries are what you want, just var_dump them for now. Take things one step at a time. I'm wondering how many times we're going to see the *SAME CODE* over and over as you progress one painful step at a time and never figure anything out for yourself because you're trying to do too much at once. The things people told you change last time are still in this code. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Prepared Statements - Select - Bind Parameters w/ correction
On 9/27/2012 12:40 PM, Ethan Rosenberg, PhD wrote: Dear list - SEE CORRECTION IN $_POST VARIABLE BELOW. Thanks to all for your help. I hope [??] that this question will solve all the remaining problems. So that we are on the same page, here is what was previously stated. mysqli_stmt_bind_param expects three variables, in this order mysqli_stmt_bind_param($stmt, num, $a, $b, $c) Where stmt is the query w/ the ?? that is SELECT Site, MedRec, Fname, Lname, Phone, Height, Sex, Hx, Bday, Age FROM Intake3 where 1 AND (Site = ?) AND (MedRec = ?) AND (Sex = ?) and num is the number and type of variables is the query, in this case 'sis' $a $b and $c are the variables to be inserted, in this case: $a = $_POST['Site']; $b = $_POST['MedRec']; $c = $_POST['Sex']; As I seem to have found, the variables cannot be a string or components of an imploded array. This is a search function that will take patient supplied data and search the Intake database to determine the Medical Record Number. There are nine variables in the database, and I never know which variables the patient will give. Based on the database, it is easy to set up the correspondence. The database is searched in the order of the correspondence and the letters can be immediately determined... $a = $_POST['Site'] $b = $_POST['MedRec'] $c = $_POST['Fname'] $d = $_POST['Lname'] $e = $_POST['Phone'] $f = $_POST[Height'] $g = $_POST['Sex'] $h = $_POST['Hx'] $i = $_POST['Bday'] $i = $_POST['Age'] - Corrected The challenge is to be able to dynamically select the variables that will go intomysqli_stmt_bind_param. Advice and help, please Ethan Sounds like you are asking for a solution to a programming problem and not a syntax/language problem. If so, it would be better to see what YOU have already tried and ask for help in fixing or improving that. We're not here to do your programming for you - just to help you with programming problems. We all have situations such as this where we spend the time to Solve the problem. At first glance, I'd have my html present a set of checkboxes tied to the arguments, and then display a new, customized screen to ask for and accept the input only for the checked fields. And btw - is that example of your bind statment REALLY the code as it exists in your current script? I think not. Tsk, Tsk. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Prepared Statements Insert Problem
On Sun, Sep 2, 2012 at 10:24 PM, Ethan Rosenberg, PhD erosenb...@hygeiabiomedical.com wrote: mysqli_stmt_bind_result(): Number of bind variables doesn't match number of fields in prepared statement What exactly is unclear about that? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Prepared Statements Insert Problem
On 9/3/2012 2:44 AM, tamouse mailing lists wrote: On Sun, Sep 2, 2012 at 10:24 PM, Ethan Rosenberg, PhD erosenb...@hygeiabiomedical.com wrote: mysqli_stmt_bind_result(): Number of bind variables doesn't match number of fields in prepared statement What exactly is unclear about that? Actually - from looking at the code the OP posted, I don't see the mis-match either, assuming that the post contains the actual code. I do have a question tho. Not being familiar with mysqli yet (soon, I know), I'm wondering what his die clause is actually saying when it mentions: ... mysqli_stmt($stmt) Does the reference to 'mysqli_stmt' mean something special, since it doesn't reference any particular function? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Prepared Statements Insert Problem
On Mon, Sep 3, 2012 at 7:45 AM, Jim Giner jim.gi...@albanyhandball.com wrote: On 9/3/2012 2:44 AM, tamouse mailing lists wrote: On Sun, Sep 2, 2012 at 10:24 PM, Ethan Rosenberg, PhD erosenb...@hygeiabiomedical.com wrote: mysqli_stmt_bind_result(): Number of bind variables doesn't match number of fields in prepared statement What exactly is unclear about that? Actually - from looking at the code the OP posted, I don't see the mis-match either, assuming that the post contains the actual code. That would be a question, considering the OP code was incorrect as it stood. I do have a question tho. Not being familiar with mysqli yet (soon, I know), I'm wondering what his die clause is actually saying when it mentions: ... mysqli_stmt($stmt) Does the reference to 'mysqli_stmt' mean something special, since it doesn't reference any particular function? It seems like it should just give a Fatal error for a call to an undefined function, as mysqli_stmt itself is a class, with no constructor method of it's own, as it's created via the mysql_prepare(). I get a little confused when mixing OO and procedural versions, though, so it might do something... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Prepared Statements Insert Problem
So do u have the revised code to show us? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Prepared Statements Insert Problem
if($stmt = mysqli_stmt_prepare($stmt, INSERT INTO Intake3 (Site, MedRec,
Fname, Lname, Phone, Height, Sex, Hx, Bday, Age)
VALUES(?,?,?,?,?,?,?,?,?,?)!=0)
Let me break this into smaller chunks:
if ($a = $b != 0)
Precedence rules show that comparisons (!= in this case) come before
assignment (=).
So, what you're doing is this adding parens:
if ($a = ($b != 0) )
when, in fact, you want this:
if ( ($a = $b) != 0)
which is syntactically equivalent to:
if ( ($a = $b) )
This is the first part of your problem.
The second part, is that you are assigning to $stmt, which is what you
pass in the mysqli_stmt_prepare function, thus, after you've prepared
the statement, you overwrite it with the return value of the function,
which in procedural context, is either TRUE or FALSE, thus destroying
the work you just performed.
What you need here is:
$sql = INSERT INTO Intake3
(Site, MedRec, Fname, Lname,
Phone, Height, Sex, Hx, Bday, Age)
VALUES (?,?,?,?,?,?,?,?,?,?);
if ( mysqli_stmt_prepare( $stmt, $sql ) )
{
// bind and process the stmt
}
else
{
die('Error occured during statement prepare: ' .
mysqli_stmt($stmt) );
}
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Prepared Statements
Ron Piggott wrote:
I am just starting to use Prepared Statements and am in a learning curve.
I am working on the code below. It is for a directory. This is to select
the listing for updating.
How do I tell if there are more than 1 search result?
Am I correctly retrieving the results, if there is more than 1?
I want to make a WHILE loop to display the search results for the listing
the user is editing. (You can see where I have started the ul ... /ul
... I want that area in the loop)
Ron
$dbh = new PDO($dsh, $username, $password);
$stmt = $dbh-prepare(SELECT `reference`, `organization`, `city`,
`province_state`, `postal_zip_code`, `country` FROM `ministry_profiles`
WHERE ( `reference` = :organization_reference ) OR ( `organization` LIKE
%:organization_name% ) OR ( `telephone` LIKE %:organization_phone% ) OR (
`toll_free` LIKE %:organization_toll_free_phone% ) ORDER BY `organization`
ASC);
$stmt-bindParam(':organization_reference', $organization_reference,
PDO::PARAM_STR); $stmt-bindParam(':organization_name',
$organization_name, PDO::PARAM_STR);
$stmt-bindParam(':organization_phone', $organization_phone,
PDO::PARAM_STR); $stmt-bindParam(':organization_toll_free_phone',
$organization_phone, PDO::PARAM_STR);
$stmt-execute();
$result = $stmt-fetch(PDO::FETCH_ASSOC);
echo ul\r\n;
$search_result_organization_reference = $result['reference'];
$search_result_organization = $result['organization'];
$search_result_city = $result['city'];
$search_result_province_state = $result['province_state'];
$search_result_postal_zip_code = $result['postal_zip_code'];
$search_result_country = $result['country'];
echo listrong . $search_result_organization . /strong (Ref:
. $search_result_organization_reference . )br /\r\n;
echo $search_result_city . , . $search_result_province_state . .
$search_result_country . . $search_result_postal_zip_code .
/li\r\n;
echo /ul\r\n;
The docs are a good source of information. For instance, example 2 on
http://php.net/manual/en/pdostatement.fetch.php looks to be one solution.
Or http://php.net/manual/en/pdostatement.fetchall.php which gives you an
array of all the result set rows.
Cheers
--
David Robley
To shoot a mime, do you use a silencer?
Today is Pungenday, the 43rd day of Chaos in the YOLD 3177.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Prepared Statements and mySQL
Ron Piggott wrote:
I am trying to implement Prepared Statements on my web site and this is
the first time I have ever used this.
#1) I received the error ?class mysql not defined?. That is a reference
#to the first line of code (below) where the database connection is
#established.
#2) How do I assign the auto_increment value to a variable use Prepared
#Statements? In the syntax I am attempting below ?record? is an
#auto_increment column.
#3) Do syntaxes such as ?$stmt-bindParam(':account_suspended',
#-00-00);? require the date -00-00 to be surrounded by ? ?
Thank you to the many of you who have supported me this year when I have
had questions. I am physically handicapped, although I don?t want to make
a big deal about it. I have appreciated the opportunity to continue
developing my PHP / mySQL programming skills in 2010. The Internet is a
life line to me.
Ron
$dbh = new mysql('localhost', '$username', '$password', '$database2');
$stmt = $dbh-prepare(INSERT INTO `$database2`.`member` ( `record` ,
`first_name` , `last_name` , `address_1` , `address_2` , `address_3` ,
`address_4` , `address_5` , `email` , `prayer_community_alias` ,
`birth_month` , `birth_day` , `pass` , `validated` , `last_login` ,
`last_activity` , `birthday_records` , `greeting_reference` ,
`registration_ip_address`, `account_created` , `account_suspended` ,
`account_closed` , `referral_source` , `friends_of_ministry_package` ,
`security_question_1` , `security_answer_1` , `security_question_2` ,
`security_answer_2` , `security_question_3` , `security_answer_3` ) VALUES
( NULL , ':f1', ':l1', '', '', '', '', '', ':e1', '', ':birth_month',
':birth_day', ':validate_password', ':validated', ':last_login',
':last_activity', ':birthday_records', ':greeting_reference',
':registration_ip_address', ':account_created', ':account_suspended',
':account_closed', ':referral_source', ':friends_of_ministry_package',
':security_question_1', '', ':security_question_2', '',
':security_question_3', '' ) ON DUPLICATE KEY UPDATE `validated` =
':validated', `pass` = ':validate_password', `account_suspended` =
':account_suspended', `account_closed` = ':account_closed',
`last_activity` = ':last_activity', `registration_ip_address` =
':registration_ip_address';);
$stmt-bindParam(':f1', $f1);
$stmt-bindParam(':l1', $l1);
$stmt-bindParam(':e1', $e1);
$stmt-bindParam(':birth_month', 0);
$stmt-bindParam(':birth_day', 0);
$stmt-bindParam(':validate_password', $validate_password);
$stmt-bindParam(':validated', 5);
$stmt-bindParam(':last_login', $todays_date);
$stmt-bindParam(':last_activity', $todays_date);
$stmt-bindParam(':birthday_records', 15);
$stmt-bindParam(':security_question_1', 0);
$stmt-bindParam(':greeting_reference', 0);
$stmt-bindParam(':registration_ip_address', $registration_ip_address);
$stmt-bindParam(':account_created', $todays_date);
$stmt-bindParam(':account_suspended', -00-00);
$stmt-bindParam(':account_closed', -00-00);
$stmt-bindParam(':referral_source', 2);
$stmt-bindParam(':friends_of_ministry_package', 0);
$stmt-bindParam(':security_question_1', $security_question_1);
$stmt-bindParam(':security_question_2', $security_question_2);
$stmt-bindParam(':security_question_3', $security_question_3);
// insert one row
$stmt-execute();
$stmt-close();
The Verse of the Day
?Encouragement from God?s Word?
http://www.TheVerseOfTheDay.info
#1) I think you re confusing mysql and mysqli
#2) Normal procedure is to eithr assign NULL to the auto-increment field, or
not reference it; I imagine the same applies for prepared statements
#3) Dates are entered aas strings, so need to be quoted as strings
Disclaimer; I have also not used prepared statements, but have looked
quickly at the mysqli docs :-)
Cheers
--
David Robley
My software never has bugs. It just develops random features.
Today is Boomtime, the 70th day of The Aftermath in the YOLD 3176.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Prepared statements via mssql extension
Gerard Samuel wrote: Is it possible? Just checking... It's not unless you use stored procedures. It'd be nice ... :) Stored procedures via mssql_init(), mssql_bind() and mssql_execute() work quite well, though. Read the user comments in the manual as they will help you get over some common gotchas. Cheers, Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
