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.

Code: Select all

$first = $_POST['first'];
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
:arrow: 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.