We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Need help with PHP and MYSQL

Medium Priority
433 Views
Last Modified: 2013-12-12
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
Comment
Watch Question

Jagadishwor DulalSenior Web Developer
CERTIFIED EXPERT

Commented:
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.

Author

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.

Author

Commented:
I hope these images will help.

 mysqltable
 dailytable

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

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

Author

Commented:
and how do I then insert the output into the table?

Author

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

Commented:
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

Author

Commented:
it doesn't. It just says 0MB 0MB 0MB

Commented:
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

Author

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

Author

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


Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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.

Author

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

Author

Commented:
also is it possible to leave dates like: 04/21/2011 ?

Commented:
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

?

Author

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.

Author

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

Most Valuable Expert 2011
Author of the Year 2014

Commented:
Can you please break this down into separate questions?  It is easier to eat an elephant one bite at a time.  Thanks, ~Ray

Author

Commented:
Question with ID: 35444383 and below is the question that I need the answer for.
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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.
https://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";

Author

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.
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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

Author

Commented:
it's not exactly what I meant, Ray. Check the previous posts.
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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

Author

Commented:
Don't give up so easy. I will make a video to explain exactly what I want.

Author

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.

Author

Commented:
Everyone who was active in this question may want to look at the same question, just a bit modified here:

https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/Q_26974657.html
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.