Why can't insert data into the database?

runeveryday · June 24, 2010, 3:33am

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>";

?>

runeveryday · June 25, 2010, 5:52am

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)
);

John_Betong · June 25, 2010, 11:00am

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(); 
?>

.

Immerse · June 25, 2010, 7:21am

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]."')";

John_Betong · June 24, 2010, 10:03am

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'].")";

.

digadesign · June 24, 2010, 8:50am

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].")";

John_Betong · June 24, 2010, 7:38am

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) . "\
";

.

runeveryday · June 24, 2010, 9:48am

“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.

digadesign · June 24, 2010, 9:51am

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.

runeveryday · June 24, 2010, 8:34am

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

i want to konw why this doesn’t work?

runeveryday · June 24, 2010, 7:22am

John_Betong:

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');
.

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.

runeveryday · June 24, 2010, 9:58am

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

John_Betong · June 24, 2010, 6:56am

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');

.

runeveryday · June 24, 2010, 6:21am

John_Betong:

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: %d\
", mysql_affected_rows());
  // check to see how many records wered posted then remove next 'die;'  line and refresh browser
die;

.

i added this

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

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

runeveryday · June 24, 2010, 6:07am

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

John_Betong · June 24, 2010, 4:14am

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;

.

John_Betong · June 24, 2010, 9:06am

What is the genereated error message?

.

hash · June 24, 2010, 9:27am

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.

John_Betong · June 24, 2010, 9:54am

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”.

.

oddz · June 24, 2010, 3:58am

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.