Page 1 of 1

MYSQL/PHP Database Interaction - At a loss

Posted: Wed Oct 22, 2003 2:05 pm
by sroussie
Excuse the long post but I have been going over all this code and cannot seem to figure out why it doesn't work. I figured a second set of eyes might be helpful.

I have this html form that I am trying to put the data from the form into a database.

This is the html code:

<html>

<head>
<meta http-equiv="content-type" content="text/html;charset=iso-8859-1">
<meta name="generator" content="Adobe GoLive 6">
<TITLE>Contact</TITLE>

</head>

<body bgcolor="#ffffff">
<form action="connection_contact.php" method="POST">


<table width="712" border="0" cellspacing="2" cellpadding="0">
<tr height="25">
<td width="170" height="25">

</td>
<td width="211" height="25"></td>
<td width="89" height="25"></td>
<td width="221" height="25"></td>
</tr>
<tr>
<td colspan="4" width="708"><b>Please fill out one application per location.
</b>
<p><font color="#ff3333">All fields are required.</font></p>
<p>Use TAB key to move between fields.</p>
</td>
</tr>
<tr height="25">
<td width="170" height="25"></td>
<td width="211" height="25"></td>
<td width="89" height="25"></td>
<td width="221" height="25"></td>
</tr>
<tr>
<td width="170"><label>Dealership Name:</label></td>
<td width="211"><input type="text" name="dealerName" size="35" tabindex="1" border="0"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr>
<td width="170"><label>Address:</label></td>
<td width="211"><input type="text" name="address" size="35" tabindex="2" border="0"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr>
<td width="170"><label>City:</label></td>
<td width="211"><input type="text" name="city" size="35" tabindex="3" border="0"></td>
<td width="89">
<div align="right">
<label>State:</label></div>
</td>
<td width="221"><input type="text" name="state" size="2" tabindex="4" border="0"></td>
</tr>
<tr>
<td width="170"><label>Zip:</label></td>
<td width="211"><input type="text" name="zip" size="10" tabindex="5" border="0"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr>
<td width="170"><label>Phone:</label></td>
<td width="211"><input type="text" name="phone" size="30" tabindex="6" border="0"></td>
<td width="89">
<div align="right">
<label>Fax:</label></div>
</td>
<td width="221"><input type="text" name="fax" size="24" tabindex="7" border="0"></td>
</tr>
<tr height="25">
<td width="170" height="25"></td>
<td width="211" height="25"></td>
<td width="89" height="25"></td>
<td width="221" height="25"></td>
</tr>
<tr>
<td width="170"><label>Dealer Owner's&nbsp;Name:</label></td>
<td width="211"><input type="text" name="ownerName" size="35" tabindex="8" border="0"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr>
<td width="170"><label>E-Mail&nbsp;Address:</label></td>
<td width="211"><input type="text" name="ownerEmail" size="35" tabindex="9" border="0"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr>
<td width="170"><label>Fax:</label></td>
<td width="211"><input type="text" name="ownerFax" size="24" tabindex="10" border="0"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr height="25">
<td width="170" height="25"></td>
<td width="211" height="25"></td>
<td width="89" height="25"></td>
<td width="221" height="25"></td>
</tr>
<tr>
<td width="170"><label>General Manager's Name:</label></td>
<td width="211"><input type="text" name="mgrName" size="35" tabindex="11" border="0"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr>
<td width="170"><label>E-Mail Address:</label></td>
<td width="211"><input type="text" name="mgrEmail" size="35" tabindex="12" border="0"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr>
<td width="170"><label>Fax:</label></td>
<td width="211"><input type="text" name="mgrFax" size="24" tabindex="13" border="0"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr>
<td width="170"></td>
<td width="211"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr height="25">
<td width="170" height="25"></td>
<td width="211" height="25"></td>
<td width="89" height="25"></td>
<td width="221" height="25"></td>
</tr>
<tr>
<td width="170"><label>Office Manager's Name:</label></td>
<td width="211"><input type="text" name="officeName" size="35" tabindex="14" border="0"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr>
<td width="170"><label>E-Mail Address:</label></td>
<td width="211"><input type="text" name="officeEmail" size="35" tabindex="15" border="0"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr>
<td width="170"><label>Fax:</label></td>
<td width="211"><input type="text" name="officeFax" size="24" tabindex="16" border="0"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr height="25">
<td width="170" height="25"></td>
<td width="211" height="25"></td>
<td width="89" height="25"></td>
<td width="221" height="25"></td>
</tr>
<tr>
<td width="170"><label>Franchises at Location:</label></td>
<td width="211"><input type="text" name="franchises" size="24" tabindex="17" border="0"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr height="25">
<td width="170" height="25"></td>
<td width="211" height="25"></td>
<td width="89" height="25"></td>
<td width="221" height="25"></td>
</tr>
<tr>
<td width="170"></td>
<td width="211"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr height="25">
<td width="170" height="25"></td>
<td width="211" height="25"></td>
<td width="89" height="25"></td>
<td width="221" height="25"></td>
</tr>
<tr>
<td colspan="4" width="708">
<div align="center">
<p>By submitting this application you agree to the terms previously set forth.</p>
<p><input type="submit" value="Submit Application" tabindex="18" border="0"></p>
</div>
</td>
</tr>
<tr>
<td width="170"></td>
<td width="211"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr>
<td width="170"></td>
<td width="211"></td>
<td width="89"></td>
<td width="221"></td>
</tr>
<tr>
<td width="170"></td>
<td width="211"></td>
<td width="89"></td>
<td width="221"></td>
</tr>

</td>
</tr>

</table>
</form>
</body>

</html>

I have a database with the corresponding fields to the form. I wrote a php script to INSERT a record into the database from the html form.

I can connect to the database successfully with a php script.

<html>
<head>
<title>connect to database</title>
</head>
<body>

<?php

$link = mysql_connect("localhost", "", "")
or die("Could not connect: " . mysql_error());
print ("Connected successfully");
mysql_close($link);

?>

</body>
</html>

I can add a record into the database using coded values in php:

<html>
<head>
<title>Database</title>
</head>
<body>

<?php

//Open connection to database

$link = mysql_connect("localhost", "", "")
or die("Could not connect: " . mysql_error());
mysql_select_db("contact", $link);

//Insert Values into database

$query = "INSERT INTO info (dealerName, address, city, state, zip, phone, fax, ownerName, ownerEmail, ownerFax, mgrName, mgrEmail, mgrFax, officeName, officeEmail, officeFax, franchises) VALUES ('dealerName', 'address', 'city', 'state', 'zip', 'phone', 'fax', 'ownerName', 'ownerEmail', 'ownerFax', 'mgrName', 'mgrEmail', 'mgrFax', 'officeName', 'officeEmail', 'officeFax', 'franchises')";
$result = mysql_query($query) or die ("Unable to record your information. Please try again later.");
print ("Your information has been recorded");


?>

</body>
</html>

I can also view the records using a php script:

<html>
<head>
<title>connect to database</title>
</head>
<body>

<?php

$link = mysql_connect("localhost", "", "");

mysql_select_db("contact", $link);

$result = mysql_query("SELECT * FROM info", $link);

print "<table border = 1 bordercolor = blue><tr><th>Dealer Name</th><th>Address</th>";
print "<th>City</th><th>State</th><th>Zip</th><th>Phone</th><th>Fax</th>";
print "<th>Owner Name</th><th>Owner E-mail</th><th>Owner Fax</th>";
print "<th>Mgr Name</th><th>Mgr E-mail</th><th>Mgr Fax</th>";
print "<th>Office Name</th><th>Office E-mail</th><th>Office Fax</th>";
print "<th>Franchises</th></tr>";

while ($row = mysql_fetch_row($result))
{
print "<tr>";
foreach ($row as $field)
{
print "<td>$field</td>";
}
print "<tr>";
}

print "</table>";


mysql_close($link);

?>

</body>
</html>

But when I use the php script to add the data from the html form it adds a blank record without the form input. Then the next time I try it tells me that it is unable to add a record at this time.

<html>
<head>
<title>Database</title>
</head>
<body>

<?php

//Open connection to database

$link = mysql_connect("localhost", "", "")
or die("Could not connect: " . mysql_error());
mysql_select_db("contact", $link);

//Insert Values into database

$query = "INSERT INTO info (dealerName, address, city, state, zip, phone, fax, ownerName, ownerEmail, ownerFax, mgrName, mgrEmail, mgrFax, officeName, officeEmail, officeFax, franchises) VALUES ('$dealerName', '$address', '$city', '$state', '$zip', '$phone', '$fax', '$ownerName', '$ownerEmail', '$ownerFax', '$mgrName', '$mgrEmail', '$mgrFax', '$officeName', '$officeEmail', '$officeFax', '$franchises')";
$result = mysql_query($query, $link) or die ("Unable to record your information. Please try again later.");
print ("Your information has been recorded");


?>

</body>
</html>

I am at a loss as to why this is happening. If anyone can shed some insight I would appreciate it.

Thanks,
Sue

Posted: Wed Oct 22, 2003 2:18 pm
by microthick
In your INSERT statement, reference the form values as $_POST['$zip'] rather than $zip.

Posted: Wed Oct 22, 2003 2:26 pm
by sroussie
I am going to assume that I will add the $_POST for all the variables in the INSERT statement???

Posted: Wed Oct 22, 2003 2:33 pm
by microthick
I'm sorry, I was vague.

You could try modifying your statement as follows:

$query = "INSERT INTO info (dealerName, address, city, state, zip, phone, fax, ownerName, ownerEmail, ownerFax, mgrName, mgrEmail, mgrFax, officeName, officeEmail, officeFax, franchises) VALUES ('$_POST[dealerName]', '$_POST[address]', '$_POST[city]', '$_POST[state]', '$_POST[zip]', '$_POST[phone]', '$_POST[fax]', '$_POST[ownerName]', '$_POST[ownerEmail]', '$_POST[ownerFax]', '$_POST[mgrName]', '$_POST[mgrEmail]', '$_POST[mgrFax]', '$_POST[officeName]', '$_POST[officeEmail]', '$_POST[officeFax]', '$_POST[franchises]')";

Or, alternatively, before your query, assign values to $dealername, $address, etc. as follows:

$dealerName = $_POST['dealerName'];
$address = $_POST['address'];

etc etc.

Then use your existing query.

Posted: Wed Oct 22, 2003 2:41 pm
by sroussie
Hi,

Modifying the statement to :

$query = "INSERT INTO info (dealerName, address, city, state, zip, phone, fax, ownerName, ownerEmail, ownerFax, mgrName, mgrEmail, mgrFax, officeName, officeEmail, officeFax, franchises) VALUES ('$_POST[dealerName]', '$_POST[address]', '$_POST[city]', '$_POST[state]', '$_POST[zip]', '$_POST[phone]', '$_POST[fax]', '$_POST[ownerName]', '$_POST[ownerEmail]', '$_POST[ownerFax]', '$_POST[mgrName]', '$_POST[mgrEmail]', '$_POST[mgrFax]', '$_POST[officeName]', '$_POST[officeEmail]', '$_POST[officeFax]', '$_POST[franchises]')";


did not work. But I took the next suggestion you had and declared the variables first and did not modify my INSERT statement. This did work.

When going through examples and tutorials I have found that I need to modify the scripts in order for them to work. Is this common or does it have to do with the platform you are running PHP and MYSQL on?

Thank you very much.

Posted: Wed Oct 22, 2003 2:47 pm
by microthick
Someone else can probably answer this better but as far as I know, a recent release of PHP (version 4.23 or something) expects you to specify the scope of the variables.

In previous versions you could go $zip and it'd work, but now, unless a specific setting is turned on, you have to state the scope as well: $_GET['zip'] or $_POST['zip'], etc.

Posted: Wed Oct 22, 2003 2:58 pm
by sroussie
That would make sense. I will have to check the books I have. I think they are older. Again, thanks for the help. All I need to do now is to put some validation in the script.

Sue