Page 1 of 1
AND and AND and AND again... :(
Posted: Thu Sep 04, 2003 6:12 pm
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?
Posted: Thu Sep 04, 2003 6:17 pm
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)
Oh...
Posted: Thu Sep 04, 2003 8:03 pm
by Caroline
Why didn't I think about that

?
As simple as a search command.
Thank
JAM, I will test it now. Hope that work!
Posted: Fri Sep 05, 2003 8:26 am
by JAM
Well, we all forget/miss something/learn from time to time.
Please let us know if it worked for future reference.
Posted: Fri Sep 05, 2003 9:27 am
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
Re: AND and AND and AND again... :(
Posted: Mon Sep 08, 2003 4:03 pm
by ericsodt
Or
("SELECT * FROM my_docs WHERE catid in ('101', '1011', '1012','10111', '10112', '10113', '10114') ORDER BY id DESC LIMIT 9");
Confused
Posted: Wed Sep 17, 2003 3:17 pm
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...
Re: Confused
Posted: Wed Sep 17, 2003 3:50 pm
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
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,
Re: Confused
Posted: Wed Sep 17, 2003 10:05 pm
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.