• Howdy! Welcome to our community of more than 130.000 members devoted to web hosting. This is a great place to get special offers from web hosts and post your own requests or ads. To start posting sign up here. Cheers! /Peo, FreeWebSpace.net
managed wordpress hosting

[SQL] Check if entry exists, if not add it

Wojtek

W as in Whisky
NLC
How do I check if an entry is already there?

say I have 5 colors:


red
blue
yellow
green
black


I want to add white.
How do I check if its already in the table, and if it is, do nothing, and if its not, add it.

tnx :)
 
PHP:
$query = "SELECT color FROM table WHERE color = 'white'"; 
$result = mysql_query($query); 
if (mysql_num_rows($result) > 0) {
echo 'do nothing';
} else {
$query = "INSERT INTO table (color) VALUES ('white')";
mysql_query($query);
echo 'added';
}
 
if you deal with my sql, don't forget to set primary key to auto increment. this will prevent database conflict, especially for select statement (fetching data).
 
in database structure, i suggest you to use "UNIQUE" key . this will make data insertion more easy.
here is the table example:

CREATE TABLE `color` (
`colorID` TINYINT(3) NOT NULL AUTO_INCREMENT,
`colorName` VARCHAR(50) NOT NULL,
PRIMARY KEY ('colorID'),
UNIQUE (`testName`)
);

by using this structure, you can add entry only by typing this simple script:

if(!mysql_query("INSERT INTO color VALUES ('','color')"))
print "Identical color. Insert 0 row";
else
print "Color added";
 
i'm sorry, a little mistake in table structure. it should be

CREATE TABLE `color` (
`colorID` TINYINT(3) NOT NULL AUTO_INCREMENT,
`colorName` VARCHAR(50) NOT NULL,
PRIMARY KEY ('colorID'),
UNIQUE (`colorName`)
);
 
SQL-query :

CREATE TABLE `color` (

`colorID` TINYINT( 3 ) NOT NULL AUTO_INCREMENT,
`colorName` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( 'colorID' ) ,
UNIQUE (
`colorName`
)
)

MySQL said:


You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''colorID' ) , UNIQUE ( `colorName` ) )' at line 1
Back
 
Back
Top