Page 1 of 1

Lost String Values in Prepared Statement

Posted: Wed Sep 02, 2009 10:10 pm
by roofus

Code: Select all

// table 'names' contains 2 fields
// 'name_id' is PK and Auto-Inc, 'name' is varchar
 
<?php
$user="foo";
$password="bar";
$database="mydb";
 
$mysqli= new mysqli('localhost',$user,$password,$database);
 
/* check connection */
if (mysqli_connect_errno()) {
   printf("Connection failed: %s\n", mysqli_connect_error());
   exit();
}
else {
   echo "connected to $database<br>";
}
 
//$stmt = $mysqli->stmt_init; ?? Doesn't seem to help
$stmt=$mysqli->prepare("INSERT INTO names VALUES (?)");
//also tried like "INSERT INTO names (name_ID,name) VALUES ('',?)");
$stmt->bind_param("s", $name);
 
$name="Joe";
 
$stmt->execute() or die("Error: ".$stmt->error);
printf("%d row inserted: %d - %s<br>",$stmt->affected_rows,$mysqli->insert_id,$name);
 
$name = "Bob";
 
$stmt->execute() or die("Error: ".$stmt->error);
printf("%d row inserted: %d - %s<br>",$stmt->affected_rows,$mysqli->insert_id,$name);
 
$name = "Jim";
 
$stmt->execute() or die("Error: ".$stmt->error);
printf("%d row inserted: %d - %s<br>",$stmt->affected_rows,$mysqli->insert_id,$name);
 
$stmt->close();
 
$mysqli->close();
?>
This (simplified) code inserts blank rows into the database. My results are:
row[1]= id=1, name= (blank)
row[2]= id=2, name= (blank)
row[3]= id=3, name= (blank)

I have tried numerous (or die) error catches and printf() to watch the results... the variables appear to hold their values throughout the loops, but the strings are not passing through to the prepared statement. The execute() succeeds, but the values don't ride along.

Note: my "real" code is meant to loop through a large CSV file and insert each record into the DB- the ID is auto-increment and I want to define, say, 5 fields for each record, out of a possible 15 or so that extist in the table.

So, the "real"

Code: Select all

$sql=INSERT INTO table (a,b,c) VALUES (?,?,?)
syntax will contain multiple fields, but no PK or auto-inc.

The OO approach and prepared statements make a lot of sense to me for security, re-use, etc... but... perhaps this is a job for a procedure?

Why don't the string values pass through to the table?

Re: Lost String Values in Prepared Statement

Posted: Wed Sep 02, 2009 10:18 pm
by requinix
What if you define $name (doesn't have to have the right value, just has to exist) before the bind_param call?

Re: Lost String Values in Prepared Statement

Posted: Thu Sep 03, 2009 12:26 am
by roofus
I just tried...

Code: Select all

$stmt=$mysqli->prepare("INSERT INTO names VALUES ('',?)");
$name="Joe";
$stmt->bind_param("s", $name);
 
$name="Joe";
 
$stmt->execute() or die("Error: ".$stmt->error);
printf("%d row inserted: %d - %s<br>",$stmt->affected_rows,$mysqli->insert_id,$name);
...and so on.

My web output looks OK...
connected to mydb
1 row inserted: 40 - Joe
1 row inserted: 41 - Bob
1 row inserted: 42 - Jim
But my DB still looks like this:
40 --NOTHING!--
41 --NOTHING!--
42 --NOTHING!--
PS: I have tried initializing the variables to blank strings, too... no dice.

Could there be some php or MySQL setting controlled by my web host which is not allowing procedures to be stored?

Could the ("INSERT INTO table (a,b) VALUES (?,?)") syntax be improper? (despite the fact that I've seen others describe using this syntax successfully?) What alternative might I be able to use?