Friday, August 2, 2019

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



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:



/* 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:







Add Music to Database



















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


Answer



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






should be







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



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()).






No comments:

Post a Comment

plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...