PHP - Insert SQL Error

Question

Database layout:

rid (auto increment) (primary key) (255)
song (varchar) (120)
artist (varchar) (30)
by(varchar) (33)
key(varchar) (60)

PHP code:

$sql = "INSERT INTO Requests (song,artist,by,key) 
        VALUES ('$song','$artist','$by','$key')";
        if($this->db->query($sql))
        {
            die("true");
        }
        echo 'false: ' . $this->db->error;

Error:

false: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'by,key) VALUES ('testsong','testing','kyle','example')' at line 1

Help? I have debuged for ages, I can't see whats wrong with that SQL? Thanks in advance!

*"I have debuged for ages, I can't see whats wrong with that SQL?"* - `right syntax to use near 'by,key)` - MySQL even told you where it "starts". — Funk Forty Niner, Mar 18 '16 at 12:21
Yes, so I checked the database columns where correct, checked syntax - sorry that I didn't know that severed words existed, I'll try know things I've never been taught next time, don't worry @Fred-ii- — Jaquarh, Mar 18 '16 at 12:29

devpro · Answer 1 · 2016-03-18T12:20:09.760

6

You need to use backtick for BY and KEY columns, both are mysql reserve words

$sql = "INSERT INTO Requests (`song`,`artist`,`by`,`key`) 
        VALUES ('$song','$artist','$by','$key')";

MYSQL Reserve Words List

Side Note:

I suggest you that, please do not use reserve words and keywords for table or column names.

edited Mar 18 '16 at 12:20

answered Mar 18 '16 at 12:14

devpro

16,184
3
27
38

score 3 · Accepted Answer · answered Mar 18 '16 at 12:13

3

You'll need back ticks for SQL-related names, also by and key are MySQL reserved words:

INSERT INTO Requests (`song`,`artist`,`by`,`key`)

answered Mar 18 '16 at 12:13

Panda

6,955
6
40
55

That is the reason, oh god.. I changed the database column name from key to api_key and by to who and its worked, thanks for this – Jaquarh Mar 18 '16 at 12:18
Will do but got to wait 7mins buddy :) Check back in 7mins ;) – Jaquarh Mar 18 '16 at 12:19
1

@KyleE4K You're welcome, glad that it solved your problem :) – Panda Mar 18 '16 at 12:20

score 2 · Answer 3 · edited Mar 18 '16 at 12:44

2

Try this query to insert data in Requests table

$sql = "INSERT INTO Requests (`song`,`artist`,`by`,`key`) 
    VALUES ('".$song."','".$artist."','".$by."','".$key."')";
    if ($this->db->query($sql)) {
        die("true");
    }
    echo 'false: ' . $this->db->error;

the main problem in the asked question's query is apostrophe on both side of column name is missed as (song, artist, by, key) Should be ('song', 'artist', 'by', key')

edited Mar 18 '16 at 12:44

Shahbaz

3,433
1
26
43

answered Mar 18 '16 at 12:14

Muhammad Arif

1,014
3
22
56

2

Why should the OP "try this"? A ***good answer*** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. – Jay Blanchard Mar 18 '16 at 12:22

PHP - Insert SQL Error

3 Answers3