SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Huge Amounts Of Data From MysqlDB

    I thought I start in here. Since coming to this forum I have been re-looking on how I program in php and have changed a lot. But the one thing I still do since I started php is the db querying like this:

    PHP Code:
    $sql "SELECT * FROM table";
    $result mysql_query($sql);
    $num mysql_num_rows($result);

    if (
    $num 0) {

    $i 0;

    while (
    $i $num) {

    $var mysql_result($result,$i,'var');
    $var2 mysql_result($result,$i,'var2');

    ++
    $i;


    Now I posted this in the other forum but I thought I go into details here. I am creating a inventory tool and there are well over 1000 items in the tool. So when I do my query I also echo out the html format along with the variables like so:

    PHP Code:
    $sql "SELECT * FROM table";
    $result mysql_query($sql);
    $num mysql_num_rows($result);

    if (
    $num 0) {

    $i 0;

    while (
    $i $num) {

    $var mysql_result($result,$i,'var');
    $var2 mysql_result($result,$i,'var2');

    $showresults $showresults.'htmlcodehere' .$var .'morehtmlcode' .$var2 .'morecode';

    ++
    $i;

    return 
    $showresults;


    But if there is over 300-400 entries It takes forever to return everything, on top of that if it pushes past 400 to like 500-600 I get the 30 second script time out error. Is there a faster more effiecient way of doing this? Or is it just that taking that much out of a db at once will take that long and I should up the time out value of the script? Or should I get out of php and do the html stuff then jump back into php like so:

    PHP Code:
    $sql = "SELECT * FROM table";
    $result = mysql_query($sql);
    $num = mysql_num_rows($result);

    if ($num > 0) {

    $i = 0;

    while ($i < $num) {

    $var = mysql_result($result,$i,'var');
    $var2 = mysql_result($result,$i,'var2');

    $showresults = '

    ?>

    html here along with variables

    <?php

    ++$i;

    return 
    $showresults;

    }

    Thanks
    Silly

  2. #2
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql_result is the slowest posible way to fetch data from the DB + you are doing it twice, basically tou are fetching everything in the table twice for each row

    mysql_fetch_row (or .._array .._object) is much faster

    PHP Code:
    <?
    //only grab what you need !//
    $sql "SELECT var,var2 FROM table";
    $result mysql_query($sql);
    $num mysql_num_rows($result);

    //$r[0] will be var$r[1] var2 etc//
    while ($r=mysql_fetch_row($result)) {
      
    $showresults .= 'htmlcodehere' .$r[0] .'morehtmlcode' .$r[1] .'morecode';
    }

    //return $showresults;
    ?>
    but even after that fetching 1000 rows is a lot of data to transport, perhaps consider paging the results showing only a 100 or so at a time ?

  3. #3
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the info firepages! Another question on that note then. Say I want to return rows of info in html so I want to return the following:

    <tr>
    <td width="18%">$var</td>
    <td width="23%">$var</td>
    <td width="20%">$var</td>
    <td width="17%">$var</td>
    <td width="22%">$var</td>
    </tr>

    So if I want to return that above, not just one entry but as many as the query finds, should I escape out of php and do it in plain html or should I echo out the html along with the var like:

    PHP Code:
    echo '<tr>
        <td width="18%">' 
    .'$var .'</td>
        <
    td width="23%">' .'$var .'</td>
        <td width="20%">' 
    .'$var .'</td>
        <
    td width="17%">' .'$var .'</td>
        <td width="22%">' 
    .'$var .'</td>
      </
    tr>'; 
    Thanks
    Silly

  4. #4
    SitePoint Addict
    Join Date
    Aug 2002
    Location
    Ottawa, Ontario, Canada
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you make use of output buffering it can significantly speed up large pages being displayed...

    PHP Code:
    //run query
    //get results in whatever format...then
    ob_start();
    //do your foreach (or whatever) loop to echo
    //the results
    ob_end_flush(); //immediately dump the buffer to the client

    //an alternate is to keep the buffered output 
    //and end the buffer:
    //$output=ob_get_contents();
    //ob_end_clean();
    //now you can do other stuff if needed and then simply echo
    //the $output variable when you need to:
    //echo $output; 
    Cheers,
    Keith.

  5. #5
    Super Ninja Monkey Travis's Avatar
    Join Date
    Dec 2001
    Location
    Sioux City, Iowa
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could always cache the output and have it reset once every hour or so.
    Travis Watkins - Hyperactive Coder
    My Blog: Realist Anew
    Projects: Alacarte - Gnome Menu Editor

  6. #6
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow! I actually busted out with a stop watch, dunno if that is accurate. But the old way of programming bringing up 700 records took 56 seconds. (Rounded) Had to up the time out for php.

    Then using some new ways of programming, using mysql_fetch_row and ob_start as well as mysql_free_result, i clocked bringing up all 1,017 at 6 seconds. (Rounded). And my dev box is on a slow laptop. Dunno if all 3 contributed to it, but whatever it was it worked

    The Chaos


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •