cant insert data into existing table in mysql database with php

Question

I get the message that the new record was created but when I reload phpmyadmin the table is the same. Also I have retrieved information from the same DB, from the same table, with SELECT command, so the connection works..(plainly said). I have no clue why is not updating. Please help. Thank you in advance.

<html>
<head>
</head>
<body>
<?php 
define('DB_NAME', 'appointments');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
define('DB_HOST', 'localhost');

$link = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

if (!$link) {
    die('Could not connect: ' . mysql_error());
}
$hos=$_POST['hos'];
echo $hos;
echo "<br/>";
$doc=$_POST['doc'];
echo $doc;
$date=$_POST['fdate'];
echo $date;
$time=$_POST['time'];
echo $time;
$pat=5;
echo $pat;
$sql = "INSERT INTO rantevou ('app_id','patient_id','date','time','hos','doc') VALUES ('4','$pat','$date','$time','$hos','$doc');";
if ($sql) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
mysqli_close($link);
?>
</body>
</html>

Wrap off `quotes form table and column` name instead use `backtick`. And forget you execute insert query — Saty, May 16 '16 at 10:50

score 0 · Answer 1 · answered May 16 '16 at 10:53

0

You forgot to execute the query, if ($sql) { merely evaluates the variable.

if (mysqli_query($link, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

Also, you need to use backticks for SQL-related variables, not single quotes:

$sql = "INSERT INTO rantevou (`app_id`,`patient_id`,`date`,`time`,`hos`,`doc`) VALUES ('4','$pat','$date','$time','$hos','$doc');";

answered May 16 '16 at 10:53

Panda

6,955
6
40
55

@ask2now Does any of these answer solve your problem? Maybe accept the best answer by clicking on the tick mark below the upvote/ downvote buttons, thanks :) – Panda May 16 '16 at 11:11

score 0 · Answer 2 · answered May 16 '16 at 11:02

You're not actually executing your query. If you add the line $result = mysqli_query($link, $sql); after declaring $sql you will execute the query.

You can then assess whether it worked using the same if, but change that line to be

if ($result) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($link);
}

In the above example, I have also changed your error reporting as it was referencing $conn, a variable you had not declared before. It now uses the same $link variable as the rest of your code.

Also, I would highly recommend escaping your data since you're inserting the contents of posted data. Escaping your data will help protect against SQL Injection. It's not comprehensively safe, but it's a good start.

To add in escaping, change each $var = $_POST['var'] line to read $var = mysqli_real_escape_string($link, $_POST['var']);

For example, $hos=$_POST['hos']; becomes $hos = mysqli_real_escape_string($link, $_POST['hos']);

This helps prevent moments like this wonderful example by XKCD

score 0 · Answer 3 · answered May 16 '16 at 11:07

1) Remove single quotes (') from column name to backtick (`)

2) Execute your query. You didn't executed.

3) If app_id column is auto incremented and primary key. Then, no need to pass value. Leave it blank.

<?php
$sql = "INSERT INTO rantevou (`app_id`,`patient_id`,`date`,`time`,`hos`,`doc`) VALUES ('','$pat','$date','$time','$hos','$doc');";
$query = mysqli_query($link,$sql) ;

if ($query) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

score 0 · Accepted Answer · edited May 23 '17 at 11:58

There are many mistake in your code

1. use of mysql_error()

you can't use mysql_error because you use mysqli for data base connection.second thing mysql is no more supported

Solution use mysqli_error($link);

2. use of $conn->error

You can't us of $conn->error beacuse you connect with mysqli procedure way not like object oriented way and you also not define a $conn instead you used $link

Solution use mysqli_error($link);

Correct Code

if(!mysqli_query($link, $sql)){
     printf("Errormessage: %s\n", mysqli_error($link));
     die;
}else{
   echo "New record created successfully";
}

Why Data Not Inserted

because you declare variable $sql but you didn't executed that

the new record was created

You get this message all ways because your if condition check that variable have a value (not 0) and yes $sql have value

1.You must use prepare statement,if you don't wan't any sql injection in insert statement SQL INJECTION

2.'' single quote or "" apply only on a string not on id if your app_id is a int don't use ('' or "") quote instead of that convert '4' to int

3.handle error log https://stackoverflow.com/a/3531852/3234646

4.Please clear Concept use of Database Extension http://php.net/manual/en/class.mysqli.php

score -1 · Answer 5 · answered May 16 '16 at 10:53

Instead of

"INSERT INTO rantevou ('app_id','patient_id','date','time','hos','doc') VALUES ('4','$pat','$date','$time','$hos','$doc');"

unquote the columns

"INSERT INTO rantevou (app_id, patient_id, date, time, hos, doc) VALUES ('4','$pat','$date','$time','$hos','$doc');"

or use backticks

"INSERT INTO rantevou (`app_id`, `patient_id`, `date`, `time`, `hos`, `doc`) VALUES ('4','$pat','$date','$time','$hos','$doc');"

you've forgot to execute your query

mysqli_execute($con, "INSERT INTO rantevou (`app_id`, `patient_id`, `date`, `time`, `hos`, `doc`) VALUES ('4','$pat','$date','$time','$hos','$doc')");

Björn Schönrock · Answer 6 · 2016-05-16T10:59:38.240

First of all, don't use single quotes for column names, either use nothing or use backticks.
Secondly, you forgot to execute the query.
Also, using OOP is better.

Please try:

$mysqli = new mysqli(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME);

and

$query = "INSERT INTO rantevou (app_id,patient_id,date,time,hos,doc) VALUES ('4','$pat','$date','$time','$hos','$doc');";
if ($mysqli->query($query)) echo "New record created";
else echo "Error: ".$mysqli->error;

score -1 · Answer 7 · answered May 16 '16 at 10:55

EDIT: What luweiqi said: the statement has yet to be executed!

It seems like you know what you are doing. Are you sure that the paramaters here:

$sql = "INSERT INTO rantevou (**'app_id','patient_id','date','time','hos','doc'**) VALUES ('4','$pat','$date','$time','$hos','$doc');";
if ($sql) {

exactly match your column titles in your database?

Another good way to check your statements, is to go to phpmyadmin and go to the SQL notepad and enter the query with the same structure and see what is being returned.

Your query may be returning a message, but a message saying that it has failed... which would still trigger your echo "New record created successfully";

This is how i've structured my most recent insert to DB:

<?php
// to get data from android app

$gardenID=$_POST["gardenID"];
$vID=$_POST["vID"];
$quantity = $_POST["quantity"];
$timePlanted = date("Y/m/d");

// establishes connection to database
require "init.php";
    echo "here";
    echo $timePlanted;
    echo $quantity;

$query = "insert into garden_veg (gardenID, vID, quantity, timePlanted) values ('".$gardenID."','".$vID."',
                                        '".$quantity."', '".$timePlanted."' );";

$result = mysqli_query($con,$query);
            $response = array();
            $code = "addItem_success"; //changed code
            $message = "Item(s) added!";
            array_push($response,array("code" => $code, "message"=>$message));
            echo json_encode(array("server_response"=>$response));
    mysqli_close($con);
?>

cant insert data into existing table in mysql database with php

7 Answers7