PHP INSERT into creates Database error

Question

I am attempting to create a function that will insert items (and will do the same to edit) items in a database through a form. I have the form and the PHP - and when I run the function, I get the correct database name to pull and the variable names to pull along with the values I input, but I then see a database error? Any help would be great (I'm still newer to PHP really and pulling out some hair)

Config File:

$hostname = 'localhost';
$username = 'DEFINED';
$password = 'DEFINED';
$database = 'DEFINED';
$table = 'recipes';

require('../config.php');  
$link = mysql_connect($hostname,$username,$password);
mysql_select_db($database,$link);

/* Get values and submit */
$rid = mysql_real_escape_string($_POST['rid']);
$name = mysql_real_escape_string($_POST['name']);
$category = mysql_real_escape_string($_POST['category']);
$tags = mysql_real_escape_string($_POST['tags']);
$search_tags = mysql_real_escape_string($_POST['search_tags']);
$description = mysql_real_escape_string($_POST['description']);
$description2 = mysql_real_escape_string($_POST['description2']);
$recipeAbout = mysql_real_escape_string($_POST['recipeAbout']);
$ingredients_1 = mysql_real_escape_string($_POST['ingredients_1']);
$directions_1 = mysql_real_escape_string($_POST['directions_1']);

$query = "INSERT INTO $table (name, category, tags, search_tags, description,description2, recipeAbout, ingredients_1,directions_1) VALUES ('$name','$category','$description','$description2' $tags','$search_tags','$description','$recipeAbout','$ingredients_1','$directions_1')";

echo $query;

I don't see a declaration of $table? do you set the value `$table = 'TABLE_NAME_GOES_HERE'` — MiltoxBeyond, Nov 09 '14 at 03:09

score 5 · Accepted Answer · edited May 23 '17 at 11:50

Besides the missing comma in '$description2' $tags' => '$description2', $tags' which you said had been added afterwards, and signaled by Ryan: there's also a missing quote, so change it to '$description2', '$tags' and having 2x '$description' variables, remove one.

VALUES 
('$name','$category','$tags','$description','$description2', '$search_tags','$recipeAbout','$ingredients_1','$directions_1')";

However, the most important part to querying, is that you must use mysql_query() which you are not using => mysql_query() which is why data isn't being inserted, once you've fixed the syntax errors.

mysql_query() is the essential part.

Add the following to your code:

if(mysql_query($sql,$link)){
echo "Success";
}

else{
echo "Error" . mysql_error();
}

Plus, use prepared statements, or PDO with prepared statements.

You're using a deprecated library and open to SQL injection..

Plus make sure you have assigned $table to the table you wish to enter data into. It's not shown in your question.

You also did not show what your HTML form contains. Make sure that you are using a POST method and that all elements are named with no typos.

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

Sidenote: Error reporting should only be done in staging, and never production.

EDIT: and using mysqli_

As a quick test, try the following and replacing the values in the line below with your own.

<?php 
$link = mysqli_connect("host","username","password","database") 

or die("Error " . mysqli_error($link)); 

$table = "recipes";

$name = mysqli_real_escape_string($link,$_POST['name']);
mysqli_query($link,"INSERT INTO `$table` (`name`) VALUES ('".$name."')")

or die(mysqli_error($link));

?>

If that still does not work, then you need to check your database, table, column name(s), including types and column lengths.

Ryan J · Answer 2 · 2014-11-09T03:20:03.813

3

Lot's of stuff wrong here...

You're missing a quote on the second of these two items, as well as either a string concat or a comma: '$description2' $tags'
You've also got your order messed up for tags, search tags, and description 1/2.
$description is in there twice (you have 9 columns defined and 10 values in your statement)
You don't seem to have declared a value for $table
As Fred -ii- has pointed out in his answer, you're missing mysql_query() to actually run it. I assumed you have it further down in your code, but it's missing from the post, which is causing some confusion...

Also, consider updating to use mysqli instead of mysql functions.

edited Nov 09 '14 at 03:20

answered Nov 09 '14 at 03:06

Ryan J

8,275
3
25
28

I added the comma, and updated to `mysqli` for all and still get a database error. – jacob Nov 09 '14 at 03:09
OP also had a missing quote `'$description2' $tags'` => `'$description2', '$tags'` – Funk Forty Niner Nov 09 '14 at 03:26
@Fred-ii- yep, caught that too. It's the first bullet point. :) – Ryan J Nov 09 '14 at 03:27
My bad, I'm more of "graphical person" lol. +1 ;) good catch, as outlined in my answer, kudos to you Ryan. – Funk Forty Niner Nov 09 '14 at 03:29
@Fred-ii- much appreciated, good catch on the query. That should have been more obvious to me... – Ryan J Nov 09 '14 at 03:30
1

You're welcome Ryan. I'm hoping this one will be resolved and soon. OP says that the connection/login is being denied. This may be a long night lol – Funk Forty Niner Nov 09 '14 at 03:36

score 0 · Answer 3 · answered Nov 09 '14 at 03:12

0

what are you echoing $query for? You do not have any reason to do that except if you just want to use it as a string variable.

it should be mysql_query($query);

answered Nov 09 '14 at 03:12

score 0 · Answer 4 · edited May 23 '17 at 12:15

0

What is the exact "database error" error you are getting?

I suggest reading this article about PDO
If you can't insert the data correctly, this might be your problem too.

edited May 23 '17 at 12:15

Community

1
1

answered Nov 09 '14 at 03:21

Ekin

1,957
2
31
45

PHP INSERT into creates Database error

4 Answers4