If i already have record with the exact same data in mysql database i dont want the php code to enter it again.
The code i am using is
mysql_query(" ALTER TABLE tablename ADD UNIQUE INDEX(pageid, name) INSERT IGNORE INTO `tablename`
VALUES ('2','3','4'));
It doesnt seem to work. What is the problem.
a) you need semicolon between queries
b) mysql_query does not support multiple queries
c) mysql_query has syntax errors should be mysql_query(" ALTER TABLE tablename ADD UNIQUE INDEX(pageid, name) INSERT IGNORE INTOtablenameVALUES ('2','3','4')");
ALTER TABLE tablename ADD UNIQUE INDEX(pageid, name) once and then use mysql_query("INSERT IGNORE INTO tablename VALUES ('2','3','4')"); in your scriptExecute this query from MySQL and create the index first.
ALTER TABLE tablename ADD UNIQUE INDEX(pageid, name);
Then you can perform the insert query from PHP
mysql_query("INSERT IGNORE INTO `tablename` VALUES ('2','3','4')");
Check the comment HERE from David OBrien...
Basically since in MySQL there is not a CREATE INDEX IF NOT EXISTS he suggest to use this kind of procedure :
DELIMITER $$
DROP PROCEDURE IF EXISTS `create_index_if_not_exists`$$
CREATE DEFINER=`user`@`%` PROCEDURE `create_index_if_not_exists`(table_name_vc varchar(50), index_name_vc varchar(50), field_list_vc varchar(200))
SQL SECURITY INVOKER
BEGIN
set @Index_cnt = (
select count(1) cnt
FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_name = table_name_vc
and index_name = index_name_vc
);
IF ifnull(@Index_cnt,0) = 0 THEN set @index_sql = concat('Alter table ',table_name_vc,' ADD INDEX ',index_name_vc,'(',field_list_vc,');');
PREPARE stmt FROM @index_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END$$
DELIMITER ;
and then call it like
call create_index_if_not_exists('tablename','indexname','thisfield,thatfield');
You could do something similar like this:
$specifieddata = //the value(s) you want to check
$checkquery = "SELECT * FROM tablename WHERE yourdata = $specifieddata";
$resultcheck = mysql_query($checkquery);
//if the database contains the same values as given in $specifieddata; mysql_fetch_row should be 1 (or more)
if(mysql_fetch_row($resultcheck) >= 1)
{
//your error(?) code
}
else
{
//your INSERT code
}
Also, it might be worth noting you'll want to sanitize your input
The reason I recommend doing it this way is that you can give the user a (if you want detailed) error about what went wrong.
