Inserting data from html form using php into data table in mysql database

Question

I'm new here and have recently started studying various forms of code to create simple solutions to my various projects. I've used many of the helpful tips a lot of you have posted on this website but I think I have finally reached a point where I can't figure out for the life of me how to fix. So I decided to turn to you all for help. It seems like it should be a simple solution to me but I cannot find it so maybe fresh eyes will help. So here it is I hope someone may be able to assist me. I help run an event here in my city that uses the video game Rock Band for karaoke purposes. I have a table setup called rbn_setlist_small that has two columns of 'Artist' and 'Song Title'. I have to periodically insert more songs into the table to account for newly purchased songs. So I created a form to insert the data into the table. It's a Simple form that has two fields Artist and Song Title. Whenever I enter test information (say Artist: 123, Song Title: test) it says the data has been entered but when I go and check the table the new data that has been entered just has a blank spot under Artist and Title under Song Title. So I'm sure I'm missing a comma or something somewhere but I cannot find it.

This is the php for the form:

<?php
/* Attempt MySQL server connection.*/
$link = mysqli_connect("host", "user", "pass", "db");

/*Check connection*/
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/*attempt insert query execution*/
$query = "INSERT INTO `rbn_setlist_small`(`Artist`, `Song Title`) 

VALUES ('$Artist', '$Song Title')";
if ($result = mysqli_query($link, $query)) {
    echo "Records added successfully.";
} else{
    echo "ERROR: Could not execute $sql. " . mysqli_error($link);
}

/*close connection*/
mysqli_close($link);
?>

and this the HTML for the form:

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Add Music to Database</title>
</head>
<body>
<form action="insert.php" method="post">
    <p>
        <label for="Artist">Artist:</label>
        <input type="text" name="Artist" id="Artist">
    </p>
    <p>
        <label for="Song Title">Song Title:</label>
        <input type="text" name="Song Title" id="Song Title">
    </p>
    <input type="submit" value="Submit">
</form>
</body>
</html>

Also any assistance in my coding is appreciated I'm a super novice. Thank you all for any assistance.

Where do you define `$Artist` and `$Song`? Does `mysqli_error()` in your code output anything? Also note that variables can't have spaces in them, and that it's bad practice to have column-names in the database with spaces. Use underscore instead. — Qirel, Mar 16 '17 at 23:25
you can't define a variable like this `$Song Title` it could be `$Song_Title` — Amr Aly, Mar 16 '17 at 23:28
I thought that would be whatever is entered into the form maybe I was wrong there. There are no errors reported because it connects and adds the data just fine. It's just not the data I want added. I could change the column name to have an underscore in it instead of space. That would be the way to go there correct? — RSanchez, Mar 16 '17 at 23:29
I think you should start by following a tutorial or online free course before asking questions. — Ibu, Mar 16 '17 at 23:42

score 0 · Accepted Answer · edited May 23 '17 at 12:02

0

It seems like you've simply forgot to fetch the value from the form! That aside, your $Song Title isn't a variable, it's a variable, then a space, then the string "Title".

I recommend you don't use any names in a form or in the database that contains spaces, use underscore as a replacement (or choose simpler names). So for instance, your

<input type="text" name="Song Title" id="Song Title">

should be

<input type="text" name="Song_Title" id="Song_Title">

instead, which can be fetched in PHP with $_POST['Song_Title']. Using that, we can send it to the database. I've modified your code with the following improvements:

Fetching the values from the form, using $_POST
Added parameterized queries with placeholders (mysqli::prepare) to protect against SQL injection
Added checks (isset()) so we insert values only if the form has been sent

The above points would result in the following PHP snippet

/* Attempt MySQL server connection.*/
$link = mysqli_connect("host", "user", "pass", "db");

/*Check connection*/
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

if (isset($_POST['Artist'], $_POST['Song_Title'])) {
    if ($stmt = $link->prepare("INSERT INTO `rbn_setlist_small`(`Artist`, `Song Title`) VALUES (?, ?)")) {
        $stmt->bind_param("ss", $_POST['Artist'], $_POST['Song_Title']);
        if (!$stmt->execute()) {
            error_log("Execute failed ".$stmt->error);
        } else {
            echo "Data successfully inserted! Artist ".$_POST['Artist']." and song ".$_POST['Song_Title'];
        }
        $stmt->close();
    } else {
        error_log("Prepare failed ".$link->error);
    }
}

Also, when troubleshooting, PHP will give you the exact errors if you just enable error-reporting, by adding

<?php 
error_reporting(E_ALL);
ini_set("display_errors", 1);

at the top of your file. MySQLi will also throw back whatever errors it might see with $link->error for normal MySQLi functions, and $stmt->error when using MySQLi-statements (for objects called on the object created by $link->prepare()).

References

edited May 23 '17 at 12:02

Community

1
1

answered Mar 16 '17 at 23:34

Qirel

25,449
7
45
62

Thank you for the quick Reply Qirel I have attempted replacing my code with your snippet but when I do and I hit submit now instead of saying "Records added successfully." which I assume is because the code doesn't tell it to say this. It now just takes me to a blank page. Also when I go to the table to check the data it did not add it. I completely forgot an important bit however that may be why. The table is actually 3 columns (id, Artist, Song_Title) the id column however is set to auto increment so I wouldn't want anything inserted here since the database will create this itself. – RSanchez Mar 16 '17 at 23:57
Because you don't specify an id-column in the query, that doesn't really matter. There could be errors in my code, so I suggest you add error-reporting like the bottom of my answer advises, and read the logs. That might tell you exactly whats wrong. And no, there isn't anything in this code originally that tells it was successfully inserted, but I'll edit it in if you want. – Qirel Mar 16 '17 at 23:59
Also a question about the (mysqli::prepare) i had read about this but decided against it because i read it stops special characters. Since this is different artists and songs sometimes special characters are needed did i read this wrong? – RSanchez Mar 16 '17 at 23:59
Yes, you read that wrong. That's a matter of *charset* which is a topic of its own. `mysqli::prepare` and `mysqli::query` handles the same characters. If you're going to deal with non-standard characters, such as ÆØÅ, Greek characters or the like, you need to specify a UTF-8 charset across your application. I have previously given [**answers with a little list**](http://stackoverflow.com/questions/31897407/mysql-and-php-utf-8-with-cyrillic-characters/31899827#31899827) on what to do to properly set the charset when dealing with databases, you could check that out. – Qirel Mar 17 '17 at 00:02
Thank you I will look at that now. – RSanchez Mar 17 '17 at 00:03
What you may have read is that it prevents using characters like `'` to break the query, or to use it for SQL-injection. In any case, always use `prepare()` over `query()`, **specially** if it's not static values. Just to be safe ;-) – Qirel Mar 17 '17 at 00:04
Thanks Qirel! This worked wonderfully the blank page issue I think is that since I changed the column name to include an underscore the 'Song Title' before VALUE also needed to have the underscore Now everything works great! :D – RSanchez Mar 17 '17 at 16:01

score 0 · Answer 2 · answered Mar 16 '17 at 23:41

0

You did not define your variables. Add

$Artist = $_POST['Artist'];
  $Song_title= $_POST['Song_title'];

Near the top of your php code

Also change the name of "Song Title" in your HTML to "Song_title"

answered Mar 16 '17 at 23:41

liquidacid

108
1
9

Inserting data from html form using php into data table in mysql database

2 Answers2

References