Two Tables Insert using PHP

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
meandrew
Forum Commoner
Posts: 49
Joined: Mon Feb 24, 2003 1:03 pm

Two Tables Insert using PHP

Post 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
meandrew
Forum Commoner
Posts: 49
Joined: Mon Feb 24, 2003 1:03 pm

Post by meandrew »

there is no reply???
meandrew
Forum Commoner
Posts: 49
Joined: Mon Feb 24, 2003 1:03 pm

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

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