daterange sorted by date put in a table

[hkalan2007]

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

 

[PFMaBiSmAd]

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?

[hkalan2007]

In the table it is  yyyy-mm-dd

[ManiacDan]

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

[PFMaBiSmAd]

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)?

[hkalan2007]

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 ?????

 

 

[BlueSkyIS]

likely, this condition isn't true:

 

if(isset($_POST['start1']) && isset($_POST['end1'])){

 

did you echo the values to check?

[PFMaBiSmAd]

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.

[ManiacDan]

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

[hkalan2007]

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

[hkalan2007]

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

[Pikachu2000]

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.

[PFMaBiSmAd]

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.

[ManiacDan]

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

[hkalan2007]

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

[hkalan2007]

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