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.

Topic		Replies	Views
I can't insert data into Database table Databases php	17	6634	January 11, 2018
HTML Form is not adding data to MySQL database? PHP mysql	46	26844	April 19, 2020
Unable to insert data in Database using PHP.. below is my PHP code with Html. Thanks PHP	5	744	November 12, 2014
Why can't I insert a data inside my database? PHP mysql	9	1593	May 1, 2018
HTML form, Javascript, PHP an MySQL integration? Inserting form data into MySQL table Get Started	1	999	June 21, 2012

Why can't insert data into the database?

Related topics