Need little help

[NewGuy21]

I have been trying sometime to improve upon this code. I am using it to query a Database and grab the imagehits  and bandwith used on certain days. I have this code and it works but it takes forever and uses alot of CPU. I was wondering if anyone can help me improve on it.

 

I dont want someone to just do it though. I would like if your gonna post to help to explain and help me fix it.

 

Thank You and here is the code im talking about.

 

// get date ranges
$link = mysql_connect($db_server, $db_user, $db_password) or die("Could not connect");
mysql_select_db($db_name) or die("Could not select database");
$query = "SELECT substring(timestamp, 1,  as ts from imagehits order by timestamp desc";
    $result = mysql_query($query) or die("Query failed");
    while ($line = mysql_fetch_array($result)) {
	$dates[$line[ts]] = "";
    }
    mysql_free_result($result);

$date = $_GET['date'];

// get bandwidth
if ($date == "") {
	$date = date("Ymd");
}
if ($date == "all") {
	$report = "All Dates";
	$query1 = "select sum(kb) as ttl from imagehits";
	$query2 = "select count(*) as ct from imagehits";
} else {
	$report = substr($date, 4, 2) . "/" . substr($date, 6, 2) . "/" . substr($date, 0, 4);
	$start = $date . "000000";
	$end = $date . "235959";
	$query1 = "select sum(kb) as ttl from imagehits where timestamp >= $start and timestamp <= $end";
	$query2 = "select count(*) as ct from imagehits where timestamp >= $start and timestamp <= $end";
}
    $result = mysql_query($query1) or die("Query failed.");
$bandwidth = 0;
    while ($line = mysql_fetch_array($result)) {
	$bandwidth = $line[ttl];
    }
    mysql_free_result($result);

    $result = mysql_query($query2) or die("Query failed.");
$images = 0;
    while ($line = mysql_fetch_array($result)) {
	$images = $line[ct];
    }

mysql_close($link);

[per1os]

The issue is going to lie within the amount of queries you are running and how you are running them. I would look into SQL Optimization.

 

I would also suggest posting the structure of the tables using so people can actually see what the tables contain and the size, because the count(*) on a table that has an image held within it, will be the killer of your process. I suggest doing the COUNT on the id of that table.

[NewGuy21]

I dont know much about MySQL so... This is what im assuming your talking about when mean table structure

 

This is all the info on the table for imagehits and images

imagehits table

http://img2.freeimagehosting.net/image.php?75ac0fb869.png

 

images table

http://img2.freeimagehosting.net/image.php?26a8d1aaa2.png

 

If this is not what asking for please explain. As said im not very familiar with MYSQL. I know php but this is one of my first projects using backend database

[per1os]

Yep that is table structure.

 

<?php
// get date ranges
$link = mysql_connect($db_server, $db_user, $db_password) or die("Could not connect");
mysql_select_db($db_name) or die("Could not select database");
$query = "SELECT substring(timestamp, 1,  as ts from imagehits order by timestamp desc";
    $result = mysql_query($query) or die("Query failed");
    while ($line = mysql_fetch_array($result)) {
	$dates[$line[ts]] = "";
    }
    mysql_free_result($result);

$date = $_GET['date'];

// get bandwidth
if ($date == "") {
	$date = date("Ymd");
}
if ($date == "all") {
	$report = "All Dates";
	$query1 = "select sum(kb) as ttl from imagehits";
	$query2 = "select count(idx) as ct from imagehits";
} else {
	$report = substr($date, 4, 2) . "/" . substr($date, 6, 2) . "/" . substr($date, 0, 4);
	$start = $date . "000000";
	$end = $date . "235959";
	$query1 = "select sum(kb) as ttl from imagehits where timestamp >= $start and timestamp <= $end";
	$query2 = "select count(idx) as ct from imagehits where timestamp >= $start and timestamp <= $end";
}
    $result = mysql_query($query1) or die("Query failed.");
    $bandwidth = mysql_fetch_assoc($result);
    $bandwidth = $bandwidth['ttl'];
    mysql_free_result($result);

    $result = mysql_query($query2) or die("Query failed.");
    $images = mysql_fetch_assoc($result);
    $images = $images['ct'];

mysql_close($link);
?>

 

Try that.

 

Since you are doing a count and a sum, you do not need the while statement as it should always only return one line.

[NewGuy21]

Thank you very much I tryed that and seems to run smoother . But I think I understand about removing the whiles and why dont need them.. Like I said this is first time with backend database like this...

 

Also if could help me with one more thing would greatly appreciate it.

 

I used this later on the page to call from that code to display to make dropdown  to select the date.. Thats the whole purpose of code.. It was kinda working but now its not working at all

 

The forever and today and yesterday work fine when click them but the dropdown is broken.

 

I took the base of code from an old example I had and changed it a little.. But I dont think I did it right.But like said im still learning..

 

 

<br>
<hr>
<br>
<form name="stats" action="<?= $_SERVER['PHP_SELF'] ?>" method="post" style="margin-bottom: 0px;">
<div align="center"><a href="<?= $_SERVER['PHP_SELF'] ?>?date=<?= date("Ymd") ?>" class="submenu">Today</a> - <a href="<?= $_SERVER['PHP_SELF'] ?>?date=<?= date("Ymd", mktime(0, 0, 0, date("m"), date("d")-1,  date("Y"))) ?>" class="submenu">Yesterday</a> - Date: <select name="date" size="1"><option value="" selected></option><? foreach ($dates as $dt1 => $dt2) { ?><option value="<?= substr($dt1, 0, 4) . substr($dt1, 4, 2) . substr($dt1, 6, 2) ?>"><?= substr($dt1, 4, 2) . "/" . substr($dt1, 6, 2) .  "/" . substr($dt1, 0, 4) ?></option><? } ?></select> <input type="submit" name="go" value="go" style="font-size: smaller;"> - <a href="<?= $_SERVER['PHP_SELF'] ?>?date=all" class="submenu">Forever</a></div>
</form>