[RESOLVED] Table organization & info retrieval

[warzol]

I am still learning PHP & MySQL, and am trying to figure some things out. I created a database with "company profiles" in it. I have also programmed a page that will display a particular companies information based on their "id". I would like to arrange it so that people visiting the site can choose a state & city to see a list of companies which would then link to the company profile page.

Instead of having to add each city in HTML whenever someone wants me to add their city, I thought it could pull from the database. I am assuming that I should create a new table for cities & states & give each of them an "id" of some sort as well, but I do not know how to make sure that they are all tied together. For example, If I create a state table & a city table, then remove the city & state rows from the company profile table, how do I make sure that the page that displays the company profile information still shows the location?

Hopefully that isn't too much overexplanation. If the solution requires anything done in MySQL & not just PHP, I am using PHPMyAdmin, so I should be able to figure it out easily enough. Thanks for any help! Below is how I am currently pulling info from the company profile table for display:

<?php
	$Host = "localhost";
	$User = "getitdon_warzol";
	$Password = "jemy45";
	$DBName = "getitdon_testing";
	$TableName = "company_profile";

$id = $_GET['id'];
$link = mysql_connect ($Host, $User, $Password);
$query = "SELECT * FROM $TableName WHERE id='$id'";
$select = mysql_select_db ($DBName);
$result = mysql_query ($query, $link) or die(mysql_error());
$row = mysql_fetch_assoc ($result);
?>

[leatherback]

Hi Warzol,

easy enough, you are getting to the point where you really start to use the potential of a database, and you have got exactly the right approach:

states: s_id, s_name (State ID, State Name)
cities: c_id, c_name, c_s_nr (City ID, City Name, State Number in City)
profiles: p_id, p_name, p_c_nr

query for one specific profile:
select * from profiles
left join cities on p_c_nr = c_id
left join states on c_s_nr = s_id
where p_id = $id
order by p_name

query for one state:

select * from profiles
left join cities on p_c_nr = c_id
left join states on c_s_nr = s_id
where s_id = $state
group by c_id
order by p_name

[warzol]

Thanks Leatherback! Glad to see I am at least on the right track. Hopefully, you can clear up some things that are still slightly confusing for me. I think I have the tables set up right (or close to it hopefully). Here is how I have them setup:

"states" table contains (state_id, state_abbr)
"cities" table contains (city_id, city_name, state_id)
"company_profiles" table contains (id, company, name, city_id, email, phone)

There are a few more irrelevent rows to the company_profiles table, but probably don't need to list them. I put the city_id & state_id under the assumption that they would need these to somehow be linked to each other. Is this the same as your "c_s_nr" & "p_c_nr" or should I rename them so they do not have the same name as they do in the other table?

Also, the "left join" part, I can't seem to quite wrap my brain around. I don't think I have figured out how to join tables (or databases?) 🙂 Thanks again for the help. Glad to see there are people who don't mind taking the time.

[leatherback]

Hi,

you do not HAVE to rename your colums. I prefer it personally. I try to avoid having the same name for same colums in one database, just to avoid confusion. You'll have to add the tablename if you keep the names the same:

cities.state_id vss states.state_id

The left joins basically tells the database to retrieve all matching recoirds, and the "on" command tells the database how the tables relate. Have a look at mysql.org for a manual, and search for join syntax. There are a few options, and it pays to spent 15-30 minutes studying it, in the long run you will benefit.

J.

[warzol]

Cool! Thanks for the info J. I will take a look at mysql & see what I can find. I think I will rename the fields as well. If they don't have to be the same, I assume they not be either. Thanks again. If I have any other questions, I will be sure to post again. Thanks!!

Jeff

[ahundiak]

Just thought I would add that at some point you may find out that a company may be in more than one city. That's called a many to many relationship. To implement you need another table.

profile_city
..profile_city_id
..profile_id
..city_id

With one record for each city that a company is in.

