Left Quote    It is possible to store the mind with a million facts and still be entirely uneducated.
- Alec Bourne    
Right Quote
[login] | [Register]
Forum Index » PHP and mySQL » Viewing Topic and Replies
Post Reply

        Viewing Topic: Optimizing?
  This user is offline  David1159
  Subject: "Optimizing?" Posted: @ 2:30 am on Jun 07 2008   

Member #: 526
Rank: User - (81)
Since: 12/27/07
Posts: 81
From: usa

K, I have been making my own forum software for fun, http://www.5th-

I received a message from my webhost stating I reached 100,000 mysql hits today, taking and receiving data. This never happened before today either My actually bandwidth use was only 32mb today.

The only thing I can think of is my SELECT statements, putting limits on them, not using SELECT * rather define exactly what I need.

This is my index.php file, it houses the organization of Categories and Sections. Which all can be adjusted by the Administration Panel.


session_start() ;
if($_SESSION['l ogged_in'] == 1) {
include("i nclude/db.php&q uot;

<link rel="style sheet" title="sty le sheet" href="temp late/skin_1/sty le.css"> ;

include("t emplate/skin_1/ header.php" ;

echo '<center> <table width="100 %">'; echo '<BR>< table class="row 1" width="900 "><t r><td width="900 " align="cen ter">FO RUM</td>& lt;/tr></ table>';

$infogetlast = mysql_query(&qu ot;SELECT MAX(`category_i d`) FROM `CATEGORIES` ORDER BY category_id ";
if(!$infogetlas t) die(mysql_error ());
while($infogetl ast2 = mysql_fetch_arr ay($infogetlast )) {
$gotlastorder = $infogetlast2[0 ] + 1;
$order = 1;
while($order > 0 && $order < $gotlastorder) {

$category_info = mysql_query(&qu ot;SELECT * FROM `CATEGORIES` WHERE `category_id` = '".$order. "' ORDER BY category_id ";
if(!$category_i nfo) die(mysql_error ());
while($category _info2 = mysql_fetch_obj ect($category_i nfo)) {
$category_name = $category_info2 ->category_n ame;

echo '<BR>< table class="row 2" width="900 " ><tr>& lt;td class="row 2" width="450 " align="cen ter">' .$category_name . '</td>< ;td class="row 2" width="75& quot; align="cen ter">TO PICS</td> <td class="row 2" width="75& quot; align="cen ter">PO STS</td>& lt;td class="row 2" width="300 " align="cen ter">LA ST POSTER</td&g t;</tr>&l t;/table>';< br />
$inf = "SELECT * FROM `CATEGORIES` WHERE `category_id` = '".$order. "' AND `category_userl evel` <= '".$userle vel."' ORDER BY sect_id ASC ";
$info = mysql_query($in f);
if(!$info) die(mysql_error ());

$info_rows = mysql_num_rows( $info);

if($info_rows > 0) {

while($info2 = mysql_fetch_obj ect($info)) {

$infolast = mysql_query(&qu ot;SELECT * FROM `TOPICS` WHERE `sect_id` = '".$info2- >sect_id.&qu ot;' ORDER BY id ASC ";
if(!$infolast) die(mysql_error ());
$infototalposte r2 = mysql_num_rows( $infolast);
while($infolast 2 = mysql_fetch_obj ect($infolast)) {
$lastposter = $infolast2-> username;
$lastposter_tim e = $infolast2-> time;
$lastposter_dat e = $infolast2-> date;
$lastposter_top ic_id = $infolast2-> topic_id;
$lastposter_sec t_id = $infolast2-> sect_id;
$lastposter_top ic_subject = $infolast2-> topic_subject;< br /> }
$infolast3 = mysql_query(&qu ot;SELECT * FROM `TOPICS` WHERE `article_id` = '1' AND `sect_id` = '".$info2- >sect_id.&qu ot;' ORDER BY id ASC ";
if(!$infolast3) die(mysql_error ());
$total_topics = mysql_num_rows( $infolast3);

$info_user_id = mysql_query(&qu ot;SELECT `user_id` FROM `USERS` WHERE `username` = '".$lastpo ster."' ";
if(!$info_user_ id ) die(mysql_error ());
while($info_us er_id2 = mysql_fetch_obj ect($info_user_ id )) {
$profile_id = $info_user_id2- >user_id;
include("i nclude/users_lo oks.php";

echo '<table class="row 3" width="900 " ><tr>& lt;td class="row 3" width="450 ">';

echo '<a href="topi cs.php?si='.$in fo2->sect_id .'">' .$info2->sec tion_title. '</a>'; echo '<BR>'; echo $info2->sect ion_about;
echo '<BR>'; echo '</td>';< br /> echo '<td class="row 3" width="75& quot; align="cen ter">'. $total_topics.' </td>< td class="row 3" width="75& quot; align="cen ter">'. $infototalposte r2.'</td> ';
echo '<td class="row 3" width="300 " align="lef t">TOPI C: <a href="post s.php?si='.$las tposter_sect_id .'&to='.$la stposter_topic_ id.'">' .$lastposter_to pic_subject.'&l t;/a> <BR>BY:&l t;a href="user info.php?u='.$p rofile_id.'&quo t; >'.$output_u sername.'</a > <BR>DATE: '.date('h:i:s a', $lastposter_tim e).' on '.$lastposter_d ate.' </td>' ;
echo '</tr>< ;/table>';
} } //end forum while repeat

$order = $order + 1;
echo '</table> ';


echo '<BR>'; include("i nclude/user_onl ine.php";

} else {
header("Lo cation: login.php"< img src=http://www. orums/images/sm ilies/wink.gif border=0>;


Coding is simply CST... Combining $hit Together. We make different $hit to run in unison correctly.
    Viewed: 4,070 Times | Reply to This | To top
  This user is offline  bs0d
  Subject: "re: Optimizing?" Posted: @ 3:37 am on Jun 16 2008    

Member #: 1
Rank: Admin. - (1,505)
Since: 02/06/05
Posts: 600
From: USA

Hmm, yes that is odd. Make sure you don't have any infinite loops? Perhaps if only a few people were to access a page with this, it would flood the DB with requests. Perhaps you can check the logs and find the source quite easily.

With your question about SELECT * option, I don't think it would hurt to specify the columns you're selecting. Maybe if they're less than 10? I'm not sure if there's any rules of thumb here. It maybe ok to use SELECT * if your able has less than 5 columns or so. I'm not sure. You could test it. Create two test pages: one with the query one way, the other with the opposite. And include PHP code to detect load time and that should give you an idea of how much it affects your scripts.

Edited at 03:45:46 am on 06/16/08

-bs0d |

    Viewed: 4,030 Times | Reply to This | To top
  This user is offline  David1159
  Subject: "re: Optimizing?" Posted: @ 5:14 am on Jun 17 2008    

Member #: 526
Rank: User - (81)
Since: 12/27/07
Posts: 81
From: usa

I am 100% positive I have no infinite loops.

I went through all my coding, changed any Select* that I could. I think that was the problem. I looked at my webhits and bandwidth and both went down a ton, plus it loads faster.

Mysql topic table has about 12 columns and 150 rows (rows growing with more new topics/posts)
lol I did not realized the time it is going to take to finish the Admin Panel. Trying to make it customizable: Usergroups/rank s, areas, restricted access for ranks to certain areas, styles/skins, etc...

I am over my head in this, but god it so much fun. I always lose track of time.

Coding is simply CST... Combining $hit Together. We make different $hit to run in unison correctly.

    Viewed: 4,018 Times | Reply to This | To top
Viewing Page: 1 of 1

1 |

You must be logged in to post on the forums. Login or Register

"" Copyright © 2002-2018; All rights lefted, all lefts righted.
Privacy Policy  |  Internet Rank