Insert problem

[tqmd1]

Dear Sir,

I have following codes to save record in MySql.

<?php
    include("connect.php");
    $insert_query = "INSERT INTO ghee (sno,pack,weight) VALUES (18,'kk',80)";
    $insertion_result = mysql_query($insert_query);
    //check whether the data insertion was successful
    if(!$insertion_result)
    {
        die("Sorry! Something went wrong.</p>"); 
    }
    else
    {
        echo "<p>Record saved successfully.</p>"; 
    }
    mysql_close();
?>

These codes work fine, no error. But I need some modification as

In above code these values are going to save

$insert_query = "INSERT INTO ghee (sno,pack,weight) VALUES (18,'kk',80)";

If I press submit button then values are written in dbf.
But if I press submit button MORE THAN ONE TIME then everytime it says:

echo "<p>Record saved successfully.</p>"; 

Before sending data to table it must check whether current SNO=18 already exist or not. If already exist then it must say:

die "<p>Current SNO=18 already exists .</p>";

Please help

[Bachu]

Try this

<?php

    include("connect.php");

    $check_insert_query = "SELECT   * FROM   ghee  WHERE sno='18'";
    $check_insert_result = mysql_query($check_insert_query);
    $num_rows = mysql_num_rows($check_insert_result);
    if($num_rows>0) {
            die ("<p>Current SNO=18 already exists</p>");
    }
    else {
        $insert_query = "INSERT INTO ghee (sno,pack,weight) VALUES (18,'kk',80)";
        $insertion_result = mysql_query($insert_query);
        //check whether the data insertion was successful
        if(!$insertion_result)
        {
            die("Sorry! Something went wrong.</p>"); 
        }
        else
        {
            echo "<p>Record saved successfully.</p>"; 
        }
    }
    mysql_close();
?>

[tqmd1]

Sir it does not work, here is table picture

Click Here

[Bachu]

@tqmd1

Please delete previous values from the table .. And test the above code again.

[naui95]

*For the duplicate entry problem an other option, or in addition to be more sure, you could also set the SNO column as primary or unique in your DB.

*For the dubble click problem you could hide the submit button with Javascript and doing so you will prevent a dobble click.

[tqmd1]

Hide the submit button is not proper solution.

[balaji9124]

Yeah.....either you could place the error msg indicator inside a condition block than else block or else you could go for a java script.

[diafol]

Depending on whether SNO is a primary key or not, you could use INSERT IGNORE or INSERT...ON DUPLICATE...

<?php

    include("connect.php");

    $query = "INSERT IGNORE INTO ghee (sno,pack,weight) VALUES (18,'kk',80)";
    $result = mysql_query($query);
    if(!$result)
        {
            die("<p>Sorry! Something went wrong, or record already exists</p>"); 
        }
        else
        {
            echo "<p>Record saved successfully.</p>"; 
        }
    }
    mysql_close();
?>

//EDIT

Unsure about the return value of the query - so that may be wrong. Also this is considerably slower than a separate SELECT and INSERT.

The INSERT...ON DUPLICATE... is much faster too. Further info here...

http://mikefenwick.com/blog/insert-into-database-or-return-id-of-duplicate-row-in-mysql/

[naui95]

an other solution that should prevent the dubble click problem is not to use the if clause and your cose should be so:

$insert_query = "INSERT INTO ghee (sno,pack,weight) VALUES (18,'kk',80)";  
$insertion_result = mysql_query($insert_query) or die("error message");
Print "Success message";

[Jamblaster]

The code from Bachov Vargese above is correct, except you don't use quotes around numeric input to databases and that is probably where the error came from: I took the quotes off from around the number 18 and that should fix it. With '18' you are telling SQL to look for a string which reads '18', but with 18 you are telling SQL to look for the number 18--big difference.

<?php
include("connect.php");
$check_insert_query = "SELECT * FROM ghee WHERE sno = 18"; // HERE
$check_insert_result = mysql_query($check_insert_query);
$num_rows = mysql_num_rows($check_insert_result);
if($num_rows>0) {
die ("<p>Current SNO=18 already exists</p>");
}
else {
$insert_query = "INSERT INTO ghee (sno,pack,weight) VALUES (18,'kk',80)";
$insertion_result = mysql_query($insert_query);
//check whether the data insertion was successful
if(!$insertion_result)
{
die("Sorry! Something went wrong.</p>");
}
else
{
echo "<p>Record saved successfully.</p>";
}
}
mysql_close();
?>

[diafol]

you don't use quotes around numeric input to databases and that is probably where the error came from: I took the quotes off from around the number 18 and that should fix it. With '18' you are telling SQL to look for a string which reads '18', but with 18 you are telling SQL to look for the number 18--big difference

That's just plain wrong. Although it is more correct to leave off the quotes, it will still work.

[Jamblaster]

Yeah, I was mistaken. MySQL does do implicit conversions for numeric data. Anyway, if you want to have a unique constraint you can you it through the MySQL command line interface like this-->

Login using the root account or whatever account you administer with.

use <Database name>;

Alter Table ghee
Add Constraint unique_sno
unique (sno);

This would add a unique constraint (Acts like a PK) to your table and then PHP wouldn't be able to insert duplicates.

If you already have a PK or another constraint that might cause a problem then you could remove it like this:

Alter table ghee
Drop Key <whatever_the_constraint_name_is>;

To find a constraint name you can use this:

SELECT * FROM information_schema.table_constraints
WHERE table_schema = schema()
and table_name = 'ghee';

You could also use multiple columns for a primary key like this:

Alter Table ghee
Add Constraint unique_entry
unique(sno, pack, weight);

Basically though, I guess your problem is that you don't have a required constraint on your Db table to stop redundant data entries. Anyway good luck and sorry about the bad information before.