PHP: Parse all data from mysql where the 3rd and 4th digit of the id

Question

I have a mysql table with some weird id's like this:

╔═══╦════════════╦═════════════╦═══════════╦═════════════╦═══════════╗
║   ║     id     ║   user_id   ║  hours_a  ║   hours_b   ║  hours_c  ║
╠═══╬════════════╬═════════════╬═══════════╬═════════════╬═══════════╣
║ 1 ║ 010120149  ║     9       ║    10     ║     6       ║    23     ║
║ 2 ║ 0212201310 ║     10      ║    2      ║     8       ║    10     ║
║ 3 ║ 021220138  ║     8       ║    1      ║     4       ║     9     ║
║ 4 ║ 020120149  ║     9       ║    3      ║     8       ║    10     ║
╚═══╩════════════╩═════════════╩═══════════╩═════════════╩═══════════╝

I am trying to parse the total hours for user id 9, for the month January and year 2014. As you can see from the table, that is the first and last row.

For example, 01 01 2014 9 is the first row's ID of which represents DD/MM/YYYY/ID.

I want to be able to retrieve all hours (hours_a, hours_b & hours_c separately) for user_id = 9 where day = 01 - 31(loop through all days?), month = 01 and year = 2014.

To be exported in something like this:

{"userid":"9","month":"01","year":"2014","total_hours_a":"13","total_hours_b":"14","total_hours_c":"33"}

The ID's represent Day, Month, Year and Userid as such:

---

Currently what I'm doing

Currently I'm selecting all the table:

$query="SELECT * FROM `weird_table` WHERE `id` LIKE 9";

Printing it to encoded json:

$result = mysql_query($query);
$temp = 0;
$json = array();

while ($row = mysql_fetch_array($result))
    {
    $json[$temp]['id'] = $row['id'];
    $json[$temp]['userid'] = $row['userid'];
    $json[$temp]['hours_a'] = $row['hours_a'];
    $json[$temp]['hours_b'] = $row['hours_b'];
    $json[$temp]['hours_c'] = $row['hours_c'];
    }

$temp++;
}

print json_encode($json);
mysql_close();

and then client side (as i am a front-end developer), i mix&match & sum it up and get the results I want. Instead of doing all this hassle and giving the user so much cpu pain; i would like the server to do the work and print me exactly what I am looking for.

Answer 1

Try this:

SELECT *, (hours_a + hours_b + hours_c) totalHours
FROM weird_table 
WHERE user_id = 9 AND 
      STR_TO_DATE(SUBSTRING(id, 1, 8), '%d%m%Y') BETWEEN '2014-01-01' AND '2014-01-31'

OR

SELECT *, (hours_a + hours_b + hours_c) totalHours 
FROM weird_table 
WHERE user_id = 9 AND SUBSTRING(id, 3, 6) = '012014'

OR

SELECT *, (hours_a + hours_b + hours_c) totalHours
FROM weird_table 
WHERE user_id = 9 AND 
      YEAR(STR_TO_DATE(SUBSTRING(id, 1, 8), '%d%m%Y')) = 2014 AND 
      MONTH(STR_TO_DATE(SUBSTRING(id, 1, 8), '%d%m%Y')) = 1