How to simplify MySQL query...

[koldvette]

Hi,

First of all MySQL server version: 5.0.91-community-log

Main idea of the query: retrieve statistics of visitors hits of a website. The code is functioning.

 

Now, the question is: is there anyway to simplify (to reduce the lines of code for the query to become "lighter") the following? The idea is the get 8 different dates, including "today" and 7 days before, so we can then use these 8 different variables for displaying them on the web.

setlocale(LC_TIME, 'es_MX');
$date_format = "%d de %b.";
$date_current = date("Y-m-d");
$date_1 = date("Y-m-d", strtotime("-1 days"));
$show_date_1 = strftime("$date_format", strtotime("-1 days"));
$date_2 = date("Y-m-d", strtotime("-2 days"));
$show_date_2 = strftime("$date_format", strtotime("-2 days"));
$date_3 = date("Y-m-d", strtotime("-3 days"));
$show_date_3 = strftime("$date_format", strtotime("-3 days"));
$date_4 = date("Y-m-d", strtotime("-4 days"));
$show_date_4 = strftime("$date_format", strtotime("-4 days"));
$date_5 = date("Y-m-d", strtotime("-5 days"));
$show_date_5 = strftime("$date_format", strtotime("-5 days"));
$date_6 = date("Y-m-d", strtotime("-6 days"));
$show_date_6 = strftime("$date_format", strtotime("-6 days"));
$date_7 = date("Y-m-d", strtotime("-7 days"));
$show_date_7 = strftime("$date_format", strtotime("-7 days"));

As you can see, variable $show_date is for displaying the date, and variable $date_ will be used in the MySQL query.

 

And then use a table for diplating results obtained:

<table>
    <thead>   
        <tr>           
            <th scope="col"><a href="#"><?=$show_date_7;?></a></th>
            <th scope="col"><a href="#"><?=$show_date_6;?></a></th>
            <th scope="col"><a href="#"><?=$show_date_5;?></a></th>
            <th scope="col"><a href="#"><?=$show_date_4;?></a></th>
            <th scope="col"><a href="#"><?=$show_date_3;?></a></th>
            <th scope="col"><a href="#"><?=$show_date_2;?></a></th>
            <th scope="col"><a href="#"><?=$show_date_1;?></a></th>
            <th scope="col"><a href="#">Today</a></th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <th scope="row">Todos</th>
            
<?php
//all visitors
$query_today = mysql_query("SELECT date FROM table WHERE date='$date_current'");
$count_today = mysql_num_rows($query_today);
$query_1 = mysql_query("SELECT date FROM table WHERE date='$date_1'");
$count_1 = mysql_num_rows($query_1);
$query_2 = mysql_query("SELECT date FROM table WHERE date='$date_2'");
$count_2 = mysql_num_rows($query_2);
$query_3 = mysql_query("SELECT date FROM table WHERE date='$date_3'");
$count_3 = mysql_num_rows($query_3);
$query_4 = mysql_query("SELECT date FROM table WHERE date='$date_4'");
$count_4 = mysql_num_rows($query_4);
$query_5 = mysql_query("SELECT date FROM table WHERE date='$date_5'");
$count_5 = mysql_num_rows($query_5);
$query_6 = mysql_query("SELECT date FROM table WHERE date='$date_6'");
$count_6 = mysql_num_rows($query_6);
$query_7 = mysql_query("SELECT date FROM table WHERE date='$date_7'");
$count_7 = mysql_num_rows($query_7);
?>
            <td><?=$count_7;?></td>
            <td><?=$count_6;?></td>
            <td><?=$count_5;?></td>
            <td><?=$count_4;?></td>
            <td><?=$count_3;?></td>
            <td><?=$count_2;?></td>
            <td><?=$count_1;?></td>
            <td><?=$count_today;?></td>
        </tr>
        <tr>
            <th scope="row">Únicos</th>
