homepage Welcome to WebmasterWorld Guest from 54.161.197.188
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Troubleshooting an insert
No errors showing
lorax




msg:4652046
 5:25 pm on Mar 7, 2014 (gmt 0)

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




msg:4652103
 9:07 pm on Mar 7, 2014 (gmt 0)

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




msg:4652112
 9:26 pm on Mar 7, 2014 (gmt 0)

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




msg:4652263
 12:19 pm on Mar 8, 2014 (gmt 0)

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




msg:4652996
 1:07 pm on Mar 11, 2014 (gmt 0)

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




msg:4653129
 8:51 pm on Mar 11, 2014 (gmt 0)

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




msg:4653155
 11:28 pm on Mar 11, 2014 (gmt 0)

$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




msg:4653268
 11:56 am on Mar 12, 2014 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved