Jump to content

Madlib story saving to sql db

Nicole2320

By Nicole2320
in PHP Coding Help

 Share

Recommended Posts

Nicole2320 Newbie

Nicole2320

Posted
Posted 
$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.

 

Link to comment
https://forums.phpfreaks.com/topic/312487-madlib-story-saving-to-sql-db/
Share on other sites
requinix Prolific Member

requinix

Posted
Posted

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

Nicole2320 Newbie

Nicole2320

Posted
  • Author
Posted
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

kicken Prolific Member

kicken

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.