How do I do this in the right way?

[vigour]

I have a table called place and another table called products. This is how the place table looks:

place_id, place
for example
2, Paris
3, London
and so on...

And the products table:

place_id
for example
2
2
3
3
2

I want to get all place_id rows from the products table and translate them to their real names that are located in the place table. I have tried this code but I get a error saying:

mysql_fetch_array(): supplied argument is not a valid MySQL result resource

$result = mysql_query("SELECT place FROM place WHERE place_id = products.place_id");
while($row=mysql_fetch_array($result))
	{
	echo $row["plats"];
	echo '<br>';
	}

[paulnaj]

Hi,

According to your question here's three ways to skin the same cat (I prefer the last one) ...

SELECT place.place
FROM products, place
WHERE products.place_id = place.place_id

SELECT place.place_id
FROM products
LEFT JOIN place ON (products.place_id = place.place_id)

SELECT place.place_id
FROM products
LEFT JOIN place USING (place_id)

I've put in the backticks for completeness (this avoids problems if your table/field names are reserved words).

Paul.

[vigour]

Thanks a lot, it worked perfectly :-)

paulnaj;10920571 wrote:

Hi,

According to your question here's three ways to skin the same cat (I prefer the last one) ...

SELECT place.place
FROM products, place
WHERE products.place_id = place.place_id

SELECT place.place_id
FROM products
LEFT JOIN place ON (products.place_id = place.place_id)

SELECT place.place_id
FROM products
LEFT JOIN place USING (place_id)

I've put in the backticks for completeness (this avoids problems if your table/field names are reserved words).

Paul.

[vigour]

The above code works great but how do I solve this?

I have 4 tables called prefix, location, owner, collection. All 4 tables have a id and a name like this: prefix_id, prefix, location_id, location, owner_id, owner, collection_id, collection

I have another table called connect_prefix. The table connect_prefix have fields that are called prefix_id, location_id, owner_id, collection_id, but I need the names from the prefix, location, owner, collection tables.

Can the same code be used as in the first example? I have tried but it does not work. I start to understand how it works but I'm not there yet.

[paulnaj]

Hi vigour,

In this case (more than one table joined to a 'base' table), you need either the first or second version ...

SELECT prefix.prefix, location.location
FROM connect_prefix
LEFT JOIN prefix ON (connect_prefix.prefix_id = prefix.prefix_id)
LEFT JOIN location ON (connect_prefix.location_id = location.location_id)

... see the pattern?

Paul

[vigour]

You explained this very well. A base tabel "joined". I have never understood the join term until now. One more question. I have a row called subdomain in the connect_prefix table. I only want to include rows where that row is for example soderhamn. I can use WHERE subdomain = 'soderhamn' but I'm unsure where to insert it in the query. This is the query so far:

