0

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

Edited by malatamil: df

2
Contributors
2
Replies
13
Views
3 Years
Discussion Span
Last Post by malatamil
0

thanks. ya now i got it.
here i add categoryid and subcategoryid and spa table id

CREATE TABLE IF NOT EXISTS `relatedcatagories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mainCatagoryid` int(11) NOT NULL,
  `subCatagoryid` varchar(100) NOT NULL,
  `spaid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `spaid` (`spaid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='image gallery for beauty spa' AUTO_INCREMENT=69 ;

--
-- Dumping data for table `relatedcatagories`
--

INSERT INTO `relatedcatagories` (`id`, `mainCatagoryid`, `subCatagoryid`, `spaid`) VALUES
(68, 96, '552', 348),
(67, 96, '548', 348),
(66, 96, '544', 348),
(65, 96, '543', 348),
(63, 143, '756', 347),
(64, 143, '757', 347);

and here i added cityid and placeid

CREATE TABLE IF NOT EXISTS `city_place` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city_id` int(11) NOT NULL,
  `place_id` int(11) NOT NULL,
  `spaid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `city_place`
--

INSERT INTO `city_place` (`id`, `city_id`, `place_id`, `spaid`) VALUES
(1, 9, 12, 348),
(2, 9, 13, 348);

main table spa

INSERT INTO `spa` (`id`,`businessregisterId`, `ContactNumber`, `ContactPerson`, `EmailId`, `Url`, `Address`, `Landmark`, `Description`, `Companyname`, `LogoImage`, `LogoPath`,  `OfferDescription`, `OfferImage`, `offerPath`, `map`, `mapImage`, `mapPath`, `minisite`, `minisitePath`, `Publish`, `CreatedDate`) VALUES   
( 347,0, '080-956786287', 'Disha', 'hr.dishaventures@gmail.com', 'http://namshimoga.com', '', 'Disha', '<p>for testing </p>', 'Disha1', '', '', '',  '', '', '', 'Hydrangeas.jpg', './upimg/mapimg/2014-05-05_1399280310Hydrangeas.jpg', '', '', 1, '2014-05-05 14:28:30'),
(348, 0, '080-956786287', 'Tester Disha', 'hr.dishaventures@gmail.com', 'http://namshimoga.com', '',  'Tester Disha1', '<p>fgtg rtrstyyttuy y iioto</p>', 'Tester Disha', '', '', '', '', '', '', '', '', '', '', 1, '2014-05-05 16:22:57');

one more question,how to write a query for retrieve spa table data based on city after that maincategory wise i have to display

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.