INSERT query in PHP Failing (returns blank error)

Question

I'm using the following code in an attempt to insert user-provided values in <form> into an sql table. When the INSERT query is used in PHPMyAdmin, the insertion is successful, but unsuccessful in the following php. Connection to the database is successful.

<?php
$servername = "localhost";
$username = "***";
$password = "***";
$db_name = "my_db";
$trip;
$odom;
$gals;
$ppg;

if (isset($_REQUEST['trip'])){

$trip = $_REQUEST['trip'];
$odom = $_REQUEST['odom'];
$gals = $_REQUEST['gals'];
$ppg = $_REQUEST['ppg'];

} 

// Create connection
$conn = mysqli_connect($servername, $username, $db_name, $password);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully ";

Here, I've tried using double quotes around the query to form a string; I've omitted the quotes as well, to no avail. Also, in VALUES, I've tried both single-quotations and the omission thereof as well, without success. The variables are double values, not strings.

 $vals = "INSERT INTO `Log` (`Trip`, `Odometer`, `Gallons_Pumped`,    
 `Price_Gallon`) 
 VALUES ('$trip', '$odom', '$gals', '$ppg')";

The return value of $retval is false, but no error is provided.

$retval = mysql_query($vals);

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

mysql_close($conn);

?>

Is my syntax incorrect? If not, why is the INSERT query unsuccessful?

Example how can u use PDO http://stackoverflow.com/questions/34781748/data-from-php-form-is-not-posting-to-mysql/34782699#34782699 — devpro, Jan 16 '16 at 05:30
There's also a problem with `mysqli_connect` and the subsequent test for a valid connection. If the connection fails, `$conn` won't be a mysqli connection object, and won't have a `connect_error` method function/method. Following the **`$conn = mysqli_connect(`** (as shown in your code), you'd need to test **`if(!$conn){`**. — spencer7593, Jan 16 '16 at 05:35

score 4 · Accepted Answer · edited May 23 '17 at 11:59

You're mixing mysqli_* functions with mysql_* functions. You can't do that; they aren't the same library.

Use only mysqli_*. Please don't use mysql_*; the mysql_* functions are outdated, deprecated, and insecure. Use MySQLi or PDO instead.

The last block of your code should be (untested):

$retval = mysqli_query($vals); // changed

if ($retval) {
    echo "New record created successfully";
} else {
    echo "Error: " . $vals . "<br>" . mysqli_error($conn); // changed
}

mysqli_close($conn); // changed

Note the change from $retval->error to mysqli_error($conn). By definition, if you reached that point in your code it was because $retval evaluated to false and therefore was not an object.

Also, please note that you are wide open to SQL injection. Your SQL statement is this:

$vals = "INSERT INTO `Log` (`Trip`, `Odometer`, `Gallons_Pumped`, `Price_Gallon`)
VALUES ('$trip', '$odom', '$gals', '$ppg')";

But those values ($trip, etc.) come straight from the user (via $_REQUEST). Very bad things can happen if your user submits values containing, for example, the ' character. You should use prepared statements, like this:

$vals = "INSERT INTO `Log` (`Trip`, `Odometer`, `Gallons_Pumped`, `Price_Gallon`)
VALUES (?, ?, ?, ?)";

if($stmt = mysqli_prepare($conn, $vals)) {
    // adjust "sddd" below to match your data types; see http://php.net/manual/en/mysqli-stmt.bind-param.php
    mysqli_stmt_bind_param($stmt, "sddd", $trip, $odom, $gals, $ppg);

    $retval = mysqli_stmt_execute($stmt);
} else {
    echo "Error: " . $vals . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);

This is best answer I suggest u add sql injection instruction chk OP code open for injection. . As mentioned in this example http://stackoverflow.com/questions/34781748/data-from-php-form-is-not-posting-to-mysql/34782699#34782699 — devpro, Jan 16 '16 at 05:34
@devpro Good point. I added a discussion of SQL injection. Adrian, please see the second half of the answer for a more secure way to do things. — elixenide, Jan 16 '16 at 06:13

bbonev · Answer 2 · 2016-01-16T05:55:34.610

2

Looks like you are mixing two incompatible APIs - the deprecated mysql functional API and the newer object oriented mysqli.

It would be best to convert your code to mysqli and it will work.

edited Jan 16 '16 at 05:55

answered Jan 16 '16 at 05:20

bbonev

1,406
2
16
33

Agreed here is the example of PDO http://stackoverflow.com/questions/34781748/data-from-php-form-is-not-posting-to-mysql/34782699#34782699 – devpro Jan 16 '16 at 05:31

score 1 · Answer 3 · answered Jan 16 '16 at 05:31

There's another problem in your code. You're mixing procedural style and object- oriented style calls. Your test for a valid connection in this code isn't valid:

  $conn = mysqli_connect($servername, $username, $db_name, $password);

  // Check connection
  if ($conn->connect_error) {

In procedural style, mysqli_connect won't return an object if the connection fails. So $conn won't have a method/function connect_error. You need to test just $conn, and then call the procedural style mysqli_connect_error.

  $conn = mysqli_connect($servername, $username, $db_name, $password);
  if(!conn){
     die("Connection failed: " . mysqli_connect_error);
  }

Another alternative fix to change the initial assignment to $conn to be object oriented style (i.e. using new)

Reference: http://php.net/manual/en/mysqli.construct.php

INSERT query in PHP Failing (returns blank error)

3 Answers3