Troubleshooting an insert

No errors showing

[lorax]

I'm trying to figure out what's wrong with an insert routine. PHP >> MySQL. I'm pretty sure the issue is my lack of understanding. Right now I'm trying to figure out how to output the errors. This gives me nothing.

 if (!($stmt = $mysqli->prepare($theinsert))) {
  echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
 }
 if (!$stmt->bind_param("issssssssssisssssssssssssssssssssss", $PID, $fname, $lname, $title, $mrank, $website, $linkedin, $twitter, $facebook, $photo, $cv, $speaker, $speaktopics, $stafforfac, $startdate, $building, $room, $phone, $email, $courses, $college, $program, $institution1, $year1, $degree1, $institution2, $year2, $degree2, $institution3, $year3, $degree3, $assocs, $awards, $pubs, $interests)) {
  echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
 }
 
 if (!$stmt->execute()) {
  echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
 }
 printf("Error message: %s\n", mysqli_error($mysqli));

A nudge in the right direction would be appreciated.

[penders]

This gives me nothing.

By nothing, I guess you mean just the string "Error message:" - so all the statements appear to have been successful?

Have you checked your error log?

What does $theinsert contain? Presumably it's not the same as stated in your other question [webmasterworld.com] - which wouldn't work in this instance.

[lorax]

You're right. Just the Error message string with no error.

The insert:

  $theinsert = "INSERT INTO myprofiles(
      PID,
      fname,
      lname,
      title,
      mrank,
      website,
      linkedin,
      twitter,
      facebook,
      photo,
      cv,
      speaker,
      speaktopics,
      stafforfac,
      startdate,
      building,
      room,
      phone,
      email,
      courses,
      college,
      program,
      institution1,
      year1,
      degree1,
      institution2,
      year2,
      degree2,
      institution3,
      year3,
      degree3,
      assocs,
      awards,
      pubs,
      interests
     ) 
     VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

I didn't realize it might output an error to the logs. I'll go check. Thx.

[penders]

Make sure you have full error_reporting enabled and make sure there is nothing non-mysqli related that is causing this, but is otherwise being suppressed?

error_reporting(E_ALL ^ E_STRICT);
ini_set('display_errors','1');

Just looking at the SQL in the other thread (assuming these are related), you are referencing a different table name here... "myprofiles", as opposed to "my_profiles"? I assume this is intentional, as otherwise this should be generating an error?

Also, in this SQL you refer to a "PID" column, whereas this is omitted in the other SQL. Is this an AUTO_INCREMENT primary key? In which case this should probably be omitted and could account for the record not being inserted if there is a primary key conflict? Although, again, I would have expected an error condition, unless INSERT IGNORE was being used.

Check:

$mysqli->affected_rows

...to return how many rows "it" thinks were INSERT'd.

[lorax]

Thanks for the reply. Yea - the table names match in my code - just anonymizing them here.

I added the error reporting code you gave and it picked up on something minor - no timezone set by the server so I set that but still no love inserting into the database.

PID is an autoincrement PK. It's the only PK. I tried removing it but it didn't work. I read somewhere (I've been reading a lot and have lost the specific article that said this) that every field in the table needs to be included. It doesn't seem to matter as I've tried it both ways.

$mysqli->affected_rows reports 1 row. Hm... makes me wonder about permissions.

[lorax]

Resorting to desperation measures. I've trimmed to code down to the bare bones. Sorry but here's the full mess:

date_default_timezone_set('America/New_York');

error_reporting(E_ALL ^ E_STRICT);
ini_set('display_errors','1');

$mysqli = mysqli_init();
if (!$mysqli) {
 die('mysqli_init failed');
}

if (!$mysqli->options(MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0')) {
 die('Setting MYSQLI_INIT_COMMAND failed');
}

if (!$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5)) {
 die('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed');
}

if (!$mysqli->real_connect("xx", "xx", "xx", "xx")) {
 die('Connect Error (' . mysqli_connect_errno() . ') '
   . mysqli_connect_error());
}

if($_POST['form_id'] == '1'){
 if($_POST['_wp_http_referer'] == "/the-form/") {
  $fname = $mysqli->real_escape_string( $_POST['vfb-5'] );
  $theinsert = "INSERT INTO myprofiles( fname ) VALUES(?)";
     
  if (!($stmt = $mysqli->prepare($theinsert) {
   echo "Prepare failed: (" . $stmt->errno . ") " . $stmt->error;
  }
  if (!$stmt->bind_param("s", $fname)) {
   echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
  }
  if (!$stmt->execute()) {
   echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
  }
  printf( $mysqli->affected_rows ); // echos 1
  printf ( $stmt->error ); // no output
  echo mysqli_error($mysqli); // no output
  // echo gettype ($fname); // string
  echo '<br/>' . $mysqli->host_info . "\n"; // echos Localhost via UNIX socket
 }
 else {
  header('Location: '.home_url());
  exit;  
 }
 $mysqli->close();
}
else {
 header('Location: '.home_url());
 exit;
}
 

[penders]

$mysqli->options(MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0')

I think this might be the "problem". Presumably this is also an InnoDB table (which supports transactions)? With AUTOCOMMIT set to 0 (ie. disabled) then you'll need to explicitly issue a COMMIT to commit the transaction, making the change permanent.

This seems to be consistent with what you are seeing. "affected_rows" is 1 (indicating success) immediately after the INSERT, but it looks as if this is being rolled back (perhaps implicitly when the connection is closed?) before it is committed.

[lorax]

BINGO!

OMFG penders. Thank you! I knew it had to be something I was doing. I removed that if statement and in it went. Good gawd. Typical though - school of hard knocks. I won't soon forget to look for that again. :)

Thanks again for the help.