Lost String in Prepared Statement

[roofus]

My strings are disappearing... I have poured over tutorials and manuals and help, and everything I have read tells me that this should work.  This prepared statement trick sure would be nice...

 

Am I missing a quote or something?  The code inserts a record into the database, but the variable contents disappear!  PS: this is a condensed example, I am trying to accomplish something more complicated... but if this won't work...

 

Thanks in advance for any help!

 

Q: Must I use a result/free result in conjunction with an insert, or do I have to $stmt->close() on each?

 

// 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 (?)");
$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();
?>

[PFMaBiSmAd]

but the variable contents disappear!

Your going to have to explain exactly what that means? Nothing is inserted? An empty row is inserted? You are getting an error? Your printf() is displaying something unexpected? What exactly do you see in front of you that would tell us what that statement means?

 

And the "INSERT INTO names VALUES (?)" syntax is only valid when you supply a value for all the columns, otherwise you must use the INSERT INTO tbl_name (col_name,...) VALUES ({expr | DEFAULT},...) syntax.

[roofus]

Empty rows I guess you would say- (sorry, I am a rookie)

 

For example, I get...

 

id:1  name: (blank)

id:2  name: (blank)

 

and so on.

 

No errors running the code I posted- in fact I have tried other error handling and confirmations such as affected_rows() which all return positive numbers. (example below).

 

Perhaps the syntax issue you describe is the trouble- although, I am not familiar with the curly braces and the "| DEFAULT" part...  My end goal, in fact, is to insert a record(with 5 defined fields) into a table with 15 or so possible fields.

 

Here's a related, more complex piece of my code that started this problem:

(please excuse the mess)

 

$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>";
}

if ($stmt = $mysqli->stmt_init()){
   $sql= "INSERT INTO customers (name, street, city, state) VALUES (?,?,?,?)";
}

else {
   echo "Init problem".$mysqli->error;
   exit;
}

if ($stmt = $mysqli->prepare($sql)){
echo $stmt->param_count." parameters<br>";
if (!$stmt){
   echo "error: ".$mysqli->error;
   exit;
}
if (!$stmt->bind_param("ssss", $name,$street,$city,$state)){
   printf("Bind error: %s<br>", $mysqli->error);
}
echo $stmt->param_count." parameters<br>";

$name = 'Joe';
$street = 'mystreet';
$city = 'Westerville';
$state = 'OH';

/* execute prepared statement */
$stmt->execute() or die("Error: ".$stmt->error);
printf("%s,%s,%s,%d Row inserted.<br>",$street,$city,$state,$stmt->affected_rows);
printf("Inserted row: %d <br>",$mysqli->insert_id);
$one=$mysqli->insert_id;

$name = 'Bob';
$street = 'street3';
$city = 'Westerville3';
$state = 'OH';

/* execute prepared statement */
$stmt->execute() or die("Error: ".$stmt->error);
printf("%s,%s,%s,%d Row inserted.<br>",$street,$city,$state,$stmt->affected_rows);
printf("Inserted row: %d <br>",$mysqli->insert_id);
$two=$mysqli->insert_id;

$name = 'Jim';
$street = 'anew street 2';
$city = 'Westerville 2';
$state = 'OH';

/* execute prepared statement */
$stmt->execute() or die("Error: ".$mysqli->error);
printf("%s,%s,%s,%d Row inserted.<br>",$street,$city,$state,$stmt->affected_rows);
printf("Inserted row: %d <br>",$mysqli->insert_id);
$three=$mysqli->insert_id;

$stmt->close();
echo "<br>$one,$two,$three";
}
else {
    /* Error */
    echo "$sql";
    printf("Prepared Statement Error: %s<br>", $mysqli->error);
}  


/* close statement and connection */


/* close connection */
$mysqli->close();
?>

 

My echos/printf's tell me what I want to hear, but....

 

My results: 3 new rows in the DB.  The first is blank, then strange results elsewhere.  Row 2 [city]="Jim" and row 3 [city]="Bob".

 

--Q: after taking a 2nd look at your comment about the syntax- are you saying I cannot use a prepared/bound statement (?) unless I provide a value for all available fields?

 

[fenway]

How else would it know what values to use?