Adding element to MySQL database from php code

Question

what's wrong here?:) I want to put data to database (I'm pretty sure that connection to db works).

$host="localhost";
$username="root";
$password="root";
$db_name="db";
$tbl_name="changes";

mysql_connect("$host","$username","$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");


$when=$_POST['when'];
$bad_teacher=$_POST['bad_teacher'];
$teacher=$_POST['teacher'];
$hour=$_POST['hour'];
$class=$_POST['class'];

$sql="INSERT INTO $tbl_name (when, bad_teacher, teacher, hour, class) VALUES (`$when`,`$bad_teacher`,`$teacher`,`$hour`,`$class`)";
mysql_query($sql);

?>

Use mysqli extension rather than mysql http://us1.php.net/mysqli — user24601, Jan 01 '14 at 20:21
And read [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) because your code is currently easily hacked. — Bill Karwin, Jan 01 '14 at 20:25

M Khalid Junaid · Answer 1 · 2014-01-01T20:35:40.863

WHEN is mysql reserved keyword you need to use bactiks arround your column names matched with Rererved Keywords,and why you have used bacticks arround the values.

$sql="INSERT INTO 
$tbl_name (`when`, `bad_teacher`, `teacher`, `hour`, `class`) 
VALUES 
('".$when."','".$bad_teacher."','".$teacher."','".$hour."','".$class."')";

Also mysql* family is depreiciated you need to move on with pdo or aleast with mysqli* family.

Pdo demo

$dbhost     = "localhost";
$dbname     = "dbname";
$dbuser     = "user";
$dbpass     = "pass";

// database connection
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);


// query
$sql = "INSERT INTO books (`when`, `bad_teacher`, `teacher`, `hour`, `class`)
 VALUES (:when,:bad_teacher,:teacher,:hour,:class)";
$q = $conn->prepare($sql);
$q-> bindParam(':when', $when);
$q-> bindParam(':bad_teacher',$bad_teacher);
$q-> bindParam(':teacher', $teacher);
$q-> bindParam(':hour', $hour);
$q-> bindParam(':class', $class);
$q->execute();

Pdo Reference

This should use query parameters instead of putting $_POST variables directly into SQL expressions. — Bill Karwin, Jan 01 '14 at 20:26
you should probably put quotation marks around the php variables in case a variable has a space in it or some other invalid markup — amaster, Jan 01 '14 at 20:26

score 0 · Answer 2 · answered Nov 20 '20 at 13:14

//connection.php 

$dsn  = 'mysql:host=localhost;dbname=db';
$user = 'root';
$pass = 'root';
$option = array(
    PDO::MYSQL_ATTR_INIT_COMMAND =>'SET NAMES utf8',
);

try {
    $con = new PDO($dsn, $user, $pass,$option);
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}

catch(PDOException $e) {
    echo 'Failed To Connect'.$e->getMessage();
}

//INSERT 

$sqli="INSERT INTO 
$tbl_name (`when`, `bad_teacher`, `teacher`, `hour`, `class`) 
VALUES 
('".$when."','".$bad_teacher."','".$teacher."','".$hour."','".$class."')";

Adding element to MySQL database from php code

2 Answers2