Trouble adding record 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
coder4life
Forum Newbie
Posts: 10
Joined: Sat May 22, 2010 6:59 pm

Trouble adding record to MySql db

Post 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
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Trouble adding record to MySql db

Post 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.
coder4life
Forum Newbie
Posts: 10
Joined: Sat May 22, 2010 6:59 pm

Re: Trouble adding record to MySql db

Post 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
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Trouble adding record to MySql db

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Trouble adding record to MySql db

Post by Benjamin »

:arrow: Moved to PHP - Code
coder4life
Forum Newbie
Posts: 10
Joined: Sat May 22, 2010 6:59 pm

Re: Trouble adding record to MySql db

Post 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?



JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Trouble adding record to MySql db

Post 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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Trouble adding record to MySql db

Post 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();
}
?> 
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Trouble adding record to MySql db

Post 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();
}
?> 
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Trouble adding record to MySql db

Post by mikosiko »

good.... team work :)
coder4life
Forum Newbie
Posts: 10
Joined: Sat May 22, 2010 6:59 pm

Re: Trouble adding record to MySql db

Post 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.
Post Reply