Couldn't be able to insert data into MySQL even if I enter write query

Question

I am having trouble inserting data in the mySQL through php,

First of all i created the Database and its table using PHpmyadmin,

my table is as follows,

CREATE TABLE users (
    firstsname varchar(12) NOT NULL,
        lastname varchar(20) NOT NULL,
        gender varchar(6) NOT NULL,
        dob varchar(10) NOT NULL,
        address varchar(40) NOT NULL,
        town varchar(20) NOT NULL,
        states varchar(20) NOT NULL,
        postcode int(4) NOT NULL,
        emailaddress varchar(100) PRIMARY KEY UNIQUE,
        phonenumber int(10) NOT NULL,
        comments varchar(1000) NULL       
);

After I created the table i connect to the database where this table is located called testing, using this code,

$host = "localhost";
$user = "root";
$pwd = "";
$sql_db = "testing";
$conn = @mysqli_connect($host,$user,$pwd,$sql_db);

if (!$conn) {
    echo "<p>Connection has failed!</p>";
} else {
    echo "<p>Success</p>";
}

I get Success message on the screen that means the connection is successful, but when I try to write in to this table using PHP i always get error, but when I run the same query in phpmyadmin then it works and add the table row into database,

This is what I am doing in PHP,

$query = "INSERT INTO users ('firstname', 'lastname', 'gender', 'dob', 'address', 'town', 'states', 'postcode', 'emailaddress', 'phonenumber', 'comments') 
VALUES ('asds', 'adssad', 'male', '04/08/1991', 'saddadsasd', 'dasasd', 'adssad', '1111', 'dasdas@dassda.com', '1111111111', 'adssaddsadsa')";
$result = mysqli_query($conn, $query);

if (!$result){
    echo "ERROR";
} else {
    echo "Done";
}
mysqli_close ($conn);

and I always get ERROR when I run it.

Why it works through phpmyadmin but not with PHP ?

Instead of echoing mere string saying "error" or "done", try using built-in functions to get potential errors... — Masiorama, May 20 '15 at 15:42
Turn on PHP's error reporting and it'll tell you exactly what the problem is. — diggersworld, May 20 '15 at 15:42
Column names has to have backticks ` (or nothing), not single quotes. — pavel, May 20 '15 at 15:43
Do this `$result = mysqli_query($conn, $query) or die(mysqli_error($conn));` and you'll see the errors you're making. — Funk Forty Niner, May 20 '15 at 15:44
@RileyWillow , single quotes should only be Outside Column Value , not Column name. — Pratik Joshi, May 20 '15 at 15:45

The Humble Rat · Accepted Answer · 2015-05-20T15:43:25.667

3

Try changing this line:

$query = "INSERT INTO users ('firstname', 'lastname', 'gender', 'dob', 'address', 'town', 'states', 'postcode', 'emailaddress', 'phonenumber', 'comments') 
VALUES ('asds', 'adssad', 'male', '04/08/1991', 'saddadsasd', 'dasasd', 'adssad', '1111', 'dasdas@dassda.com', '1111111111', 'adssaddsadsa')";

To

$query = "INSERT INTO users (firstname, lastname, gender, dob, address, town, states, postcode, emailaddress, phonenumber, comments) 
VALUES ('asds', 'adssad', 'male', '04/08/1991', 'saddadsasd', 'dasasd', 'adssad', '1111', 'dasdas@dassda.com', '1111111111', 'adssaddsadsa')";

Notice I have changed the column names to not have the quotes.

If you really wanted you could potentially use ticks:

INSERT INTO users (`firstname`, `lastname`

However, I would suggest no quotes and no ticks.

edited May 20 '15 at 15:43

answered May 20 '15 at 15:40

The Humble Rat

4,586
6
39
73

I tried that already it didn't work either. – Riley Willow May 20 '15 at 15:43
actually i also tried inserting directly from phpmyadmin and then using the generated query with ticks in php, but it didn't work either. – Riley Willow May 20 '15 at 15:44
@RileyWillow when you execute the PHP code above, is anything actually inserted into the DB? As I believe I have had issues with `UPDATES` as technically nothing was returned, but the query was successful. So would be an idea to check this. – The Humble Rat May 20 '15 at 15:47
1

*"However, I would suggest no quotes and no ticks."* - One would use ticks, if and when the OP happens to unknowingly, use a reserved word, or it contains a space, a hyphen or anything else that will give MySQL something to complain about ;-) – Funk Forty Niner May 20 '15 at 15:50
1

@Fred-ii- very true, for the OP's info here are the reserved words, http://dev.mysql.com/doc/refman/5.6/en/reserved-words.html. It's suprising how many times you can actually conflict with a reserved word. – The Humble Rat May 20 '15 at 15:53

Couldn't be able to insert data into MySQL even if I enter write query

1 Answers1