Re: [PHP] SQL in array() ?

[James Holloway]

Thanks for your comments, Miles.  To answer your question(s), I'll not be
wanting to select multiple categories in anything other than the
administration section.  The main search form has the categories listed, and
the user only has the option to select from one category.

The reason I need to be able to give an entry multiple category listings is
this:
The Snail and Lettuce is a pub / restaurant.  The categories list contains a
listing, pub, restaurant.  So the user can choose from either pub or
restaurant when performing a search, but the entry must be displayed in
searches for both pub and restaurant Does that makes sense?

Perhaps I'm going about this the wrong way  Sleep deprivation does funny
things to the mind :)

James


> All this brings me to the question I should have asked up front. How often
> will you want to select multiple categories, and will it be done by range
> (>2 , <=3) or individual items ( 1 and 5 and 7 )?


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP] SQL in array() ?

[Miles Thompson]

I'm not going to comment on array processing techniques, but I suggest you 
are into a situation where you have to rethink your database design from 
the point of view of performance and normalization.

I don't know how many records you will eventually have, but the key to 
performance in SQL databases is the effective use of indexes, and queries 
which match. Even if you have an index on the cat_id field, a query like
SELECT * FROM entries WHERE cat_id IN($getcats)
forces the database engine into a sequential read of the cat_id field.

So you can drop the cat_id field from entries and create a new table 
entry_cat which has two fields
id
cat_id

Which gives you this query, although I'm a little uncertain about the 
bracketing of the condition.

select entries.id, entries.name, entry.cat_id from entries, entry_cat where 
(entry_cat.id = 1 || entry_cat.id = 3)

(And which, on reflection, may not yield better performance.)

All this brings me to the question I should have asked up front. How often 
will you want to select multiple categories, and will it be done by range 
(>2 , <=3) or individual items ( 1 and 5 and 7 )?

I don't think I've been terribly helpful - Miles

At 12:14 PM 7/17/01 +0100, James Holloway wrote:
>Hey guys,
>
>I saw a post in here the other day that's prompted me to ask this
>question...  Because I can't seem to get the solution mentioned to work.
>Maybe I'm missing something obvious...  Anyway, here goes.
>
>I have a list of categories contained in one table, and a list of entries in
>another.  So
>
>categories:
>id name
>1 category1
>2 category2
>3 category3
>
>At the moment, the entries table has a field which contains a field
>mentioning the category by id, so:
>
>entries
>id cat_id name
>1 1 name1
>2 1 name2
>3 1 name3
>
>The problem is this:  I'd like to give some entries more than one category.
>I attempted to put the category id's into an array.  So a row in the revised
>entries table now looks like this:
>
>entries
>id cat_id name
>1 2 name1
>2 1,3 name2
>3 1 name3
>
>And I'm trying to list from that table with code similar to this:
>
>
>$cat[0] = 1;
>$cat[1] = 3;
>
>$getcats = implode(",", $cat);
>
>$query = @mysql_query("SELECT * FROM entries WHERE cat_id IN($getcats)")
> or die (mysql_error());
>
>// Get the results
>
>?>
>
>I get no errors when executing that code, but I don't get any results back,
>either.. even though I know there are entries in the table with the
>category id listed as 1,3
>
>Any ideas?  I'm having "one of those days" :)
>
>Cheers,
>James.
>
>
>
>--
>PHP General Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

[PHP] SQL in array() ?

[James Holloway]

Hey guys,

I saw a post in here the other day that's prompted me to ask this
question...  Because I can't seem to get the solution mentioned to work.
Maybe I'm missing something obvious...  Anyway, here goes.

I have a list of categories contained in one table, and a list of entries in
another.  So

categories:
id name
1 category1
2 category2
3 category3

At the moment, the entries table has a field which contains a field
mentioning the category by id, so:

entries
id cat_id name
1 1 name1
2 1 name2
3 1 name3

The problem is this:  I'd like to give some entries more than one category.
I attempted to put the category id's into an array.  So a row in the revised
entries table now looks like this:

entries
id cat_id name
1 2 name1
2 1,3 name2
3 1 name3

And I'm trying to list from that table with code similar to this:



I get no errors when executing that code, but I don't get any results back,
either.. even though I know there are entries in the table with the
category id listed as 1,3

Any ideas?  I'm having "one of those days" :)

Cheers,
James.



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]