Issue inserting data into MySQL database using php

Question

I'm trying to insert data into a table using php. I have done this to register users and it works fine, but I'm now trying to do it to submit reviews and I keep getting errors. I have tried searching for an answer but I can't seem to figure out what the problem is.

I have done some debugging and I know that the variables are storing the correct data and that the php is connecting to the correct table, however when I try to insert the variables into the table it doesn't work.

Here is my PHP:

 <?php
session_start();
$dbhost = 'localhost';
$dbuser = 'rlr17';
$dbpass = 'rlr17';
$dbname = 'rlr17';
$dbtable = 'bookclubreviews';

// connect to the database
$db = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql database '. mysql_error());

 $bookID=$_GET["bookID"];
 $userID=$_GET["userID"];
 $reviewTitle=$_GET["reviewTitle"];
 $reviewContent=$_GET["reviewContent"];
 $rating=$_GET["ratingToSubmit"];
 $reviewID= uniqid($id).date("ymd");               

if (!$db) {
    die('Not connected : ' . mysql_error());
} else {

}

// select the table
$dbselect = mysql_select_db($dbname);


if (!$dbselect) {
    die ('Can\'t use $dbname : ' . mysql_error());
} else {
    echo "connected to $dbname";
}

if ($bookID=='') {
    $bookID="empty";
}
if ($userID=='') {
    $userID="empty";
}
if ($reviewTitle=='') {
    $reviewTitle="empty";
}

if ($reviewContent=='') {
    $reviewTitle="empty";
}
if ($rating=='') {
    $rating="empty";
}

//the next 4 lines are to test that the right table is being connected to - it is, this works
$sql1="SELECT * FROM $dbtable WHERE userID='$userID'";
$result1 = mysql_query($sql1,$db);
$result4 = mysql_num_rows($result1);
echo "worked - $result4 <br>";

//This is the bit that I can't get to work. 
$insert = "INSERT INTO  $dbtable VALUES('$userID','$bookID','$reviewTitle','$reviewContent','$rating')";
$result=mysql_query($insert,$db); 

if ($result) {
    echo "review submitted". ".<br>"; 
    $data = '';
    include( 'home.php' ) ;

} else {  
    echo 'Error with submitting data <br>' . $bookID . $userID . $reviewTitle . $reviewContent . $rating . $reviewID . "<br> db: " .$db;  
} 
mysql_close($db);
?>

And this is a screenshot of how my table is set up

and this is a link to my work - http://itsuite.it.brighton.ac.uk/rlr17/bookClub/insertReview.php?bookID=5&userID=rlr17&reviewTitle=Test&reviewContent=test&ratingToSubmit=4

Any hints would be greatly appreciated!

mysql_ functions have been deprecated since 2013 and **don't exist in PHP anymore**, please stop using them. Also see [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) On top of that, your code is wide open to SQL injection attacks. — Oldskool, Apr 07 '16 at 12:39
[Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Apr 07 '16 at 12:45
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Apr 07 '16 at 12:45
use mysqli or PDO. if you are lazy, changing mysql to mysqli is just a letter in most of the cases to start with. eg: mysql_query is mysqli_query, mysql_fetch_assoc is mysqli_fetch_assoc refer documentation for more info — krishna, Apr 07 '16 at 12:45
Have you checked your error logs? You're making an assumption the query is working. Add error reporting to the top of your file(s) right after your opening ` — Jay Blanchard, Apr 07 '16 at 12:46

score 1 · Accepted Answer · answered Apr 07 '16 at 12:46

1

Your table has 6 fields and you are trying to insert only 5 field values.

If you are not mentioning fields list in the INSERT query, then it means, you are inserting all columns.

Try this (Insert all columns):

$ratingId = '';
$insert = "INSERT INTO  $dbtable VALUES('$userID','$bookID','$reviewTitle','$reviewContent','$rating','$ratingId')";

OR Specify name of columns

$insert = "INSERT INTO  $dbtable (userID,bookID,reviewTitle,reviewContent,rating)VALUES('$userID','$bookID','$reviewTitle','$reviewContent','$rating')";

answered Apr 07 '16 at 12:46

Pupil

23,141
5
40
62

Better use `NOW()` instead of `'$ratingId'`. – syck Apr 07 '16 at 12:49
I thought I'd tried that - I tried such a combination of things but they must have been mismatched. Thanks for your help, specifying the names of the columns without reviewid being involved at all seems to have fixed it. Thanks!! – Becky Rush Apr 07 '16 at 13:39

Issue inserting data into MySQL database using php

1 Answers1