Two Tables Insert using PHP
Posted: Sat Mar 22, 2003 6:03 am
I am now working on the insert into two tables
items and items_city
In each table there is the ID which is unique. This is what I have so far and I am not getting very far with it.
select from city and display ready for check box selection.
once selected this is the form that should do the inserts ->
tables structure:
#
# Table structure for table `city`
#
CREATE TABLE city (
CountyID int(10) unsigned NOT NULL default '0',
City varchar(100) NOT NULL default '',
CityID bigint(20) NOT NULL auto_increment,
MoPID int(10) unsigned NOT NULL default '0',
PRIMARY KEY (CityID),
KEY CountyID (CountyID),
KEY MoPID (MoPID)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Table structure for table `item_city`
#
CREATE TABLE item_city (
id int(11) NOT NULL auto_increment,
item_id int(11) default NULL,
city_id int(11) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Table structure for table `items`
#
CREATE TABLE items (
ItemSKU varchar(25) NOT NULL default '',
ItemName varchar(100) NOT NULL default '',
ItemDescription mediumtext NOT NULL,
PostCode varchar(100) NOT NULL default '',
Category bigint(20) NOT NULL default '0',
CityID bigint(20) NOT NULL default '0',
CTelephone varchar(100) NOT NULL default '',
ItemID bigint(20) NOT NULL auto_increment,
Cfax varchar(100) NOT NULL default '',
Cemail varchar(200) NOT NULL default '',
Caddress varchar(200) NOT NULL default '',
CTown varchar(200) NOT NULL default '',
Cwww varchar(200) NOT NULL default '',
PRIMARY KEY (ItemID)
) TYPE=MyISAM;
I have looked at this for too long and seem to be seeing the same thing
so if someone can see something aloof that would be great 
Andrew
items and items_city
In each table there is the ID which is unique. This is what I have so far and I am not getting very far with it.
select from city and display ready for check box selection.
Code: Select all
echo "Select Cities and Towns<br>";
echo "</td></tr>";
$queryC="SELECT CountyID, City, CityID FROM city WHERE CountyID=".$CountyID." ORDER BY City";
$result=mysql_query($queryC,$con_id);
$count=0;
echo "<tr>";
while($row=mysql_fetch_array($result)){
$county_id=$rowї'CountyID'];
$city_id=$rowї'CityID'];
$city=$rowї'City'];
if ($CityID == $city_id)
$city="city_".$cityId;
echo"<td width="25%"><input type="checkbox" name="$city_id">$city</td>";
$count++;
if($count ==3){
echo "</tr><tr>";
$count=0;
}
}Code: Select all
<?
require("connection.php");
DBInfo();
Root();
$con_id=mysql_connect("$DBHost","$DBUser","$DBPass");
mysql_select_db($DBName);
mysql("$DBName","INSERT INTO Items VALUES(
'$ItemSKU',
'$ItemName',
'$ItemDescription',
'$PostCode',
'$Category',
'$CityID',
'$CTelephone',
'$ItemID',
'$Cfax',
'$Cemail',
'$Caddress',
'$CTown',
'$Cwww')");
$nItemId=mysql_insert_id();
$queryC="select * from city";
$result=mysql_query($queryC,$con_id);
while($row=mysql_fetch_array($result)){
$cityId=$rowї'CityID'];
$city="city_".$cityId;
$c=$$city;
if($c == "on"){
$query1="insert into item_city (item_id,city_id)values('$nItemId','$cityId')";
$result1=mysql_query($query1);
}
}
/////////////////////////
?>#
# Table structure for table `city`
#
CREATE TABLE city (
CountyID int(10) unsigned NOT NULL default '0',
City varchar(100) NOT NULL default '',
CityID bigint(20) NOT NULL auto_increment,
MoPID int(10) unsigned NOT NULL default '0',
PRIMARY KEY (CityID),
KEY CountyID (CountyID),
KEY MoPID (MoPID)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Table structure for table `item_city`
#
CREATE TABLE item_city (
id int(11) NOT NULL auto_increment,
item_id int(11) default NULL,
city_id int(11) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Table structure for table `items`
#
CREATE TABLE items (
ItemSKU varchar(25) NOT NULL default '',
ItemName varchar(100) NOT NULL default '',
ItemDescription mediumtext NOT NULL,
PostCode varchar(100) NOT NULL default '',
Category bigint(20) NOT NULL default '0',
CityID bigint(20) NOT NULL default '0',
CTelephone varchar(100) NOT NULL default '',
ItemID bigint(20) NOT NULL auto_increment,
Cfax varchar(100) NOT NULL default '',
Cemail varchar(200) NOT NULL default '',
Caddress varchar(200) NOT NULL default '',
CTown varchar(200) NOT NULL default '',
Cwww varchar(200) NOT NULL default '',
PRIMARY KEY (ItemID)
) TYPE=MyISAM;
I have looked at this for too long and seem to be seeing the same thing
Andrew