MySQL problem
I made a PHP script that uses mysql to store torrent information into a database.
The INSERT commands work, and returns that there were rows affected with no errors, but when a search is done (in the same script/thread using the same connection) only the last row inserted is returned. When I go to phpmyadmin, none of the data is saved. I've tried setting AUTOCOMMIT, and tried to commit each transaction, nothing works. what can cause this?
The script used to work before ipowerweb did a transition from vDeck 1.
here is the function that's used to store the information, the first set of statements work, but the commands in and after the for loop work but dont save permanately.
<?php
function store($search,$results,$minseed) {
$iFlag = false;
$tCacheModified = false;
$ttdate = time();
if (!empty($results)) {
mysql_query(\"START TRANSACTION;\");
mysql_query(\"DELETE FROM cache where hash = '{$search}';\");
mysql_query(\"DELETE FROM hash where hash = '{$search}';\");
mysql_query(\"INSERT INTO `hash` (`hash` , `time`) VALUES ('{$search}',{$ttdate});\");
mysql_query('ANALYZE TABLE `hash`;');
mysql_query(\"COMMIT;\");
}
for($i=0;$i<count($results);$i++){
if ($results[$i]['seeder'] >= $minseed) {
if (($results[$i]['link'] != \"\") && ($results[$i]['name'] != \"\") && ($results[$i]['id'] != '')) {
mysql_query(\"DELETE FROM `cache` WHERE id = '{$results[$i]['id']}';\");
$results[$i]['id'] = preg_replace('!\s+!', ' ', trim($results[$i]['id']));
$results[$i]['size'] = preg_replace('!\s+!', ' ', trim($results[$i]['size']));
$results[$i]['seeder'] = preg_replace('!\s+!', ' ', trim($results[$i]['seeder']));
$results[$i]['leecher'] = preg_replace('!\s+!', ' ', trim($results[$i]['leecher']));
$results[$i]['downloads'] = preg_replace('!\s+!', ' ', trim($results[$i]['downloads']));
$results[$i]['name'] = str_replace('_',' ',$results[$i]['name']);
$results[$i]['name'] = preg_replace('![\s|\']+!', ' ', trim($results[$i]['name']));
$results[$i]['name'] = hide_torrent_source($results[$i]['name']);
$results[$i]['size'] = $results[$i]['size'];
$results[$i]['link'] = $results[$i]['link'];
$ttdate = time();
mysql_query(\"START TRANSACTION;\");
$saveQuery = \"INSERT INTO `cache` (`id` , `name` , `size` , `seed` , `leech` , `category` , `subcategory` , `link` , `hash` , `datestamp` ) VALUES ('{$results[$i]['id']}','{$results[$i]['name']}','{$results[$i]['size']}','{$results[$i]['seeder']}','{$results[$i]['leecher']}','{$results[$i]['category']}','{$results[$i]['subcategory']}','{$results[$i]['link']}','{$search}',{$ttdate});\";
if (mysql_query($saveQuery)) {
mysql_query(\"COMMIT;\");
$tCacheModified = true;
}
echo '<!--'.$saveQuery.'-->';
echo '<!--'.mysql_error().'-->';
}
}
}
if ($tCacheModified === true) {
mysql_query('ANALYZE TABLE `cache`;');
}
}
?>
pr0gr4mm3r posted this at 14:55 — 12th December 2007.
He has: 1,502 posts
Joined: Sep 2006
I've never used the START TRANSACTION; and COMMIT; commands before. Have you tried it without them?
Patrick A Baer posted this at 16:57 — 12th December 2007.
He has: 32 posts
Joined: Mar 1999
Have you tried to print the INSERT-command and checked what it actually does?
adjroth posted this at 19:25 — 12th December 2007.
He has: 33 posts
Joined: Jun 2006
I added START TRANSACTION; and COMMIT; to try to get it to work, doesn't work without them either.
I did make it print each transaction when i ran the script, along with mysql_error(), and mysql_affected_rows(). Each line printed like it should, and after each INSERT statement, it showed no errors, with 1 row affected. Each time, they weren't permanently saved and a select statement would only select the last row that was done.
like i said, it just stopped working after an upgrade was done to the servers.
JeevesBond posted this at 13:14 — 13th December 2007.
He has: 3,956 posts
Joined: Jun 2002
Probably time to contact your hosts then. You could also try to repair the databases and rebuild indexes, have you got SSH access? I usually run something like:
mysqlcheck -cep -u<username> --auto-repair <database_name>mysqlcheck -op -u<username> --auto-repair <database_name>
This has rescued me in the past. A quick scan of your code doesn't reveal any obvious errors, particularly since it was working before the hosts did an upgrade.
a Padded Cell our articles site!
adjroth posted this at 05:17 — 17th December 2007.
He has: 33 posts
Joined: Jun 2006
I've been able to figure it out, there wasn't a problem with the script or database at all, there was a very short delay in saving all the data to the database. I fixed it by adding a 500ms pause in the script.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.