Page 1 of 1
Trouble adding record to MySql db
Posted: Mon May 24, 2010 8:52 pm
by coder4life
the form to complete is this:
Code: Select all
<form action="insert.php" method="post">
First Name: <input type="text" name="first"><br>
Last Name: <input type="text" name="last"><br>
Street: <input type="text" name="street"><br>
City: <input type="text" name="city"><br>
State: <input type="text" name="state"><br>
Zip: <input type="text" name="zip"><br>
Fee Receipt (Y/N): <input type="text" name="fee_rcpt"><br>
Date Rcd: <input type="text" name="date_rcd"><br>
Opened By: <input type="text" name="opnd_by"><br>
Check Encl (Y/N): <input type="text" name="check_encl"><br>
<input type="Submit">
</form>
and the php code to insert the data is here:
Code: Select all
<?php
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "INSERT INTO data1 VALUES ('$first','$last','$street','$city','$state','$zip','$fee_rcpt','$date_rcd','$opnd_by','$check_encl')";
mysql_query($query);
mysql_close();
?>
i know i am connected to the db, the table name is right, field names are exact. not sure why a new record is not being added
Re: Trouble adding record to MySql db
Posted: Mon May 24, 2010 9:48 pm
by JakeJ
A few problems I see.
First of all, you're not converting the $_POST data from your form in to the variables you're trying to submit to your database.
It's also a good idea to use mysql_real_escape_string() so do this:
Code: Select all
$first = mysql_real_escape_string($_POST['first']);
It's all generally good practice to specify what fields you're inserting the data in to.
Code: Select all
$query = "INSERT INTO data1 (first, last, street, city, state, zip, fee_rcpt, date_rcd, opnd_by, check_encl) VALUES ('$first','$last','$street','$city','$state','$zip','$fee_rcpt','$date_rcd','$opnd_by','$check_encl')";
The above assumes that your field names are the same as your variable names, adjust as needed.
I hope this helps.
Re: Trouble adding record to MySql db
Posted: Mon May 24, 2010 10:38 pm
by coder4life
i tried this but still no post to the db:
Code: Select all
<?php
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$first = mysql_real_escape_string ($_POST['first']);
$last = mysql_real_escape_string($_POST['last']);
$street = mysql_real_escape_string($_POST['street']);
$city = mysql_real_escape_string($_POST['city']);
$state = mysql_real_escape_string($_POST['state']);
$zip = mysql_real_escape_string($_POST['zip']);
$fee_rcpt = mysql_real_escape_string($_POST['fee_rcpt']);
$first = mysql_real_escape_string($_POST['date_rcd']);
$date_rcd = mysql_real_escape_string($_POST['opnd_by']);
$check_encl = mysql_real_escape_string($_POST['check_encl']);
$query = "INSERT INTO data1 (first, last, street, city, state, zip, fee_rcpt, date_rcd, opnd_by, check_encl) VALUES ('$first','$last','$street','$city','$state','$zip','$fee_rcpt','$date_rcd','$opnd_by','$check_encl')";
mysql_query($query);
mysql_close();
here is the db structure fwiw:
Code: Select all
Field Type Collation Attributes Null Default
id int(11) No None
street text latin1_swedish_ci No None
city text latin1_swedish_ci No None
state text latin1_swedish_ci No None
zip text latin1_swedish_ci No None
first text latin1_swedish_ci No None
last text latin1_swedish_ci No None
fee_rcpt text latin1_swedish_ci No None
date_rcd text latin1_swedish_ci No None
opnd_by text latin1_swedish_ci No None
check_encl text latin1_swedish_ci No None
the latin swedish thing was a default
not sure what the issue is
thanks for the reply
Re: Trouble adding record to MySql db
Posted: Mon May 24, 2010 10:51 pm
by JakeJ
I don't see any obvious problems.
Here's some things to check.
1. Make sure you have a data base connection
2. Echo out each of your variables to make sure you have data.
3. Cut down your query and add each variable back until the insert fails and then you know which field failed.
Beyond that I'm not sure what to say. It should work.
Re: Trouble adding record to MySql db
Posted: Mon May 24, 2010 11:29 pm
by Benjamin

Moved to PHP - Code
Re: Trouble adding record to MySql db
Posted: Mon May 24, 2010 11:55 pm
by coder4life
ok, i reduced it to
Code: Select all
<?php
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$first = mysql_real_escape_string ($_POST['first']);
echo $first;
$query = "INSERT INTO data1 (first) VALUES ('$first')";
mysql_query($query);
mysql_close();
?>
the dbinfo.inc.php file looks like this
Code: Select all
<?php
$username="inovacap_alf";
$password="alf123";
$database="inovacap_intake";
?>
if i tinker with the dbinfo file, i will get an access error, so it seems there is a connection?
i manually inserted a row in the db and this code reads it and shows it on the web page with no trouble:
Code: Select all
<?php
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM data1";
$result=mysql_query ($query);
$num=mysql_num_rows ($result);
mysql_close();
echo "<b><center>Database Output</center></b><br><br>";
?>
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Street</font></th>
<th><font face="Arial, Helvetica, sans-serif">City</font></th>
<th><font face="Arial, Helvetica, sans-serif">State</font></th>
<th><font face="Arial, Helvetica, sans-serif">Fee Rcpt (Y/N)</font></th>
<th><font face="Arial, Helvetica, sans-serif">Date Rcd</font></th>
<th><font face="Arial, Helvetica, sans-serif">Opened By</font></th>
<th><font face="Arial, Helvetica, sans-serif">Check Encl (Y/N)</font></th>
</tr>
<?php
$i=0;
while ($i < $num) {
$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$street=mysql_result($result,$i,"street");
$city=mysql_result($result,$i,"city");
$zip=mysql_result($result,$i,"zip");
$fee_rcpt=mysql_result($result,$i,"fee_rcpt");
$date_rcd=mysql_result($result,$i,"date_rcd");
$opnd_by=mysql_result($result,$i,"opnd_by");
$check_encl=mysql_result($result,$i,"check_encl");
?>
<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "$first $last"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "$street"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "$city"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "$state"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "$zip"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "$fee_rcpt"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "$date_rcd"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "$opnd_by"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "$check_encl"; ?></font></td>
</tr>
<?php
++$i;
}
echo "</table>";
?>
so there has to be a connection ... but the insert isnt working ... might be an issue with how the db is setup?
Re: Trouble adding record to MySql db
Posted: Tue May 25, 2010 12:46 am
by JakeJ
Is it possible that the db user is read only and you should be using a different user to write to the db?
I really can't see anything else wrong.
Re: Trouble adding record to MySql db
Posted: Tue May 25, 2010 9:03 am
by mikosiko
I will write your code a little different... and notice that in your code you were using localhost instead of 'localhost'
Code: Select all
<?php
include("dbinfo.inc.php");
/* First validate the passed fields .... here only one and simple validation just to show an example */
if (isset($_POST['first])) {
$link = mysql_connect('localhost',$username,$password) or die('Could not connect: ' . mysql_error());
mysql_select_db($database) or die( "Unable to select database");
$first = mysql_real_escape_string ($_POST['first']);
$query = "INSERT INTO data1 (first) VALUES ('$first')";
mysql_query($query, $link) or die('Insert Error : ' . mysql_error());
mysql_close($link);
} else {
/* Control the errors.... simple code here just for the example */
echo "Fields have not been set";
exit();
}
?>
Re: Trouble adding record to MySql db
Posted: Tue May 25, 2010 10:34 am
by Jonah Bron
Turn on error reporting.
Oops, syntax error. Fixed.
Code: Select all
<?php
include("dbinfo.inc.php");
/* First validate the passed fields .... here only one and simple validation just to show an example */
if (isset($_POST['first'])) {
$link = mysql_connect('localhost',$username,$password) or die('Could not connect: ' . mysql_error());
mysql_select_db($database) or die( "Unable to select database");
$first = mysql_real_escape_string ($_POST['first']);
$query = "INSERT INTO data1 (first) VALUES ('$first')";
mysql_query($query, $link) or die('Insert Error : ' . mysql_error());
mysql_close($link);
} else {
/* Control the errors.... simple code here just for the example */
echo "Fields have not been set";
exit();
}
?>
Re: Trouble adding record to MySql db
Posted: Tue May 25, 2010 11:06 am
by mikosiko
good.... team work

Re: Trouble adding record to MySql db
Posted: Tue May 25, 2010 3:20 pm
by coder4life
thank you so much!
with the error checking code i saw that in my primary 'id' field i received a duplicate entry error, so i clicked 'auto increment' in the db setup thing and now its working.