Inserting data to a database through a form not working

PHP
1 
<?php
error_reporting(E_ALL);
var_dump( $_POST );
ini_set('display_errors', 1);

$conn = mysqli_connect('localhost','root','root','database_name');
if (!$conn)
  {
  echo 'Could not connect: ' . mysqli_connect_error();
  exit;
  }

$sql="INSERT INTO table_name (column1, column2, column3, column4, column5, column6, column7, column8, column9)
VALUES
('$_POST[column1]','$_POST[column2]','$_POST[column3]','$_POST[column4]','$_POST[column5]','$_POST[column6]','$_POST[column7]','$_POST[column8]','$_POST[column9]')";

if (!mysqli_query($conn,$sql))
  {
  echo 'Error: ' . mysqli_connect_error());
  }
echo "1 record added";

mysqli_close($conn);
?>

What’s wrong in this code?

2

The problem is the same as the one you had with the session :http://www.sitepoint.com/forums/showthread.php?t=713415

Take a look at the link I posted in that thread, and se if you can come up with a solution…
If it all fails, then post the code you arrive at, and lets take it from there.

3

Ahah I don’t know why, but I thought it was a problem with the quotes, and so I was expecting an answer from you :smiley:

Let me try ^^

4

Wait a second, on the w3schools website they do exactly the same thing: http://www.w3schools.com/php/php_mysql_insert.asp

5

You’re forgetting about the array variables…
For simple variables it works just fine that way, but for arrays you need to go out of the string and concatenate…

your $_POST variables should look like this:

$_POST[[COLOR="Red"]'[/COLOR]field[COLOR="Red"]'[/COLOR]] or $_POST[[COLOR="Red"]"[/COLOR]field[COLOR="Red"]"[/COLOR]]
6

You missed to mention the actual problem. But to construct the variables within string would be to separate the variables:


$sql = "INSERT INTO table_name (column1, column2, column3, column4, column5, column6, column7, column8, column9)
VALUES
('" . $_POST['column1'] . "','" . $_POST['column2'] . "','" . $_POST['column3'] . "',
'" . $_POST['column4'] . "','" . $_POST['column5'] . "','" . $_POST['column6'] . "',
'" . $_POST['column7'] . "','" . $_POST['column8'] . "','" . $_POST['column9'] . "')";
7

99 times out of a 100 if a query is not working, then either you are not connected to the database or the structure or syntax in your query is wrong - or both.

after you establish you are connected to the database, if you echo to the screen the sql query that is actually being run you should be able to work out what is wrong with the query.

8

Exactly, but isn’t the example on the w3schools website the same as this one?

This is still not working :frowning:

The fact is, often I am not even able to echo anything, like in this case.

9

at the very top of your php script you should echo out the sent form data - $_POST or $_GET variables to make sure it has come across ok before doing anything else in your script.

there is no point writing or testing additional code if you haven’t first confirmed that all the data has come into the script correctly.

10

I’ve managed to echo the content of $_POST and it’s correct.

Problem solved: the problem was that I had a typo in the name of a column in the database table :smiley:

11

then this could be a good opportunity to hone your debugging skills.

what I would normally do in a situation like this is

  1. start at the top of the script as specified in your form’s action attribute and add
echo 'got here'; die();

  1. run your form to check if it gets to your php script

  2. then move the above echo/die down, line by line if you have to, and add appropriate echo statements to display values of variables and then run the form again each time you move the echos.

  3. as part of 3) insert the echos in each part of conditional blocks (IF blocks) to check your code logic is correct

keep doing this until your echos show something is not right. then back track your code to fix the error.

  1. keep repeating 3) and 4) until you get to the end of your script and it works ok.

if you have a debugger, then debugguing will be easier as you can set break points and check values of variables which is essentially what the above steps are doing.

think of debugging as character building.

12

let me then suggest to you another very valuable debugging technique: never code a query into php that you haven’t tested outside of php first

13

Sorry for the stupid question, but how do you suggest that I test a query outside PHP?

14

it’s only a stupid question if you don’t ask it :slight_smile:

try heidisql, sqlyog, the mysql query browser, phpmyadmin, toad, or any of the many other available front end apps

or even (gulp) the dreaded command line

15

Ok, I was thinking about using phpmyadmin in the first place :slight_smile: I’ll have a look at the other tools too. Thanks for your help :wink:

16

you shouldn’t need to test very simple queries outside of php first but for more complicated queries I normally develop and test in SQLyog where error message are usually more meaningful.

or if you are debugging someone else’s non working sql query coded in php, or whatever, it can be helpful to first echo out the actual query being run to check for syntax and/or structure errors.