And then it's just a question of adjusting the joins in your query.

[warzol]

True. I had considered letting companies represent multiple cities, & how that would effect it. Thanks for the addition. Would I have to create a whole new table, or could I just add columns to one of the existing tables? Or maybe that is a bad way to do it? I also went through mysql.org to learn about the joins. Is there a benefit to using the left join instead of an inner join?

Also, from what I understand, if I keep the table columns named the same (i.e. state_id & city_id) that I could simplify the query a little by using "USING" instead of "ON" in a left join. Any pros/cons to doing it that way?

Lastly, I just want to clarify the syntax I would use to create the join. It LOOKS like I should have it like this (with tablenames being company_profile, cities, states):

<?php
	$Host = "localhost";
	$User = "getitdon_warzol";
	$Password = "jemy45";
	$DBName = "getitdon_testing";
	$TableName = "company_profile";

$id = $_GET['id'];
$link = mysql_connect ($Host, $User, $Password);
$query = "SELECT * FROM $TableName LEFT JOIN cities USING (city_id) LEFT JOIN states USING (state_id)";
$select = mysql_select_db ($DBName);
$result = mysql_query ($query, $link) or die(mysql_error());
$row = mysql_fetch_assoc ($result);

?>

First, is this correct (probably not 🙂 )? Second, if I join this way, do I need to have a state_id column in the company_profile table? I currently have only the city_id column in it. Thanks again folks!!

[ahundiak]

Your query will probably work. Try it. However, I generally avoid the whole column naming issue by using table_name.column_name syntax. Bit more typing but it saves headaches.

SELECT *
FROM company_profile
LEFT JOIN cities ON cities.city_id = company_profile.city_id
LEFT JOIN states ON states.state_id = cities.state_id

I generally use ON in joins just to explicitly call things out. Less majic the better.

Each type of join has it's own specific purpose. You need to work through some tutorials to understand which does what. Suffice it to say that LEFT JOIN is what you want for this particular query.

And far as adding columns to support multiple cities, in the end having another table will make things easier. And it is the approach used by most database professionals. But try it both ways and see for yourself.

[warzol]

Thanks Ahundiak! Looks like it works.

Got another question in regards to creating records for these. I am, obviously, not going to be able to remember all the city & state id's to update these records & changing 3 tables would be a pain, so I am guessing this is where I should start creating a form using the left join to update all the tables at once. So hypothetically, I create a form that basically just creates a new company profile including a city & state. Will the database know to make the city_id column the same in both the "company_profile" table & "cities" table? Similarly, will it know that by choosing a state from a dropdown menu to pull the state_id from the "states" table to put into the "cities" table? Or do I have to tell it to do this somehow? Thanks again for all the help.

[ahundiak]

Basically, it's up to you to do all the work.

Joins are for selecting and not for updating. It is up to you to make sure the correct city_id gets put into the company table.

Divide and conquer.

Avoid at all costs having one form trying to update three tables. That way lies madness.

Assume you have a form for adding a new company. Query the city/state table and build a select list of the various available cities. The city_id will be the value of the selected option. So the user can pick a city from a list. When the form is posted, you store the city_id in the company record.

If the city is not in the list then provide a link to an AddCity form. This form would present list of states as a pick list and allow the user to enter the city name. Upon posting the city name as well as the state_id comes back and you enter a new city record. When done you redirect back to the original company page and regenerate your city pick list.

At some point you may get enough cities to make using a pick list impractical. So then you probably want a little search function. But that's a bit down the line.

[warzol]

Yeah perhaps I will do it in a sequence of a few forms rather than using one. I attempted to create another table so people could have multiple cities like we mentioned earlier, but am having difficulties. I created the table like you mentioned. I then deleted the "city_id" column from company_profiles table since it seemed to be creating problems. That sort of worked, but it seems to delete the first recorded city in their list. I am guessing it either has something to do with deleting the "city_id" column or something in my code. Maybe you can let me know if you know what it is. Here is the code:

