Why can't insert data into the database?

the html file


<form method="post" action="add.php">
<input type="hidden" name="id" value="NULL" />
<table>
<tr height="20">
<td colspan="2"><font size="+0" face="verdana">Below is a sample form for our php</td>
</tr>
<tr height="50">
<td></td>
</tr>
<tr>
<td align="left"><font size="+0" face="verdana"><b>Your name<br />
Your Email Address</b></td>
<td><input type="text" name="name"><br />
<input type="text" name="email" />
</td> 
</tr>
<tr>
<td colspan="2"><center>
<select name="opinion">
<option value="is greate">I like your site</option>
<option value="is OK">you site is ok</option>
<option value="is horrible">your site is horrible</option>
</select>
<input  type="submit" value="tell us"!/>
</td>
</tr>
</table>
</form>


add.php file


<?php

/**
 * @author runeveryday
 * @copyright 2010
 */

$DBhost ="localhost"; 
$DBuser = "root"; //mysqluser
$DBpass = "123"; //mysql password
$DBName = "learnphp";// database name
$table = "information"; //table name
mysql_connect($DBhost,$DBuser,$DBpass) or die("error");
mysql_select_db("$DBName") or die("error");
$sqlquery = "INSERT INTO $table VALUES ('$_POST[id]','$_POST[name]','$_POST[email]','$_POST[opinion]')";
$results=mysql_query($sqlquery);
mysql_close();
echo "<html><titile>PHP and mysql</title><body><p><center>you just enter this information into the database
<p><blockquote>";
print "Name:$_POST[name]<p>E-mail:$_POST[email]<p>opinion:$_POST[opinion]</blockquote></center>";

?>

i run this command in the PhpMyAdmin. it’s ok.

INSERT INTO information (name,email,opinion) VALUES(‘qq’,‘admin@gmail.com’,‘is great’)

maybe the question is the type of the ID,but i don’t know how to correct it ,so i can’t insert the data to the table.
this is the code that i created the database.

mysql> CREATE TABLE information (
> id INT NOT NULL AUTO_INCREMENT,
> name VARCHAR (40),
> email VARCHAR (40),
> opinion VARCHAR (30),
> PRIMARY KEY (id)
);

Here we go, tried and tested on my LOCALHOST:

add.php


