Tutorial issues using INSERT INTO without adding a row to database for certain entries

Question

I am following the last part of the following video tutorial "How to create a database website with PHP and mySQL 07 - Add in input form" :

https://www.youtube.com/watch?v=MGIG00d1Xzc&list=PLhPyEFL5u-i0zEaDF0IPLYvm8zOKnz70r&index=7

At the end here is my code, for the inserting portion to the database for the new_jokes.php script (everything up to this point of the series I have gotten to work fine so far)

Basically I am getting the seemingly classic "INSERT INTO" not working although all my syntax looks correct. Am I missing something obvious here? I get no errors, just the row isn't added.

<?php

include "db_connect.php";

$new_joke_question = $_GET["newjoke"];
$new_joke_answer = $_GET["newanswer"];

// Search the database for the word chicken
echo "<h2>Trying to add a new joke and answer: $new_joke_question  
$new_joke_answer </h2>";

$sql = "INSERT INTO Jokes_table (JokeID, Joke_question, Joke_answer) VALUES 
(NULL, '$new_joke_question', '$new_joke_answer' )";
$result = $mysqli->query($sql);

include "search_all_jokes.php";
?>
<a href="index.php">Return to the main page</a>

Here is the db_connect.php code as requested:

<?php

// four variables to connect the database
$host = "localhost";
$username = "root";
$user_pass = "usbw";
$database = "test";

// create a database connection instance
$mysqli = new mysqli($host, $username, $user_pass, $database);

?>

Here is search_all_jokes.php (which has minor error checking):

// if there are any values in the table, select them one at a time 
if ($mysqli->connect_errno) {
    echo "Connection to MySQL failed: (" . $mysqli->connect_errno . ") " . 
$mysqli->connect_error;
} 
echo $mysqli->host_info . "<br>";
$sql = "SELECT JokeID, Joke_question, Joke_answer FROM Jokes_table";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "JokeID: " . $row["JokeID"]. " - Joke_question: " . 
$row["Joke_question"]. " " . $row["Joke_answer"]. "<br>";
    }
} else {
    echo "0 results";
}


?>

Also here is the table structure screenshot viewed in myPHPAdmin:

I added error capturing into new_jokes.php inspired by this Stack Overflow post: INSERT INTO SYNTAX ERROR

And get the following error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't jump.' )' at line 1localhost via TCP/IP

Mandatory comment: This code is susceptible to SQL Injection attacks. You should consider using a [prepared statement](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead. — Mureinik, Dec 08 '18 at 07:26
Maybe Jokeid has a constrain "not null", you should post the table definition. — Luca Lupidi, Dec 08 '18 at 07:48
Hi all I need to figure out how tag everyone but I edited the post to answer all these follow up questions. — Daniel Royer, Dec 10 '18 at 00:40
Earlier parts of the tutorial had error checking but this is basically the eighth part of the tutorial and the code query and connection error checking has already been done and confirmed to work in the localhost webpage (output of the table is displayed from "search_all_jokes.php"). — Daniel Royer, Dec 15 '18 at 13:57
Possible duplicate of [When to use single quotes, double quotes, and back ticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) — miken32, Dec 15 '18 at 17:38
Clearly putting a single quote into a single quote-delimited string won't work. Congratulations, you've just learned one reason why [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) are helpful. — miken32, Dec 15 '18 at 17:40

score 0 · Answer 1 · answered Dec 15 '18 at 19:40

Thank you everyone for helping out with this! Syntax can really throw a wrench in everything. I also will read up on prepared statements since that also could have prevented the issue. The ultimate help to this I found the solution to by adding the function referenced here for MySQLi real_escape_string to clean the single quote I had within the answer I was submitting to my joke table:

(Can a kangaroo jump higher than the empire state building? Of course, the empire state building can't jump.)

As shown in the documentation @miken32 linked as a comment here it is says: "But if $val1 or $val2 contains single quotes, that will make your SQL be wrong. So you need to escape it before it is used in sql; that is what mysql_real_escape_string is for. (Although a prepared statement is better.)"

But now the code for this part 7 of the tutorial on you tube I found works and adds it into a row on the database table, then displaying the full new table on the next webpage. I spent a good while shooting in the dark on while the answer ended up being fairly simple. Again special thanks to @miken32 for pointing me the right direction.

Here is my completed code that ended up working to at least achieve the goal of the tutorial:

<?php

include "db_connect.php";

$new_joke_question = $_GET["newjoke"];
$new_joke_answer = $_GET["newanswer"];

$new_joke_question = $mysqli->real_escape_string($new_joke_question);
$new_joke_answer = $mysqli->real_escape_string($new_joke_answer);

// Search the database for the word chicken
echo "<h2>Trying to add a new joke and answer: $new_joke_question  $new_joke_answer 
</h2>";

if ($mysqli->connect_errno) {
    echo "Connection to MySQL failed: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
} 
echo $mysqli->host_info . "<br>";
$sql = "INSERT INTO Jokes_table (JokeID, Joke_question, Joke_answer) VALUES ('   ', 
'$new_joke_question', '$new_joke_answer' )";
$result = $mysqli->query($sql);


if ($mysqli->query($sql) === TRUE) {
  echo 'users entry saved successfully';
}   
else {
  echo 'Error: '. $mysqli->error .'<br>';
}


include "search_all_jokes.php";
?>
<a href="index.php">Return to the main page</a>

Tutorial issues using INSERT INTO without adding a row to database for certain entries

1 Answers1