php/mysql adding to db - what have I done wrong? [Archive] - International Web Developers Network

Tjobbe

January 3rd, 2006, 19:41

I ahve made a very simple database system where im adding detail to a database and then displaying them.

for some reason i can not add more than one!

I assume it is somethin to do with my id field but not sure!

here are the files:

bdb.sql
# phpMyAdmin SQL Dump
# version 2.5.6
# http://www.phpmyadmin.net
#
# Host: localhost
# Generation Time: Jan 03, 2006 at 06:29 PM
# Server version: 4.0.18
# PHP Version: 4.3.6
#
# Database : `bdb`
#

# --------------------------------------------------------

#
# Table structure for table `business`
#

CREATE TABLE `business` (
`id` tinyint(4) NOT NULL auto_increment,
`coname` text NOT NULL,
`www` text NOT NULL,
`address1` text NOT NULL,
`address2` text NOT NULL,
`address3` text NOT NULL,
`address4` text NOT NULL,
`county` text NOT NULL,
`postcode` text NOT NULL,
`email` text NOT NULL,
`phone` text NOT NULL,
`registrant` text NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

#
# Dumping data for table `business`
#

INSERT INTO `business` VALUES (1, 'North Oxon Web Design', 'www.now-design.co.uk', 'Green Garden Cottage', 'Main Street', 'North Newington', 'Banbury', 'Oxfordshire', 'OX15 6AJ', 'info@now-design.co.uk', '07789176017', 'Tjobbe Andrews');

company-add.php
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Add a new company</title>
<style type="text/css">

</style>
</head>

<body>
<h3>Company Database</h3>
<p><a href="company-add.php">Add</a> | <a href="display.php">Display</a> | <a href="http://www.whois.net/">whois.net</a> | <a href="http://www.nominet.org.uk/">nominet</a></p>
<hr />
<form name="form1" method="post" action="company-store.php">
<table border="0" align="center" cellpadding="4" cellspacing="1" bgcolor="#333333">
<tr>
<td bgcolor="#FFFFCC">Company Name </td>
<td bgcolor="#FFFFCC"><label>
<input type="text" name="coname" />
</label></td>
</tr>
<tr>
<td bgcolor="#FFFFCC">Web site address </td>
<td bgcolor="#FFFFCC"><input type="text" name="www" /></td>
</tr>
<tr>
<td bgcolor="#FFFFCC">Address line 1 </td>
<td bgcolor="#FFFFCC"><input type="text" name="address1" /></td>
</tr>
<tr>
<td bgcolor="#FFFFCC">Address line 2</td>
<td bgcolor="#FFFFCC"><input type="text" name="address2" /></td>
</tr>
<tr>
<td bgcolor="#FFFFCC">Address line 3 </td>
<td bgcolor="#FFFFCC"><input name="address3" type="text" id="address3" /></td>
</tr>
<tr>
<td bgcolor="#FFFFCC">Address line 4 </td>
<td bgcolor="#FFFFCC"><input name="address4" type="text" id="address4" /></td>
</tr>
<tr>
<td bgcolor="#FFFFCC">County</td>
<td bgcolor="#FFFFCC"><input type="text" name="county" /></td>
</tr>
<tr>
<td bgcolor="#FFFFCC">Postcode</td>
<td bgcolor="#FFFFCC"><input type="text" name="postcode" /></td>
</tr>
<tr>
<td bgcolor="#FFFFCC">Email</td>
<td bgcolor="#FFFFCC"><input type="text" name="email" /></td>
</tr>
<tr>
<td bgcolor="#FFFFCC">Phone Number </td>
<td bgcolor="#FFFFCC"><input type="text" name="phone" /></td>
</tr>
<tr>
<td bgcolor="#FFFFCC">Registrant Name </td>
<td bgcolor="#FFFFCC"><input type="text" name="registrant" /></td>
</tr>
<tr>
<td colspan="2" bgcolor="#FFFFCC"><div align="center">
<input type="submit" name="Submit" value="Submit" />
</div></td>
</tr>
</table>
</form>
</body>
</html>

company-store.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Store</title>
</head>

<body><?php

$DBhost = 'localhost';
$DBName = "bdb";
$table = "business";
mysql_connect($DBhost) or die("Unable to connect to database");

@mysql_select_db("$DBName") or die("Unable to select database $DBName");

$sqlquery = "INSERT INTO $table VALUES('$id','$coname','$www','$address1','$addres s2','$county','$postcode','$email','$phone','$regi strant')";
$results = mysql_query($sqlquery);
mysql_close();
echo '<p><strong>You have just enterred the following:</strong></p><p> </p>';
echo '<p>'.$coname.'</p>';
echo '<p>'.$www.'</p>';
echo '<p>'.$address1.'</p>';
echo '<p>'.$address2.'</p>';
echo '<p>'.$address3.'</p>';
echo '<p>'.$address4.'</p>';
echo '<p>'.$county.'</p>';
echo '<p>'.$postcode.'</p>';
echo '<p>'.$email.'</p>';
echo '<p>'.$phone.'</p>';
echo '<p>'.$registrant.'</p>';
?>
</body>
</html>

display.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Display</title>
<style type="text/css">

</style>
</head>

<body>
<h3>Company Database</h3>
<p><a href="company-add.php">Add</a> | <a href="display.php">Display</a> | <a href="http://www.whois.net/">whois.net</a> | <a href="http://www.nominet.org.uk/">nominet</a></p>
<?
$username="";
$password="";
$database="bdb";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM business";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

$i=0;
while ($i < $num) {

$coname=mysql_result($result,$i,"coname");
$www=mysql_result($result,$i,"www");
$address1=mysql_result($result,$i,"address1");
$address2=mysql_result($result,$i,"address2");
$address3=mysql_result($result,$i,"address3");
$address4=mysql_result($result,$i,"address4");
$county=mysql_result($result,$i,"county");
$postcode=mysql_result($result,$i,"postcode");
$email=mysql_result($result,$i,"email");
$phone=mysql_result($result,$i,"phone");
$registrant=mysql_result($result,$i,"registrant");

echo '<hr />';
echo '<strong><a href="http://'.$www.'">'.$coname.'</a></strong><br />';
echo ''.$address1.'<br />';
echo ''.$address2.'<br />';
echo ''.$address3.'<br />';
echo ''.$address4.'<br />';
echo ''.$postcode.'<br /><br />';
echo '<a href="mailto:'.$email.'">'.$email.'</a><br />';
echo ''.$phone.'<br /><br />';
echo '<strong>'.$registrant.'</strong><br />';

$i++;

}

?>

</body>
</html>

vigo

January 3rd, 2006, 20:18

In your insert query you are explictly setting id to a value: don't do this. Remove that bit - it is evil! It is the devil!

Ok, kidding, but it is whats going wrong. An auto_increment field should never have a value set for it. Remove the setting of the id in your query and you should be fine.

Cameron

January 3rd, 2006, 20:19

Change $sqlquery = "INSERT INTO $table VALUES('$id','$coname','$www','$address1','$addres s2','$county','$postcode','$email','$phone','$regi strant')";
$results = mysql_query($sqlquery);
To $sqlquery = "INSERT INTO $table VALUES('','$coname','$www','$address1','$address2' ,'$county','$postcode','$email','$phone','$registr ant')";
$results = mysql_query($sqlquery);

You shouldn't set an id for an auto_incrementing field. you may also want to use INT or MEDIUMINT instead of TINYINT

Cameron

January 3rd, 2006, 20:19

vigo beat me by a hair! :o

Tjobbe

January 3rd, 2006, 20:20

thanks guys, ill fix it now, knew it would be something simple!

Doh!

vigo

January 3rd, 2006, 20:22

Very slim hair :D

IIRC setting an auto_increment field to '' in a query will set it to null (0) so I'd remove it completely (not tested - but better to be safe :))

$sqlquery = "INSERT INTO $table VALUES('$coname','$www','$address1','$address2','$ county','$postcode','$email','$phone','$registrant ')";

Cameron

January 3rd, 2006, 20:25

Also me being a nitpick, you should NOT NOT NOT NOT NOT rely on register_globals.

you should initialize your variables with something ala $var = str_replace('\'', "''", addslashes($_POST['formVar'])); or similiar.

Cameron

January 3rd, 2006, 20:26

Actually, he'll need to define what fields he's using INSERT INTO table(field, field1, field2int) VALUES('$field', '$field1', $field2int);
As there will be a column mismatch.

Tjobbe

January 3rd, 2006, 20:26

thats strange, have just ammended the script and it is not adding anything to the database still.. any ideas?

Tjobbe

January 3rd, 2006, 20:30

wow, quick posts, give me a sec to catch up! lol.

Tjobbe

January 3rd, 2006, 20:36

definign the fields worked a treat, thanks you two!

vigo

January 4th, 2006, 11:59

Wahey! Glad we got it working in the end between us :)