homepage Welcome to WebmasterWorld Guest from 54.227.215.140
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
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

WebmasterWorld Administrator lorax us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4652044 posted 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

WebmasterWorld Senior Member penders us a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



 
Msg#: 4652044 posted 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

WebmasterWorld Administrator lorax us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4652044 posted 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

WebmasterWorld Senior Member penders us a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



 
Msg#: 4652044 posted 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

WebmasterWorld Administrator lorax us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4652044 posted 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

WebmasterWorld Administrator lorax us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4652044 posted 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

WebmasterWorld Senior Member penders us a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



 
Msg#: 4652044 posted 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

WebmasterWorld Administrator lorax us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4652044 posted 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