$result = mysql_query("SELECT DISTINCT `prefix`.`prefix`, `place`.`place`, `owner`.`owner `, `collection`.`collection `
	FROM `connect_prefix` 
	LEFT JOIN `prefix` ON (`connect_prefix `.`prefix_id` = `prefix`.`prefix_id`) 
	LEFT JOIN `place` ON (`connect_prefix `.`place_id` = `place`.`place_id`)
	LEFT JOIN `owner ` ON (`connect_prefix `.`owner_id` = `owner `.`owner_id`)
	LEFT JOIN `collection ` ON (`connect_prefix `.`collection_id` = `collection `.`collection_id`) ORDER BY prefix");

paulnaj;10920664 wrote:

Hi vigour,

In this case (more than one table joined to a 'base' table), you need either the first or second version ...

SELECT prefix.prefix, location.location
FROM connect_prefix
LEFT JOIN prefix ON (connect_prefix.prefix_id = prefix.prefix_id)
LEFT JOIN location ON (connect_prefix.location_id = location.location_id)

... see the pattern?

Paul

[vigour]

I used WHERE prefix.subdomain = 'soderhamn' now and it seems to work. Is that the right way? The query is like this:

$result = mysql_query("SELECT DISTINCT `prefix`.`prefix`, `place`.`place`, `owner`.`owner `, `collection`.`collection `
	FROM `connect_prefix` 
	LEFT JOIN `prefix` ON (`connect_prefix `.`prefix_id` = `prefix`.`prefix_id`) 
	LEFT JOIN `place` ON (`connect_prefix `.`place_id` = `place`.`place_id`)
	LEFT JOIN `owner ` ON (`connect_prefix `.`owner_id` = `owner `.`owner_id`)
	LEFT JOIN `collection ` ON (`connect_prefix `.`collection_id` = `collection `.`collection_id`) WHERE prefix.subdomain = 'soderhamn' ORDER BY prefix");

It also seems that I can use LEFT JOIN or only JOIN in this example. What's the difference?

vigour;10920666 wrote:

You explained this very well. A base tabel "joined". I have never understood the join term until now. One more question. I have a row called subdomain in the connect_prefix table. I only want to include rows where that row is for example soderhamn. I can use WHERE subdomain = 'soderhamn' but I'm unsure where to insert it in the query. This is the query so far:

$result = mysql_query("SELECT DISTINCT `prefix`.`prefix`, `place`.`place`, `owner`.`owner `, `collection`.`collection `
	FROM `connect_prefix` 
	LEFT JOIN `prefix` ON (`connect_prefix `.`prefix_id` = `prefix`.`prefix_id`) 
	LEFT JOIN `place` ON (`connect_prefix `.`place_id` = `place`.`place_id`)
	LEFT JOIN `owner ` ON (`connect_prefix `.`owner_id` = `owner `.`owner_id`)
	LEFT JOIN `collection ` ON (`connect_prefix `.`collection_id` = `collection `.`collection_id`) ORDER BY prefix");

[paulnaj]

Looks good to me ... only thing I would do is to follow through using backticks and also table.field all the time ...

$result = mysql_query("
SELECT DISTINCT `prefix`.`prefix`, `place`.`place`, `owner`.`owner `, `collection`.`collection `
FROM `connect_prefix`
LEFT JOIN `prefix` ON (`connect_prefix `.`prefix_id` = `prefix`.`prefix_id`)
LEFT JOIN `place` ON (`connect_prefix `.`place_id` = `place`.`place_id`)
LEFT JOIN `owner ` ON (`connect_prefix `.`owner_id` = `owner `.`owner_id`)
LEFT JOIN `collection ` ON (`connect_prefix `.`collection_id` = `collection `.`collection_id`) 
WHERE `prefix`.`subdomain` = 'soderhamn' 
ORDER BY `prefix`.`prefix`
");

The difference between JOIN and LEFT JOIN is to do with which records are returned. With a LEFT JOIN all records from the FROM table are included whether or not there are matching records in the 'LEFT JOIN'ed tables. With a JOIN, only matching records (non-NULL values) will return.

In your case, it seems that every record in the connect_prefix table shouldn't have NULL values for the id's so JOIN will return all the records anyway, but LEFT JOIN is more correct.

Paul.

[vigour]

Thanks a lot my friend. You have learned me a lot of things with this. Thanks again.

paulnaj;10920678 wrote:

Looks good to me ... only thing I would do is to follow through using backticks and also table.field all the time ...

$result = mysql_query("
SELECT DISTINCT `prefix`.`prefix`, `place`.`place`, `owner`.`owner `, `collection`.`collection `
FROM `connect_prefix`
LEFT JOIN `prefix` ON (`connect_prefix `.`prefix_id` = `prefix`.`prefix_id`)
LEFT JOIN `place` ON (`connect_prefix `.`place_id` = `place`.`place_id`)
LEFT JOIN `owner ` ON (`connect_prefix `.`owner_id` = `owner `.`owner_id`)
LEFT JOIN `collection ` ON (`connect_prefix `.`collection_id` = `collection `.`collection_id`) 
WHERE `prefix`.`subdomain` = 'soderhamn' 
ORDER BY `prefix`.`prefix`
");

The difference between JOIN and LEFT JOIN is to do with which records are returned. With a LEFT JOIN all records from the FROM table are included whether or not there are matching records in the 'LEFT JOIN'ed tables. With a JOIN, only matching records (non-NULL values) will return.

In your case, it seems that every record in the connect_prefix table shouldn't have NULL values for the id's so JOIN will return all the records anyway, but LEFT JOIN is more correct.

Paul.