PHP, check if a record was inserted via MySQL...

[morestar]

So basically I'm trying to determine of a record was actually inserted into my database with the insert statement in MySQL.

My code is as follows:

$sQuery = "INSERT INTO `$database`.`Users` 
(`User` ,`Email` ,`Email_verified`)
VALUES (NULL , '$sEmail', '0')";
mysql_query($sQuery);

From here I would like to send an email to the new member but before I do I need to know for sure that the record was inserted.

I have already setup checks before this step, that makes sure everything is totally ok before the insert but I would like it as a last precaution.

If anyone knows the proper syntax to check if a record was inserted into the MySQL database that would be great. I'm still going to search the net and find it...if I do I will post it here...

[Uncle Dog]

if (mysql_query($sQuery))
{
echo "Record inserted";
}
else
{
echo "Error inserting record: " . mysql_error();
}

[morestar]

Thank you UD, once I get the fricken mail to send, I'll add in the insert check...

thanks again man...

[morestar]

Yep it worked! I can't believe how long it actually takes to write a perfectly error proof registration form...

I added javascript validation and then had to write server side validation in case the javascript is turned off or not working for the user...added a little ajax to check if the username exists - in real time...

whew...that's done...

Thanks Uncle Dog...

[danlefree]

You might want to try variable assignment instead of wrapping the mysql_query() call in an if/else statement (things can get tricky otherwise).

PHP Code:

$flag_inserted = mysql_query($sQuery);

if ( $flag_inserted ) {

  // Success handling
  // . . .

} else {

  // Error handling
  // . . .

} 


(Then you can chain multiple conditions)

In the future you may want to look at the Return Values section of the PHP manual for functions you are using.

[morestar]

Ya I looked at the link you provided before I posted here...I couldn't quite figure out the syntax or way of coding it with those examples...

Now what do you mean by chaining multiple conditions?

[morestar]

Ya I did as you mentioned..

$result=mysql_query($sQuery);
   if($result){
     /// do this
   } else {
    dothat();
}

[wige]

Another (tricky) way to do it is to get the insert id of the newly created record, assuming you are entering a new row into the database. This can be used in two ways - to confirm that the row was created successfully, and to perform additional work on the entry or to link other tables to the new entry. The function, run after the insert query, is mysql_insert_id(), which will return 0 if no new id was created. You can get more info and some samples here: PHP: mysql_insert_id - Manual

Another good method, which I often use, is to simply test if an error has been set. If the error id is 0, the previous command executed correctly, otherwise call the error handler... if (mysql_errno()) echo mysql_error;

[danlefree]

Originally Posted by morestar

Now what do you mean by chaining multiple conditions?

There may be some cases where multiple queries must succeed (or other conditions must be met) before you perform an action - for example, if your form requires that the user be inserted and a registration e-mail be sent, you might want to cancel the user insert if there is a problem sending the e-mail.

(The mail() command will return FALSE on error as well)

[morestar]

Very well wige and danlefree, thank you for your advice(s). I'm noticing that my php coding skills aren't nearly as advanced as some of the code I'm seeing out there...after I launch this new site I just might get a more experienced someone to take a peek at the code and optimize it as much as possible.

the sign up form is done except now I have to code to prevent sql injection...

wish me luck!

[danlefree]

Originally Posted by morestar

the sign up form is done except now I have to code to prevent sql injection...

wish me luck!

How about we just wish that you use prepared statements?

No luck required

[mushroom]

The code I like to use

if (mysql_affected_rows() ==1)
{     # YOUR ACTION
}

[morestar]

Originally Posted by danlefree

How about we just wish that you use prepared statements?

No luck required

hm, I'll be looking into this danlefree...thank you!

[morestar]

Quoted from the page on Prepared Statements: however, if other portions of the query are being built up with unescaped input, SQL injection is still possible.

what would be considered unescaped input? I know it involves quotes but with a prepared statement when would unescaped input come into play - where?

[morestar]

so what would be the major difference between editing my existing code with addslashes();
compare to stored procedures...I have a feeling it's because stored procedures are cleaner and more advanced...

so wouldn't the following suffice?

<?php 
 
function sanitize_data($input_data) {
  return htmlentities(stripslashes($input_data), ENT_QUOTES);
} 
?>

[danlefree]

I think that mysql_real_escape_string() is the function you are looking for - combined with sprintf() it should handle all your edge cases (the Ctype functions may also be useful, if you would like to perform strict validation to avoid truncated inputs when calls to sprintf() are made and a regexp would be overkill) - and that is only if you are not using prepared statements to handle all variables in your queries.

The warning on the prepared statements document is intended to address kludges like this one:

PHP Code:

$stmt = $dbh->prepare("INSERT INTO REGISTRY SET name=:name, value=:value, id=" . $_GET['id'] );
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value); 


i.e. if one were using prepared statements without understanding the reason prepared statements are preferable over vulnerable statements

[morestar]

whew! this is confusing but I'll get it...I'll get back to you with some results in the future but thank you all for your help and suggestions...I know I need to definitely write this code properly...

[Uncle Dog]

It seems like a lot of people want you to run before you can walk.
Might I recommend a gentle stroll through PHP MySQL Introduction

[morestar]

lol Uncle Dog you're funny...I'm not that basic in SQL and PHP. No where near that !! I've got PHP, ColdFusion, ASP - SQL under my belt...PM me, I'll send you my resume...

[Uncle Dog]

It's not that basic.

[morestar]

Originally Posted by Uncle Dog

It's not that basic.

I do like that site though...and I like tizag.com, really good examples...

So once I get a moment and get back into this, I'll touch base with you guys...thanks for the help too...