> Fixed it. I first thought it was a database driver issue, so I made a
> version that can work with PDO as well as with MySQL, depending on
> what database connection u use in dbconnect.inc.php. I timed the
> results with microtime() before and after the function call and for a
> tree with 755 nodes to an extent of maximum 5 levels it took PDO about
> 0.73 seconds to echo all the SQL queries to the browser, whereas MySQL
> takes 1.22 seconds for the exact same operation
> (P.S.: I don't mean this thread to change into an off-topic PDO vs.
> MySQL bash. Just sayin'):
>
> Here's the code (you can make your own dbconnect.inc.php file ;) )
>
> <?php
>
> require("./dbconnect.inc.php");
>
> function rebuild_tree($parent_id, $left, $dbtype)
> {
> global $dbh; // this is my PDO handle
> global $dbh2; // this is my MySQL handle
> $right = $left+1;
> $sql = "SELECT id FROM keywords WHERE parent_id='" . $parent_id . "'
> AND approved = 1";
> //echo $sql;
> switch($dbtype)
> {
>
>
> case "MYSQL":
>
> $res = mysql_query($sql, $dbh2);
> while($row = mysql_fetch_assoc($res))
> {
> $right = rebuild_tree($row['id'], $right, $dbtype);
> }
>
> break;
>
>
> case "PDO":
>
> $stmt = $dbh -> prepare($sql);
> $stmt -> execute();
> while ($row = $stmt -> fetch(PDO::FETCH_ASSOC))
> {
> $right = rebuild_tree($row['id'], $right,$dbtype);
> }
>
> break;
>
> default:
>
> echo 'wtf?';
>
> break;
> }
>
>
> echo('UPDATE keywords SET lft='.$left.', rght='. $right.' WHERE id="'.
> $parent_id.'";' . "\n");
>
> return $right+1;
> }
>
> // call the function or it won't do anything :)
>
> rebuild_tree(0, 1, "PDO"); // replace PDO with MYSQL if that's the
> connection you use
>
> ?>
>
I just tried it with a test table and it worked for me. I made a few
changes, though.
First, you should create the prepared statement outside of the
function rather than recreating it each iteration. Else it defeats
much of the purpose of using a PS and is an unnecessary load on the
DB. Also, you can likewise pass the DB handle to the function.
Second, I'd remove the "approved = 1" condition. The goal is to set up
a proper tree structure and that should include all keywords
regardless of whether they've been approved. You can always still
include the condition when querying the DB in production.
Here's my MDB2 version:
require_once 'MDB2.php';
header('Content-type: text/plain');
$dsn = array(
'phptype' => 'mysql',
'hostspec' => 'localhost',
'database' => 'test',
'username' => USER,
'password' => PASS
);
$options = array(
'debug' => 2,
'result_buffering' => true
);
$mdb2 = @MDB2::connect($dsn, $options);
if (PEAR::isError($mdb2))
{
die ('Failed to connect. ' . $mdb2->getUserInfo());
}
@$mdb2->loadModule('Extended');
@$mdb2->setFetchMode (MDB2_FETCHMODE_ASSOC);
$sql = 'SELECT id, word FROM keywords WHERE parent_id= ?';
$type = array('parent_id' => 'integer');
$stmt = $mdb2->prepare($sql, $types, MDB2_PREPARE_RESULT);
if (PEAR::isError($stmt))
{
die($stmt->getUserInfo());
}
rebuild_tree($mdb2, $stmt, 0, 1);
function rebuild_tree($mdb2, $stmt, $parent_id, $left)
{
$result = $stmt->execute(array($parent_id));
$right = $left + 1;
while ($row = $result->fetchRow())
{
$right = rebuild_tree($mdb2, $stmt, $row['id'], $right);
}
echo "UPDATE categories SET lft = ${left}, rght = ${right} WHERE id =
${parent_id};\n";
return $right + 1;
}
With PDO, instead of this line:
$result = $stmt->execute(array($parent_id));
... you'd use:
$stmt->bindParam(1, $parent_id);
$stmt->execute();
> You can copy the browser output and paste it into the SQL tab of
> PHPMyAdmin. I find this method to be safer because it allows me to see
> the queries before they're executed (and possibly see empty fields or
> syntax errors before they're committed to the DB)
I often do the same, especially where I'm experimenting or things are
very complex. Once everything looks good, I save the output to an SQL
file for importing.
Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions.
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
No comments:
Post a Comment