how can I insert data into table php/mysql?

Question

I'm making a quiz and once the calculation of the grade is finished, I want to add that data to test_attempt table.

Here's its structure.

Here's the code of the query:

<?php

            $connection = mysqli_connect("localhost", "root", "", "vartvald");
            if ($connection->connect_error) {
                die("Connection failed:" . $connection->connect_error);
            }

            $user=$_SESSION['user'];
            $userid=$_SESSION['userid'];

            $sql = "INSERT INTO test_attempts (date, id, mark, top_mark, fk_user, fk_test) VALUES
                ('',null,'$grade','$top_grade','$userid','$fk');";

            var_dump($sql);

            $connection->close();
            ?>

What am I doing wrong?

you are inserting in to the date (timestamp) an empty string. — Constantin Trepadus, Dec 02 '19 at 09:20
You are not sending sql stament, you need to prepare (mysqli_prepare), bind (mysqli_stmt_bind_param) and execute (mysqli_stmt_execute). — sochas, Dec 02 '19 at 09:20

score 3 · Accepted Answer · answered Dec 02 '19 at 09:50

You have few mistakes. Your main problem is that you never prepared any query and never executed it. To do it you need to use prepare(), bind_param(), and execute(). Also you are not opening the mysqli connection correctly and your error checking will never work (Please read: Should we ever check for mysqli_connect() errors manually?)

After fixing your errors your code would look something like this:

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$connection = new mysqli("localhost", "root", "", "vartvald");
$connection->set_charset('utf8mb4');

$user = $_SESSION['user'];
$userid = $_SESSION['userid'];

$stmt = $connection->prepare('INSERT INTO test_attempts (date, id, mark, top_mark, fk_user, fk_test) VALUES(NULL,NULL,?,?,?,?)');
$stmt->bind_param('ssss', $grade, $top_grade, $userid, $fk);
$stmt->execute();

I have not validated whether your SQL is correct in itself, but if you have error reporting switched on, PHP should tell you if you have a mistake.

user2342558 · Answer 2 · 2019-12-02T09:58:32.413

0

Your code will never add data in the database because you aren't calling any funciton that insert data:

 $sql = "INSERT INTO test_attempts (date, id, mark, top_mark, fk_user, fk_test) VALUES
            ('',null,'$grade','$top_grade','$userid','$fk');";

var_dump($sql);

// missed code to insert data in the database

$connection->close(); // here you close the connection

Before closing the connection, call mysqli_query:

mysqli_query($connection,"$sql");

edited Dec 02 '19 at 09:58

answered Dec 02 '19 at 09:20

user2342558

5,567
5
33
54

Don't show them how to fix something they should not use. Show them how to do it with prepared statements. – Dharman Dec 02 '19 at 09:42
1

it is *your* query is using user inputs without check their contents. which makes your answer quite inconsistent - here you are saying such a code shouldn't be used and here you are promptly writing it as though it's OK – Your Common Sense Dec 02 '19 at 09:54
@YourCommonSense I wrong read `_POST` instead of `_SESSION` in the question, so I deleted the warning from my answer. thanks – user2342558 Dec 02 '19 at 10:01
2

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Dec 02 '19 at 10:05
@Dharman I know that escaping is not enough and all data must be checked and "prepared" but in the question I can see only `_SESSION` where it get data, so the user can't change the `_SESSION` contents without accessing the server... Therefore the other variables may be already checked somewhere in his script. So, I didn't preventively suppose that he was in wrong about that. – user2342558 Dec 02 '19 at 10:09
@Dharman from my answer code you cannot see that I'm open to SQL Injections only because you don't see **in that code** that I'm not preparing the query. Those variables may be set in the code and previously checked to fit the correct format and content. So, the simple usage of `mysqli_query` does not mean SQL injection. – user2342558 Dec 02 '19 at 10:11
It doesn't mean that you should suggest such way. Assume that the data is insecure, because it is a string and it could contain anything. Always use prepared statements when you have variable data. It doesn't matter where it comes from. – Dharman Dec 02 '19 at 10:14
@Dharman I got it. – user2342558 Dec 02 '19 at 10:18

score -2 · Answer 3 · answered Dec 02 '19 at 09:20

-2

Try this:

 $sql = "INSERT INTO test_attempts (date, id, mark, top_mark, fk_user, fk_test) VALUES
                (CURRENT_TIMESTAMP,null,'$grade','$top_grade','$userid','$fk');";

answered Dec 02 '19 at 09:20

Constantin Trepadus

505
3
9

score -2 · Answer 4 · answered Dec 02 '19 at 09:58

-2

Try the following, here you can see that the CURRENT_TIMESTAMP is passed as first params for data and also below the $sql you can see the mysqli_query which is useed here to execute the insert query.

$sql = "INSERT INTO test_attempts (date, id, mark, top_mark, fk_user, fk_test) VALUES
                (CURRENT_TIMESTAMP,null,'$grade','$top_grade','$userid','$fk');";

mysqli_query($connection, $sql);

answered Dec 02 '19 at 09:58

Nikhil Gyan

682
9
16

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Dec 02 '19 at 10:15
this is just for giving an idea what is missing into the question. – Nikhil Gyan Dec 02 '19 at 10:35
Thank you @NikhilGyan successfully added. – Tushar Kumar Kundan Dec 27 '19 at 09:31

how can I insert data into table php/mysql?

4 Answers4