Weird query ??

[knoak]

Hi there,

I have a table with items in it.
Table contents are: id, title, text, combination

Each product has a few other products with
which it can be combined. In the 'combination'-
field is an array of 3 other id's the product
can be combined with. (e.g. 78-34-94)

When i run a query, i display 20 results / page,
so results are LIMIT-ed. How can i display 20
items with title, text, and then 3 links to
corresponding items in the 'combination' collumn.
The links have to be the titles of those other items.

Thanks in advance & greetings,

Knoak

[Michael Fesser]

Re: Weird query ??

.oO(knoak)
[color=blue]
>I have a table with items in it.
>Table contents are: id, title, text, combination
>
>Each product has a few other products with
>which it can be combined. In the 'combination'-
>field is an array of 3 other id's the product
>can be combined with. (e.g. 78-34-94)[/color]

Broken design. Use another table for the combinations of products, don't
put multiple informations into a single field (keyword: normalization).

table products
--------------
ID
title
text

table combinations
------------------
productID
combinationID

For a product with the ID 42 and your three combinations with other
products there would be three records in the second table:

42, 78
42, 34
42, 94
[color=blue]
>When i run a query, i display 20 results / page,
>so results are LIMIT-ed. How can i display 20
>items with title, text, and then 3 links to
>correspondin g items in the 'combination' collumn.[/color]

With your current design you would have to split the combination field
with PHP and send another query to the server to get the associated
records. With a better design you should be able to get all informations
with a single query.

Micha

[knoak]

Re: Weird query ??

Could you maybe explain a little bit more?
About how i would put this into reality?

Thanks!

[knoak]

Re: Weird query ??

*Anyone* ? Please...

[Jerry Stuckle]

Re: Weird query ??

knoak wrote:[color=blue]
> *Anyone* ? Please...
>[/color]

1. Try searching the Internet on things like database normalization
2. Go to the library and check out a book on database design
3. Buy a book on database design

No, I'm not being flippant. Database design is not something you can
learn in a few messages. There are a few ways to do it right - and a
million ways to do it wrong!

[Geoff Berrow]

Re: Weird query ??

I noticed that Message-ID: <4qu821t0i37mfj p0jjsb54cfgtu6l s79am@4ax.com>
from Michael Fesser contained the following:
[color=blue][color=green]
>>Each product has a few other products with
>>which it can be combined. In the 'combination'-
>>field is an array of 3 other id's the product
>>can be combined with. (e.g. 78-34-94)[/color]
>
>Broken design. Use another table for the combinations of products, don't
>put multiple informations into a single field (keyword: normalization).[/color]

Correctly normalising the data would be best but actually it would be
possible to do what he wants to do, provided he was consistent in the
way he stored the data. You would just explode the field into an array
and then produce the links from the array values.


$comb_id = explode("-", $myrow['combination']);

for ($i=0;$i<count( $comb_id);$i++) {
echo"<a href=\"lookuppa ge.php?id=".$co mb_id[$i]."\">Combinatio n
$i</a><br>\n";
}
Then in lookuppage.php use $_GET[id] as the basis of a query.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[knoak]

Re: Weird query ??

Thanks Geoff,

But wouldn't this mean i'd have to run a query for every result?

Trying to understand the solutions everyone's providing.

Knoak

[Geoff Berrow]

Re: Weird query ??

I noticed that Message-ID:
<1110023662.152 627.91820@z14g2 000cwz.googlegr oups.com> from knoak
contained the following:
[color=blue]
>Thanks Geoff,
>
>But wouldn't this mean i'd have to run a query for every result?[/color]

Yep.

But databases are good at that.


--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/