SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Sep 2001
    Location
    Vancouver
    Posts
    809
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Please Help me speed up old script

    Hello,

    I have a page on my website that takes too long to load because of the outdated script I'm using to pull the results from my database.

    This same code below is copied 22 times over on the same page so that it pulls the results. Is there any way to speed up this process?

    The page is located at www.vancouverprofile.com/business/index.php

    PHP Code:
    <?php 

    // Set the variables for the database access:

    $Host "";
    $User "";
    $Password "";
    $DBName "";
    $Link mysql_connect ($Host$User$Password);

    $Query "SELECT id,name,ogtableid,catid FROM category WHERE ogtableid = '1' ORDER by name";
    $Result mysql_db_query ($DBName$Query$Link);

    // Fetch the results from the database.

    while($Row mysql_fetch_array($Result)){

    $catid "$Row[catid]";

    print (
    " <a href=\"http://www.vancouverprofile.com/business/agricultural/category.php/catid/$Row[catid]\" class=\"link\">$Row[name]</a>");
    ?>
    <?php 

    // Set the variables for the database access:

    $Host23 "";
    $User23 "";
    $Password23 "";
    $DBName23 "";
    $Link23 mysql_connect ($Host23$User23$Password23);

    $Query23 "SELECT count(id) FROM profiles WHERE productcodes1='$catid' OR productcodes2='$catid' OR productcodes3='$catid' OR productcodes4='$catid' OR productcodes5='$catid' OR productcodes6='$catid' OR productcodes7='$catid' OR productcodes8='$catid' OR productcodes9='$catid' OR productcodes10='$catid' OR productcodes11='$catid' OR productcodes12='$catid' OR productcodes13='$catid' OR productcodes14='$catid' OR productcodes15='$catid'";
    $Result23 mysql_db_query ($DBName23$Query23$Link23);
    $num_rows23 mysql_result($Result23,0,"count(id)");

    // Fetch the results from the database.

    echo "$num_rows23";

    mysql_close ($Link23);

    ?> )
    <?
    }

    ?>

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    are $host and $host23 (User, Password, DBName etc) always the same values?

  3. #3
    SitePoint Guru
    Join Date
    Sep 2001
    Location
    Vancouver
    Posts
    809
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    are $host and $host23 (User, Password, DBName etc) always the same values?
    Yes they are always the same value.

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    This is how I rewrote your script, imagining there being 22 missing calls to the db, but you should get the idea.

    I have not tested this code, and there may well be some syntax errors as I just typed it out.

    PHP Code:
    <?php 

    // Set the variables for the database access, you only need to do this once
    // if the same db holds all the info your script needs
    // you just change the SQL query

    $Host "";
    $User "";
    $Password "";
    $DBName "";
    $Link mysql_connect ($Host$User$Password);

    // give your query a name which represents what it is doing
    // I am guessing this is getting categories?

    $catsQuery "SELECT id,name,ogtableid,catid FROM category WHERE ogtableid = '1' ORDER by name";

    // give your results set a name which then is representative of 
    // what it contains, in this case I am guessing a load of categories

    $categories mysql_db_query ($DBName$catsQuery$Link);

    // Fetch the results from the database.

    while($cat mysql_fetch_array($categories)){

    $catid $cat['catid'];  // NB you quote the key not the entire array ...

    print (" <a href=\"http://www.vancouverprofile.com/business/agricultural/category.php/catid/" $cat['catid] ."\" class=\"link\">" . $cat['name'] . "</a>");
    ?>
    ( <?php 


    // again make the variable names be descriptive of their contents

    $profileCountQuery = "SELECT count(id) FROM profiles WHERE productcodes1='
    $catid' OR productcodes2='$catid' OR productcodes3='$catid' OR productcodes4='$catid' OR productcodes5='$catid' OR productcodes6='$catid' OR productcodes7='$catid' OR productcodes8='$catid' OR productcodes9='$catid' OR productcodes10='$catid' OR productcodes11='$catid' OR productcodes12='$catid' OR productcodes13='$catid' OR productcodes14='$catid' OR productcodes15='$catid'";

    // you already connected to the dbase so just re-use the connection you made at the start of the script

    $profileCounts = mysql_db_query ($DBName, $Query, $Link);
    $counts = mysql_result($profileCounts,0,"count(id)");

    // Fetch the results from the database.

    echo $counts;

    ?> )
    <?
    }

    ?>
    note:

    You only need to connect to the db once
    You do not need to close the db connection, this happens when the script dies
    $Query23 is a meaningless variable name, it does not help you understand what it contains, you may want to reuse that variable further down the script

    To access an array variable you do

    echo $row['id'];

    NOT

    echo "$row[id]";

    To access an array variable inside a string you concatenate the string using the dot character.

    echo "Your id is " . $row['id'] . ", thank you.<br />";

  5. #5
    SitePoint Guru
    Join Date
    Sep 2001
    Location
    Vancouver
    Posts
    809
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help! I tried the code and I'm getting the following error.

    http://www.vancouverprofile.com/business/index2.php

    Warning: mysql_result() expects parameter 1 to be resource, boolean given in /home/vancou/public_html/business/index2.php on line 165
    )

    Line 165 is this one:

    $counts = mysql_result($profileCounts,0,"count(id)");

    Any ideas on how to fix that? Thanks for your help again!

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,910
    Mentioned
    96 Post(s)
    Tagged
    0 Thread(s)
    What is the table structure for the profile and category tables?

    Looking at the page in the link is the goal of the script (including the other database calls) to get the main categories and the sub categories for each main category along with the number of entries in the profile table for each sub category?

    Also you should migrate over from the mysql_* extension over to the mysqli_* extension as the mysql_* extension has been depreciated as of php version 5.5
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  7. #7
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Pretty sure you've posted this before.

    One question - why are you copying the code 22 times? That's a sign of extremely poor design.

    You should actually never repeat code - if it appeared even twice I'd say it would point to a design problem. Why can this code not appear just once on your site?

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,827
    Mentioned
    142 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Darin View Post
    Thanks for your help! I tried the code and I'm getting the following error.

    http://www.vancouverprofile.com/business/index2.php

    Warning: mysql_result() expects parameter 1 to be resource, boolean given in /home/vancou/public_html/business/index2.php on line 165
    )

    Line 165 is this one:

    $counts = mysql_result($profileCounts,0,"count(id)");

    Any ideas on how to fix that? Thanks for your help again!
    Fixed the typo (missing ')
    PHP Code:
    <?php 

    // Set the variables for the database access, you only need to do this once
    // if the same db holds all the info your script needs
    // you just change the SQL query

    $Host "";
    $User "";
    $Password "";
    $DBName "";
    $Link mysql_connect ($Host$User$Password);

    // give your query a name which represents what it is doing
    // I am guessing this is getting categories?

    $catsQuery "SELECT id,name,ogtableid,catid FROM category WHERE ogtableid = '1' ORDER by name";

    // give your results set a name which then is representative of 
    // what it contains, in this case I am guessing a load of categories

    $categories mysql_db_query ($DBName$catsQuery$Link);

    // Fetch the results from the database.

    while($cat mysql_fetch_array($categories)){

    $catid $cat['catid'];  // NB you quote the key not the entire array ...

    print (" <a href=\"http://www.vancouverprofile.com/business/agricultural/category.php/catid/" $cat['catid'] ."\" class=\"link\">" $cat['name'] . "</a>");
    ?>
    <?php 


    // again make the variable names be descriptive of their contents

    $profileCountQuery "SELECT count(id) FROM profiles WHERE productcodes1='$catid' OR productcodes2='$catid' OR productcodes3='$catid' OR productcodes4='$catid' OR productcodes5='$catid' OR productcodes6='$catid' OR productcodes7='$catid' OR productcodes8='$catid' OR productcodes9='$catid' OR productcodes10='$catid' OR productcodes11='$catid' OR productcodes12='$catid' OR productcodes13='$catid' OR productcodes14='$catid' OR productcodes15='$catid'";

    // you already connected to the dbase so just re-use the connection you made at the start of the script

    $profileCounts mysql_db_query ($DBName$Query$Link);
    $counts mysql_result($profileCounts,0,"count(id)");

    // Fetch the results from the database.

    echo $counts;

    ?> )
    <?
    }

    ?>
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes


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
  •