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? On top of that, your code is wide open to SQL injection attacks. — Oldskool
– Oldskool, Commented Apr 7, 2016 at 12:39
Little Bobby says your script is at risk for SQL Injection Attacks.. Even escaping the string is not safe! — Jay Blanchard
– Jay Blanchard, Commented Apr 7, 2016 at 12:45
Please stop using mysql_* functions. These extensions have been removed in PHP 7. Learn about prepared statements for PDO and MySQLi and consider using PDO, it's really pretty easy. — Jay Blanchard
– Jay Blanchard, Commented Apr 7, 2016 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
– krishna, Commented Apr 7, 2016 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 <?php tag error_reporting(E_ALL); ini_set('display_errors', 1); Add error checking, such as or die(mysql_error()) to your queries. Or you can find the issues in your current error logs. — Jay Blanchard
– Jay Blanchard, Commented Apr 7, 2016 at 12:46

Pupil · Accepted Answer · 2016-04-07 12:46:17Z

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 7, 2016 at 12:46

Pupil

24k6 gold badges49 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

syck Over a year ago

Better use NOW() instead of '$ratingId'.

Becky Rush Over a year ago

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!!

Collectives™ on Stack Overflow

Issue inserting data into MySQL database using php

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related