How can I JOIN (I think) these two tables ??

spacepoet · February 22, 2011

Hello:

I want to JOIN (I think) 2 tables together so they can work together.

I have this little page to select all the stores in a "stores" table that are assigned to a zip code from the "zip_codes" table:

<a href=\"Stores.php?zip_id=".$row['zip_id']." \">
...
<?php

include('include/myConn.php');
include('include/myCodeLib.php');

$zip_id = $_REQUEST['zip_id'];

?>

<?php

$query=mysql_query("SELECT store_id,name FROM stores WHERE $zip_id = $zip_id") or die("Could not get data from db: ".mysql_error());

while($result=mysql_fetch_array($query))
{

$store_id=$result['store_id'];
$name=$result['name'];

}

?>

<html>

<head>
<title></title>
</head>

<body>


<?php echo $name; ?> | <?php echo $store_id ?>


</body>

</html>

It is currenty not complete or working becasue I a stuck on how to make the two tables talk to each other.

This is the "stores" table:

CREATE TABLE `stores` (
  `store_id` INT(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `address` VARCHAR(50) NOT NULL,
  `town` VARCHAR(25) NOT NULL,
  `state` VARCHAR(50) NOT NULL,
  `postal` VARCHAR(5) NOT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `hours` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY  (`store_id`),
  KEY `postal` (`postal`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `stores` */

INSERT INTO `stores`(`store_id`,`name`,`address`,`town`,`state`,`postal`,`phone`,`hours`) VALUES
(1,'Main Shop','123 Church Street.','New York','New York','10007','(212) 992-3399','Mon-Fri: 9-5, Sat: 8-6, Sun: Closed');

ETC....

This is the "zip_code" table:

CREATE TABLE `zip_codes` (
  `zip_id` INT(11) NOT NULL AUTO_INCREMENT,
  `zip` VARCHAR(5) NOT NULL DEFAULT '',
  `lat` VARCHAR(10) NOT NULL DEFAULT '',
  `lon` VARCHAR(10) NOT NULL DEFAULT '',
  `city` VARCHAR(50) DEFAULT NULL,
  `full_state` VARCHAR(50) DEFAULT NULL,
  `abbr_state` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY  (`zip_id`),
  UNIQUE KEY `zip` (`zip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `zip_codes` (`zip`, `abbr_state`, `lat`, `lon`, `city`, `full_state`) VALUES ('00501', 'NY', 40.92233, -72.63708, 'HOLTSVILLE', 'NEW YORK');

ETC....

I basically want to be able to assign stores to each zip code based on the "zip_id", edit the stores based on the "zip_id", delete the stores based on the "zip_id" ...

If I have to change one of the two tables, it would be easier to change the "stores" table.

Do I need to make "postal" become "zip" in the stores table?

And somehow create a FOREIGN KEY in stores for "zip_id"

How might I be able to get this working properly?? I think I'm on the right track in my thinking, but not sure how to create the logic.

Thanks.

kickstart · February 22, 2011

Hi

Probably best to change the stores table so that it has a column for zip_id.

You can set it up as a foreign key, but it isn't essential. If you do then it builds in protection from people giving a store a non existent zip id.

If you had selected a zip code and got the id for it then you could use:-

"SELECT store_id,name 
FROM stores 
WHERE zip_id = $zip_id"

If you just had the zip code then

SELECT store_id,name 
FROM stores a
INNER JOIN zip_code b
ON a.zip_id = b.zip_id
WHERE b.zip = '$zip'"

All the best

Keith

spacepoet · February 22, 2011

Hi Keith:

Thanks for thr reply.

I'm trying to get my brain around this .. I think I have the theory correct .. little uncertain how to impliment it.

Would I not need to also (somehow) pull in the data from the "zip_code" table as well?

Like:

SELECT zip FROM zip_code WHERE zip_id = "$zip_id

I think this would work .. my example code probably isn't the best.

It's going to be a form that will have fields for Store Name, Address, City, State, Zip (and probably a few photo uploads).

My idea is to pre-populate the City, State, And Zip with the data from the "zip_codes" table, since it already exists, and the allow the user to add the Store name and address, then submit the form into the "stores" table.

Does that make sense what I'm trying to do?

So, I should probably change the "postal" to be "zip" and add a new field for "zip_id" in the "stores" table?

BTW - I don't know how to make a FOREIGN key! Is that something I set in the mySQL admin panel, or the SQL itself?

I will not add it if it's not needed, but it would be good to know.

Thanks.

Muddy_Funster · February 23, 2011

Yeah, that is pretty much the code you will need to get the info our the database to populate the form with.

Just an FYI for future refference - try and keep your php variable names different from your table fields and table names. It's much less confusing in the long run, and more secure (for people who worry about these things).

Foreign keys can be set in the admin - most commonly they will be set using this at the point of creating the table its self. But anything that you can do in the admin can also be done with an SQL statement. You never actualy "NEED" a foreign key, but by the same token you never actualy "NEED" a primary key either. Obviously you try and use a primary key wherever practical (and yes, there are some occasions where it's just not - though they are few and far between), so you sould also be trying to use foreign keys under the same philosophy. The problem is foreign keys are less well known in the general public (yet another thing I am going to blame that bloody M$ Access for). Most people, will pick up on primary keys, either by using some "user friendly" database system or from skimming the first few chapters of a DBA book...

anyway I digress, I was trying to get across:- don't think of it in terms of whether you "need" it or not, trying to get away with the bare minimum is not a mindset that will behove this kind of practice, think rather "will it make my database better if I use it" and you will get along much better in the long run.

spacepoet · February 23, 2011

Hello:

Thanks for the input. You said some good and useful things here.

I think you mean I should be doing:

zip_id = $my_zip_id"

to keep the "php variable names different from your table fields and table names."

Note taken about the KEYs as well.

Thanks!

Muddy_Funster · February 23, 2011

No problem, hope it helps.

kickstart · February 24, 2011

I'm trying to get my brain around this .. I think I have the theory correct .. little uncertain how to impliment it.

Would I not need to also (somehow) pull in the data from the "zip_code" table as well?

You do, and the SQL I gave does this. I take it you haven't come across joining tables before.

In simple SQL you select FROM a single table. However you can also select data from multiple tables at once where that data matches.

For example if you had a table of school kids saying what form they are in and a second table of forms listing the form teacher and the room they are based in then this can be brought back in a single query. To do this you use a JOIN. There are a couple of different types of join. Normally you use an INNER JOIN which in this example would be used to bring back a row for every kid and their matching details for the form they are in. However if they were not assigned to a form there is no matching form row, and with an INNER JOIN this would mean that nothing would be brought back for that kid. Using a LEFT OUTER JOIN would bring back a row even when there was no matching form row, just their fields from the form table would be set to NULL.

Going to your code you could use:-

SELECT a.store_id, a.name, b.City, b.State, b.Zip

FROM stores a

INNER JOIN zip_code b

ON a.zip_id = b.zip_id

WHERE b.zip = '$zip'"

That would get you a list of all the stores (including zip related info) with the zip code that you give

BTW - I don't know how to make a FOREIGN key! Is that something I set in the mySQL admin panel, or the SQL itself?

I will not add it if it's not needed, but it would be good to know.

You can set them up after a table is designed, but they can be defined with the table. They are not essential but are good practice.

All the best

Keith

Sign In

How can I JOIN (I think) these two tables ??

Recommended Posts

spacepoet

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

spacepoet

Link to comment

Share on other sites

Muddy_Funster

Link to comment

Share on other sites

spacepoet

Link to comment

Share on other sites

Muddy_Funster

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information