Hostgator SQL database not updating with PHP form

Question

I have a PHP form that should insert data into my SQL database on hostgator. However it is not adding any data but the id field keeps incrementing. I do not receive any error message when submitting the form and when i go to the database the other fields are just empty thus not displaying any data. I am pulling my hair and cant figure out what the problem is. Can someone please help me Thanks

<?php

$host="localhost"; // Host name 
$username="xxxxxx"; // Mysql username 
$password="xxxxxx"; // Mysql password 
$db_name="rob1124_inventory"; // Database name 
$tbl_name="data"; // Table name 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// Get values from form 
$qty=$_POST['qty'];
$product=$_POST['product'];
$price=$_POST['price'];
$totalprice=$_POST['totalprice'];
$seller=$_POST['seller'];
$city=$_POST['city'];

// Insert data into mysql 
$sql="INSERT INTO $tbl_name(qty, product, price, totalprice, seller,city)
    VALUES('$qty', '$product', '$price', '$totalprice', '$seller', '$city')";
$result=mysql_query($sql);

// if successfully insert data into database, displays message "Successful". 
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='insert.php'>Back to main page</a>";
}

else {
echo "ERROR";
}
?> 

<?php 
// close connection 
mysql_close();
?>

Be careful when you post code not to reveal details about your login information publicly. I've replaced your user and password in your code. — Cfreak, Oct 09 '13 at 15:52
Also your code is vulnerable to SQL injection. See http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php for solutions. As for your actual problem you should check that the `$_POST` variables have the values you expect and use `mysql_error()` to see if there are database errors. — Cfreak, Oct 09 '13 at 15:55
Thanks it is my first time posting a question :) I have checked the $_POST variables and they are correct. What do you mean by using mysql_error(). Sorry for asking many questions i am not really experienced at coding. I also forgot to mention that i have the exact same code on another website with another hosting company and everything works great. — alvarez1124, Oct 09 '13 at 15:58
Are you able to run the exact same query in phpMyAdmin? Add `echo $sql;` to your code to see how the query look and copy/paste it in phpMyAdmin to verify...and may be add it to your question :) — Fabien TheSolution, Oct 09 '13 at 16:19

A. Zalonis · Answer 1 · 2013-10-09T16:25:04.007

1

Change to utf-8 from all varchar fields of your table and try to get mysql_error().

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
mysql_query("set names 'utf8'");

//You codes....

    // Insert data into mysql 
    $sql="INSERT INTO $tbl_name(qty, product, price, totalprice, seller,city)
        VALUES('$qty', '$product', '$price', '$totalprice', '$seller', '$city')";
    $result=mysql_query($sql) or die(mysql_error());

//Your codes...

edited Oct 09 '13 at 16:25

answered Oct 09 '13 at 16:01

A. Zalonis

1,599
6
26
41

Just tried your suggestion but all i get is successful back to main page. Now if i go into phpadmin and manually enter data into the fields then they show up on the page, but if i use the php form nothing gets inserted except the id, really weird – alvarez1124 Oct 09 '13 at 16:08
Your data is in english language? If not then maybe you must use UTF-8 charset. – A. Zalonis Oct 09 '13 at 16:12
Yes the data is in english and i am using varchar and latin1_swedish_ci as collation – alvarez1124 Oct 09 '13 at 16:16
Change to utf-8 from all varch fields and check again my answer. I will edit it – A. Zalonis Oct 09 '13 at 16:22
I changed them all except the id to utf8_general_ci but it is still not working :( – alvarez1124 Oct 09 '13 at 16:46
i edited the code as suggested and after hitting submit this is what i get INSERT INTO data(qty, product, price, totalprice, seller,city) VALUES('', '', '', '', '', '')Successful Back to main page. But i am having the same problem – alvarez1124 Oct 09 '13 at 16:53
you have code somewhere that's blanking your `$_POST` variables. Are they named the same in your HTML? – Cfreak Oct 09 '13 at 17:48
That was it Cfreak :) the fields on the html form were not the same, i changed them in the html form and everything is working perfect. Thank You so much. Can i buy you a beer? lol – alvarez1124 Oct 09 '13 at 18:55

score 0 · Answer 2 · answered Oct 09 '13 at 16:23

Since the id is incrementing atleast the form and the DB connect, it tries to enter data.

One usually occurring error is that the data types in the databases columns don't match with the type of data recieved. Like trying to insert chars into ints etc. Or the length of the data is to large for the assigned size in the database. Check to see that the types are correct and try again.

But still, those that are correct should be inserted. Hard to tell without knowing more about the database design.

What data type should be used for these fields? qty, product, price, total price, seller, city. I have Varchar 128character for all — alvarez1124, Oct 09 '13 at 16:48

Hostgator SQL database not updating with PHP form

2 Answers2