Madlib story saving to sql db

Nicole2320 · April 18, 2021

$name = mysqli_real_escape_string($link, $_POST['name']);
$adjective = mysqli_real_escape_string($link, $_POST['adjective']);
$verb = mysqli_real_escape_string($link, $_POST['verb']);
$noun = mysqli_real_escape_string($link, $_POST['noun']);

$sql = "INSERT INTO mad (name, adjective, verb, noun) VALUES ('$name', '$adjective', '$verb', '$noun')";
if(mysqli_query($link, $sql)){
    echo "Records added successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

$name= ucwords($name);

$title="The Life of $name";
echo "<title>$title</title>";
?>

<style type="text/css">
form {text-align: right;}
    
.madlibbox {       
    background: #F2F1E9;
    border-right: #25201A 1px solid;
    border-top: #25201A 1px solid;
    border-left: #25201A 1px solid;
    border-bottom: #25201A 1px solid;
    padding: 10px 10px 10px 10px;
    font-size: medium;
    margin: auto;
    width: 340px;
    font-family: Verdana, "Myriad Web", Syntax, sans-serif; 
        }  

body    {   
    padding: 0;
    border: 0;
    background: #DFDFC8;
    width:  auto;
    text-align: center;
    }       
    
h1 {padding: 10px; text-align: center; text-transform: uppercase;}
</style>
</head>
<body>
<div class = "content">
<?php
echo "<h1>$title</h1>";
?>

<div class = "madlibbox">
<?php

echo "There once was a named $name who lived in $noun
and liked to $verb and talk to the neighbor's $adjective. This bothered 
$name's best friend drew. The end.";

?>
</div>
</div></body></html>

I am new to programming and I have no clue on how to do the following. I want to be able to save the output of the story to a sql database. Once it is display, have them click on a link to have previous save stories to be displayed randomly or have it generate a random story based on previous save inputs to the database. This code works to display the story and save the variables inputs.

requinix · April 18, 2021

A few ways of tackling the problem. The one that probably makes most sense for how you've set this up so far would be the simplest one: add a column to your "mad" table for the final phrase. If you don't have one already, there should also be an AUTO_INCREMENT ID in that table so you can easily refer to specific rows - this column is dealt with for you so you don't need to come up with values or put it into your INSERT queries.
Then you'll need to get that phrase. Right now you just output it, so change that process so (1) you store the phrase in a string variable and then (2) you echo that.

Depending on what values you pick for your madlib, you may or may not discover that you can't use $name and $adjective and such after you've escaped them. Try it: pick a name like "O'Malley" or a noun like "my father's shoe" and find out what happens.
See the backslash in front of the quotes? $name and such as fine for using in a SQL query but that's because their values have been altered, and that will interfere with you trying to put it into a sentence.

To fix that you should forget about mysqli_real_escape_string and instead switch to prepared statements. What you do with your $sql now is combining SQL syntax (parts like the INSERT and the list of column names) with data (the parts of speech) and this has the potential to confuse MySQL if something in the data isn't quite right. It's why you have to use mysqli_real_escape_string - but then you have the backslashes. There are simple ways to resolve that, but the best way is a prepared statement: instead of combining syntax and data, it keeps them separate and tells MySQL "here's the query and here are places where you need to use a piece of data". No combining them means no confusion.
Prepared statements are easy: look here for a reference that tells you how to use ->prepare and ->bind_param and ->execute.

With all that in place, you should have:
1. A string variable for the phrase that has all the data inserted into it. Make sure it looks right when your words have apostrophes.
2. A "mad" table that has columns for an ID, name, adjective, verb, noun, and the final phrase.
3. An INSERT query that mentions the phrase column (but not ID) and uses question marks for where the different pieces of data go.
4. Code that ->prepare()s the query string you had, ->bind_param()s the different variables (the data coming from $_POST directly is fine, and the string phrase), and ->execute()s.

That's enough work to start with. When that is in place, look in your database to see that it's inserting everything correctly. Then post your code, we'll look over it, maybe have a suggestion or two, and then move on to the next part: the random link to a previous story (this will be easy), and/or the random story from previous values (this will be more complicated because we should do some more changes to your database to help with it).

Barand · April 18, 2021

1 hour ago, Nicole2320 said:

the neighbor's $adjective

Shouldn't that be...

the $adjective neighbors

Nicole2320 · April 19, 2021

4 hours ago, requinix said:

A few ways of tackling the problem. The one that probably makes most sense for how you've set this up so far would be the simplest one: add a column to your "mad" table for the final phrase. If you don't have one already, there should also be an AUTO_INCREMENT ID in that table so you can easily refer to specific rows - this column is dealt with for you so you don't need to come up with values or put it into your INSERT queries.
Then you'll need to get that phrase. Right now you just output it, so change that process so (1) you store the phrase in a string variable and then (2) you echo that.

Depending on what values you pick for your madlib, you may or may not discover that you can't use $name and $adjective and such after you've escaped them. Try it: pick a name like "O'Malley" or a noun like "my father's shoe" and find out what happens.
See the backslash in front of the quotes? $name and such as fine for using in a SQL query but that's because their values have been altered, and that will interfere with you trying to put it into a sentence.

To fix that you should forget about mysqli_real_escape_string and instead switch to prepared statements. What you do with your $sql now is combining SQL syntax (parts like the INSERT and the list of column names) with data (the parts of speech) and this has the potential to confuse MySQL if something in the data isn't quite right. It's why you have to use mysqli_real_escape_string - but then you have the backslashes. There are simple ways to resolve that, but the best way is a prepared statement: instead of combining syntax and data, it keeps them separate and tells MySQL "here's the query and here are places where you need to use a piece of data". No combining them means no confusion.
Prepared statements are easy: look here for a reference that tells you how to use ->prepare and ->bind_param and ->execute.

With all that in place, you should have:
1. A string variable for the phrase that has all the data inserted into it. Make sure it looks right when your words have apostrophes.
2. A "mad" table that has columns for an ID, name, adjective, verb, noun, and the final phrase.
3. An INSERT query that mentions the phrase column (but not ID) and uses question marks for where the different pieces of data go.
4. Code that ->prepare()s the query string you had, ->bind_param()s the different variables (the data coming from $_POST directly is fine, and the string phrase), and ->execute()s.

That's enough work to start with. When that is in place, look in your database to see that it's inserting everything correctly. Then post your code, we'll look over it, maybe have a suggestion or two, and then move on to the next part: the random link to a previous story (this will be easy), and/or the random story from previous values (this will be more complicated because we should do some more changes to your database to help with it).

<?php

$servername = "sn";
$username = "us";
$password = "pw";
$dbname = "db";

$name = $_POST['name'];
$adjective = $_POST['adjective'];
$noun = $_POST['noun'];
$verb = $_POST['verb'];


// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
  die("Connection failed: " . $conn->connect_error));
}
echo "Connected successfully";



