php - Cannot insert data into MySQL Database

270

So I'm having kind of a problem here. I'm trying to do something very basic, which is store data via Apache localhost website into a MySQL Database. After I send the data on my website, it's not stored into the database. I will post my code first and then give you some more info:

Code in header:

<?php
$link=mysqli_connect("localhost","root","mypassword","database") or die("Couldn't connect to DB");
?>

Code:

<?php include ( "./inc/header.inc.php" ); ?>
<?php
$reg = @$_POST['reg'];
// declaring variables to prevent errors
$fn = ""; // First Name
$ln = ""; // Last Name
$un = ""; // Username
$em = ""; // Email
$emc = ""; // Emailcheck
$pswd = ""; // Password
$pswdc = ""; // Passwordcheck
$d = ""; // Signup Date
$u_check = ""; // Check if username exists
// registration form
$fn = strip_tags(@$_POST['fname']);
$ln = strip_tags(@$_POST['lname']);
$un = strip_tags(@$_POST['username']);
$em = strip_tags(@$_POST['email']);
$emc = strip_tags(@$_POST['emailcheck']);
$pswd = strip_tags(@$_POST['password']);
$pswdc = strip_tags(@$_POST['passwordcheck']);
date_default_timezone_set('Europe/Berlin');
$d = date("Y-M-D"); // Year - Month - Day

if ($reg) {
    // Check if emails are equal
    if ($em==$emc) {
        // Check if user already exists
        $u_check = mysqli_query($link, "SELECT username FROM users WHERE username= '$un'");
        // Count the amount of rows where username = $un
        $check = mysqli_num_rows($u_check);
        if ($check == 0) {
            // Check all of the fields have been filled in
            if ($fn&&$ln&&$un&&$em&&$emc&&$pswd&&$pswdc) {
                // Check that passwords match
                if ($pswd==$pswdc) {
                    // Check the max length of username/first name/last name does not exeed 25 characters
                    if (strlen($un)>25||strlen($fn)>25||strlen($ln)>25) {
                        echo "The maximum limit for username/first name/last name is 25 characters!";
                    } else {
                        // Check the max length of password does not exeed 25 characters and is not less than 5 characters
                        if (strlen($pswd)>30||strlen($pswd)<5) {
                            echo "Your password must be between 5 and 30 characters long. So should your ######!";
                        } else {
                            // encrypt password and passwordcheck using md5 before sending to database
                            $pswd = md5($pswd);
                            $pswdc = md5($pswdc);
                            $query = mysqli_query($link, "INSERT INTO users (id, username, first_name, last_name, email, password, sign_up_date, activated) VALUES ('','$un','$fn','$ln','$em','$pswd','$d','0')");
                            die("<h2>Welcome to #######</h2>Login to your account to get ######!");
                        }
                    }
                } else {
                    echo "Your passwords don't match.";
                }
            } else {
                echo "Please fill in all of the fields.";
            }
        } else {
            echo "This Username already exists.";
        }
    } else {
        echo "Your Emails don't match.";
    }
}
?>
        <div id="wrapper2">
            <table>
                <tr>
                    <td width="60%" valign="top">
                        <h2>Join ####### today!</h2>
                    </td>
                    <td width="40%" valign="top">
                        <h2>Sign up below.</h2>
                        <form action="#" method="POST">
                            <input type="text" name="fname" size="25" placeholder="First Name" /><br /><br />
                            <input type="text" name="lname" size="25" placeholder="Last Name" /><br /><br />
                            <input type="text" name="username" size="25" placeholder="User Name" /><br /><br />
                            <input type="text" name="email" size="25" placeholder="E-Mail" /><br /><br />
                            <input type="text" name="emailcheck" size="25" placeholder="E-Mail Check" /><br /><br />
                            <input type="text" name="password" size="25" placeholder="Password" /><br /><br />
                            <input type="text" name="passwordcheck" size="25" placeholder="Password Check" /><br /><br />
                            <input type="submit" name="reg" value="Sign Up!">
                        </form>
                    </td>
                </tr>
            </table>
<?php include ( "./inc/footer.inc.php" ); ?>

So the connection to the database is working. I already checked that. The data is correctly stored in the variables. I manually inserted a 'Test' username via phpMyAdmin in the database and if I put the same username in my website, the code correctly fetches the username from the database and compares it with the input (ergo the result is "Username already exists"). Like I already said, when I fill the form correctly with data, the code terminates but the data is not stored into the database.

Here some version info: Apache/2.4.16 (Unix) PHP/5.5.29

Basically used this guide to set up Apache, PHP, MySQL, phpMyAdmin: Guide for Mac OSX

I checked for errors viadie(mysqli_error($link));, and got this response:

Incorrect integer value: '' for column 'id' at row 1 ->Changed argument '' to NULL.

Second error check gave me this:

Incorrect date value: '2015-Nov-Wed' for column 'sign_up_date' at row 1

TheNOW() method did the trick. And thanks for pointing out the date mistake: should be $d = date("Y-m-d");

572

Answer

Solution:

Try this:

INSERT INTO users (username, first_name, last_name, email, password, sign_up_date, activated) VALUES ('$un','$fn','$ln','$em','$pswd','$d','0')`

If yourid column is auto increment, you're not required to fill this up by yourself.

And for your date, you can check this link for date reference.date structured column requiresYY-MM-DD format, which you can get by:

$d = date("Y-m-d");

And does users from your system get to upload videos? :P

687

Answer

Solution:

  1. To start add the following to the top of your php file (right after opening [

    error_reporting(E_ALL); ini_set('display_errors', 'on');

This will show you what errors you have on the page.

  1. Then check your query error by using

    $query = mysqli_query($link, "INSERT INTO users (id, username, first_name, last_name, email, password, sign_up_date, activated) VALUES ('','$un','$fn','$ln','$em','$pswd','$d','0')") or die(mysqli_error($link);
    

The second will print the mysql error which you can then use to fix your problem .

You can always use the above to find problems, yours is probably the following

In your query your sending an empty value for id.

Change

"INSERT INTO users (id, username, first_name, last_name, email, password, sign_up_date, activated) VALUES ('','$un','$fn','$ln','$em','$pswd','$d','0')"

into

"INSERT INTO users (username, first_name, last_name, email, password, sign_up_date, activated) VALUES ('$un','$fn','$ln','$em','$pswd','$d','0')"

People are also looking for solutions to the problem: php - wordpress plugin for get mysql data

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.