Mysqli and PHP not sending data

Question

I'm building a recruiting website and need to save user data in my database but my form isn't sending anything to the database in phpmyadmin (using WAMP).

I checked the error logs for PHP, MySQL and Apache but don't see any errors. I also added "if/echo" blocks inside the $conn variables to test the connection, which returned true. Code below.

<!-- index.html-->
<form action="process.php" method="post">

  <input type="text" name="first_name" placeholder="First Name" /><br/>
   <input type="text" name="last_name" placeholder="Last Name" /><br/>

      <button type="submit" name="submit"></button>
</form>

//database.php
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "xxxx";
$dberror1 = "Could not connect to the database!";
$dberror2 = "Could not find selected table!";

// Connection to the database, Already tried this with echo statement and works
$conn  = mysqli_connect($dbhost, $dbuser, $dbpass) or die ($dberror1);

// Selecting the database to connect to
$select_db = mysqli_select_db($conn, 'mainbase') or die ($dberror2);

//process.php
<?php include 'database.php'; 

 if(isset($_POST['submit'])) {
// Creating variables to store form values
$first_name= $_POST['first_name'];
$last_name=$_POST['last_name'];


//Executing the query
mysqli_query($conn, " INSERT INTO 'candidates'('first_name', 'last_name') //Values in 'candidates' table on phpmyadmin 
VALUES ('$first_name','$last_name')");/*variables from above*/
}

ever heard of this function? `mysqli_error()`. No? Well now you do and use it from now on. — Funk Forty Niner, Feb 05 '16 at 01:13

Phiter · Answer 1 · 2016-02-05T01:15:36.080

The problem

Don't put table name and column names between apostrophes. That's what's causing your query to fail. Apostrophes are used to pass strings.

mysqli_query($conn, " INSERT INTO 'candidates'('first_name', 'last_name')
VALUES ('$first_name','$last_name')");

Should be

mysqli_query($conn, " INSERT INTO candidates(first_name, last_name)
VALUES ('$first_name','$last_name')");

Or

mysqli_query($conn, " INSERT INTO `candidates`(`first_name`, `last_name`)
VALUES ('$first_name','$last_name')");

if you like it better.

The error handling

In order to verify the problem you can echo the mysqli_error() function result whenever the query fails, it's a nice practice and would probably have helped you find a solution faster than asking it here.

$query= mysqli_query($conn, " INSERT INTO `candidates`(`first_name`, `last_name`)
    VALUES ('$first_name','$last_name')");

if(!$query) //the query will return 0 if it fails
{
   echo mysqli_error($conn);
}

The security issue

You're adding POST value directly into your query, which is dangerous.

On these lines:

$first_name= $_POST['first_name'];
$last_name=$_POST['last_name'];

You should be escaping user input.

This will escape any special characters that can cause issues in the mysql query.

$first_name = mysqli_real_escape_string($conn, $_POST['first_name']);
$last_name  = mysqli_real_escape_string($conn, $_POST['last_name']);

plus [sql injections](http://php.net/manual/en/security.database.sql-injection.php), plus missing error handling. — VolkerK, Feb 05 '16 at 01:14
Freaking ticks. I just tried it and working perfect, Thanks! — , Feb 05 '16 at 01:55

abetwothree · Accepted Answer · 2016-02-05T02:39:02.057

You're using myqli incorrectly. But on top of that, use PDO to connect to your database instead. It's safer and easy to expand in the future. Here is an example of how to connect to your database with PDO.

<?php

$myUser = "XXXXXX";
$myPass = "XXXXXX";

try{

    $dbPDO = new PDO('mysql:host=localhost;dbname=xxxxxxxx', $myUser, $myPass);
    $dbPDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    echo "Connection was successful";

} catch(PDOException $e){

    print "Error!: " . $e->getMessage() . "<br />";
    die();

}

?>

Simply change the Xs to your server's settings.

When you want to start a query simply you can do it like so:

$query = $dbPDO->prepare("SELECT * FROM Table_Name");
$query->execute();

Of course you'd want to pass variables to your queries so you can do that like this:

$query = $dbPDO->prepare("SELECT * FROM Table_Name WHERE ID = :id");
$query->bindParam(':id', $id);
$query->execute();

That keeps SQL injection off your worries. Just make sure to sanitize your variables before binding them to the query as well.

I figured I'd show how to insert your variables into your table with PDO.

$firstName = $_POST['first_name'];
$lastName = $_POST['last_name'];
$query = $dbPDO->prepare("INSERT INTO candidates first_name, last_name VALUES (:fname, :lname)");
$query->bindParam(':fname', $firstName);
$query->bindParam(':lname', $lastName);
$query->execute();

You could also make an array of both of your POST variables and pass that instead of binding each variable at a time.

$candidateName = array('$_POST['first_name']', '$_POST['last_name']');
$query = $dbPDO->prepare("INSERT INTO candidates first_name, last_name VALUES (?, ?)");
$query->execute($candidateName);

I hope that helps!

Happy coding!

@ManuelDiera Yes, I would highly recommend switching to PDO instead of mysqli because it runs much faster when it does multiple similar queries and PHP might at some point stop supporting mysqli. You don't want all your hard work to stop working out of nowhere. — abetwothree, Feb 05 '16 at 01:57
@ManuelDiera also please make sure to mark someone's comment as the answer and or up vote comment s that helped you. By the way, if you're thinking of uploading pictures on your project here's some code I wrote that stores images on the server and links it to the database for quick displaying. https://github.com/skatetdieu/Multiple-Image-Upload-WITH-PHP — abetwothree, Feb 05 '16 at 02:05

Mysqli and PHP not sending data

2 Answers2