PHP Split array values based on time

Question

I want this human readable words into php code:

Take all values from column "Ratings" WHERE Time >= 24 hours Split them into 1 hour each Take average Rating values for each hour and put them into 24 variables

WhatI'm doing now is a bad long code :/

I'm taking them an hour after another, each one in a separate function where I specify an hour via mysql select statement!

I'm new in programming, and I couldn't figure out what to do to get all values at once, and split them into 1 hour each

Thanks

please rephrase: I have a db, this is its structure, here is a few lines, i want to query it and output XXX — user557846
– user557846, Commented Jul 1, 2013 at 3:10
Can you give us a little more information about your setup? What does the table look like (you can use a SHOW CREATE table foo query, where 'foo' is the name of your table)? What is your expected output (mock it up with notepad or something). — Skudd
– Skudd, Commented Jul 1, 2013 at 3:11
yeah we need some more info. could you post your database table here and its data, your code, and what you expected to get. — CaffeineShots
– CaffeineShots, Commented Jul 1, 2013 at 3:18
Sorry guys. Here you go: Database name: db 1 Table called: info 2 columns: Rating (numbers from 1 to 5) | Time (unix time stamp) I hope this is helpful. I'm new on this, and still learning the basics, so bare with me :( — Mohammad Alfares
– Mohammad Alfares, Commented Jul 1, 2013 at 14:43

Alexander Jardim · Accepted Answer · 2013-07-03 10:13:28Z

1

EDIT: changed the fiddle, so it uses time as an int, that represents seconds since January 1st, 1970.

Take a look at this fiddle: http://sqlfiddle.com/#!2/6e6b6/5/0 It is returning the rating average on a hourly basis. Is this that you need? I think you might tune the query for your other needs.

You can read more about it at http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html

edited Jul 3, 2013 at 10:13

answered Jul 1, 2013 at 3:18

Alexander Jardim

2,4761 gold badge16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mohammad Alfares Over a year ago

Thanks Alexand, this tool looks really helpful. But the Time data saved in my database are in unix formar. How do I alert the fiddle tool to save it in this format too?

Alexander Jardim Over a year ago

I think you should change the data type I used for the column. For unix time you milliseconds since January 1st, 1970 right? Can you print the create table script you are using, plz?

Mohammad Alfares Over a year ago

I think it won't work Alexander, because time is saved as unix time, which means as (int). How can we slice integers into hours in mySQL SELECT statment?

Alexander Jardim Over a year ago

Do you mean you are saving a rank entry for right now like 20130702093500 (which means year month day hour minute second, all concatenated) or like 1372768688 (seconds since January 1st 1970) ?

Alexander Jardim Over a year ago

that will do the trick: select date_format(from_unixtime(date), '%H') as hour, avg(rank) from ratings group by hour;

|

Mohammad Alfares · Accepted Answer · 2013-07-03 23:04:42Z

0

Got it, after so many searches. Learning is beautiful :)

This is the code

$mysql = "select date_format(from_unixtime(Time), '%H') as hour, avg(Rate) from za7ma1 group by hour";

$result = mysql_query($mysql) or die(mysql_error());

            // Print out result
    while($row = mysql_fetch_array($result)){
        echo "The average for this hour ". $row['hour']. " is ".$row['avg(Rate)'];
        echo "<br />";
    }

Thanks to Alexander Jardim for the great tool http://sqlfiddle.com/#!2/6e6b6/5/0 which opened my eyes on avg() mysql function

answered Jul 3, 2013 at 23:04

Mohammad Alfares

331 silver badge7 bronze badges

1 Comment

Alexander Jardim Over a year ago

@Mohammmad, plz, don't use mysql_* functions. You should use PDO for better compliance with php modern standards. Take a lookt at php.net/manual/en/book.pdo.php

Collectives™ on Stack Overflow

PHP Split array values based on time

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related