Cannot insert data into my MySQL database with PHP

Question

I'm creating a music website where users should be able to add artists, albums, and songs to a database and discuss them. However, I cannot get the data the user inputs into the signup form to transfer to my "users" table in my database. I'm not exactly sure what's wrong, but I suspect it's a problem with my syntax, because I'm not having any database connection issues. I've been searching for hours but I haven't yet found a solution. Can anyone help me?

To clarify: the error is that none of the information from the signup form is getting entered into my database after the user hits "submit"

signup.php:

<?php include 'header.php'; ?>

<form class="form" action="register.php" method="POST">
  <fieldset>
    <label for="firstName">First Name:</label>
    <input type="text" name="firstName" placeholder="John Smith">
    <br>
    <label for="lastName">Last Name:</label>
    <input type="text" name="lastName" placeholder="John Smith">
    <br>
    <label for="email">Email:</label>
    <input type="email" name="email" value="some@one.com">
    <br>
    <label for="username">Username:</label>
    <input type="text" name="username" value="helloitsme">
    <br>
    <label for="password">Password:</label>
    <input type="password" name="password">
    <br>
    <label for="confirmPassword">Confirm Password:</label>
    <input type="password" name="confirmPassword">
    <br>
    <label for="age">Age:</label>
    <input type="number" name="age">
    <br>
  </fieldset>
  <fieldset>
    <input type="submit" name="submit" value="Register">
  </fieldset>
</form>

register.php:

<?php
session_start();

if ($_POST["password"] != $_POST["confirmPassword"]) {
  $_SESSION['signUpBadPassword'] = true;
  header('Location: signup.php');
}
// Re use connection stuffs
include "db.php";
// get connection
$conn = DB::connect();

$stmt = $conn->prepare("INSERT INTO users (firstName, lastName, email, username, password, age) VALUES (?, ?, ?, ?, ?, ?)");

$stmt->bind_param(
  "sssssi",
  $_POST["firstName"],
  $_POST["lastName"],
  $_POST["email"],
  $_POST["username"],
  $_POST["password"],
  $_POST["age"]
);

$stmt->execute();

// Close the connection
$conn->close();
header('Location: index.php');

 ?>

And db.php:

<?php

class DB
{

  public static function connect()
  {
    $hostname = 'localhost';
    $username = 'user';
    $password = 'password';
    $dbName = 'dbname';
    $connection = new mysqli($hostname, $username, $password, $dbName);

  if ($connection->connect_error) {
     die('Failed to connect');
   }

  return $connection;
  }
}

Sorry, I wasn't clear enough - the error is that the data isn't being inserted into the musique database after I "submit" on the signup form. — Alex Kamens, May 02 '16 at 06:48
The posters who are stating "remove sssssi" are wrong. DO NOT remove sssssi type setting as this is a ***required*** part of the MySQLi binding system. — Martin, May 02 '16 at 10:47

score 2 · Answer 1 · edited May 02 '16 at 10:53

while binding the parameter please check that post value isset or not i.e, check below code:

$stmt = $conn->prepare("INSERT INTO users (firstName, lastName, email, username, password, age) VALUES (?, ?, ?, ?, ?, ?)");

$stmt->bind_param(
  "sssssi", $firstName, $lastName, $email, $userName, $passWord, $age
);

//check firstname is set or not
if(isset($_POST['firstName']))
{
    $firstName= $_POST['firstName'];    
}
else
{
    $firstName= NULL;
} 
//check lastname is set or not
if(isset($_POST['lastName']))
{
    $lastName= $_POST['lastName'];  
}
else
{
    $lastName= NULL;
} 
//check email is set or not
if(isset($_POST['email']))
{
    $email= $_POST['email'];    
}
else
{
    $email= NULL;
} 
//check username is set or not
if(isset($_POST['username']))
{
    $userName= $_POST['username'];  
}
else
{
    $userName= NULL;
} 
//check password is set or not
if(isset($_POST['password']))
{
    $passWord= $_POST['password'];  
}
else
{
    $passWord= NULL;
} 
//check age is set or not
if(isset($_POST['age']))
{
    $age= $_POST['age'];    
}
else
{
    $age= 0;
}

score 1 · Answer 2 · answered May 02 '16 at 07:37

1

Sorry I can't comment yet... Are those 6 fields the only ones in the 'users' table ? The problem could come from a 'not null' field in the table with no data inserted and no default value. Maybe we could see the table structure ?

answered May 02 '16 at 07:37

D14n4

120
6

score 1 · Answer 3 · edited May 23 '17 at 11:52

$_POST values are always going to be of the type "string", so $_POST["age"] will always be:

$_POST['age'] !== (int)$_POST['age']

Yet your type declaration (the ssssi bit) states that $_POST["age"] is of the "i"nteger type.

Therefore to fix your ->bind_param to reflect this. The easiest way would be to establish:

$_POST['age'] = (int)$_POST['age'];

In your PHP you should be calling exit or die immediately after header calls to prevent the rest of the script executing. For example, in your correct script it will still be running the execute insert code because while the header has been sent, the rest of the page is still executing.

Add error logging to your PHP.
Add Error logging to your MySQLi queries.
Another important note is that you are doing nothing to prevent Cross Site Request Forgeries on your form.
Show us your MySQLi table to better establish why the insert is failing if the above does not solve your problem.

score -1 · Answer 4 · answered May 02 '16 at 06:55

-1

use die(mysql_error()); after your Query than you will find your solution

$stmt = $conn->prepare("INSERT INTO users (firstName, lastName, email, 
username, password, age) VALUES (?, ?, ?, ?, ?, ?)") or die(mysql_error());

answered May 02 '16 at 06:55

KAREN

388
2
10

I'm still not getting any errors back after I put that in there – Alex Kamens May 02 '16 at 07:13
@AlexKamens but Everything seems perfect up there – KAREN May 02 '16 at 07:29
This answer is fundamentally incorrect, first off it is using `mysql_` rather than mysqli_ and secondly it isusing procedural approach to an object orientated connection. @AlexKamens it is no worries this doesn't work for you. – Martin May 02 '16 at 12:11

Cannot insert data into my MySQL database with PHP

4 Answers4