// prepare and bind
$stmt = $conn->prepare("INSERT INTO mad (name, adjective, verb, noun)  VALUES (?, ?, ?, ?)";
$stmt->bind_param("ssss", $name, $adjective, $verb, $noun);

$stmt->execute();



$stmt->close();
$conn->close();

When I try to run the prepared stmt part to make sure I can get the data inserted in the table from the form, I get an error. If i take everything out at the prepared statment, it connects successfully. The error I get is This page isn’t working HTTP ERROR 500

Nicole2320 · April 19, 2021

3 hours ago, Barand said:

Shouldn't that be...

the $adjective neighbors

I will update the story once I get everything working.

kicken · April 19, 2021

2 hours ago, Nicole2320 said:

The error I get is This page isn’t working HTTP ERROR 500

You have a syntax error here:

2 hours ago, Nicole2320 said:

$stmt = $conn->prepare("INSERT INTO mad (name, adjective, verb, noun) VALUES (?, ?, ?, ?)";

You're missing your closing ) at the end of your call to prepare().

Nicole2320 · April 19, 2021

1 hour ago, kicken said:

You have a syntax error here:

You're missing your closing ) at the end of your call to prepare().

I fixed it and still get the error.

kicken · April 19, 2021

8 hours ago, Nicole2320 said:

die("Connection failed: " . $conn->connect_error));

You have another syntax error there. Extra ).

Barand · April 19, 2021

You could save everyone's time by turning on php's

error_reporting,
display_errors,
display_startup_errors

in your php.ini file

Sign In

Madlib story saving to sql db

Recommended Posts

Nicole2320

Link to comment

Share on other sites

requinix

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Nicole2320

Link to comment

Share on other sites

Nicole2320

Link to comment

Share on other sites

kicken

Link to comment

Share on other sites

Nicole2320

Link to comment

Share on other sites

kicken

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information