Re: [PHP] verifying sql INSERTs
From: Curt Zirzow [EMAIL PROTECTED] I should instead just try to perform the INSERT and then if it fails I know I've already got a record. If it doesn't I know everything is cool. I've debated with myself several times if this is really a good method to not have duplicates in the database. Like what happens if for some reason the constraint gets dropped? having your progam rely on the constraint will cause all data integrity to go to hell. For one little script inserting into it's own little table, yeah, it really doesn't matter. Consider, though, a business making use of this database not only through a PHP script, but through an Access frontend in marketing, some ASP scripts in HR, etc. Now, if you don't enforce the constraints in the database, each and every program must enforce the constraits itself. It's easier to do it in one place. :) ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] verifying sql INSERTs
Hi!
(MySQL)
What's the best way to determine a sql INSERT statement has executed
successfully?
What I've got is a function whose sole purpose is to add new staff
members to an app. I've written. There's a basic form that the user
fills out (first name, last name, email address) and submits. Only one
record per email address is allowed.
Here is a pseudo code version of what I have right now.
function insertStaffer($fname, $lname, $email)
{
$sql = SELECT table WHERE email = '$email';
if(1 or more records are found)
{
return 0; // means email address is already used
}
else
{
$sql = INSERT INTO table ...;
return 1; // assume record inserted correctly
}
}
Now for my question: Should I just assume the INSERT INTO has worked
correctly (like I'm currently doing) and return a 1? Or should I verify
that the INSERT worked correctly (by using the MySQL last_insert_id()
function)?
Alternatively... maybe the mysql_query() function returns a true/false
status based on what happens? Hmm... I should look into that.
I'd appreciate any advice you have to give.
Thanks,
Chris.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] verifying sql INSERTs
Chris W. Parker wrote: Hi! (MySQL) What's the best way to determine a sql INSERT statement has executed successfully? You should use mysql_error() to ensure your query did not fail for any reason. Assuming the query was successful, you can use mysql_affected_rows() to see if it actually had any impact on the database, i.e. the row was inserted. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] verifying sql INSERTs
mysql_query() will return true for queries that don't return rows and
were executed without error, so you can use
return mysql_query();
You should also make a UNIQUE index on email column, then you can check
the number returned by mysql_errno(), one number I don't remember
signals duplicate entry error.
Chris W. Parker wrote:
Hi!
(MySQL)
What's the best way to determine a sql INSERT statement has executed
successfully?
What I've got is a function whose sole purpose is to add new staff
members to an app. I've written. There's a basic form that the user
fills out (first name, last name, email address) and submits. Only one
record per email address is allowed.
Here is a pseudo code version of what I have right now.
function insertStaffer($fname, $lname, $email)
{
$sql = SELECT table WHERE email = '$email';
if(1 or more records are found)
{
return 0; // means email address is already used
}
else
{
$sql = INSERT INTO table ...;
return 1; // assume record inserted correctly
}
}
Now for my question: Should I just assume the INSERT INTO has worked
correctly (like I'm currently doing) and return a 1? Or should I verify
that the INSERT worked correctly (by using the MySQL last_insert_id()
function)?
Alternatively... maybe the mysql_query() function returns a true/false
status based on what happens? Hmm... I should look into that.
I'd appreciate any advice you have to give.
Thanks,
Chris.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] verifying sql INSERTs
John W. Holmes mailto:[EMAIL PROTECTED] on Monday, August 25, 2003 5:37 PM said: You should use mysql_error() to ensure your query did not fail for any reason. Assuming the query was successful, you can use mysql_affected_rows() to see if it actually had any impact on the database, i.e. the row was inserted. Hey good idea. I'll do some experiment'n. Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] verifying sql INSERTs
Marek Kilimajer mailto:[EMAIL PROTECTED] on Tuesday, August 26, 2003 3:59 AM said: mysql_query() will return true for queries that don't return rows and were executed without error, so you can use return mysql_query(); You should also make a UNIQUE index on email column, then you can check the number returned by mysql_errno(), one number I don't remember signals duplicate entry error. OOhhh... You know, I already HAVE done this, and I just came to the realization that if I've already got a constraint on the email column then why the heck am I checking to see if the value is already there? That's just a waste of resources. I should instead just try to perform the INSERT and then if it fails I know I've already got a record. If it doesn't I know everything is cool. Good idea! Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] verifying sql INSERTs
From: Chris W. Parker [EMAIL PROTECTED] I should instead just try to perform the INSERT and then if it fails I know I've already got a record. If it doesn't I know everything is cool. If it is a certain kind of failure, then you have a duplicate, otherwise it could be another type of failure/error. Use mysql_errno() to make sure you catch the right error as a duplicate. ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] verifying sql INSERTs
* Thus wrote Chris W. Parker ([EMAIL PROTECTED]): Marek Kilimajer mailto:[EMAIL PROTECTED] on Tuesday, August 26, 2003 3:59 AM said: mysql_query() will return true for queries that don't return rows and were executed without error, so you can use return mysql_query(); You should also make a UNIQUE index on email column, then you can check the number returned by mysql_errno(), one number I don't remember signals duplicate entry error. OOhhh... You know, I already HAVE done this, and I just came to the realization that if I've already got a constraint on the email column then why the heck am I checking to see if the value is already there? That's just a waste of resources. I should instead just try to perform the INSERT and then if it fails I know I've already got a record. If it doesn't I know everything is cool. I've debated with myself several times if this is really a good method to not have duplicates in the database. Like what happens if for some reason the constraint gets dropped? having your progam rely on the constraint will cause all data integrity to go to hell. Since the field is a unique index, there wont be much overhead in the query itself. Still undecided... Curt -- I used to think I was indecisive, but now I'm not so sure. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] verifying sql INSERTs
Curt Zirzow mailto:[EMAIL PROTECTED] on Tuesday, August 26, 2003 11:26 AM said: Like what happens if for some reason the constraint gets dropped? having your progam rely on the constraint will cause all data integrity to go to hell. In that case I hope you're also incrementing your own IDs and not leaving that up to the db either. You might also do yourself a favor and write down each transaction instead of hoping the computer will do it's job correctly. ;) But seriously, how likely is it for a db/table to just drop it's constraint? And in what situations would/could that happen? Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
