Page 1 of 1

Two Tables Insert using PHP

Posted: Sat Mar 22, 2003 6:03 am
by meandrew
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.

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))&#123;

$county_id=$row&#1111;'CountyID'];
        $city_id=$row&#1111;'CityID'];
		$city=$row&#1111;'City'];
if ($CityID == $city_id)

$city="city_".$cityId;
echo"<td width="25%"><input type="checkbox" name="$city_id">$city</td>";
$count++;
 if($count ==3)&#123;
  echo "</tr><tr>";
  $count=0;
 &#125;
&#125;
once selected this is the form that should do the inserts ->


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))&#123;

$cityId=$row&#1111;'CityID'];
$city="city_".$cityId;
 $c=$$city;
if($c == "on")&#123;
	$query1="insert into item_city (item_id,city_id)values('$nItemId','$cityId')";
	$result1=mysql_query($query1);
 &#125;
&#125;
/////////////////////////
?>
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

Posted: Sat Mar 22, 2003 7:44 am
by meandrew
there is no reply???

Posted: Sat Mar 22, 2003 7:46 am
by meandrew
I have just hurt my eyes but alas have solved this query insert problem of mine all on my own :)

Thank you for not helping :(

I do have another query question that I will probably end up solving myself but I'll post it anyway as a new topic

Andrew

Posted: Sat Mar 22, 2003 8:04 am
by twigletmac
I'm happy you solved this yourself although why the snotty attitude - so someone didn't give you the answer and you had to do all the work yourself why is that such a bad thing? I mean you only posted the question a few hours ago, it's hardly like you've been waiting days and were being ignored.

Plus you might want to give something back to the forum by explaining how you solved the problem so that if someone else stumbles across your thread whilst trying to answer a similar question they might not have to post to ask because you could've answered it.

Mac