<?php
$query_unique_today = mysql_query("SELECT DISTINCT ip FROM table WHERE date='$date_current'");
$unique_today = mysql_num_rows($query_unique_today);
$query_unique_1 = mysql_query("SELECT DISTINCT ip FROM table WHERE date='$date_1'");
$unique_1 = mysql_num_rows($query_unique_1);
$query_unique_2 = mysql_query("SELECT DISTINCT ip FROM table WHERE date='$date_2'");
$unique_2 = mysql_num_rows($query_unique_2);
$query_unique_3 = mysql_query("SELECT DISTINCT ip FROM table WHERE date='$date_3'");
$unique_3 = mysql_num_rows($query_unique_3);
$query_unique_4 = mysql_query("SELECT DISTINCT ip FROM table WHERE date='$date_4'");
$unique_4 = mysql_num_rows($query_unique_4);
$query_unique_5 = mysql_query("SELECT DISTINCT ip FROM table WHERE date='$date_5'");
$unique_5 = mysql_num_rows($query_unique_5);
$query_unique_6 = mysql_query("SELECT DISTINCT ip FROM table WHERE date='$date_6'");
$unique_6 = mysql_num_rows($query_unique_6);
$query_unique_7 = mysql_query("SELECT DISTINCT ip FROM table WHERE date='$date_7'");
$unique_7 = mysql_num_rows($query_unique_7);
?>            
            <td><?=$unique_7;?></td>
            <td><?=$unique_6;?></td>
            <td><?=$unique_5;?></td>
            <td><?=$unique_4;?></td>
            <td><?=$unique_3;?></td>
            <td><?=$unique_2;?></td>
            <td><?=$unique_1;?></td>
            <td><?=$unique_today;?></td>
        </tr>
	<tr>
		<th scope="row">Móvil</th>
<?php
$q_mobile_today = mysql_query("SELECT * FROM table WHERE date='$date_current' AND os!='Windows' AND os!='Apple' AND os!='unknown'");
$mobile_today = mysql_num_rows($q_mobile_today);
$q_mobile_1 = mysql_query("SELECT * FROM table WHERE date='$date_1' AND os!='Windows' AND os!='Apple' AND os!='unknown'");
$mobile_1 = mysql_num_rows($q_mobile_1);
$q_mobile_2 = mysql_query("SELECT * FROM table WHERE date='$date_2' AND os!='Windows' AND os!='Apple' AND os!='unknown'");
$mobile_2 = mysql_num_rows($q_mobile_2);
$q_mobile_3 = mysql_query("SELECT * FROM table WHERE date='$date_3' AND os!='Windows' AND os!='Apple' AND os!='unknown'");
$mobile_3 = mysql_num_rows($q_mobile_3);
$q_mobile_4 = mysql_query("SELECT * FROM table WHERE date='$date_4' AND os!='Windows' AND os!='Apple' AND os!='unknown'");
$mobile_4 = mysql_num_rows($q_mobile_4);
$q_mobile_5 = mysql_query("SELECT * FROM table WHERE date='$date_5' AND os!='Windows' AND os!='Apple' AND os!='unknown'");
$mobile_5 = mysql_num_rows($q_mobile_5);
$q_mobile_6 = mysql_query("SELECT * FROM table WHERE date='$date_6' AND os!='Windows' AND os!='Apple' AND os!='unknown'");
$mobile_6 = mysql_num_rows($q_mobile_6);
$q_mobile_7 = mysql_query("SELECT * FROM table WHERE date='$date_7' AND os!='Windows' AND os!='Apple' AND os!='unknown'");
$mobile_7 = mysql_num_rows($q_mobile_7);
?>
            <td><?=$mobile_7;?></td>
            <td><?=$mobile_6;?></td>
            <td><?=$mobile_5;?></td>
            <td><?=$mobile_4;?></td>
            <td><?=$mobile_3;?></td>
            <td><?=$mobile_2;?></td>
            <td><?=$mobile_1;?></td>
		<td><?=$mobile_today;?></td>
	</tr>
    </tbody>
</table>

[mikosiko]

sure it can be simplified...

 

all your SELECT's can be reduced to just 1 SELECT

 

play with this (not fully tested):

 

SELECT `date`, count(`date`) AS daytotal, count(DISTINCT(`ip`)) AS singleip

FROM `table`

WHERE `date` between date_sub(curdate(), INTERVAL 7 DAY) and curdate()

GROUP BY `date`;

 

after that should be only a matter of loop thought the resulset and display the information.

 

edited because I forgot the Group BY

 

 

[koldvette]

Thanks! Will try it out and let you know my results...