• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

Need help with PHP and MYSQL

Hi there, I'm using MYSQL database to store the data, now I need to get it out in a specific way and I'm stuck doing that.

What I already have is on the attached code snippet

This will display all the entries from the database related to the pcid.

Now I also need to display in the other tab Weekly and Monthly and Total

So let's say if I click on weekly tab php script should display in the table stats for every 7 days calculated together for selected pcid.

Hope it makes sense.

Database View is on the image dj.jpg
<?php   
        echo('<div id="Daily">');
        echo('<table class="tablebox">');
        echo('<thead class="table-header">');
        echo('<tr>');
            echo('<th><li class="icons_pack"><span class="pack _90"></span></li>Period</th>');  
            echo('<th><li class="icons_pack"><span class="pack _279"></span></li>Downloaded</th>');  
            echo('<th><li class="icons_pack"><span class="pack _278"></span></li>Uploaded</th>');  
            echo('<th class="tc"><li class="icons_pack"><span class="pack _281"></span></li>Total</th>');  
        echo('</tr>');  
        echo('</thead>');  
        echo('<tbody>'); 
 include('conn.php');
  $result = mysql_query("SELECT * FROM config")
or die(mysql_error());
 $number = "1";
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    
    if($number % 2) {
    echo '<tr class="odd">';
    } else {
    echo '<tr class="even">'; 
}
      echo "<td>";
    echo $row['period'];
        echo "</td><td>";
        $d =  $row['downloaded'];
        $d = ($d / 1024 / 1024);
    echo $d . (" MB");
        echo "</td><td>";
  
    $u = $row['uploaded'];
       $u = ($u / 1024 / 1024);  
        echo $u . (" MB");  
        echo "</td><td>";    
            $t = $row['totals']; 
       $t = ($t / 1024 / 1024);  
        echo $t . (" MB");  
    echo "</td></tr>";
    $number = $number + 1;    
}
echo('</tbody></table></div>');
?>
        </div>

Open in new window

db.jpg
0
altimofejevs
Asked:
altimofejevs
  • 19
  • 5
  • 4
  • +1
1 Solution
 
Jagadishwor DulalBraces MediaCommented:
OK altimofejevs you just need query which can count weekly uploaded and monthly uploaded. Here I assume that your table field period is upload date and my example query will be based on that :

Simply I think you want the records of last 7 days from today means 1 week, or you want records between today and last 30/31 days ( month).

For easy way you can use MySQL function CURDATE() to get the today's date and to get the difference in today date and previous day or month you have to use the MySQL function DATE_SUB

Now for week your query may be like:

SELECT count(pcid) FROM config WHERE period>=DATE_SUB(CURDATE(), INTERVAL 7 DAY)

Open in new window


The above query will return last 7 days records counting the pcid.

If you want to get specific pc id you man add more statement in your where condition like below:

SELECT count(pcid) FROM config WHERE period>=DATE_SUB(CURDATE(), INTERVAL 7 DAY) and pcid='[b]pcid which you want[/b]'

Open in new window


For a month:
Let us try to get records added in last one month

SELECT count(pcid) from config WHERE period >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

Open in new window


Hope you will get your result. All the best.
0
 
altimofejevsAuthor Commented:
I don't  know if mysql query is correct because I cannot figure out how to combine that with putting output into the table.

Check the code above that i've used to display daily statistics and the image provided in this reply. Table
Now when I press weekly it should in the period field state dates that he used let's say from today's date until 7 days earlier and summarize the upload and download between those days. then next entry would be from 7 days earlier to 7 more days earlier.

Thanks.
0
 
altimofejevsAuthor Commented:
I hope these images will help.

 mysqltable
 dailytable
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
altimofejevsAuthor Commented:
0
 
ropennerCommented:
just add the sums of the other columns to what is selected in the answers above

SELECT sum(downloaded), sum(uploaded), count(pcid)

0
 
altimofejevsAuthor Commented:
and how do I then insert the output into the table?
0
 
