AND and AND and AND again... :(

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

AND and AND and AND again... :(

Post by Caroline »

I have to code a magazine site, and the following is what has been done

The table structure

Code: Select all

CREATE TABLE my_docs (
id int auto_increment,
title varchar (150) not null,
catid int not null,
intro text not null,
doc longtext not null,
primary key (id),
unique id (id)
);
Think that in the CATID column we have

101 is computer science

1011 is programming
1012 is operating system

10111 is ASP
10112 is JavaServlet
10113 is Perl
10114 is PHP

102 is music

1021 is world wide music
1022 is nation music

and so on...

When certain user click on computer science link, we have to show them articles in computer science section and thus we do the following

Code: Select all

<?php
$computer_science = mysql_query ("SELECT * FROM my_docs WHERE catid=101 AND catid=1011 AND catid=1012 AND catid=10111 AND catid=10112 AND catid=10113 AND catid=10114 ORDER BY id DESC LIMIT 9");
while ($cdata = mysql_fetch_array($computer_science)) {
	echo "
	<p><b>$cdata[title]</b>
	<p>$cdata[intro]
	<p>$cdata[doc]
	";
}
?>
I know the above SQL statement with lots of AND is surely stupid cause it we expand our programming category to JavaScript, Python, Tcl, C, Visual Basic... and operating system to Redhat Linux, Solaris, Windows2000, Windows XP... we will have a long long long SQL.
But poor me, I dunno how to refine it.

Any solution for that?
Can PHP looks into the CATID column, searching for only three begining digits then putting it into the SQL statement so that we don't have to AND and AND and AND too many times?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

If I understood correctly, use LIKE...

Code: Select all

$computer_science = mysql_query ("SELECT * FROM my_docs WHERE catid LIKE '101%' ORDER BY id DESC LIMIT 9");
% is anything (examples %something%, %something, something%, some%thing, etc. etc.). You know that the comp. sience cat starts with 101, so the above should work.

And just to let you know, the opposite would be

Code: Select all

$computer_science = mysql_query ("SELECT * FROM my_docs WHERE catid NOT LIKE '101%' ORDER BY id DESC LIMIT 9");
(thought it might come in handy later one day)
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

Oh...

Post by Caroline »

Why didn't I think about that :roll: ?

As simple as a search command.

Thank JAM, I will test it now. Hope that work!
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Well, we all forget/miss something/learn from time to time.
Please let us know if it worked for future reference.
hedge
Forum Contributor
Posts: 234
Joined: Fri Aug 30, 2002 10:19 am
Location: Calgary, AB, Canada

Post by hedge »

That design of having catid contain multiple pieces of info is a bad design IMHO.

That may seem convenient now but when you have thousands of rows in the table it will be a problem. The 'like' will not use an index so you won't be able to add them later to help performance.

I would strongly recommend splitting that column into other columns, you really should only put one piece of data in a column.

BTW, the code you had originally would have worked but you didn't want an AND between sets you needed to use OR between them
ericsodt
Forum Commoner
Posts: 51
Joined: Fri Aug 22, 2003 12:12 pm
Location: VA

Re: AND and AND and AND again... :(

Post by ericsodt »

Or

("SELECT * FROM my_docs WHERE catid in ('101', '1011', '1012','10111', '10112', '10113', '10114') ORDER BY id DESC LIMIT 9");
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

Confused

Post by Caroline »

JAM: The query works well. Thanks a million for that!

hedge: Your post scared me...

The catid column only contain digits and they are not exceed 10 chars. In this case, it smaller than other columns. So why did it be a bad design?

I think AND should be used in this case, not OR as the real table structure is here

Code: Select all

create table my_docs (
id int auto_increment,
title varchar (150) not null,
spot tinyint not null,
catid int not null,
headline text not null,
level tinyint not null default '0',
udate varchar (15) not null,
author varchar (25) not null,
displayname varchar (55) not null,
intro text not null,
doc longtext not null,
view int not null default '0',
comment int not null default '0',
rate int not null default '0',
primary key (id),
unique id (id)
);
and I would use WHERE catid like '101%' (as JAM has told me to) AND level = 5 (If not 5, it won't be displayed but waiting for 4 level approvals) AND spot !=1 (If 1, it will be displayed in INDEX page)

But... splitting table...
For what, and how?

ericsodt: Thanks, but it is still a long query. Think of 1013, 1014, 1015... with their following subcats as 10121, 10122...
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Re: Confused

Post by xisle »

Caroline wrote: hedge: Your post scared me...

The catid column only contain digits and they are not exceed 10 chars. in this case, it smaller than other column. So why did it be a bad design?
Its just input, no need to be scared :lol:
The 'category' is 101 and the 'subcategory' is 10111, both values are being stored in the same column. It would be better to store them individually (split the column) as hedge recommended so later on you can...

Code: Select all

"SELECT catid, subcategory_id FROM my_docs WHERE catid=101
ORDER BY subcategory_id"
for example to grab all computer science subcategories and avoid the 'LIKE' and utilize the index on the column.

-hope this helps,
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Re: Confused

Post by McGruff »

Caroline wrote: But... splitting table...
For what, and how?
See http://www.oreilly.de/catalog/javadtabp ... r/ch02.pdf for an excellent tutorial on relational db design.

You should create separate tables for categories (eg computer science) and subcategories (OS, etc).

Ideally you should have auto-incrementing, INT-type "id" cols set as primary keys for all tables - use these to link tables.

With a properly normalised db, categories could be a lookup table for subcategories ie a one-to-many relationship with categories at the "one" end.

You could make that a many-to-many relationship with yet another new table: a join table. Depending on your setup, an item might have more than one category or subcategory.

I'm not sure if you should create category AND subcategory fields in my_docs as suggested above. This states there is a link between the two columns in a table which doesn't know anything about it. What happens if you edit a subcategory, putting it under a new category?

You'll need to work out a bit with JOIN queries to pull it all together. Get the mysql manual from mysql.com if you haven't already.
Post Reply