Basic Insert Script (from HTML form to MYSQL DB)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
trythisone
Forum Newbie
Posts: 5
Joined: Thu Mar 10, 2011 10:32 am

Basic Insert Script (from HTML form to MYSQL DB)

Post by trythisone »

I am just starting out learning php using w3schools and trial and error (more like error) with my webhost-provided MYSQL Database. I got a select query to run just fine, then I somehow miraculously got the insert statement to work...but unfortunately somehow the php script got erased (the file didn't, just the contents) and I can't recreate it. I keep getting an HTTP 500 Error. Since then I have tried a bunch of stuff, so I may have done more damage than good. Can anyone please tell me what I need to fix in this code to make it work? I will provide the HTML form code if you want, but the short of it is that it provides the variables in this manner:

<form method="post" action="http://www.domain.com/insertcontacts.php" name="Contact Form">
The variables being posted are: realname, email, phone.

The form never changed the entire time the php was being changed, so I know it's not the problem.

Here is the php script:

Code: Select all

<h1>Testing MYSQL Connection...</h1>
<body>
<? 
$connection = mysql_connect("webhostserver","database","password"); 


if (!$connection) { 
echo "Couldn't make a connection!"; 
exit; 
}

$db = mysql_select_db("database", $connection); 

if (!$db) { 
echo "Couldn't select database!"; 
exit; 
} 


$sql = "INSERT INTO 'database'.'contacts' ('contact_name', 'contact_email', 'contact_phone') VALUES ('$_POST["realname"]', '$_POST["email"]', '$_POST["phone"]')";


if (!mysql_query($sql,$connection))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";


$output = "SELECT `contact_name`,`contact_email`,`contact_phone`,`initial_contact` FROM `database` LIMIT 0, 30 ";


$sql_result = mysql_query($output,$connection);  
 
 
echo "<TABLE BORDER=1>"; 
echo "<TR><TH>contact_name</TH><TH>contact_email</TH><TH>contact_phone</TH><TH>initial_contact</TH>"; 

while ($row = mysql_fetch_array($sql_result)) { 
$contact_name = $row["contact_name"]; 
$contact_email = $row["contact_email"]; 
$contact_phone = $row["contact_phone"];  
$initial_contact = $row["initial_contact"];  
 


echo "<TR><TD>$contact_name</TD><TD>$contact_email</TD><TD>$contact_phone</TD><TD>$initial_contact</TD></TR>"; 
}
echo "</TABLE>"; 
mysql_free_result($sql_result); 
mysql_close($connection); 
?> 
</body>


I have done as much research as I could before posting here. I am confused because it seems some examples use backslashes in their queries and others do not. But while I am curious about that, it isn't my primary concern right now.
jim.barrett
Forum Newbie
Posts: 20
Joined: Tue Mar 01, 2011 5:47 am

Re: Basic Insert Script (from HTML form to MYSQL DB)

Post by jim.barrett »

I would start by echoing your $_POST variables to make sure that they are coming in properly...then echo your $sql (the INSERT statement) and see if that looks right...make sure that the values of your $_POSTs are being interpreted into the sql properly.

I think that you need curly braces around your values in the insert statement?

Code: Select all

...VALUES("{$_POST['realname']}", ...etc
but I may well be wrong about that.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Basic Insert Script (from HTML form to MYSQL DB)

Post by califdon »

Upon a fast look at your code, I don't see any obvious errors, although the code could be greatly simplified. You have a gaping security hole by directly using $_POST variables without "sanitizing" them with mysql_real_escape_string(), but that won't prevent your script from running.

What I'm having trouble with is understanding just what you mean with respect to your script file. Are you saying that the file is still on the server, but it is empty?? And what do you mean by "I can't recreate it"?? Do you mean that when you try to edit the file, you are prevented from doing so?? Or do you mean that you have forgotten what you did previously?? If you are directly editing these files on your host server, that's a bad idea. What you should be doing is maintaining a mirror directory on your own computer, where you do all of your editing, then upload the files to the server. There are many reasons to do this, one of which is in anticipation of exactly this situation, where one or all your files on the server become unusable. It also allows you to install Apache, PHP and MySQL on your local computer, if you choose to do so, which makes development and testing MUCH easier, prior to uploading to your remote server.

What is it that you are trying to do when you get the HTTP 500 error??
trythisone wrote:I am just starting out learning php using w3schools and trial and error (more like error) with my webhost-provided MYSQL Database. I got a select query to run just fine, then I somehow miraculously got the insert statement to work...but unfortunately somehow the php script got erased (the file didn't, just the contents) and I can't recreate it. I keep getting an HTTP 500 Error. Since then I have tried a bunch of stuff, so I may have done more damage than good. Can anyone please tell me what I need to fix in this code to make it work?
...
I have done as much research as I could before posting here. I am confused because it seems some examples use backslashes in their queries and others do not. But while I am curious about that, it isn't my primary concern right now.
Backslashes are "escape" characters, used, for example, when you want to embed literal double-quotes within a string that is, itself, delimited with double-quotes. I like to avoid using them wherever possible, to make my scripts more readable, but occasionally it is necessary to use them.
trythisone
Forum Newbie
Posts: 5
Joined: Thu Mar 10, 2011 10:32 am

Re: Basic Insert Script (from HTML form to MYSQL DB)

Post by trythisone »

(in order of posting)
Jim, Echo showed that the variables are coming through as expected. The curly brackets worked in terms of fixing the 500 error, but now the select query seems to be returning 0 rows even though I verified the rows still exist in the table. I'll keep trying.

Califdon, I'll have to check into the security part once I get this running again. Just to clarify about the cause - it's an ID10T error. I was uploading the files to the server each time I wanted to try something new. I eventually got lazy and started editing the file in the file manager provided by the webhost. Once I got it working beautifully, somehow the window went blank and I ended up saving it as blank I guess...or the webhost had some failure, but I'd put money on me. So I pasted in the version from 3-4 prior attempts and tried to retrace my steps and didn't make it. I knew it was risky before that happened, but since I am doing this for the learning process, I wasn't too worried about it...of course now I feel differently :)
Lesson learned. Anyway, you asked what I was doing to get the error. I would bring up the form that is defining the variables, hit submit, and then it would show it was connecting...etc. then it would show the 500 error.
By the way, where would I get php and mysql? The official websites mentioned on this forum for help?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Basic Insert Script (from HTML form to MYSQL DB)

Post by califdon »

What OS do you use at home? If it's Windows, the easiest way to get all 3 (Apache, MySQL, PHP) and install them at one time is to download WAMP (or XAMP or, I think, a couple of alternatives) which then installs all 3 and configures them at once. You can also download each of them separately and install them, but then need to make a couple of small (but crucial) configuration changes. I think there's a similar product for Macs. If you're using Linux, you can just download each of them, but again, you will probably need to make a few changes in the configuration files.
WAMP: http://sourceforge.net/projects/wampserver/ (for Windows)
Apache: http://httpd.apache.org/download.cgi (for Windows)
MySQL: http://dev.mysql.com/downloads/ (for Windows)
PHP: http://windows.php.net/download/ (for Windows)
trythisone
Forum Newbie
Posts: 5
Joined: Thu Mar 10, 2011 10:32 am

Re: Basic Insert Script (from HTML form to MYSQL DB)

Post by trythisone »

I am all set. Here is what happened:
After initially posting the question, for whatever reason I took out the debug portion of code when I was echo-ing various parts. So when I put the curly brackets back in, I wasn't getting the SQL error message. Once I put that back in, I got this message:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''database'.'contacts' ('contact_name', 'contact_email', 'contact_phone')' at line 1. I consulted the MYSQL manual, and apparently the tics around the table and columns were unnecessary, at least for version 5.

So everything is working perfectly now. Thanks for the help! (and I will be more careful in the future!)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Basic Insert Script (from HTML form to MYSQL DB)

Post by califdon »

That's correct, except those were NOT tics--technically, back-tics--they were apostrophes, which delimit a string and are inappropriate surrounding the name of a database object. For that, you can use back-tics--the key that is usually just to the left of the "1" key, above the Tab key on most keyboards. But those are optional, you can just leave them off. But it's an entirely different character than an apostrophe (sometimes called a single quote), despite the fact that they look so similar.

Just for your comparison, here is how I would code that:

Code: Select all

<?php
$connection = mysql_connect("webhostserver", "database", "password") or die("Couldn't make a connection!");
$db = mysql_select_db("database") or die("Couldn't select database!");
// note that there must be a MySQL connection before you can use mysql_real_escape_string() !
$realname = mysql_real_escape_string($_POST['realname']);
$email = mysql_real_escape_string($_POST['email']);
$phone = mysql_real_escape_string($_POST['phone']);

$sql = "INSERT INTO `contacts` (contact_name, contact_email, contact_phone) VALUES ('$realname', '$email', '$phone')";
mysql_query($sql) or die(mysql_error());
echo "1 record added";

$output = "SELECT `contact_name`, `contact_email`, `contact_phone`, `initial_contact` FROM `database` LIMIT 0, 30 ";
$sql_result = mysql_query($output,$connection) or die(mysql_error());  
 
 
echo "<TABLE BORDER=1>";
echo "<TR><TH>contact_name</TH><TH>contact_email</TH><TH>contact_phone</TH><TH>initial_contact</TH>";

while ($row = mysql_fetch_assoc($sql_result)) {
   extract($row);
   echo "<TR><TD>$contact_name</TD><TD>$contact_email</TD><TD>$contact_phone</TD><TD>$initial_contact</TD></TR>";
}
echo "</TABLE>";
?> 
jim.barrett
Forum Newbie
Posts: 20
Joined: Tue Mar 01, 2011 5:47 am

Re: Basic Insert Script (from HTML form to MYSQL DB)

Post by jim.barrett »

trythisone wrote:I am just starting out learning php using w3schools and trial and error (more like error) with my webhost-provided MYSQL Database.
Hey, if I can make a suggestion...Lynda.com, check out the PHP courses by Kevin Skoglund. They're a little pricey (I lucked out and received both of them as a gift!), but I promise you they are worth the cost.
trythisone
Forum Newbie
Posts: 5
Joined: Thu Mar 10, 2011 10:32 am

Re: Basic Insert Script (from HTML form to MYSQL DB)

Post by trythisone »

Oh wow, I didn't realize there was such a thing as back-tics, but that makes sense. I think I had taken the back-tics out at one time and when I put them back in, I would have put in apostrophes. Also, thanks for your version with myql_real_escape_string.
Awesome, thanks to you both!
Post Reply