Anyone with good knowledge on ADOdb, looking to optimize this code for ADOdb

Pulsorock

Hello,

I just found about ADOdb when searching for a way to cache my database content when doing queries for better performance. Currently I use the normal php/mysql connection and I noticed that on pages that I have lot of queries, it takes a few seconds to generate the page and its being heavy on the server load. So I think that ADOdb query cache method would help me on this.

Is there a way I can optimize my connections/queries with mysql initially without the need to change the entire website code. Currently I have a function that executes every query and sets an array of the db values to the variable assigned. I'll post an example code of the way I currently execute queries on my page and to see if there's a way to use ADOdb with it.

Here is the code I currently use:

<?
// Start file called via include file conn.inc.php
$hostname = "localhost";
$database = "mydb";
$username = "root";
$password = "passwd";
$conn = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database, $conn);

function run_query($sql) {
	$rows = array();
		$tmp = mysql_query($sql) or die(mysql_error());
		while($row = mysql_fetch_assoc($tmp)) $rows[] = $row;
		$num = mysql_num_rows($tmp);
	return $rows;
}
// End file called via include file conn.inc.php
?>

<?
// Define and run queries via the run_query() function
$sql1 = "SELECT * FROM table_name WHERE (tcpr_type = '2' AND catid = '13') ORDER BY created DESC LIMIT 10";
$row1 = run_query($sql1);

$sql2 = "SELECT * FROM table_name WHERE (tcpr_type = '1' AND catid = '14') ORDER BY created DESC LIMIT 15";
$row2 = run_query($sql2);

$sql3 = "SELECT * FROM table_name WHERE (tcpr_type = '2' AND catid = '15') ORDER BY created DESC LIMIT 20";
$row3 = run_query($sql1);

// And so on with similar queries....
?>


<?
// Methods of calling the data:

// Loop the records with a foreach
$count = 0;
	foreach($row1 AS $row1) //main while loop
	{

echo $row1['title'] . "<br>";

$count++;
	} //end of while
?>


<?
// Or call a specific row directly in the code
echo $row2[0]['title'] . "<br>";
echo $row2[1]['title'] . "<br>";
echo $row2[2]['title'] . "<br>";
echo $row2[3]['title'] . "<br>";
echo $row2[4]['title'] . "<br>";
echo $row2[5]['title'] . "<br>";
echo $row2[6]['title'] . "<br>";
?>

So its basically simple. There's a include file that has the connection variables and a function that when called and passed the queries it execute its and set the array of the returned records to it. The all I need to do is call the variable array in the page. Either a direct row of a loop of them via a foreach.

I'm looking a way to optimize this with ADOdb, preferable without the need to change my entire page code, maybe just the way the connection is declared and the way the function works. This way I just need to make the changes in just one page, and continue to use the way I call the records on the page, but using ADOdb for better performance.

Suggestions welcomed... thanks

Joseph07

Have you taken a look at this before?
If you have, it should be pretty easy for you to use ADOdb.

Pulsorock

Hi Joseph, I looked at their manual, but still I'm very new to ADOdb to found this.

How about try this other thing, I need to create a recordset and be able to display a specific row of that recordset array where I want.

For example:

include_once('adodb.inc.php'); 
$ADODB_CACHE_DIR = '/var/www/cache'; 
$conn = &ADONewConnection('mysql'); 
$conn->PConnect($hostname,$username,$password,$database); 
$rs = $conn->CacheExecute(60, "SELECT * FROM table WHERE (tcpr_type = '1') ORDER BY created DESC LIMIT 20");

foreach ($rs as $row) {
echo $row['title'] . "<br \>";
}

This code will show the 20 titles of the query I have. One in each row using a foreach. But lets say that I just want, for whatever reason, just display the title of the row 3 of that recordset. But I don't want to do another mysql query to display just that record. I want to use the array of that recordset to display the row I want. How can I do this?

Thanks,

Pulsorock

Hello,

I reply the post to add more information to see if it helps.

I found about ADOdb last week when searching for a way to cache my db queries results and this way lower my server load. I first found about the PHP Class class_db.php by Troy Wolf (http://www.troywolf.com/articles/php/class_db/). There he mentions that his class works great and all that, but that he is using now ADOdb that is more popular and active.

His class has what I'm trying to do, that is to grab a specific column within a specific row. He's example is: $data[4]['model']; (see above link for details)

I've been trying to do this in ADOdb but haven't found a way.

I've could use his class, but since he mentions that there will not be any more updates or features, I would prefer using ADOdb.