altimofejevs
asked on
PHP & MYSQL HELP NEEDED
Hi there, I need help with PHP & MYSQL.
For more details please watch the video that I made to explain what exactly I need because people had problems with understanding what needs to be done.
Video can be found here: http://www.e3mc.net/eevideo/
PHP Script that I used to display Daily usage:
For more details please watch the video that I made to explain what exactly I need because people had problems with understanding what needs to be done.
Video can be found here: http://www.e3mc.net/eevideo/
PHP Script that I used to display Daily usage:
<?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');
$pcid = $_GET['go'];
$result = mysql_query("SELECT * FROM config where 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 "</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>');
?>
Sidebar note... You might want to consider getting this book and working through the exercises. I won't make you a pro, but it is very readable with great examples.
http://www.sitepoint.com/books/phpmysql4/
Here is an article about DATETIME processing in PHP and MySQL. It may help to understand how you can manipulate the dates in the data base.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
http://www.sitepoint.com/books/phpmysql4/
Here is an article about DATETIME processing in PHP and MySQL. It may help to understand how you can manipulate the dates in the data base.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
Here is one thing you might want to try to get all of the entries for today:
$today = date('Y-m-d'); // COULD ALSO BE date('c');
$query = "SELECT things FROM myTable WHERE period = '$today' ";
This would probably get you all of the things from last week:
$today = date('Y-m-d'); // COULD ALSO BE date('c');
$start = date('Y-m-d', strtotime($today . ' - 7 days'));
$query = "SELECT things FROM myTable WHERE period BETWEEN '$start' AND '$today' ";
The question of a month or a year is a little different. See the article notes on monthly ambiguity. Since the number of days is not the same from one month or one year to the next, you may need some extra programming to account for that. Or you might just limit the queries to weekly periods.
$today = date('Y-m-d'); // COULD ALSO BE date('c');
$query = "SELECT things FROM myTable WHERE period = '$today' ";
This would probably get you all of the things from last week:
$today = date('Y-m-d'); // COULD ALSO BE date('c');
$start = date('Y-m-d', strtotime($today . ' - 7 days'));
$query = "SELECT things FROM myTable WHERE period BETWEEN '$start' AND '$today' ";
The question of a month or a year is a little different. See the article notes on monthly ambiguity. Since the number of days is not the same from one month or one year to the next, you may need some extra programming to account for that. Or you might just limit the queries to weekly periods.
ASKER
MYSQL DATA:
"I want to find the most recent entry in my table. Using that date as an anchor point, I want to find all the entries that are within 7 days (1 week) prior to that entry, using the period column as the date information. When I have retrieved a record set I want to "Count all the data within those 7 days - downloaded, uploaded, totals." And I need it to be a loop. So when he does find those 7 days he has to count all the data and display one entry in the table then after that i need the script to look if there are another 7 days to count like lets say if he already counted from 01/01/2000 to 07/01/2000 then he should look if there is a data from 08/01/2000 if yes and it goes until 15/01/2000 then there are 7 days to count if not then he should display as many days left as the last entry.
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
CREATE TABLE `config` (
`id` int(10) NOT NULL auto_increment,
`pcid` varchar(32) NOT NULL,
`period` date 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=19 ;
INSERT INTO `config` VALUES (1, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-19', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (2, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-18', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (3, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-17', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (4, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-16', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (5, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-15', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (6, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-14', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (7, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-13', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (8, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-12', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (9, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-11', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (10, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-10', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (11, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-09', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (12, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-08', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (13, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-07', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (14, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-06', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (15, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-05', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (16, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-04', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (17, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-03', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (18, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-02', '1073741824', '1073741824', '2147483648');"I want to find the most recent entry in my table. Using that date as an anchor point, I want to find all the entries that are within 7 days (1 week) prior to that entry, using the period column as the date information. When I have retrieved a record set I want to "Count all the data within those 7 days - downloaded, uploaded, totals." And I need it to be a loop. So when he does find those 7 days he has to count all the data and display one entry in the table then after that i need the script to look if there are another 7 days to count like lets say if he already counted from 01/01/2000 to 07/01/2000 then he should look if there is a data from 08/01/2000 if yes and it goes until 15/01/2000 then there are 7 days to count if not then he should display as many days left as the last entry.
See if this is helpful. Moving parts start somewhere around line 93. It is a fairly brittle mock-up of the query structure, for example it would end prematurely if the data had large gaps of more than a week. But at least it will help you get the information out of the data base and into an associative array where you can do the arithmetic.
Best regards, ~Ray
Best regards, ~Ray
<?php // RAY_temp_altimo.php
error_reporting(E_ALL);
date_default_timezone_set('America/Chicago');
// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??"; // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB CONNECTION: ";
echo "<br/> $errmsg <br/>";
}
// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB SELECTION: ";
echo "<br/> $errmsg <br/>";
die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES
// TABLE AND DATA FROM THE POST AT EE
$sql = "CREATE TEMPORARY TABLE `RAY_altimo` (
`id` int(10) NOT NULL auto_increment,
`pcid` varchar(32) NOT NULL,
`period` date 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=19";
$res = mysql_query($sql) or die(mysql_error());
mysql_query("INSERT INTO `RAY_altimo` VALUES (1, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-19', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (2, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-18', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (3, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-17', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (4, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-16', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (5, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-15', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (6, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-14', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (7, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-13', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (8, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-12', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (9, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-11', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (10, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-10', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (11, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-09', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (12, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-08', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (13, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-07', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (14, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-06', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (15, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-05', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (16, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-04', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (17, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-03', '1073741824', '1073741824', '2147483648')");
mysql_query("INSERT INTO `RAY_altimo` VALUES (18, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-02', '1073741824', '1073741824', '2147483648')");
// SEE IF WE GOT THE TABLE LOADED
// MAN PAGE: http://php.net/mysql_fetch_array
$sql = "SELECT COUNT(*) FROM RAY_altimo";
$res = mysql_query($sql);
// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>QUERY FAIL: ";
echo "<br/>$sql <br/>";
die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";
// DETERMINE THE NEWEST ROW IN THE TABLE
$sql = "SELECT period FROM RAY_altimo ORDER BY period DESC LIMIT 1";
$res = mysql_query($sql);
// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>QUERY FAIL: ";
echo "<br/>$sql <br/>";
die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH ASSOCIATIVE KEYS
$row = mysql_fetch_assoc($res);
$new = $row["period"];
echo "<br/>THE NEWEST DATE IN THE TABLE IS $new";
// SET A LOOP INDICATOR
$loop = TRUE;
while ($loop)
{
$old = date('Y-m-d', strtotime($new . ' - 1 week'));
$sql = "SELECT * FROM RAY_altimo WHERE period BETWEEN '$old' AND '$new' ";
$res = mysql_query($sql);
if (!$res)
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>QUERY FAIL: ";
echo "<br/>$sql <br/>";
die($errmsg);
}
$num = mysql_num_rows($res);
echo "<br/>FROM $old TO $new";
if (!$num)
{
$loop = FALSE;
echo "<br/>NO DATA";
}
else
{
echo "<br/>$num DATA ROWS";
}
while ($row = mysql_fetch_assoc($res))
{
echo "<br/>";
var_dump($row);
}
$new = $old;
}ASKER
THERE ARE 18 ROWS IN THE TABLE
THE NEWEST DATE IN THE TABLE IS 2011-04-19
FROM 2011-04-12 TO 2011-04-19
8 DATA ROWS
array(6) { ["id"]=> string(1) "1" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(1) "2" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(1) "3" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(1) "4" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(1) "5" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(1) "6" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(1) "7" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(1) "8" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
FROM 2011-04-05 TO 2011-04-12
8 DATA ROWS
array(6) { ["id"]=> string(1) "8" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(1) "9" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(2) "10" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(2) "11" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(2) "12" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(2) "13" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(2) "14" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(2) "15" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
FROM 2011-03-29 TO 2011-04-05
4 DATA ROWS
array(6) { ["id"]=> string(2) "15" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(2) "16" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(2) "17" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
array(6) { ["id"]=> string(2) "18" ["pcid"]=> string(32) "4db58eb70efe9157cb2e1bea4
FROM 2011-03-22 TO 2011-03-29
The output is what I need but how to do the calcucations and get the data out from the array?
ASKER
why it takes 8 days instead of 7 ?
ASKER
also it ends with 8th and begins other output with the same date
You probably want to hire a professional developer to help you with this. EE is a good place to get your questions answered, but not so good if you need someone to write bespoke programming. That is a professional activity and professionals would reasonably expect to be paid for their time and specialized skills and knowledge. So if there is economic value to this project my advice is to get a professional involved.
If you're just doing this so you can learn PHP, this is a really good book:
http://www.sitepoint.com/books/phpmysql4/
To get variable values "out" of an array, you can either assign them to a separate variable or you can just use them right where they are in the array. Something like this:
$total = $row["downloaded"] + $row["uploaded"];
echo $total;
If you're just doing this so you can learn PHP, this is a really good book:
http://www.sitepoint.com/books/phpmysql4/
To get variable values "out" of an array, you can either assign them to a separate variable or you can just use them right where they are in the array. Something like this:
$total = $row["downloaded"] + $row["uploaded"];
echo $total;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok I will play around with it. Thanks.
Thanks for the points and please do consider getting that book. It's a small investment with a great deal of value! Best regards, ~Ray
ASKER
I will need some more help with this, I will open separate question for this.
ASKER
Thanks - I have some comments over there. I still think you would really benefit a lot from that SitePoint book!
Please post the CREATE TABLE statement for netmeterdb.config and give us a test data set that we can load to make a copy of your table. A CSV file would be fine.
I'm also having a little difficulty following the logic of what you want to do. Let's see if this paraphrases it correctly for part of the "weeks" version of the question.
"I want to find the most recent entry in my table. Using that date as an anchor point, I want to find all the entries that are within 7 days (1 week) prior to that entry, using the period column as the date information. When I have retrieved a record set I want to..."
That's about all I understand of it. I can show you how to get the dates so you can use those in a query, but I'm not sure what you want to do with the data set you might get from the query.