altimofejevsAuthor Commented:
Please correct what is wrong.

 
<?php   
        echo('<div id="Weekly">');
        echo('<table class="tablebox">');
        echo('<thead class="table-header">');
        echo('<tr>');
            echo('<th><li class="icons_pack"><span class="pack _90"></span></li>Period</th>');  
            echo('<th><li class="icons_pack"><span class="pack _279"></span></li>Downloaded</th>');  
            echo('<th><li class="icons_pack"><span class="pack _278"></span></li>Uploaded</th>');  
            echo('<th class="tc"><li class="icons_pack"><span class="pack _281"></span></li>Total</th>');  
        echo('</tr>');  
        echo('</thead>');  
        echo('<tbody>'); 
 include('conn.php');
  $pcid = $_GET['pcid'];        
  $result = mysql_query("SELECT sum(downloaded), sum(uploaded), sum(totals) FROM config WHERE period>=DATE_SUB(CURDATE(), INTERVAL 7 DAY) and pcid='$pcid'")
or die(mysql_error());
 $number = "1";
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    
    if($number % 2) {
    echo '<tr class="odd">';
    } else {
    echo '<tr class="even">'; 
}
      echo "<td>";
    //echo $row['period'];
    echo "";
        echo "</td><td>";
        $d =  $row['sum(downloaded)'];
        $d = ($d / 1024 / 1024);
    echo $d . (" MB");
        echo "</td><td>";
  
    $u = $row['sum(uploaded)'];
       $u = ($u / 1024 / 1024);  
        echo $u . (" MB");  
        echo "</td><td>";    
            $t = $row['sum(totals)']; 
       $t = ($t / 1024 / 1024);  
        echo $t . (" MB");  
    echo "</td></tr>";
    $number = $number + 1;    
}
echo('</tbody></table></div>');
?>

Open in new window

0
 
ropennerCommented:
I believe this will work
<?php   
echo('<div id="Weekly">');
echo('<table class="tablebox">');
echo('<thead class="table-header">');
echo('<tr>');
echo('<th><li class="icons_pack"><span class="pack _90"></span></li>Period</th>');  
echo('<th><li class="icons_pack"><span class="pack _279"></span></li>Downloaded</th>');  
echo('<th><li class="icons_pack"><span class="pack _278"></span></li>Uploaded</th>');  
echo('<th class="tc"><li class="icons_pack"><span class="pack _281"></span></li>Total</th>');  
echo('</tr>');  
echo('</thead>');  
echo('<tbody>'); 
include('conn.php');
$pcid = $_GET['pcid'];        
$result = mysql_query("SELECT sum(downloaded), sum(uploaded), sum(totals) FROM config WHERE period>=DATE_SUB(CURDATE(), INTERVAL 7 DAY) and pcid='$pcid'") or die(mysql_error());
$number = "1";
 
while($row = mysql_fetch_array( $result )) {
	// Print out the contents of each row into a table
	
	if($number % 2) {
		echo '<tr class="odd">';
	} else {
		echo '<tr class="even">'; 
	}
	echo "<td>";
	echo "";
	echo "</td><td>";
	
	$d =  $row[0];
	$d = ($d / 1024 / 1024);
	echo $d . (" MB");
	echo "</td><td>";
	
	$u = $row[1];
	$u = ($u / 1024 / 1024);  
	echo $u . (" MB");  
	echo "</td><td>";
	
	$t = $row[2]; 
	$t = ($t / 1024 / 1024);  
	echo $t . (" MB");  
	echo "</td></tr>";
	$number = $number + 1;    
}
echo('</tbody></table></div>');
?>

Open in new window

0
 
altimofejevsAuthor Commented:
it doesn't. It just says 0MB 0MB 0MB
0
 
ropennerCommented:
Works on my system so perhaps 'alex100'  is no longer being sent in.

Try these lines (replaces lines 14 and 15 above) to test whether the query is being created properly.  The query should print to the screen and look like:

SELECT sum(downloaded), sum(uploaded), sum(totals) FROM config WHERE period>=DATE_SUB(CURDATE(), INTERVAL 7 DAY) and pcid='alex100'


$pcid = "alex100"; 
$sql = "SELECT sum(downloaded), sum(uploaded), sum(totals) FROM config WHERE period>=DATE_SUB(CURDATE(), INTERVAL 7 DAY) and pcid='$pcid'";
print "$sql";
$result = mysql_query($sql) or die(mysql_error());

Open in new window

0
 
altimofejevsAuthor Commented:
Database is on my local computer and alex100 is there for sure, in fact config table looks exactly like shown on the image above "mysqltable.png".

print "$sql" returns this:

SELECT sum(downloaded), sum(uploaded), sum(totals) FROM config WHERE period>=DATE_SUB(CURDATE(), INTERVAL 7 DAY) and pcid='alex100'

