php and mysql, setting up directory structure (and other stuff)

[supermanjnk]

right now the site i'm working on will be dynamically generated using php and mysql, My question is, this site will have several different sections, redwidgets, blue widgets, ect. Each section will be rather large, While I know that mysql can store a lot of records before taking a hit, my question is this. right now i have a script that is on one page, that depending on certain factors, determines which page you want, ie site.com/index.php?bluewidgets=1

okay on to the questions:

1. would it be better to the two sections in different databases, I am guessing know since mysql can handle a lot of records.

2. Will performance improve by having
site.com/bluewidgets/index.php?bluewidgets=1
and
site.com/redwidgets/index.php?redwidgets=1
instead of having site.com/index.php?bluewidgets=1

3. should I use a session to determine what the page is instead of using?bluewidget=1

4. Is there a max number of queries one shouldn't go over per page? I know there technically isn't but i'm talking performance wise.

5. here is a sample query of mine

$result = mysql_query(
"SELECT DISTINCT title
FROM $db
WHERE title = cat
AND enabled='1'"
)
or die
("SELECT Error: ".mysql_error());
while ($get_info = mysql_fetch_object($result)){
print "<h2>$get_info->title</h2>\n";

$result2 = mysql_query(
"SELECT DISTINCT title, article
FROM $db
WHERE cat='$get_info->title'
AND subcat1 = title
AND enabled='1'"
)
or die("SELECT Error: ".mysql_error());
while ($get_info2 = mysql_fetch_object($result2)){
print "<h2>$get_info2->title</h2>\n";
}
}

is there a better way to do this? or do is that about it? Maybe by only doing one query and replace title with * and remove everything except WHERE enabled='1'
or something... then using like $get_info->title and some kind of where statement like where $get_info->title == $get_info->cat

I think thats all my questions for now, I will add more later if I think of anything else. Thanks in advance

[Zipper]

1. not neccessarily. you can put both widgets in the same.

2. there's no big performance issues here. but the method you use will change the way you code your script. however logically, you don't need to use both bluewidgets & redwidgets in your query. instead use one call 'widgets' and give the color value. e.g.

site.com/bluewidgets/index.php?widgets=red
site.com/bluewidgets/index.php?widgets=blue

3. this is a simple query sting manipulation. so you can leave sessions out of it.

4. well, it really depends on the weight of each record. like if there are more fields for each widget, then you might improve the speed and efficiency of the script by limiting the number of records per page. frontends like phpmyadmin has defaults of 30 rpp. So feel to make up your own depending on the data you'll be using.

5. i'm not quite clear of what you're trying to accomplish here, as I have no info on the table you're using to populate ther records. but from what I see there is some confusion among the entities of your database and your loop structure.

in you're first query you're condition includes 'title = cat' which implies that all title's returned has the same value as in cat.

then in the second query you're again using both conditions as in the first (where $get_info->title already is the same as cat) under a loop which could repeat itself many times depending on the number of records you're dealing with.

will it serve your purpose if you use two direct queries in this form?

SELECT DISTINCT title FROM db WHERE title = cat AND enabled=1
SELECT DISTINCT title, article FROM db WHERE cat=title
AND subcat1 = title AND enabled=1

once again, I have no idea about you're table structure, so I might have got the wrong picture. but i can sense that you're table has a lot of repetitive data that you might need reduce to improve performance.