0

I have 2 tables: "catalog" and "press" being "press" the child table and I have them related by id_catalog (pointing to "id" in catalog) inside "press".

I managed to get one field on catalog ("name") and their "press" childs like this for testing porpouses:

$query="SELECT catalog.book_title, press.* FROM catalog, press WHERE press.id_catalog = $id";

$book_title = mysql_result($result,$i,"_catalog.book_title");
$media_name = mysql_result($result,$i,"_press.media");
$type = mysql_result($result,$i,"_press.type");
$url = mysql_result($result,$i,"_press.url");

echo $book_title $media_name $type $url;

$book_title = mysql_result($result,$i+1,"catalog.book_title");
$media_name = mysql_result($result,$i+1,"press.media");
$type = mysql_result($result,$i*+1*,"press.type");
$url = mysql_result($result,$i*+1*,"press.url");

echo $book_title $media_name $type $url;

In the query, $id is passed by GET_[] It returns what's expected

Here's the question: What if I don't do the relation in the database? (with the INDEX on id_catalog) Would the query work?

I'm answering this 'cause I think if I don't do the relation I could match the fields and make it work anyway... or not?

Zombo
  • 1
  • 62
  • 391
  • 407
Ariel Noname
  • 31
  • 1
  • 5
  • If your query above is returning what you expect, it is by chance. You have no relationship in the query itself, and defining a `FOREIGN KEY` in the table definitions does not cause the key to be automatically related in queries. – Michael Berkowski Feb 15 '13 at 23:11
  • Your query above is returning a cartesian product. Really, you need to do `FROM press JOIN catalog ON press.id_catalog = catalog.id WHERE press.id_catalog = $id` – Michael Berkowski Feb 15 '13 at 23:12

1 Answers1

0

I don't think your issue is with the index (although it would help increase performance). I think the issue is this query is producing a Cartesian product -- you aren't relating the 2 tables:

SELECT catalog.book_title, press.* 
FROM catalog, press WHERE press.id_catalog = $id

Instead, it should be something like:

SELECT catalog.book_title, press.* 
FROM catalog 
   JOIN press ON catalog.id = press.id_catalog
WHERE press.id_catalog = $id
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • Ok got it. So I'm not doing any relation whatsoever and I'm getting it working 'cause I'm kind of like doing the relation by a "query trick" if I may.. Is that correct? The difference between "my way" and the "correct way" by @sgeddes lies on performance then? Edit: I did the relation in phpmyadmin on the table "press", cascading. – Ariel Noname Feb 15 '13 at 23:26
  • @noname -- how many records are in your catalog table. With your query, every record in that table will be returned. When you add the JOIN (relationship) between the 2 tables, only the record that corresponds with the search criteria will get returned. Hope this helps. – sgeddes Feb 15 '13 at 23:34
  • So doing that increases the performance in large databases! Now my question is clear I'm very thankful! I'd give you the rep point but I can't 'cause I'm new here. Cheers! – Ariel Noname Feb 15 '13 at 23:41