Lost String Values in Prepared Statement

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
roofus
Forum Newbie
Posts: 2
Joined: Wed Sep 02, 2009 9:40 pm

Lost String Values in Prepared Statement

Post 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?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Lost String Values in Prepared Statement

Post by requinix »

What if you define $name (doesn't have to have the right value, just has to exist) before the bind_param call?
roofus
Forum Newbie
Posts: 2
Joined: Wed Sep 02, 2009 9:40 pm

Re: Lost String Values in Prepared Statement

Post 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?
Post Reply