<?php 
    $Host = "localhost"; 
    $User = "getitdon_warzol"; 
    $Password = "jemy45"; 
    $DBName = "getitdon_testing"; 
    $TableName = "company_profile"; 

$state_id = $_GET['id']; 
$link = mysql_connect ($Host, $User, $Password); 
$query = "SELECT * FROM $TableName LEFT JOIN profile_cities ON company_profile.id = profile_cities.profile_id LEFT JOIN cities ON cities.city_id = profile_cities.city_id WHERE id='$id'"; 
$select = mysql_select_db ($DBName); 
$result = mysql_query ($query, $link) or die(mysql_error()); 
$row = mysql_fetch_assoc ($result); 

while ($row = mysql_fetch_array ($result)) 
{	print ("You are in ".$row[city]."</br>\n"); 

	 } 	

mysql_close ($link);
?>

Last little thing, is that if I try to also do a left join with the states table, I get a "cross dependency, check your ON statements" error. Can you only join 2 tables at a time, or is there a secret to joining more than 2? Thanks a ton!!

[ahundiak]

This:
WHERE id='$id'"
should be
WHERE company_profile.id = '$id'
Just to be consistent. Make sure $id is being set since it's not in your code.

I'd suggest echoing the sql out then pasting it into phpAdmin and make sure the query works.

Then join in the state table. You can have as many joins as you need so I'm not sure what the problem is. But get your first joins working.

[warzol]

Yeah, I actually did switch in the company_profiles.id just in case. Still get the same results. The part that is weird is a created a bunch of cities and assigned four of them to my test account & it grabs the 2nd, 3rd, & 4th correctly, but not the 1st one. So it seems like it is working, but just not echoing the first result for some reason. [URL=http://********itdonedesign.com/CreateDB.php?id=1]Here is the link to the results.[/URL] These are all cities in Lansing. The first one that is supposed to be listed is Bath, but is not there. You can also use id=2 for another company I tested & all the cities are named Testing2, 3, etc... You will notice that the first one is missing there as well).

Could it be something in the table setup? It seems correct. Or any other ideas?

I ran a sql query on phpMyAdmin on my profile_cities table (SELECT * FROM profile_cities WHERE profile_id =1) and it brings up the correct city_id's. But I changed the code to get rid of all the joins & pull directly from the profile_cities using the same SELECT statement (got rid of all other unnecessary steps as well)& it still skips the first record. Just for kicks, here is the code I used to try it this way:

<?php 
    $Host = "localhost"; 
    $User = "getitdon_warzol"; 
    $Password = "jemy45"; 
    $DBName = "getitdon_testing"; 
    $TableName = "profile_cities"; 


$link = mysql_connect ($Host, $User, $Password); 
$query = "SELECT * FROM $TableName WHERE profile_id=1"; 
$select = mysql_select_db ($DBName); 
$result = mysql_query ($query, $link) or die(mysql_error()); 
$row = mysql_fetch_assoc ($result); 

while ($row = mysql_fetch_array ($result)) 
{	print ("You are in ".$row[city_id]."</br>\n"); 

	 } 	

mysql_close ($link);
?>

Anybody have any clue on this? Another weird thing I noticed is that if I simply remove the "while" loop, it by default pulls up the first record (the one that it won't bring up with the loop is used). It is all very weird 🙂

[ahundiak]

$result = mysql_query ($query, $link) or die(mysql_error());

$row = mysql_fetch_assoc ($result);  // DELETE ME, this is causing the skip

while ($row = mysql_fetch_array ($result)) 

[warzol]

ah, sweet. That did it. So, leaving that file in I realize tells it that it is to look for a particular record. So basically, it was finding the first record & then not seeing a print it moves on to the while loop & prints out the rest of them? Sorry, trying to clarify so I can try to actually learn what it is doing. Thanks a ton Ahundiak!