Update mysql table if store_num query completes
All!
I am working on an application using php and mysql. Basically, initially I insert new data records using an html form into the database, where store # is my primary key. So far, I can't update the existing store # (as my primary key) and get the message "Duplicate record for store 967 (example)". I want to update the "store" table if an installation exists. Here is my code below but I am getting another error
Error: you have an error in your SQL syntax; check the manual corresponding to your MySQL server version for the correct syntax to use next to '[' 967 '], address
= [' 500 kipling avenue 1 '], dsm_name
= [' n / a '], phone
= [' 416-967- ' on line 1
I'm not sure if I'm using the if condition in the right place.
**$sql = "INSERT INTO `stores`(`store_num`, `address`, `dsm_name`, `phone`, `router_type`, `high_speed_pri`, `dsl_log`, `dsl_pass`, `secondary_conn`, `sec_dsl`, `sec_pass`) VALUES ('$store' , '$address', '$dsm', '$phone', '$router', '$highspeedpr', '$dsllog', '$dslpas', '$secondary_conn' , '$secdsl' , '$sec_pass')";
$mysqli_query = "SELECT * from 'stores' WHERE $store = 'store_num'";
if ($mysqli_query == TRUE){
$sql = "UPDATE `stores` SET `store_num`=['$store'],`address`=['$address'],`dsm_name`=['$dsm'],`phone`=['$phone'],`router_type`=['$router'],`high_speed_pri`=['$highspeedpr'],`dsl_log`=['$dsllog'],`dsl_pass`=['$dslpas'],`secondary_conn`=['$secondary_conn'],`sec_dsl`=['$secdsl'],`sec_pass`=['$sec_pass'] WHERE 1";
}
if (!mysqli_query($con,$sql)) {
die('Error: ' . mysqli_error($con));
}
echo "1 record added";
mysqli_close($con);
?>**
source to share
Replace instead of Insert
Since your update statement contains all the same fields as Insert, you can simply use the REPLACE statement , as stated in the linked documentation:
REPLACE works exactly the same as INSERT, except that if the old row in the table has the same meaning as the new row for PRIMARY KEY or UNIQUE index, the old row is deleted before inserting the new row. See Section 13.2.5, "INSERT Syntax".
So, you need to change the code to the following:
$sql = "REPLACE INTO `stores`(`store_num`, `address`, `dsm_name`, `phone`, `router_type`, `high_speed_pri`, `dsl_log`, `dsl_pass`, `secondary_conn`, `sec_dsl`, `sec_pass`) VALUES ('$store' , '$address', '$dsm', '$phone', '$router', '$highspeedpr', '$dsllog', '$dslpas', '$secondary_conn' , '$secdsl' , '$sec_pass')";
Cause of error
Your problem is with the syntax in the update statement. What is it store_num
, a number or a string?
You must change your syntax to not include square brackets in the actual mysql query.
If $ Store is Number:
=['$store'],
before =$store
If $ Store is text:
=['$store'],
before ='$store'
Final recommendation
Better yet, they will use prepared statements that are also safe and avoid SQL injection attack.
source to share
You can do this logic with a single query using on duplicate key update
. First, you must define store_num
as a unique key if it is not already a unique or primary key:
CREATE UNIQUE INDEX idx_stored_storenum on stores(store_num);
Then use this one insert
:
INSERT INTO `stores`(`store_num`, `address`, `dsm_name`, `phone`, `router_type`, `high_speed_pri`,
`dsl_log`, `dsl_pass`, `secondary_conn`, `sec_dsl`, `sec_pass`
)
VALUES ('$store' , '$address', '$dsm', '$phone', '$router', '$highspeedpr',
'$dsllog', '$dslpas', '$secondary_conn' , '$secdsl' , '$sec_pass')
ON DUPLICATE KEY UPDATE address = values (address),
dsm_name = values(dsm_name),
. . .
sec_pass = values(sec_pass);
Your specific problem is the square brackets, which MySQL does not recognize.
source to share