Checking if data exists before adding to table?

[ncncnc]

Hi,

 

I'm making a website that uses data stored in SQL.

 

One of my pages allows the user to enter a new product into a product table. I was wondering if somebody could help me with some validation or Query that will not allow the user to enter a product name that already exists in the table?

 

 
$nameNew = $_POST['new];
$nameID = $_POST['newID];
$namePrice = $_POST['newPrice];

INSERT INTO products
VALUES ($nameNew, $newID, $newPrice)

$describeQuery = "SELECT ID, Name, Price FROM Products";


$results = sqlsrv_query($conn, $describeQuery);

echo '<table border="1" BORDERCOLOR=Black>';
echo '<tr><th bgcolor = "LightBlue">Name</th><th bgcolor = "LightBlue" >ID</th> <th bgcolor = "LightBlue" >Price</th></tr>';


while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) 
  {   
    echo '<tr>';
echo '<td >' .$row['Name'].'</td>'; 
echo '<td>' .$row['ID'].'</td>'; 
echo '<td>' .$row['Price'].'</td>'; 

echo '</tr>';
} 

echo '</table>';
sqlsrv_close($conn);


   

 

Thanks

[Psycho]

It's as simple as running a SELECT query using a WHERE clause such as WHERE prod_name='$new_name' then checking if the number of results [i.e. mysql_num_rows()] is greater than 0. However, there is another solution as well. Set the name field as unique. Then skip the process of checking for duplicates and just run the INSERT statement with a "ON DUPLICATE IGNORE" clause. If the insert query will cause a duplicate entry in a unique field the INSERT will not take place. Then, after you run the insert query you can check mysql_affected_rows() to see if the record was inserted or not. If yes, then it was a new record and you can show the success message. If not, then you can display a message that the record was a duplicate.

[ncncnc]

Hi

 

I should have been more clear I'm not using mySQL I'm using SQL Server.

 

INSERT INTO products VALUES ($nameNew, $newID, $newPrice) ON DUPLICATE IGNORE

 

Is it as simple as this? I'm unable to test because I'm away from my workstation.

 

Thanks.

[ncncnc]

BTW

 

I'm sorry I just realised that my code is more like Pseudo-code. I typed it quickly from memory, but I'm sure you see what I'm trying to do. I don't believe I can use the DUPLICATE command in SQL Server.

[ncncnc]

Am I right  in think that setting Name as a Unique Constraint could solve this?

[creata.physics]

Psycho already answered your question, that's probably why nobody else stopped by to reply in this thread. Based on what you've talked to yourself about, you didn't bother reading Psycho's post, which is another reason why nobody else stopped by to say anything.

 

You'll need an alternative query to check for record existance. Your checks will need to consist with what duplicate data you do not want to enter.

I'm going to post an example on how to prevent from adding another record in an sql server with php to handle the processing.

$check_if_data_exists = mysql_query("select unique_field from sql_table where unique_field = '{$unique_value}'");
if( mysql_num_rows( $check_if_data_exists ) == false )
{
    // you'll insert data here, because mysql_num_rows returned that there was not any matching records
}
else
{
    echo 'This data already exists';
}

mysql_num_rows will return the number of rows if the query returned any results, or it will return false if no rows were found.

You'll need to alter the query to benefit your script and also check if there is more than one field that is not allowed to have matching data.

[xyph]

@ creata.physics - Perhaps before accusing someone of being unable to read, you should read yourself

 

Using a query to check if a record already exists is a bad idea. It can lead to race conditions, where a value is inserted to the DB between checking, and inserting, causing duplicates.

 

A unique constraint/index on the column is exactly what you want.

 

ON DUPLICATE IGNORE isn't available on MSSQL, I don't think.

 

A very specific error code will be thrown when you try to insert a row with duplicate data, you should instead check for that error if the query fails.

 

Alternately, you could use a WHERE NOT EXISTS with a subquery, but my method will be much faster.

[creata.physics]

you're right, i didn't even read the entire thread and just skimmed through it, definitely my fault. i really don't understand why I even come to these forums unless i'm fully capable, motivated and willing to help others. sorry for being a douche

[ncncnc]

@ creata.physics - Perhaps before accusing someone of being unable to read, you should read yourself

 

Using a query to check if a record already exists is a bad idea. It can lead to race conditions, where a value is inserted to the DB between checking, and inserting, causing duplicates.

 

A unique constraint/index on the column is exactly what you want.

 

ON DUPLICATE IGNORE isn't available on MSSQL, I don't think.

 

A very specific error code will be thrown when you try to insert a row with duplicate data, you should instead check for that error if the query fails.

 

Alternately, you could use a WHERE NOT EXISTS with a subquery, but my method will be much faster.

 

Thanks a lot.