Displaying data from mysql database using php from the last 24 hours

Hi there,

I am wanting to create a log system which records data of every even has happen in the last 24 hours im not sure on how to do this

somthing like on the lines

$r=“SELECT * FROM logs With-in the last 24 hours”;

Somthing like that but is that possible im not sure how to go about it can anyone suggest what i should do with this type of situation?

And what i shud do about the way i should do it.

Thanks,William

it’s merely MySQL question, not PHP.
Try to realize the fact that MySQL is not just a storage but data manipulation system.
It can count, filter, sort your data for you and do many more things.
One can even use SQL to draw a pie chart!
But your case is not that complicated. Just add a condition to your query, using mysql data and time functions
they have also good example page
But you may need some basic SQL understanding first

that’s disgusting

to draw a pie chart with SQL, you’ll need a ~heck~ of a lot more than “basic”

SELECT [I]list of columns[/I]
  FROM logs 
 WHERE log_datetime > CURRENT_TIMESTAMP - 24 HOUR

:cool:

Some people take my humble posts too personal :slight_smile:

perhaps i should have been more clear

the method described in the article that you linked to for drawing a pie chart with SQL is disgusting, but you are an absolute darling for drawing it to our attention

:smiley: :smiley:

how about that one? makes about as much sense to me as I’m sure Shrapnel’s native language would :stuck_out_tongue: but still impressive I think.

Graphs and charts are best done with php using a library like JpGraph (note it can’t be used for a commercial site but they probably have a suitable licence that you could buy for a commercial site).

Why am i getting this error?

SELECT * FROM logs WHERE name=‘William BorseClark’ AND WHERE lastattack > CURRENT_TIMESTAMP - 24 HOURError:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE lastattack > CURRENT_TIMESTAMP - 24 HOUR’ at line 1

Now this is my table structure for that query

[SQL]
CREATE TABLE IF NOT EXISTS logs (
lid int(255) unsigned NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
Gold varchar(255) NOT NULL,
lastattack varchar(255) NOT NULL,
who varchar(255) NOT NULL,
PRIMARY KEY (lid)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

[/SQL]

This is my php code


include("dbconnect.php");
		$e="SELECT * FROM logs WHERE name='$F_user' AND WHERE lastattack > CURRENT_TIMESTAMP - 24 HOUR";
		echo $e;
		$result=mysqli_query($con,$e);
		if(!$result)
		{
			echo "Error:".mysqli_error($con);
		}
		else
		{
			$count=$result->num_rows;
			if($count>0)
			{
				while($data=$result->fetch_assoc())
				{
					//
					echo " <tr>
						<th>".$data['who']."</th>
						<th>".$data['Gold']."</th>
						<th>".$data['lastattack']."</th>
					</tr>";
					//
				}
			}
			else
			{
				echo "No Recent Attacks";	
			}
		}?></table><?php
	}

What am i doing wrong as for the last attack do i need to include the date or just the time eg 2010-26-2:12:40 noon or what?

heres what i placed in the table atm

[SQL]
INSERT INTO logs (lid, name, Gold, lastattack, who) VALUES
(1, 'Will, ‘500’, ‘20100226’, ‘userb’);
[/SQL]

20100226 is being the date should i include the time aswell?

Thanks,William

SELECT * FROM logs WHERE name=‘William BorseClark’ AND WHERE lastattack …

Remove the second WHERE

okay done that why do i get this error

SELECT * FROM logs WHERE name=‘William BC’ AND lastattack > CURRENT_TIMESTAMP - 24 HOURError:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘HOUR’ at line 1

I think it needs to be
SELECT * FROM logs WHERE name=‘William BC’ AND lastattack > CURRENT_TIMESTAMP - INTERVAL 24 HOUR

that’s my fault, sorry, i typed too fast and forgot the keyword INTERVAL

not sure if my calculations are correct but i want to calculate the minutes and hours this is my attempt at doing the calculations.


$ti=$data['time'];$e=date("H:i");
						$difference = $ti - $e;
						echo $e;
						$secs="60";
						//$hoursDiff = $difference / $secs;

						$minutesDiffRemainder = $difference &#37; $secs;
						echo "".$difference. "hr(s)" . $minutesDiffRemainder . "mins";

The times are coming out of the database above with the times 18:01 and 18:04 but some how i dont think its working the way i want it to work should be only 1 hour and 3 minutes are apart but not sure if my calulations are correct and not what?

Basically the time on the server is saying 18:15 in 24 hour time in the database time field it says 19.01

So is there anything wrong with my calculation above?

shouldnt it be 1 hour and 20 minutes apart?