hkalan2007 Posted November 17, 2010 Share Posted November 17, 2010 Hello, I have a msql table that has data input every hour of every day, so each date is entered 24 times next to the hour field as seen below the PHP... How do I get the information between a daterange that comes from the datepickers (start and end date) to show values of Power, Volt, and current for each day and hour in a report ? Here is what I have written, but I think the time, or multiple of same dates is stopping it from working... <?php if(isset($_POST['start1']) && isset($_POST['end1'])){ $start = (isset($_POST['start1'])) ? date("Y-m-d",strtotime($_POST['start1'])) : date("Y-m-d"); $end = (isset($_POST['end1'])) ? date("Y-m-d",strtotime($_POST['end1'])) : date("Y-m-d"); $con = mysql_connect('xxxxxx', 'xxxxxx', 'xxxxxxxxxx'); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("inverters", $con); $sql = "SELECT * FROM report WHERE date BETWEEN '$start' AND '$end'"; echo "<table border='1'> <tr> <th>Date</th> <th>Hour</th> <th>Power</th> <th>Volt</th> <th>Current</th> </tr>"; $res = mysql_query($sql) or die(__LINE__.' '.$sql.' '.mysql_error()); while($row = mysql_fetch_array($res)){ echo "<tr>"; echo "<td>" . $row['Date'] . "</td>"; echo "<td>" . $row['Time'] . "</td>"; echo "<td>" . $row['Power'] . "</td>"; echo "<td>" . $row['Volt'] . "</td>"; echo "<td>" . $row['Current'] . "</td>"; echo "</tr>"; } echo "</table>"; mysql_close($con); } ?> The DATE is a DATE, the TIME is TIME, the power,volt, current are FLOAT unit ID | Date | Time | Power | Volts | Current | 1 10/15/2010 21:00:00 0 220 100 1 10/15/2010 22:00:00 0 220 100 1 10/15/2010 23:00:00 0 220 100 1 10/16/2010 00:00:00 0 220 100 1 10/16/2010 01:00:00 0 220 100 1 10/16/2010 02:00:00 0 220 100 1 10/16/2010 03:00:00 0 220 100 1 10/16/2010 04:00:00 0 220 100 1 10/16/2010 05:00:00 245 220 100 1 10/16/2010 06:00:00 360 220 100 1 10/16/2010 07:00:00 596 220 100 1 10/16/2010 08:00:00 1567 220 100 1 10/16/2010 09:00:00 1568 220 100 1 10/16/2010 10:00:00 1598 220 100 1 10/16/2010 11:00:00 1642 220 100 1 10/16/2010 12:00:00 1658 220 100 1 10/16/2010 13:00:00 1664 220 100 1 10/16/2010 14:00:00 1598 220 100 1 10/16/2010 15:00:00 1527 220 100 1 10/16/2010 16:00:00 980 220 100 1 10/16/2010 17:00:00 410 220 100 1 10/16/2010 18:00:00 208 220 100 1 10/16/2010 19:00:00 0 220 100 1 10/16/2010 20:00:00 0 220 100 1 10/16/2010 21:00:00 0 220 100 1 10/16/2010 22:00:00 0 220 100 1 10/16/2010 23:00:00 0 220 100 1 10/17/2010 00:00:00 0 220 100 1 10/17/2010 01:00:00 0 220 100 1 10/17/2010 02:00:00 0 220 100 Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/ Share on other sites More sharing options...
PFMaBiSmAd Posted November 17, 2010 Share Posted November 17, 2010 10/15/2010 <--- That's not a mysql DATE format, so it will be a little hard and inefficient to compare date values with it. Is that really what you have stored in your date column? Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/#findComment-1135492 Share on other sites More sharing options...
hkalan2007 Posted November 17, 2010 Author Share Posted November 17, 2010 In the table it is yyyy-mm-dd Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/#findComment-1135496 Share on other sites More sharing options...
ManiacDan Posted November 17, 2010 Share Posted November 17, 2010 How can it be one way in the table, and another way in your example, if your example code doesn't reformat the date? Alter your table so that it uses the MySQL DATETIME format for a single column (for the date and time) then you can just use the BETWEEN operator to do your filter. -Dan Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/#findComment-1135501 Share on other sites More sharing options...
PFMaBiSmAd Posted November 17, 2010 Share Posted November 17, 2010 So, what is your actual question, problem, or error (and an example of what you are getting vs what you expect is worth a 1000 words)? Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/#findComment-1135502 Share on other sites More sharing options...
hkalan2007 Posted November 17, 2010 Author Share Posted November 17, 2010 Hello, Right now I am getting nothing... The PHP that you see here is not working. On the web page the JQuery Javascript is calling the report.php file (what you see here) to post in a DIV on the page when a button is clicked after the 2 dates are selected. Nothing happens... If I remove the <? from the PHP file, I see everything in the report.php file show in the DIV on the page, so I know the JQuery is working. My thoughts are that maybe the... $sql = "SELECT * FROM report WHERE date BETWEEN '$start' AND '$end'"; Is what my problem is, but anything I rewrite does not make any change... Your thoughts ????? Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/#findComment-1135518 Share on other sites More sharing options...
BlueSkyIS Posted November 17, 2010 Share Posted November 17, 2010 likely, this condition isn't true: if(isset($_POST['start1']) && isset($_POST['end1'])){ did you echo the values to check? Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/#findComment-1135525 Share on other sites More sharing options...
PFMaBiSmAd Posted November 17, 2010 Share Posted November 17, 2010 Right now I am getting nothing... The PHP that you see here is not working. LOL, ^^^ That's the most important piece of information and you didn't bother to share it until the #5th reply in the thread. Here is a hint about asking for and getting help: We are not standing right next to you. We only see the information you supply in your posts. When you don't tell us what you saw in front of you when you tried something, it takes a really long time to get a solution to your problem. Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/#findComment-1135534 Share on other sites More sharing options...
ManiacDan Posted November 17, 2010 Share Posted November 17, 2010 You get absolutely no output from the PHP script? Do you have error_reporting turned all the way up? You're making the call from ajax, are you viewing the raw response directly? Are you echoing the critical PHP variables to see if they're correct? Are you echoing the SQL query to see if it's malformed? Are you trying to SQL query in a real SQL browser to see if it's returning the right data? Basic debugging will help you faster than any of us can. -Dan Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/#findComment-1135536 Share on other sites More sharing options...
hkalan2007 Posted November 17, 2010 Author Share Posted November 17, 2010 Hello, I am new, and although it may have upset you, your assistance in even this situation has moved me forward in positive direction. This is how I am trying to teach myself outside the online PHP manuals... This is real life examples and the "hard-knocks" of learning in a real-world situations I accept to better my knowledge... I am learning from my mistakes, and I am sorry if I wasted any of your time. Thanks to everyone here, I am enjoying everything about PHP ! Alan Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/#findComment-1135623 Share on other sites More sharing options...
hkalan2007 Posted November 17, 2010 Author Share Posted November 17, 2010 That brings me to the point... I realize that the date format that will come into the report.php file is mm-dd-yyyy from the JQuery datepicker, and I need to get the PHP to make it so the mysql will read it as yyyy-mm-dd. I thought that this code would do that... $start = (isset($_POST['start1'])) ? date("Y-m-d",strtotime($_POST['start1'])) : date("Y-m-d"); $end = (isset($_POST['end1'])) ? date("Y-m-d",strtotime($_POST['end1'])) : date("Y-m-d"); I now realize it that there seems to be a problem as the php will echo only the current date, no matter what date is selected in the datepicker... Thanks, ALAN Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/#findComment-1135640 Share on other sites More sharing options...
Pikachu2000 Posted November 17, 2010 Share Posted November 17, 2010 You haven't wasted anyone's time, and I seriously doubt that you've truly upset anyone. The point that was being made is that the more information you can supply up-front, the easier it is to figure out what's going on, resulting in a quicker solution, and wasting less of your own time. Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/#findComment-1135645 Share on other sites More sharing options...
PFMaBiSmAd Posted November 17, 2010 Share Posted November 17, 2010 php will echo only the current date That would suggest that one or both of $_POST['start1'] and $_POST['end1'] are not set and you would need to determine why. Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/#findComment-1135650 Share on other sites More sharing options...
ManiacDan Posted November 17, 2010 Share Posted November 17, 2010 Back to the basic debugging steps I gave you before. use echo and print_r to examine what you believe to be in the $_POST array. -Dan Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/#findComment-1135739 Share on other sites More sharing options...
hkalan2007 Posted November 18, 2010 Author Share Posted November 18, 2010 Hello, I have debugged until I am blue in the face, and the manual is not providing any answers. When I echo print_r, I get the date array I should from the datepickers. As I said, the code at the top of this page is causing me major issues... I have the first 3 lines written as the manual says to get the date from the JQuery POST function , but I find from debugging that the { in the first line keeps anything from showing, so I removed it and the } at the bottom of the page (no visable errors on the page), but then I get every row to show from mysql table... I read that a ; should resolve this at the end of the first line rather then the { .... Wrong, that makes it so only today's date will be the only date for my queries... I have read the MySQL manual and PHP website about dates and that blew my mind for a while, but this should do it, but it does not... Nothing seems to be the same in the real world as to what the manuals are saying. At this time, I have the files and code online, and "all" the table rows are showing when you click the button to call the query... but the data is not inside the table cels online... on my localhost test server I see the numbers but NOT when it is actually online... Have a look http://www.pvmonitor.000a.biz/sw-report.php I am lost... Any advice or places I can look that google does not know about to assist me in getting this right ... ALAN Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/#findComment-1135923 Share on other sites More sharing options...
hkalan2007 Posted November 18, 2010 Author Share Posted November 18, 2010 HELLO !!!!!!!! It Is FIXED !!! I have been so focused on the code, dates, and times, it was the "Name" all the while !! Feel like I just gave birth ! Thanks for your understanding and assistance ! Now to start making some PHP graphs from the data... Is it beer-30 yet ? Alan Quote Link to comment https://forums.phpfreaks.com/topic/218954-daterange-sorted-by-date-put-in-a-table/#findComment-1135949 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.