I have a pretty simple mysql request, 1. request calls to table to get an id, if an id exists function ends, if no id, then the id is not banned and 2nd requests, see if the id exists in public table, if no it adds, if it does it counts up 1 for a page view.
Currently this function seems to be causing a serious slow down. af_ban table has about 8000 rows af_freefeed table has about 1,000,000 rows. About 10,000 rows added per day.
function pageview($pageid, $pagename){
$sql=mysql_num_rows(mysql_query("SELECT * FROM anotherfeedv3.af_ban WHERE pageid = '".$pageid."'"));
if(!$sql){
$sql=mysql_num_rows(mysql_query("SELECT pageid FROM anotherfeedv3.af_freefeed WHERE pageid = '".$pageid."'"));
if(!$sql){
$sql = 'INSERT INTO `anotherfeedv3`.`af_freefeed` (`id`, `pageid`, `userid`, `views`, `pagename`) VALUES (NULL, \''.$pageid.'\', \'\', \'1\', \''.htmlspecialchars($pagename, ENT_QUOTES).'\');';
$insert=mysql_query($sql) or die ('Error! in Insert '.$pageid.' SQL');
}else{
$sql = 'UPDATE `anotherfeedv3`.`af_freefeed` SET `pagename` = \''.htmlspecialchars($pagename, ENT_QUOTES).'\', views=views+1 WHERE `af_freefeed`.`pageid` = '.$pageid.';';
//$sql = 'UPDATE `anotherfeedv3`.`af_freefeed` SET views=views+1 WHERE `af_freefeed`.`pageid` = '.$pageid.';';
$update=mysql_query($sql) or die ('Failed update of '.$pageid.' in mysql.');
}
}
};
Is there a better way i can write this?
Solution!
function pageview($pageid, $pagename){
$q = sprintf('SELECT COUNT(*) AS numrows FROM anotherfeedv3.af_ban WHERE pageid = %d', $pageid);
$r = mysql_query($q);
$a = mysql_fetch_assoc($r);
if($a['numrows'] > 0){ /* ... */ }else {
$q = sprintf('SELECT COUNT(*) AS numrows FROM anotherfeedv3.af_freefeed WHERE pageid = %d', $pageid);
$r = mysql_query($q);
$a = mysql_fetch_assoc($r);
if($a['numrows'] > 0){ /* ... */
$sql = 'UPDATE `anotherfeedv3`.`af_freefeed` SET `pagename` = \''.htmlspecialchars($pagename, ENT_QUOTES).'\', views=views+1 WHERE `af_freefeed`.`pageid` = '.$pageid.';';
//$sql = 'UPDATE `anotherfeedv3`.`af_freefeed` SET views=views+1 WHERE `af_freefeed`.`pageid` = '.$pageid.';';
$update=mysql_query($sql) or die ('Failed update of '.$pageid.' in mysql.');
}else {
$sql = 'INSERT INTO `anotherfeedv3`.`af_freefeed` (`id`, `pageid`, `userid`, `views`, `pagename`) VALUES (NULL, \''.$pageid.'\', \'\', \'1\', \''.htmlspecialchars($pagename, ENT_QUOTES).'\');';
$insert=mysql_query($sql) or die ('Error! in Insert '.$pageid.' SQL');
}
}