Now I executed that query in phpmyadmin and this is what I get:

 mysql query
0
 
altimofejevsAuthor Commented:
SQL Dump:

 
-- phpMyAdmin SQL Dump
-- version 2.10.2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Apr 21, 2011 at 01:07 PM
-- Server version: 5.0.45
-- PHP Version: 5.2.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

-- 
-- Database: `netmeterdb`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `config`
-- 

CREATE TABLE `config` (
  `id` int(10) NOT NULL auto_increment,
  `pcid` varchar(32) NOT NULL,
  `period` varchar(32) NOT NULL,
  `downloaded` varchar(64) NOT NULL,
  `uploaded` varchar(64) NOT NULL,
  `totals` varchar(128) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

-- 
-- Dumping data for table `config`
-- 

INSERT INTO `config` VALUES (1, 'alex100', '4/19/2011', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (2, 'alex100', '4/18/2011', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (3, 'alex100', '4/17/2011', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (4, 'alex100', '4/16/2011', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (5, 'alex100', '4/15/2011', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (6, 'alex100', '4/14/2011', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (7, 'alex100', '4/13/2011', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (8, 'alex100', '4/12/2011', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (9, 'alex100', '4/11/2011', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (10, 'alex100', '4/10/2011', '1073741824', '1073741824', '2147483648');

Open in new window


0
 
ropennerCommented:
The dates don't work in Ubuntu 10.10 LINUX like you've done them but I see them in the table above ... oh now I see the problem.   We assumed you had a date field in your database.

  `period` date NOT NULL,

This solution only works if the 'period' field is of type DATE.  You have it set to varchar(32).

Make that change and change the date input format to that like below.


INSERT INTO `config` VALUES (1, 'alex100', '2011-04-19', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (2, 'alex100', '2011-04-18', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (3, 'alex100', '2011-04-17', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (4, 'alex100', '2011-04-16', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (5, 'alex100', '2011-04-15', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (6, 'alex100', '2011-04-14', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (7, 'alex100', '2011-04-13', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (8, 'alex100', '2011-04-12', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (9, 'alex100', '2011-04-11', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (10, 'alex100', '2011-04-10', '1073741824', '1073741824', '2147483648');

Open in new window

0
 
altimofejevsAuthor Commented:
that bit works but it doesn't display anything at the PERIOD field in the table and there should be some kind of loop that makes a new entry for every 7 days not just one entry for last 7 days.
0
 
altimofejevsAuthor Commented:
at the period field in the table it should display what dates the data was taken from so let's say
first entry would be like 2011-04-19 - 2011-04-13 then another one would be from 2011-04-12 to 2011-04-06
0
 
altimofejevsAuthor Commented:
also is it possible to leave dates like: 04/21/2011 ?
0
 
ropennerCommented:
You can import normally with type as VARCHAR and then do the function below to get it into the other format. came from http://forums.mysql.com/read.php?10,177848,178162#msg-178162

1. Changed the field definition for the date field to varchar
2. Imported normally
3. then run
update MYTABLENAME set MYDATEFIELD=concat(
substring(
substring_index(MYDATEFIELD,"/",-1),1,4
),"-",
substring_index(MYDATEFIELD,"/",1),"-",
substring_index(
substring_index(MYDATEFIELD,"/",2),"/",-1
)
)
4. Then I changed the field definition back to date

As for your weekly view.  Do you want it to always start from the current day so that if I do it today it will look like:

april 15- april 21, 2011
april 8- april 14, 2011

and if I do it tomorrow it will look like:

april 16 - april 22, 2011
april 9- april 15, 2011

?
0
 
altimofejevsAuthor Commented:
ok date can remain the same format as it is. What i'm trying to say is that in the table it appears the wrong way.

 Data in the database
 How the weekly table looks now

How it should look.
So the weekly script should find out witch date was the last and start counting from that.
until everything is counted.

When the last record is put lets say if there are not 7 days but 5 it should place those 5.
0
 
altimofejevsAuthor Commented:
I assume this code needs to be modified.:

 
<?php   
echo('<div id="Weekly">');
echo('<table class="tablebox">');
echo('<thead class="table-header">');
echo('<tr>');
echo('<th><li class="icons_pack"><span class="pack _90"></span></li>Period</th>');  
echo('<th><li class="icons_pack"><span class="pack _279"></span></li>Downloaded</th>');  
echo('<th><li class="icons_pack"><span class="pack _278"></span></li>Uploaded</th>');  
echo('<th class="tc"><li class="icons_pack"><span class="pack _281"></span></li>Total</th>');  
echo('</tr>');  
echo('</thead>');  
echo('<tbody>'); 
include('conn.php');
$pcid = $_GET['pcid'];
$sql = "SELECT sum(downloaded), sum(uploaded), sum(totals) FROM config WHERE period>=DATE_SUB(CURDATE(), INTERVAL 7 DAY) and pcid='$pcid'";
$result = mysql_query($sql) or die(mysql_error());
 
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    
    if($number % 2) {
        echo '<tr class="odd">';
    } else {
        echo '<tr class="even">'; 
    }
    echo "<td>";
    echo "PERIOD_DATES_GO_HERE";
    echo "</td><td>";
    
    $d =  $row[0];
    $d = ($d / 1024 / 1024);
    echo $d . (" MB");
    echo "</td><td>";
    
    $u = $row[1];
    $u = ($u / 1024 / 1024);  
    echo $u . (" MB");  
    echo "</td><td>";
    
    $t = $row[2]; 
    $t = ($t / 1024 / 1024);  
    echo $t . (" MB");  
    echo "</td></tr>";
    $number = $number + 1;    
}
echo('</tbody></table></div>');
?>

Open in new window

0
 
Ray PaseurCommented:
Can you please break this down into separate questions?  It is easier to eat an elephant one bite at a time.  Thanks, ~Ray
0
 
altimofejevsAuthor Commented:
Question with ID: 35444383 and below is the question that I need the answer for.
0
 
Ray PaseurCommented:
I still may not be understanding this, but I can help you with DATETIME fields.  First, please read this article to get the basics about how PHP and MySQL work together to handle DATETIME information.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

If you want to get the rows with a DATETIME value between, for example, last week and today, you might do something like this:

$today = date('c');
$week_ago = date('c', strtotime('-1 week'));
$query = "SELECT things FROM tables WHERE my_date_column BETWEEN '$week_ago' AND '$today' LIMIT 20";
0
 
altimofejevsAuthor Commented:
right. I wrote a program that is counting bandwidth. Every single day it puts into the database how much user has downloaded.

Now using that data I have to populate table. How much user downloaded daily. How much weekly and how much monthly. let's say if user uses program 3 months then basically there should be 12 entries in weekly field 3 entries in monthly field.
0
 
Ray PaseurCommented:
Well, you could do this a couple of different ways.  I think I would start by taking the simplest approach.  It would not be the most "computer-efficient" but it would be easy to get this correct.  Write separate queries for each time period you wanted to report.

As you do this, bear in mind that seconds, minutes, hours, days, and weeks are consistent.  Months and years are not, having different numbers of days.  You can probably make sense of these inconsistencies if you do things creatively with strtotime() and date().  Example - today is not yet the end of the month of April, but let's say you want to find information from this month and the two months before this month.  Those would be February, March and April.  Each has a different number of days.  So you might use something like the code snippet to get the boundary dates for your query.

http://www.laprbass.com/RAY_temp_altim.php
Outputs:
SELECT things FROM table WHERE my_date_column BETWEEN '2011-02-01' AND '2011-04-30'
<?php // RAY_temp_altim.php
error_reporting(E_ALL);
date_default_timezone_set('America/Chicago');

// MAN PAGE: http://php.net/manual/en/function.date.php
$last_of_this_month  = date('Y-m-t');
$first_of_this_month = date('Y-m-01');
$two_months_ago      = date('Y-m-01', strtotime($first_of_this_month . ' - 2 months'));

$query = "SELECT things FROM table WHERE my_date_column BETWEEN '$two_months_ago' AND '$last_of_this_month'";

echo $query;

Open in new window

0
 
altimofejevsAuthor Commented:
it's not exactly what I meant, Ray. Check the previous posts.
0
 
Ray PaseurCommented:
No, thanks.  I can't figure out what your question is.  I'll leave it to someone else.  Best of luck with your project, ~Ray
0
 
altimofejevsAuthor Commented:
Don't give up so easy. I will make a video to explain exactly what I want.
0
 
altimofejevsAuthor Commented:
Meanwhile this question was kind of answered by the guy above. So to be fair I will accept his solution and open separate question.
0
 
altimofejevsAuthor Commented:
Everyone who was active in this question may want to look at the same question, just a bit modified here:

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/Q_26974657.html
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 19
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now