here i have to store comapnyname,contactperson,city,place,maincategory,subcategory values.but i have 4 tables with data's like maincategory,subcategory,city,place. so how can i store the below data's in table.in spa table i added maincategoryid and cityid is enough or else these city and category i have to store in another table. please give some suggestion.
$sql = mysql_query("INSERT INTO `spa`(`mainCatagoryid`, `City`,`ContactPerson`,`Companyname`)
VALUES ('$catagory', '$city','$conatctperson','$companyname'");
place and subcategories are in array.
$catagory = trim(ucwords(strtolower(mysql_real_escape_string($_POST['maincatagory']))));
$subcatagory = ucwords(strtolower(mysql_real_escape_string($_POST['relatedcategories'])));
$place = trim(mysql_real_escape_string($_POST['place']));
$city = trim(ucwords(strtolower(mysql_real_escape_string($_POST['city']))));
$companyname =ucwords(strtolower(mysql_real_escape_string($_POST['companyname'])));
$conatctperson =ucwords(strtolower(mysql_real_escape_string($_POST['conactperson'])));
CREATE TABLE IF NOT EXISTS `catagorykeywords` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`MainCatagory` varchar(100) NOT NULL,
`name` varchar(100) NOT NULL,
`size` bigint(20) NOT NULL,
`path` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='maincatagory
keyword' AUTO_INCREMENT=147 ;
--
-- Dumping data for table `catagorykeywords`
--
INSERT INTO `catagorykeywords` (`id`, `MainCatagory`, `name`,
`size`, `path`) VALUES
(96, 'Health & Fitness', '', 0, './upimg/caticon/'),
(97, 'Education', 'education.jpg', 0,
'./upimg/caticon/education.jpg');
CREATE TABLE IF NOT EXISTS `subcatagorykeywords` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Subcatagory` varchar(150) NOT NULL,
`MainCatid` int(11) NOT NULL,
KEY `MainCatid` (`MainCatid`),
KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=765 ;
--
-- Dumping data for table `subcatagorykeywords`
--
INSERT INTO `subcatagorykeywords` (`id`, `Subcatagory`,
`MainCatid`) VALUES
(543, 'Pediatrician', 96),
(544, 'Ayurveda', 96),
(545, 'Eye Hospitals', 96),
(556, 'Pre-primary (Lkg Ukg)', 97),
(557, 'Primary Education', 97),
(558, 'Higher Primary Education', 97),
(559, 'PUC', 97);
CREATE TABLE IF NOT EXISTS `states` (
`state_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`state` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`state_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
AUTO_INCREMENT=10 ;
--
-- Dumping data for table `states`
--
INSERT INTO `states` (`state_id`, `state`) VALUES
(8, 'karnataka'),
(9, 'tamilnadu');
CREATE TABLE IF NOT EXISTS `cities` (
`city_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`city` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`state_id` int(11) DEFAULT NULL,
PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
AUTO_INCREMENT=14 ;
--
-- Dumping data for table `cities`
--
INSERT INTO `cities` (`city_id`, `city`, `state_id`) VALUES
(1, 'bangalore', 8),
(2, 'mangalore', 8),
(3, 'coimbatore', 9),
(4, 'chennai', 9);
javascript code
<script type="text/javascript">
function getPlace(stateId) {
var strURL="findplace.php?city="+stateId;
var req = getXMLHTTP();
if (req) {
req.onreadystatechange = function() {
if (req.readyState == 4) {
// only if "OK"
if (req.status == 200)
{
document.getElementById('citydiv').innerHTML=req.responseText;
} else {
alert("There
was a problem while using XMLHTTP:\n" + req.statusText);
}
}
}
req.open("GET", strURL, true);
req.send(null);
}
}
//get subcategory keywords
function getCatagory(catagoryId) {
var strURL="findsubcategory.php?
subcat="+catagoryId;
var req = getXMLHTTP();
if (req) {
req.onreadystatechange = function() {
if (req.readyState == 4) {
// only if "OK"
if (req.status == 200)
{
document.getElementById
('categorydiv').innerHTML=req.responseText;
} else {
alert("There
was a problem while using XMLHTTP:\n" + req.statusText);
}
}
}
req.open("GET", strURL, true);
req.send(null);
}
}
</script>
form like
Company name: <input type="text" name="companyname" />
Contact Person Name: <input type="text" name="conactperson" />
City : <select id="city" name="city" onChange="getPlace
(this.value)">
<option value="" selected="selected">... Select
City ...</option>
<?php
include("db.php");
$query = "SELECT `state_id`,
`state` FROM `states` ";
$result = mysql_query($query);
//echo mysql_error();
while($rows = mysql_fetch_array($result))
{
$selected = "";
$categoryId = $rows['state_id'];
$categoryName = $rows['state'];
if($catid == $categoryId)
{
$selected = "selected";
}
?>
<option value="<?php echo $categoryId; ?>" <?
php echo $selected; ?> ><?php echo $categoryName; ?></option>
<?php } ?>
</select>
Main category: <select name="catagory" id="catagory"
onChange="getCatagory(this.value)">
<option value="" selected="selected">...Select
a Category...</option>
<?php
include("db.php");
$query = "SELECT `id`,
`MainCatagory`, `name`, `size`, `path` FROM
`catagorykeywords`";
$result = mysql_query($query);
//echo mysql_error();
while($rows = mysql_fetch_array($result))
{
$selected = "";
$categoryId = $rows['id'];
$categoryName = $rows['MainCatagory'];
if($catid == $categoryId)
{
$selected = "selected";
}
?>
<option value="<?php echo $categoryId; ?>" <?
php echo $selected; ?> ><?php echo $categoryName; ?></option>
<?php } ?>
</select>
findplace.php
<? $city=intval($_GET['city']);
include("db.php");
$query="SELECT `city_id`, `city` FROM `cities` WHERE
`state_id`='$city'";
$result=mysql_query($query);
?>
<td><div align="left">Place:</div></td><td>
<? while($row=mysql_fetch_array($result)) { ?>
<input type="checkbox" name="place[]" value="<?=$row
['city_id']?>" /><?=$row['city']?>
<? } ?>
</td>
findsubcategory.php
<? $subcat = intval($_GET['subcat']);
include("db.php");
$query="SELECT `id`, `Subcatagory` FROM `subcatagorykeywords`
WHERE MainCatid='$subcat'";
$result=mysql_query($query);
?>
<td><div align="left">Subcategory:</div></td><td>
<? while($row=mysql_fetch_array($result)) { ?>
<input type="checkbox" name="relatedcatagories[]" value="<?=
$row['id']?>" /><?=$row['Subcatagory']?>
<? } ?>
</td>
and i want to store these values in database,which format type i have to store