Check before insert and update if title_id exist?

[sayedsohail]

Hi everone,

 

I wish to know, how to stop insert and update if the users submitted a duplicate title_id and inform the user, why the insert or update had failed.

 

My table structure is:

 

Titles:

id int autoincrement (which is fine)

title_id int

description

 

 

My insert and update is working fine on its own. but i am finding lot of duplication occurs. is there anyway i could stop insert and update if duplication occurs.

 

Please help.

 

[Iceman512]

Hi there,

 

You could start with a check to the db to see if a duplicate field exists, and if it does, produce an error message. If it does not, conduct the query as intended. Like this:

 

<?php
require_once('config.php'); // Your connection info to the DB

$sql = ("SELECT id, title_id, description FROM table"); // To speed up the query, only select the field you need
while($row = mysql_fetch_array($sql))
{
$id = $row['id'];
$title_id = $row['title_id'];
$description = $row[''description];

if ($id == $_POST['id'] || $id = $)POST['id']){ // Check the user-submitted field against the one in the DB
// A row with that number already exists, give them an error message.
echo 'Sorry, that user already exists, please go <a href="back.php">back</a> and try again';
}

elseif ($id !== $_POST['id'] || $id != $)POST['id']){
// The id is unique, perform the insert/update query
}

else {
echo 'An unknown error has occurred.';
}
mysql_close($con); // Close the connection used in the config.php file
?>

 

The above is very simplified, not secure and will require much altering to work, but I hope it puts you on the right track. You could also consider Javascript to help with this, but some users disable it and that can be very dangerous. The last thing you want is a user who has had their account overwritten!

 

Hope it works!

Regards,

Iceman

[sayedsohail]

is there anyway i could create unique index (title_id+description), and let mysql handle or raise the errors for duplicates on receiving insert and update from the browser.?

 

 

 

[Iceman512]

Hi again,

 

Yes, I'm sure that is possible, but there are a number of reason why that probably is not a good idea:

 

• It will put a lot of load on the db server, which will slow the page down. Remember that the unique id will have to be checked against every existing entry in the db.
  
• MySQL will probably issue an error once it discovers a duplicate entry, or just overwrite the duplicate depending on the settings it's running under.
  

 

Furthermore, I'm just a humble part-timer  , So I wouldn't know how to actually get it to work - sorry!

The php code can be implemented a lot quicker and it is server-side so it cannot be bypassed with sufficient security measures, unlike Javascript.

 

I hope you find a solution!

All the best,

Iceman

[fenway]

Yes, you can create a unique index, and you can "catch" this error if you want to...

[sayedsohail]

what you reckon about performance issues,  i am not sure which method would have least amount of overhead on the server.

 

although its just one table which has constant updates and insert, but it is the one that would have rapid requests, therefore i just wish to use something very light on server.

 

 

[akitchin]

if you have a high enough version of MySQL, you can use the INSERT .. ON DUPLICATE KEY UPDATE statement.  the manual has an entry for it:

 

http://dev.mysql.com/doc/refman/4.1/en/insert-on-duplicate.html

 

works with UNIQUE or PRIMARY KEY indeces, so you'd have to set a UNIQUE on the title_id (assuming this is the application you're after).

[sayedsohail]

I am not sure if i am getting it right, if we use insert [iNSERT INTO table (a,b,c) VALUES (1,2,3)

  ON DUPLICATE KEY UPDATE c=c+1;], than there is a possibility of loosing previous entries silently, that could be a disaster.  Since users didn't know what had happen.

 

 

I would think, it is better to inform the user, when updates occur.  May i whole understanding is bit out of the track.

 

[akitchin]

if you want to simply trigger an error message for the user rather than updating the entry, set the key as a unique and catch the error as fenway said was possible.  all you have to do is check mysql_affected_rows() after trying to run the INSERT query; if it's 0, you know it triggered an error (not necessarily for the key duplicate, mind you).  to check the source of the error, use mysql_errno() to determine the error code.

 

see the manual for more information on these functions and an appendix of MySQL error codes.

[sayedsohail]

but if we consider fenway method for this scenario, the statment check each and everyone records, now the performance issue?

[fenway]

but if we consider fenway method for this scenario, the statment check each and everyone records, now the performance issue?

If you need to "know" that it happens, you have to have something monitor it, you have no choice.