I need some PHP & MySQL help...

[Scarpozzi]

I posted the question a while back about how to create a contact form and output the data to a SQL database. I got some good responses. I've created a table and an insert.php file to insert data into the table....my problem is that it doesn't seem to be working. Can anyone take a look and see if I made a mistake somewhere?

I setup the DB to have an id as the primary key and it's an autonumber. I don't know if I need to reference that somewhere... The fields in the table are fname, lname, etc...and I created variables for these, just to follow the tutorial's example.

The error messages built-in let me know that I'm connecting to the DB successfully and selecting it. Thanks for any insight.

$username="dbuser";
$password="password";
$database="Customerdb";

$fname=$_POST['fname'];
$lname=$_POST['lname'];
$company=$_POST['company'];
$addr1=$_POST['addr1'];
$addr2=$_POST['addr2'];
$city=$_POST['city'];
$state=$_POST['state'];
$zip=$_POST['zip'];
$phone=$_POST['phone'];
$mobile=$_POST['mobile'];
$fax=$_POST['fax'];
$email=$_POST['email'];
$natureID=$_POST['natureID'];
$comments=$_POST['comments'];

$link = mysql_connect(localhost,$username,$password);
if (!$link) {
die('Not connected : ' . mysql_error());
}
$db_selected = mysql_select_db($database, $link);
if (!$db_selected) {
die ('Can\'t use selected database : ' . mysql_error());
}

$query = "INSERT INTO contact (id,fname,lname,company,addr1,addr2,city,state,zip,phone,mobile,fax,email,natur
ID,comments) VALUES ('','$fname','$lname','$company','$addr1',$addr2','$city','$state','$zip','$phone','$mobile','$fax','$email','$natureID','$comments')";
mysql_query($query);

mysql_close();

 

[Alone]

EDIT: Nevermind.

What exactly is the problem? Is nothing being inserted at all?

 

[Drakkon]

you dont need the id in your insert:

$query = "INSERT INTO contact (fname,lname,company,addr1,addr2,city,state,zip,phone,mobile,fax,email,natur
ID,comments) VALUES ('$fname','$lname','$company','$addr1',$addr2','$city','$state','$zip','$phone'
'$mobile','$fax','$email','$natureID','$comments')";

also check out the mysql_real_escape_string function

EDIT : Might also want to check out if your column or table names to to be capitolized...on my server it is stup for some reason where case matters.

 

[Alone]

Yeh, PHP and MySQL are case sensitive.

 

[Scarpozzi]

Originally posted by: Alone
Yeh, PHP and MySQL are case sensitive.

I assumed that from its linux roots. Everything is in its proper case.

 

[stoma]

Like someone said above...remove the ID from the INSERT statement, if you have an autonumber set for it you don't want to specify it.

If it's not inserting it should throw you an error I would think?

 

[Alone]

Originally posted by: stoma
Like someone said above...remove the ID from the INSERT statement, if you have an autonumber set for it you don't want to specify it.

If it's not inserting it should throw you an error I would think?

It should say at least something worth knowing.

 

[Scarpozzi]

I didn't have the "id" field on there originally. I just added it after it failed without it.

The result...when I go to the html that calls the insert.php file, it ends up loading the insert.php file and there is nothing but a blank screen. I don't get any kind of feedback. I may have to load this stuff on my system at work where I can run apache, mysql and php all on one system instead of testing this stuff on a remote server.

Thanks guys...I'm going to spend a little more time trying to figure this out tomorrow. I'm just getting tired of typing in all the values in the form and getting the same result of nothing.

 

[clamum]

Originally posted by: Drakkon
you dont need the id in your insert:

$query = "INSERT INTO contact (fname,lname,company,addr1,addr2,city,state,zip,phone,mobile,fax,email,natur
ID,comments) VALUES ('$fname','$lname','$company','$addr1',$addr2','$city','$state','$zip','$phone'
'$mobile','$fax','$email','$natureID','$comments')";

also check out the mysql_real_escape_string function

EDIT : Might also want to check out if your column or table names to to be capitolized...on my server it is stup for some reason where case matters.

This is pretty much it. You ignore the id field and explicitly write out the fields you are inserting data into like Darkkon has above (INSERT INTO <table> VALUES (field_names_go_here) (your_variables_go_here).

After you connect to your database (I'd recommend die()ing after failure or skipping the insert), you want to run the variables through mysql_real_escape_string() to get rid of any bad stuff people may put in your form (Google "sql injection"). I usually do this to form variables:

$first_name = mysql_real_escape_string(trim($_POST['first_name']));

 

[Aikouka]

Scarp, you forgot the #1 rule of fixing possibly buggy code...!

Testing the important parts!

What does your query actually "say" after you assign the value... is there possibly an error in it? Because it looks like you're following the correct "INSERT INTO tablename (colname1, colname2) VALUES(value1, value2)" After researching this different way of entering data, I came across that this is typically used for inserting multiple rows at once, but should be fine for a single row. Although, this method will fail if the number of columns isn't equal to the number of values, which after you added the ID, you were off by one.

EDIT:

PS. The best way to avoid having to type the data is to design your HTML page with preset values in the form~

 

[Alone]

Originally posted by: Scarpozzi
I don't get any kind of feedback.

No feedback is a good thing, that means it did what it was supposed to.

 

[Drakkon]

or it means somethings not coming back ... maybe try instead of jsut the query:

if(mysql_query($query)) {
die(mysql_error());
}

mysql_close();

 

[KLin]

Are the single quotes necessary in the sql statement since they're defined in the variables?

EDIT: and it looks like you're missing the e in NatureID. Could just be a typo in the message though .

 

[Thyme]

Instead of running the query, print the query variable after you've built it. If that looks right, then run it in phpmysql in mysql directly. It should give you feedback that way.

 

[Scarpozzi]

It took me a while to get it working, but it's working now. I still don't know what was wrong.

Anyhow, my new question is....when you do a post to an insert.php, what's the best way to return to an html page. Is there a redirect function or a standard way of returning the client to a web page letting them know the information was submitted sucessfully? Thanks.

 

[Alone]

<?php
header("Location: index.html");
?>

EDIT: You could always put some code on the insert.php page that lets them know it was successful.

 

[jjones]

<?php if(mysql_query($query)) { ?>
<p>You have successfully entered your data.</p>
<?php } else { ?>
<p>We could not enter the data into the database at this time.</p>
<?php } ?>

Or use the header function as Alone mentioned.