<?php
	if (! empty($_POST))
	{
		echo '<pre>';
			print_r($_POST);
		echo '</pre>';
	}//endif
	
	define('TABLE_NAME', 	'information_002');
	
	define('HOST_NAME', 	'your_server_name');
	define('USER_NAME', 	'your_user_name');
	define('PASSWORD', 	'your_password');
	define('DATABASE',	'your_database');
	
	$link=mysql_connect
	(
		HOST_NAME, 
		USER_NAME, 
		PASSWORD
      )  or die('Cannot connect to the database  because: ' . mysql_error());

	mysql_select_db (DATABASE);
	echo '<br />Connected OK:';
	
	$sql =	'CREATE TABLE IF NOT EXISTS ' .TABLE_NAME 
				.	'(
						 id INT NOT NULL AUTO_INCREMENT,
						 name 		VARCHAR (40),
						 email 		VARCHAR (40),
						 opinion 	VARCHAR (30),
						 PRIMARY	KEY (id)
						);
					';	
	echo '<br />'.TABLE_NAME .' table created: : '. $result = mysql_query($sql);
	
	echo '<br />'.mysql_error(); 
	echo 'Connected successfully';
	
	// $sql = 'INSERT INTO ' .TABLE_NAME ."(name,email,opinion) VALUES('qq','admin@gmail.com','is great')";
	$sql = 'INSERT INTO '
			 .	TABLE_NAME 
			 .	"	(name, email, opinion) 
			 			VALUES
			 			("
			 				."'". $_POST['name'] 		."', "
			 				."'". $_POST['email'] 	."', "
			 				."'". $_POST['opinion'] ."'  "
			 .	 ");";

	echo '<br />'.$sql; 
	echo '<br />'.mysql_error(); 
	
	echo '<br />Result: '. $result = mysql_query($sql);
	echo '<br />'.mysql_error(); 
	
	echo '<br />mysql_affected_rows(): '. $result = mysql_affected_rows();
	echo '<br />'.mysql_error(); 
	
	echo '<br />'.$sql = 'SELECT * FROM ' . TABLE_NAME .';' ;
	echo '<br />Result: ' .$result = mysql_query($sql);
	
	if (!$result) {
	    die('Could not query table:' . mysql_error());
	}
	
	while ($row = mysql_fetch_object($result)) {
	    echo '<br />'			.$row->id;
	    echo ', &nbsp; '	.$row->name;
	    echo ', &nbsp; '	.$row->email;
	    echo ', &nbsp; '	.$row->opinion;
	}
	
	echo '<br />mysql_close(): '. mysql_close($link);
	echo '<br />'.mysql_error(); 
?>



.

As someone said before, the id field is an integer, and in your query you’re supplying a string (‘NULL’).

If you remove the quotes around NULL you’ll get a bit further.


$sqlquery = "INSERT INTO $table VALUES (NULL, '".mysql_real_escape_string($_POST[name])."','".mysql_real_escape_string($_POST[email])."','".mysql_real_escape_string($_POST[opinion]."')"; 

So…



 $sqlquery = "INSERT INTO $table VALUES ('$_POST[id]','$_POST[name]','$_POST[email]','$_POST[opinion]')";

  // 
  echo $sqlquery; 


Please post the echoed $sqlquery string.

Is the first item you are trying to insert an indexed field?

If so try this method of speficifally naming the fields to be inserted:



"INSERT INTO 
   $table ('field_name', field_name', field_name' )
 VALUES 
    (".$_POST['name'].",".$_POST['email'].",".$_POST['opinion'].")";


.

I think it has to do with the $_POST[‘…’] embedded in double quotes. I remember I had that problem, it didn’t retrive the post in double quotes. You need to separate it with:


"INSERT INTO $table VALUES ('NULL',".$_POST[name].",".$_POST[email].",".$_POST[opinion].")";

Now that you have some $_POST variables to insert then try inserting them one at a time using this SQL statement.



INSERT INTO tbl_name (a,b,c) VALUES(1,2,3), (4,5,6), (7,8,9);

// Also check for any errors
echo mysql_errno($link) . ": " . mysql_error($link) . "\
";


.

“INSERT INTO $table VALUES (‘NULL’,”.$_POST[‘name’].“,”.$_POST[‘email’].“,”.$_POST[‘opinion’].“)”;

i corrected the insert code to the above,after i run the code,in the table,there is no data.

Print out $sqlquery before executing the query, then try to execute it in PhpMyAdmin to see if there are any errors. If you can post the printed $sqlquery here that would be good also.

$sqlquery = “INSERT INTO $table VALUES (‘NULL’,$_POST[name]‘,’$_POST[email]‘,’$_POST[opinion]')”;

i want to konw why this doesn’t work?

to me. it also displays the submitted $_POST variables. but i don’t know why the code in add.php can’t insert the data to the database table.

i add

or die(mysql_error());
behind the code
$results=mysql_query($sqlquery).
it show an error
you have an error in you SQL syntax,check the manual that corresponds to your MySQL server version for the right syntax to use near 'is horrible)'at line 1

I tried your script and it displays the submitted $_POST variables.

Maybe try fixing the HTML validation errors using: http://validator.w3.org/

and use this in your PHP file:


  
  error_reporting(E_ALL);
  ini_set('display_errors', 'On');


.

i added this

  echo '<pre>';
     print_r($_POST);
  echo '</pre>';

to the add.php,but there isn’t any output.

i am sorry, i have tested the $_POST[‘name’].it show me an error

Try this:


...
...
  echo '<pre>';
     print_r($_POST);
  echo '</pre>';
// check that you have something to post then remove next 'die;'  line and refresh browser
die;


  $table = "information"; //table name 
  mysql_connect($DBhost,$DBuser,$DBpass) or die("error"); 
  mysql_select_db("$DBName") or die("error"); 
  $sqlquery = "INSERT INTO $table VALUES ('$_POST[id]','$_POST[name]','$_POST[email]','$_POST[opinion]')"; 
  echo $sqlquery;
  // copy and paste above string into http://localhost/phpmyadmin/
  // check that you have something to post then remove next 'die;'  line and refresh browser
die;

  $results=mysql_query($sqlquery); 
  echo "Records inserted: &#37;d\
", mysql_affected_rows());
  // check to see how many records wered posted then remove next 'die;'  line and refresh browser
die;


.

What is the genereated error message?

.

With $_POST[name], php thinks name is a constant, $_POST[‘name’] is the correct way to do this. You can either use the advice oddz gave several posts ago, or use concatenation to get around the quoting problems.

Can you give some more information?



  echo '<pre>'; 
     print_r($_POST); 
  echo '</pre>'; 
// check that you have something to post

  $table = "information"; //table name 
  mysql_connect($DBhost,$DBuser,$DBpass) or die("error"); 
  mysql_select_db("$DBName") or die("error"); 
  $sqlquery = "INSERT INTO $table VALUES ('$_POST[id]','$_POST[name]','$_POST[email]','$_POST[opinion]')";

  // 
  echo $sqlquery; 

  $results=mysql_query($sqlquery); 
  echo "Records inserted: &#37;d\
", mysql_affected_rows()); 
  // check to see how many records wered posted then remove next 'die;'  line and refresh browser 
die; 


Please use the above scripot and let us know the results rather than “there is no data in the table”.

.

Well the immediate problem I see that NULL (value for id) will be surrounded by quotes when it shouldn’t be.

The other problem is that $_POST[name] is not the correct syntax. That should be $_POST[‘name’]. You will also need to surround array access by {} when embedding directly in a string. So you should have something like {$_POST[‘name’]}.

Lastly, you should be escaping and cleaning user before placing it into the SQL to avoid common security gaps involving injection.

Upon immediate glance those are the major problems I see.