Page 1 of 1

Two Tables Displaying using PHP

Posted: Sat Mar 22, 2003 7:50 am
by meandrew
So the two tables insert is now working so now the display is needed :)

I have this:

Code: Select all

// This returns all the companies from items for the selected city and business type
$result=mysql_query("SELECT items.ItemSKU, items.ItemName, items.ItemDescription, items.PostCode, items.Category, items.CityID, items.CTelephone, items.ItemID, items.Cfax, items.Cemail, items.Caddress, items.CTown, items.Cwww FROM items, city WHERE Category='$Category' and CityID='$CityID' ORDER BY CityID");
if (!$result) echo mysql_error();
else {
    }
while ($row=mysql_fetch_row($result)) {
$IS=$rowї'0'];
$IN=$rowї'1'];
$ID=$rowї'2'];
$CpostC=$rowї'3'];
$Ca=$rowї'4'];
$City=$rowї'5'];
$Ctele=$rowї'6'];
$II=$rowї'7'];
$Cf=$rowї'8'];
$Ce=$rowї'9'];
$Caddress=$rowї'10'];
$Ctown=$rowї'11'];
$Cw=$rowї'12'];
however the CityID is no longer stored in the items table. It is in the city table :)

So I presumably need to do a join?

Any suggestions to get me on the right track?

Andrew

Posted: Sat Mar 22, 2003 8:37 am
by jollyjumper
Hi Andrew,

You indeed need a join.

I presume you have to tables, one named items and one name city.

I don't exactly know what those two tables are, but I guess you have items which are located in a city. The detailed information about the items is in the items table, and the detailed information of the city is placed in the city table. If my assumption is incorrect, please tell me, because it could be that the rest of my story is incorrect then.

When I make a join query, this is what I do:

First of all, make a list of all the fields from all the tables you need. For example if you would need ItemName and ItemDescription from the items table and CityName from the city table, make a list like this:

items.ItemName, items.ItemDescription, city.CityName

If you put the word SELECT in front of it you've got half the query :-)

Then make the From statement. The primary data is coming from the items table, so extend the query with: From items

You also want information from the city table, so you have to join the items table with the city table. So extend your query with: left join city

Now the query needs to know on what field the two tables can be joined.

If you have a field CityId in you items table and a field ID in the City table(it's the primary key field, you can give it another name, but I commonly use ID for my primary key fields). So extend your query with:
on items.CityId = city.ID

know you can add the where and order by statements. But do not forget to use table.fieldname because you are using multiple tables, which could have some fields with the same name. So WHERE category = '$Category' would become WHERE items.category = '$Category'

This is the simple join query I build in this example:

SELECT items.ItemName, items.ItemDescription, city.CityName from items left join city on items.CityId = city.ID

Hope this gives you an idea.

You can find a lot more information about queries at http://www.mysql.com. Just use the search function at the top right of their site.

Also if you have MS Access, you can use the query builder to make queries. They do not always work right away in MySQL but it get's close most of the time.

Good luck

Greetz Jolly.

Posted: Sat Mar 22, 2003 9:08 am
by meandrew
thanks Jolly

You are right in your interpretation. The items table hold all the details and the items_city table caters for recognises that the record in items can itentified with more than one city/town.

I am getting errors with the query still and not sure if it is a join that I need?
http://www.punterspower.co.uk/test/ is the query in action if you follow it through you will see the error.

This is the full query:

$result=mysql_query("SELECT items.ItemSKU, items.ItemName, items.ItemDescription, items.PostCode, items.Category, items.CityID, items.CTelephone, items.ItemID, items.Cfax, items.Cemail, items.Caddress, items.CTown, items.Cwww item_city.item_id, item_city.city_id FROM items LEFT JOIN city WHERE items.Category='$Category' and items.ItemID='$city_id' ORDER BY CityID");

the main search is based on the ItemID being eqaul to city_id in the city table as that is waht we want to display. :)

Andrew

Posted: Sat Mar 22, 2003 10:21 am
by jollyjumper
Hi Andrew,

I can't seem to get an error. When I select a county then I only get the message that there we're 0 companies found.

I don't exactly get the idea of what you want to do with the query, I believe my interpretation of the items table was right, but the item_city not quite.

I've adapted your query so that it shouldn't give an error, but I can't really say if it gives the result you want.

SELECT items.ItemSKU, items.ItemName, items.ItemDescription, items.PostCode, items.Category, items.CityID, items.CTelephone, items.ItemID, items.Cfax, items.Cemail, items.Caddress, items.CTown, items.Cwww, item_city.item_id, item_city.city_id FROM items LEFT JOIN item_city on items.ItemId = item_city.city_id WHERE items.Category='$Category' and items.ItemID='$city_id' ORDER BY items.CityID

Do you have MSN messenger on your computer, it maybe talks a little bit easier.

You can find me under lkistenkas@passport.com

Greetings Jolly.
[/b]

Posted: Sat Mar 22, 2003 10:36 am
by meandrew
I think I managed to get the query not to show an error before you looked at the link :) but I think that it is more complicated because the item_city table is holding city id and item id I originally had the CityID in the items table but this only allowed each item to be recognised in one city where in fact they could be in more than one city.

Andrew

Posted: Sat Mar 22, 2003 10:43 am
by meandrew
this is more complicated as I thought! The city_id is determined by the city from the city table :(

Posted: Sat Mar 22, 2003 10:49 am
by jollyjumper
Hi Andrew,

If you want to show all the items which are located in one city you should adjust your where statement:

Items.ItemID='$city_id' ORDER BY items.CityID

should become

item_city.CityID='$city_id' ORDER BY item_city.CityID

Okay I just read your last post, don't get desperate, these are the nice queries :-)

Do you have the program MySQL Front? If so maybe you could export the database to an sql file and send it to me, so I can see how the tables are build so I can try and make a query for you, and explain how I did it.

If the data in the database are private, please don't send the data, only the table structures.

You could also give me the tablenames, and all the field names, so I can create a test database here.

Greetings Jolly.

Posted: Sat Mar 22, 2003 11:07 am
by meandrew
I have sent the sql to create the table structure :)

I have tried chnaging the query around but I think this something to do with item_city table not beoing recognised?

Andrew

Posted: Sat Mar 22, 2003 11:38 am
by meandrew
Looking at this again! I now think that the problem may lie with the CityID in the items table as this now show 0 when a new record is diplayed and subsquently a record displayed for the category regarless of city_id :( do queries get easier than this!

Andrew

Posted: Sat Mar 22, 2003 4:18 pm
by jollyjumper
Hi Andrew,

Did you send it to lkistenkas@passport.com? I'm sorry, but that is just my msn messenger address, it is not a email box(at least not that I know of). Could you send it to laurens@kistenkas.nl.

Greetz Jolly.