#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    10
    Rep Power
    0

    How to add zero(0) for data, not found in a mysql table


    Hi there,
    Suppose I have a mysql table with following data:

    table: timeValue
    ==================
    id ǀǀ time ǀǀ Value
    ================
    01 ǀǀ 10:45 ǀǀ 300
    ----------------------
    02 ǀǀ 10:46 ǀǀ 350
    ----------------------
    03 ǀǀ 10:48 ǀǀ 400
    ----------------------
    04 ǀǀ 10:49ǀǀ 450
    ==================

    in php script, First I create an array of time. The array will begin from 10:45 and continue up to 10:50:
    $time = array(10:45, 10:46, 10:47, 10:48, 10:49, 10:50);
    Now I want to extract value of each time from the mysql table (timeValue) by something like this sql:
    SELECT * FROM timeValue WHERE time INBETWEEN 10:45 AND 10:50;

    Please watch here that in the mysql table, there is no entry for 10:47 and 10:50. But I want a blank value( or Zero/ "0") if there is no value for any time.
    So I expect that the returned value will be like this: $data = 300,350,0,400,450,0; (for 10:45, 10:46, 10:47, 10:48, 10:49, 10:50 respectively).
    How to create a sql query to do that?
    I know it is possible by creating a loop. But I don't want to use loop if any sql query can do that at once.

    Do you have any idea?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,407
    Rep Power
    4286
    would actually be easy if you were talking about datetime values and not time values

    as it is, you need a LEFT OUTER JOIN from the time values to your data table, in order to generate a result row for time values that are missing from the data table

    this means you will ahve to store the array of times in a table

    it would be easier if they were datetimes, because then you could generate the time array values "on the fly" from a numbers table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    10
    Rep Power
    0
    would actually be easy if you were talking about datetime values and not time values
    I think it would not create a big problem, because I am already using time in mysql table in date/time column format. In that case, I have to store data in millisecond such as 10:20:30, and I can sort the time using mysql query as well.

    Left join can be implemented easily to solve this problem, but it would be better if it could be resolved in different way. Main reason is that the starting and ending date will be selected by the user by a html form. start date might be 00:00:00 to 23:59:99. So, it would be better if can be done by sql query instead.

    By the by, do you have any idea about temporary table? I mean, let userX is sending query to create and store data in a temporary table and userY is sending query to create & store data in the same time. Will the temporary table will be different for userX and userY? In another word, if userX will input any data to a temporary table, will it affect the temporary table of userY if occurred at the same time or those two temporary tables are completely different and separate for each user of a website?

    Any better idea will be appreciated.

    Regards
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,407
    Rep Power
    4286
    so are you saying that the table actually contains a datetime value for this purpose?

    try this... first, create this table --
    Code:
    CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY ) ;
    INSERT INTO numbers VALUES (0),(1),(2),(3),...,(11) ;
    then run this query --
    Code:
    SELECT '2012-09-09 09:37' + INTERVAL n HOUR AS thetime
      FROM numbers
    you can use this for your LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    10
    Rep Power
    0
    Originally Posted by r937
    so are you saying that the table actually contains a datetime value for this purpose?

    try this... first, create this table --
    Code:
    CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY ) ;
    INSERT INTO numbers VALUES (0),(1),(2),(3),...,(11) ;
    then run this query --
    Code:
    SELECT '2012-09-09 09:37' + INTERVAL n HOUR AS thetime
      FROM numbers
    you can use this for your LEFT OUTER JOIN
    I am sorry but could not understand what you said.
    After creating the table what to do?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,407
    Rep Power
    4286
    Originally Posted by infomamun
    I am sorry but could not understand what you said.
    After creating the table what to do?
    did you run that query i suggested?

    this will generate the times you want for your LEFT OUTER JOIN

    do you know how a LEFT OUTER JOIN works?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    10
    Rep Power
    0
    No, I did not run the query yet but can understand the way you are trying to solve. Anyway thanks for your reply.
    I think, although I don't, the easier way is to create a loop for returning whole time series. At least, it will reduce the load on mysql database.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,407
    Rep Power
    4286
    Originally Posted by infomamun
    At least, it will reduce the load on mysql database.
    on the contrary, it will increase the load on mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo