Please Help me speed up old script

[Darin]

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 

// 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);

?> )
<?
}

?>

[Cups]

are $host and $host23 (User, Password, DBName etc) always the same values?

[Darin]

are $host and $host23 (User, Password, DBName etc) always the same values?

Yes they are always the same value.

[Cups]

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 

// 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 />";

[Darin]

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!

[SpacePhoenix]

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

[aaarrrggh]

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?

[cpradio]

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 

// 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;

?> )
<?
}

?>