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="[email protected]">
    <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
– Alex Kamens, Commented May 2, 2016 at 6: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. — Mm-Art-In
– Mm-Art-In, Commented May 2, 2016 at 10:47

Ankur Tiwari · Accepted Answer · 2016-05-02 10:53:42Z

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;
}

D14n4 · Accepted Answer · 2016-05-02 07:37:08Z

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 2, 2016 at 7:37

D14n4

1306 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:52:44Z

$_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.

KAREN · Accepted Answer · 2016-05-02 06:55:38Z

-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 2, 2016 at 6:55

KAREN

3862 silver badges10 bronze badges

3 Comments

Alex Kamens Over a year ago

I'm still not getting any errors back after I put that in there

KAREN Over a year ago

@AlexKamens but Everything seems perfect up there

Mm-Art-In Over a year ago

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.

Collectives™ on Stack Overflow

Cannot insert data into my MySQL database with PHP

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related