PHP/MySQL Using Variables and INSERT INTO syntax

Question

I am trying to pass variable values to a MySQL database table. I am using a PDO to get access to the database, and am able to echo the variable values that I want to insert to my browser. The only thing I can think of is that my syntax is wrong. I am clearly a novice at using PHP/MySQL.

I am not getting any errors. The info isn't going into my table. What am I doing wrong?

$sql = "INSERT INTO testquiz (version, points, passing_percent, gained_score, username, email, quiz_title, date)
VALUES ('$version', $points, $passing_percent, $gained_score, '$username', '$email', '$quiz_title', CURDATE() )";

Query to create table:

MySQL CREATE TABLE Query:

    CREATE TABLE testquiz (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    version TEXT,
    points INT,
    passing_percent DOUBLE,
    gained_score DOUBLE,
    username TEXT,
    email TEXT,
    quiz_title TEXT,
    date DATE NOT NULL
    ) DEFAULTCHARACTER SET utf8 ENGINE=InnoDB

are all fields strings? If not, do not put quotes around those fields. My guess is something like VALUES ($version, $points, ... etc. — vitro
– vitro, Commented Apr 26, 2014 at 2:59
Updated original question with appropriate quotes for variables. Thank you. — pigneedle
– pigneedle, Commented Apr 26, 2014 at 3:05

Community · Accepted Answer · 2023-11-17 20:33:26Z

1

When using PDO, the generally accepted practice is to use prepared statements for SQL, which essentially are a method used to sanitize your string input.

If your database connection object is $dbo then it would usually go like this.

Create a prepared statement by calling the prepare method on your database connection object:

$sql = $dbo->prepare("INSERT INTO testquiz (version, points, passing_percent, gained_score, username, email, quiz_title, date)
VALUES (:version, :points, :passing_percent, :gained_score, :username, :email, :quiz_title, CURDATE())");

As you can see, instead of passing in the variables I want for the values directly, I've created placeholders. Then, call the execute method on the $sql obect and pass the values in for the placeholders as key-value pairs in an array.

$sql->execute(array(":version" => $version, ":points" => $points, ":passing_percent" => $passing_percent, ":gained_score" => $gained_score, ":username" => $username, ":email" => $email, ":quiz_title" => $quiz_title));

This code passes in the values you define instead of the placeholders, and it properly escapes and sanitizes the variables you pass in for security, while executing your INSERT statement.

https://www.php.net/pdo.prepared-statements

edited Nov 17, 2023 at 20:33

CommunityBot

11 silver badge

answered Apr 26, 2014 at 3:13

user3567510

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

6 Comments

pigneedle Over a year ago

Someone give this guy a vote. I don't have enough rep. This immediately fixed my issue. I love you.

pigneedle Over a year ago

Does this work if I have the variables being assigned from $_POST values?

user3567510 Over a year ago

Yes, it works with any value or variable you pass in. However, it's usually good practice to do some error-check besides sanitizing, so it may be best for security and readibility to assign the $_POST values to variables first.

pigneedle Over a year ago

OK cool. That's what I've done so far, which is where the variables in my initial post were derived from. Still not working on my live server though...

user3567510 Over a year ago

Maybe you should check to make sure your connection string on your PDO object is correct. And try surrounding the column and table names in backticks (`)

|

Giacomo1968 · Accepted Answer · 2014-04-26 03:18:57Z

0

Change the insert statement to the below format and try.

$sql = "INSERT INTO testquiz (version, points, passing_percent, gained_score, username, email, quiz_title, date)
VALUES ('".$version."', '".$points."', '".$passing_percent."', '".$gained_score."', '".$username."', '".$email."', '".$quiz_title."', CURDATE())";

edited Apr 26, 2014 at 3:18

Giacomo1968

25.3k11 gold badges78 silver badges106 bronze badges

answered Apr 26, 2014 at 3:05

Ajay Chauhan

963 bronze badges

1 Comment

pigneedle Over a year ago

This had no effect on my results. Thanks for trying but my table is still blank after execution.

Collectives™ on Stack Overflow

PHP/MySQL Using Variables and INSERT INTO syntax

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related