使用php将数据从html表单插入到mysql数据库中的数据表中
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
", 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.
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
", 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
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"