tags:

views:

38

answers:

1

I am a bit new to sql and php so I need some help here. I created two tables and I want to establish a 'one-to-many relationship' but I can't figure out how I can give the data through the php file. Here is some code:

   CREATE TABLE `details` (
  `details_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `mytext` varchar(60) NOT NULL,
  `description` varchar(600) NOT NULL,
  `city_details` int(11) NOT NULL,
  PRIMARY KEY (`details_id`),
  FOREIGN KEY (`city_details`) REFERENCES city(`city_id`)
  on update cascade
);

CREATE TABLE  `city` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
   `cityName` varchar(30) NOT NULL,
  PRIMARY KEY (`city_id`)
);

So I want to write a cityName and then be able to give some data for various places of the city. How can I create a php file so that I will only have to give the name of the city (to table city) and then write things for multiple locations inside the city (to table details).

+1  A: 
$sql="SELECT * FROM `details` as d INNER JOIN `city` as c ON d.city_details=c.city_id;";

if you want to look for a city name you can use this

$sql="SELECT * FROM `details` as d INNER JOIN `city` as c ON d.city_details=c.city_id WHERE c.cityName='The name you're looking for'";

then to fetch everyting from the table use this code

while($row=mysql_fetch_assoc(mysql_query($sql))){
  echo $row['name']; //for name
  echo $row['mytext']; //etc.
}

for more info see http://dev.mysql.com/doc/refman/5.1/en/join.html and http://php.net/manual/en/function.mysql-fetch-assoc.php

Christian Smorra
shouldn't the city_id have the same value as the city_details???
alecnash
yes thats where you join both tables on
Christian Smorra
updated my answer the second query selects all details related to the name you state in the where clause
Christian Smorra
But I want to use a post method so that someone can post a city and then details of it. How will I be able to connect the city which is unique to the details which may be multiple?
alecnash
and thanks for your replies
alecnash
well in that case you need to submit the city details first, then get the city_id with mysql_insert_id() [http://php.net/manual/en/function.mysql-insert-id.php] and then write the details into the according table with city_id as a reference
Christian Smorra