SQL Update Query Error in PHP Function

Question

I'm currently trying to use the following PHP function/SQL query with a page that edits medications on a project I'm building for school. I'm getting the following error and having trouble to finding where the error is :

Syntax error or access violation: 1064 You have an error in your SQL syntax;

function edit_medicine($medicine_name, $medicine_dose, $medicine_date, $medicine_current, $medicine_id) {
global $db;
$query = "UPDATE Medicine
             SET MedicineName = :medicine_name, 
             Medicine Dose = :medicine_dose, 
             MedicineDatePrescribed = :medicine_date, 
             MedicineCurrent = :medicine_current
              WHERE MedicineKey = :medicine_id";
$statement = $db->prepare($query);
$statement->bindValue(':medicine_name', $medicine_name);
$statement->bindValue(':medicine_dose', $medicine_dose);
$statement->bindValue(':medicine_date', $medicine_date);
$statement->bindValue(':medicine_current', $medicine_current);
$statement->bindValue(':medicine_id', $medicine_id);
$statement->execute();
$statement->closeCursor();
}

I'd appreciate any help anyone can offer- it's the end of finals week and I'm totally burnt out. Thanks!

``Medicine Dose` , not 'Medicine Dose', please refer to Logan s answer — SIDU
– SIDU, Commented Jul 21, 2016 at 3:52

Logan Wayne · Accepted Answer · 2016-07-21 02:23:10Z

4

Use quote to your column names, especially your Medicine Dose column because of its space (). Next time, don't use space to name your columns:

$query = "UPDATE `Medicine`
             SET `MedicineName` = :medicine_name, 
             `Medicine Dose` = :medicine_dose, 
             `MedicineDatePrescribed` = :medicine_date, 
             `MedicineCurrent` = :medicine_current
             WHERE `MedicineKey` = :medicine_id";

answered Jul 21, 2016 at 2:23

Logan Wayne

5,98716 gold badges33 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sarah B Over a year ago

Thanks! However, that didn't fix my query. I appreciate your answer, though. I'm still getting "Syntax error or access violation: 1064 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 ''MedicineName' = 'Topamax', 'Medicine Dose' = '20 mg', ' at line 2' "

Saurabh Over a year ago

You need to alter table and change column name to something like MedicineDose or Medicine_Dose

Logan Wayne Over a year ago

@SarahB - are you sure you used backticks (`), not apostrophe/single tick ('). Because they are different to each other.

Tamil · Accepted Answer · 2016-07-21 07:23:31Z

0

If double quotes or backtick does not work , try including the string within square brackets.

For example

SELECT [Business Name],[Other Name] FROM your_Table

answered Jul 21, 2016 at 7:23

Tamil

1,44811 silver badges25 bronze badges

Collectives™ on Stack Overflow

SQL Update Query Error in PHP Function

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related