How do I connect my website to a MySQL database?

Question

Below is the code I have in my Sublime, but the database isn't being called.

<?php$username="root";
$password="changedpassword";$database="User";
$field1-name=$_POST['name'];
$field2-name=$_POST['password'];
$field3-name=$_POST['email'];
$field4-name=$_POST['sex'];
$field5-name=$_POST['school'];
$field6-name=$_POST['birth'];
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "INSERT INTO create_user (name, password, email, sex, school, birth) VALUES('','$field1-name','$field2-name',
'$field3-name','$field4-name','$field5-name','$field6-name')";mysql_query($query);mysql_close();?>

[**Do not use `mysql_*` functions in new code**](http://stackoverflow.com/q/12859942). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://php.net/mysql-connect)? Learn about [*prepared statements*](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) can help you decide which. — Qirel, Aug 29 '17 at 16:19
Some sensible code-formatting and variable-naming should be done - it will help you debug your code too. — Qirel, Aug 29 '17 at 16:19
Turn on error reporting - PHP will be trying to help you here, but you aren't listening — iainn, Aug 29 '17 at 16:22
That code is *really* hard to read. @Qirel is right: you need to tidy up the formatting. This isn't [Code Golf](https://codegolf.stackexchange.com) or an obfuscated code contest. Use some white space, some line breaks. Let the code breathe a little — TRiG, Aug 29 '17 at 16:23
There's also 6 columns specified, but 7 values given. That's also going to cause an issue, in addition to the variable-naming. — Qirel, Aug 29 '17 at 16:24
The problem is that you used hyphens inside as a variable name which is not allowed in PHP, and you are sending 7 values while having 6 columns only. Also, your code is a bit hard to be read. Check my answer to solve your problem using MySQLi. — Mr Pro Pop, Aug 29 '17 at 16:55

score 0 · Answer 1 · answered Aug 29 '17 at 16:40

Let's go through this step by step. First, here's your current code, tidied up to be readable:

<?php
$username = "root";
$password = "changedpassword";
$database = "User";
$field1_name = $_POST['name'];
$field2_name = $_POST['password'];
$field3_name = $_POST['email'];
$field4_name = $_POST['sex'];
$field5_name = $_POST['school'];
$field6_name = $_POST['birth'];
mysql_connect(localhost, $username, $password);
@mysql_select_db($database) or die("Unable to select database");
$query = "
    INSERT INTO
            create_user
                (
                    name,
                    password,
                    email,
                    sex,
                    school,
                    birth
                )
            VALUES
                (
                    '',
                    '$field1_name',
                    '$field2_name',
                    '$field3_name',
                    '$field4_name',
                    '$field5_name',
                    '$field6_name'
            )
";
mysql_query($query);
mysql_close();
?>

I've made only two changes (tidied the whitespace, and used _name instead of -name, as PHP variables cannot contain hyphens), but it's already a big improvement. The code is no longer an eyesore. It does not have syntax errors, and it is readable. There are still, though, a large number of problems.

First, you see that we are inserting seven values into six columns. This will be a problem. Fix that by removing the first blank value:

$query = "
    INSERT INTO
            create_user
                (
                    name,
                    password,
                    email,
                    sex,
                    school,
                    birth
                )
            VALUES
                (
                    '$field1_name',
                    '$field2_name',
                    '$field3_name',
                    '$field4_name',
                    '$field5_name',
                    '$field6_name'
            )
";

Now we have something that might actually work. It's painfully insecure, with massive potential for SQL injection attacks, and it won't work on the latest PHP because the mysql_ functions have been removed, but it might actually kind of work somewhere. You wouldn't want to put it into production, but for test purposes, we're getting somewhere.

ok thanks when I pulled this code from elsewhere I didn't realize I was using the incorrect format — Nate, Aug 29 '17 at 18:02
I want to go on to show you how to fix those problems, but I'm too tired to think clearly. Maybe tomorrow. — TRiG, Aug 29 '17 at 18:04
I tried running what you pasted above with the extra value removed but even then once I load the localhost and enter information into the site's login page, the mysql database isn't being updated to reflect the new inserted info — Nate, Aug 29 '17 at 18:08

score 0 · Answer 2 · answered Aug 29 '17 at 16:49

0

MySQL is deprecated since PHP 5.6 and is insecure, use PDO or MySQLi instead.

Connecting with MySQLi

        <?php


        //MySQLi information

        $db_host     = "localhost";
        $db_username = "username";
        $db_password = "password";

        //connect to mysqli database (Host/Username/Password)
        $connection = mysqli_connect($db_host, $db_username, $db_password) or die("Error " . mysqli_error());

        //select MySQLi dabatase table
        $db = mysqli_select_db($connection, "table") or die("Error " . mysqli_error());

    $field1_name = $_POST['name'];
    $field2_name = $_POST['password'];
    $field3_name = $_POST['email'];
    $field4_name = $_POST['sex'];
    $field5_name = $_POST['school'];
    $field6_name = $_POST['birth'];

    $query = mysqli_query($connection, "INSERT INTO create_user
           (name, password, email, sex, school, birth ) VALUES
                    (
                        '$field1_name',
                        '$field2_name',
                        '$field3_name',
                        '$field4_name',
                        '$field5_name',
                        '$field6_name'
                )
    ");

Use this and you will be good. I hope this has helped you!

answered Aug 29 '17 at 16:49

Mr Pro Pop

666
5
19

There are still SQL injection problems here. And the `$_POST` variables may be undefined. I'd like to improve my own answer, but I ran out of time. Maybe tomorrow. – TRiG Aug 29 '17 at 17:36
@TRiG Yes, of course, there will still be a chance of getting a SQL injection attack but not as using MySQL. Never said it is 100% secure. What is more secure is using prepared statements but showed him this method so he can understand it as of his level, and why can $_POST be undefined? because date/sex should be used with checkboxes and select options? I just fixed what he posted not writing it from over. And why do you take it as a competition who posted what? – Mr Pro Pop Aug 29 '17 at 17:49
@TRiG I did not challenge you, you can post or improve whatever you like. The way you comment made me feel like you just want your answer to be posted and nothing else. – Mr Pro Pop Aug 29 '17 at 17:50
Sorry. Didn't mean to make it a challenge or to sound like an attack. Just saying that both of our answers really need some caveats. – TRiG Aug 29 '17 at 17:56
@TRiG if that was what you mean so we are good, and yes you are right in this case ;) – Mr Pro Pop Aug 29 '17 at 20:22

How do I connect my website to a MySQL database?

2 Answers2