Professional Web Applications Themes

Mysql query - PHP Development

Hi, I have a database with a date field. And I would like to see the report grouped by a ceratin month and year, how can I do that without multiple queries? Albert Ahtenberg...

  1. #1

    Default Mysql query

    Hi,

    I have a database with a date field. And I would like to see the report
    grouped by a ceratin month and year, how can I do that without multiple
    queries?

    Albert Ahtenberg


    Albert Ahtenberg Guest

  2. #2

    Default Re: Mysql query

    What format is the date field stored in?

    Is there a day, month, and year field (separate)?
    Is it a timestamp?
    Is it something else?

    -Jim Stapleton

    "Albert Ahtenberg" <donotcontactthis.address> wrote in message
    news:bdhud4$tev$1tabloid.uwaterloo.ca...
    > Hi,
    >
    > I have a database with a date field. And I would like to see the report
    > grouped by a ceratin month and year, how can I do that without multiple
    > queries?
    >
    > Albert Ahtenberg
    >
    >

    Steven James Samuel Stapleton Guest

  3. #3

    Default Re: Mysql query

    It is the MySql DATE type. looks like (DD-MM-YYYY).

    "Steven James Samuel Stapleton" <stapleton.41mps.ohio-state.edu> wrote in
    message news:mcgHx12drhfuohstpy...
    > What format is the date field stored in?
    >
    > Is there a day, month, and year field (separate)?
    > Is it a timestamp?
    > Is it something else?
    >
    > -Jim Stapleton
    >
    > "Albert Ahtenberg" <donotcontactthis.address> wrote in message
    > news:bdhud4$tev$1tabloid.uwaterloo.ca...
    > > Hi,
    > >
    > > I have a database with a date field. And I would like to see the report
    > > grouped by a ceratin month and year, how can I do that without multiple
    > > queries?
    > >
    > > Albert Ahtenberg
    > >
    > >
    >
    >

    Albert Ahtenberg Guest

  4. #4

    Default Re: Mysql query


    First get a real email address if you want real replies.

    On Fri, 27 Jun 2003 20:54:25 -0400, Albert <donotcontactthis.address> wrote:
    > It is the MySql DATE type. looks like (DD-MM-YYYY).
    ^^^^^^^^^^
    This isn't a MySQL date format.


    select * from mydatabase where datefield like "2003-07%";

    This works for me.

    >
    > "Steven James Samuel Stapleton" <stapleton.41mps.ohio-state.edu> wrote in
    > message news:mcgHx12drhfuohstpy...
    >> What format is the date field stored in?
    >>
    >> Is there a day, month, and year field (separate)?
    >> Is it a timestamp?
    >> Is it something else?
    >>
    >> -Jim Stapleton
    >>
    >> "Albert Ahtenberg" <donotcontactthis.address> wrote in message
    >> news:bdhud4$tev$1tabloid.uwaterloo.ca...
    >> > Hi,
    >> >
    >> > I have a database with a date field. And I would like to see the report
    >> > grouped by a ceratin month and year, how can I do that without multiple
    >> > queries?
    >> >
    >> > Albert Ahtenberg
    >> >
    >> >
    >>
    >>
    >
    >

    --
    Michael Vore, W3CCV M-ASA [Ka8]; WHIRL, ABC; CAW, CW, AAW
    [url]http://mike.vorefamily.net/ohmywoodness[/url] <-Custom Woodworking
    [url]http://mike.vorefamily.net/thewoodenradio[/url] <-The weblog
    Mike Vore Guest

  5. #5

    Default Re: Mysql query

    Mike,

    Regarding my Email address, I got so much spam when I posted my Email
    address on this forum so I decided not to. Besides, I can't see the
    relevancy of my Email to this at all.

    Now, what you suggest will choose all the records with the date July/03.
    What I need is not to have them selected by the date but grouped by a
    certain date. I will give you an example. Suppose I have a 'date' field and
    a 'sum' field. What I need is a report about the total sum in each month.
    And I wonder what is the most simple way to do it.

    Regards,
    Albert Ahtenberg

    "Mike Vore" <mvoreix.netcom.cam> wrote in message
    news:slrnbfr1ln.sqp.mvoresnowmass.mystic.ct...
    >
    > First get a real email address if you want real replies.
    >
    > On Fri, 27 Jun 2003 20:54:25 -0400, Albert <donotcontactthis.address>
    wrote:
    > > It is the MySql DATE type. looks like (DD-MM-YYYY).
    > ^^^^^^^^^^
    > This isn't a MySQL date format.
    >
    >
    > select * from mydatabase where datefield like "2003-07%";
    >
    > This works for me.
    >
    >
    > >
    > > "Steven James Samuel Stapleton" <stapleton.41mps.ohio-state.edu> wrote
    in
    > > message news:mcgHx12drhfuohstpy...
    > >> What format is the date field stored in?
    > >>
    > >> Is there a day, month, and year field (separate)?
    > >> Is it a timestamp?
    > >> Is it something else?
    > >>
    > >> -Jim Stapleton
    > >>
    > >> "Albert Ahtenberg" <donotcontactthis.address> wrote in message
    > >> news:bdhud4$tev$1tabloid.uwaterloo.ca...
    > >> > Hi,
    > >> >
    > >> > I have a database with a date field. And I would like to see the
    report
    > >> > grouped by a ceratin month and year, how can I do that without
    multiple
    > >> > queries?
    > >> >
    > >> > Albert Ahtenberg
    > >> >
    > >> >
    > >>
    > >>
    > >
    > >
    >
    >
    > --
    > Michael Vore, W3CCV M-ASA [Ka8]; WHIRL, ABC; CAW, CW, AAW
    > [url]http://mike.vorefamily.net/ohmywoodness[/url] <-Custom Woodworking
    > [url]http://mike.vorefamily.net/thewoodenradio[/url] <-The weblog

    Albert Ahtenberg Guest

  6. #6

    Default Re: Mysql query

    Albert Ahtenberg wrote:
    >> select * from mydatabase where datefield like "2003-07%";
    maybe
    select year(datefield), month(datefield), sum(valuefield)
    from table
    group by year(datefield), month(datefield)

    HTH

    --
    "Yes, I'm positive."
    "Are you sure?"
    "Help, somebody has stolen one of my electrons!"
    Two atoms are talking:
    Pedro Guest

  7. #7

    Default mysql query

    ok im stumped lol i have used this code in the past to insert data into
    mysql (im relitively new though)
    ------code--------
    mysql_query("INSERT INTO Images (Image, desc) VALUES ('$name',
    '$description')") or die (mysql_error());
    --end code---------
    but i get this error]
    ---------error-------------
    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 ''Image', 'desc')
    VALUES ('2419091.jpg', 'stone')' at line 1
    ----------- end error--------------
    I found if i just try and record the name value it works fine but if i try
    to add the description i get the error.
    Any clues?
    :)
    Cameron Guest

  8. #8

    Default Re: [PHP] mysql query

    DESC is a reserved word (used to indicate a DESCending ORDER bY).

    N

    On Friday 03 Oct 2003 11:17 am, Cameron Metzke wrote: 
    Nicholas Guest

  9. #9

    Default MySQL Query

    If I have a table set up like this:

    Name | VARCHAR
    Email | VARCHAR
    Age | TINYINT | NULL (Default: NULL)

    And I want the user to enter his or her name, email, and age - but AGE
    is optional.

    My insert would look something like:

    INSERT INTO data (Name, Email, Age) VALUES ('$name', '$email', $age)

    This is all good, except if the user doesn't enter an age. Then $age is
    an empty variable. I thought that since the table is set up where Age
    can be NULL, that this should be fine. But MySQL is giving me an error.
    If $age is a number, it is no problem.

    Anyway to make it accept $age as an empty variable (and just make it
    NULL)? I know I could say:

    if (empty($age)) { $age = 0; }

    and write it that way, but this is just an example and there are many
    more variables that could be empty.

    Thanks.
    Dave Guest

  10. #10

    Default Re: MySQL Query

    Either don't include them in the insert:

    insert into data (name, email) values ('$name', '$email')

    or make the variable 'null' (literally). Something like:

    $age = !empty($age) ? $age : 'null'; //'null' is a string containing
    the word 'null'

    Then do your query as before.

    ZeldorBlat Guest

  11. #11

    Default Re: MySQL Query

    ZeldorBlat wrote: 

    I was trying to do it without having to take it out of the query or
    assign it NULL... just because there are a large number of variables
    that could be empty.
    Dave Guest

  12. #12

    Default Re: MySQL Query

    If you want the value to be null in the database, then you need some
    way of telling MySQL that's what you want. One way is to ommit it from
    the insert -- in which case the default value (in this case, null) is
    used. The other way is to specify the value as the literal string
    'null'.

    ZeldorBlat Guest

  13. #13

    Default Re: MySQL Query

    set default value to 0 gor AGE field in structure of database!

    ranii4u@gmail.com Guest

  14. #14

    Default Re: MySQL Query

    just dont enter in that field while executing anu INSERT query it will
    automatically take that field DEFAULT value NULL without any error.

    ranii4u@gmail.com Guest

  15. #15

    Default Re: MySQL Query

    Dont include that field in ur INSERT clause while inserting record it
    will take DEFAULT value as NULL as u have specified and set NULL
    attribute as 'yes' in ur structure of table and DEFAULT value as 'null'

    OR IF,

    ur getting values dynamically from user then store all values in
    variable and then execute INSERT query even if any value will come null
    it will be store in variable and that all variables u used in ur INSERT
    query!

    ranii4u@gmail.com Guest

  16. #16

    Default Re: MySQL Query

    Just change your insert query with

    INSERT INTO data (Name, Email, Age) VALUES ('$name', '$email', '$age')

    don't use $age, instead use '$age'

    this means when query will be executed it will look like
    INSERT INTO data (Name, Email, Age) VALUES ( 'myname', 'email', '' ) if
    user is not specifying his/her age.

    currently your query looks like this
    INSERT INTO data (Name, Email, Age) VALUES ( 'myname', 'email', ) if
    user is not specifying his/her age.
    and it gives you syntax error.

    Do you understan wat i mean?

    just do it for all your numeric vaule to be entered.

    and recall your PHP fundamentals.

    KERUL
    ['ProDesignZ']

    kerul4u Guest

  17. #17

    Default Re: MySQL Query

    On Mon, 29 Aug 2005 11:24:55 -0400, Dave Thomas wrote:
     


    BTW, a field containg an AGE has no real value. What will happen net year.
    Youd'better take care of the birth day, a fixed data.

    Johan

    johan Guest

  18. #18

    Default Re: MySQL Query

    "johan" <com> kirjoitti
    viestissä:com... 
    >
    >
    > BTW, a field containg an AGE has no real value. What will happen net year.
    > Youd'better take care of the birth day, a fixed data.[/ref]


    Asking age is easier on the user in my opnion, but he could store
    (date('Y') - $age) instead, to get the birthyear.

    --
    SETI Home - Donate your cpu's idle time to science.
    Further reading at <http://setiweb.ssl.berkeley.edu/>
    Kimmo Laine <com>


    Kimmo Guest

  19. #19

    Default Re: MySQL Query

    > > BTW, a field containg an AGE has no real value. What will happen net year. 
    >
    > Asking age is easier on the user in my opnion, but he could store
    > (date('Y') - $age) instead, to get the birthyear.[/ref]


    He could also use HTML <select> and show "available" ages in it, but
    use birth year as values (or show both but send year only). Something
    like:

    Age: <select name="birth_year">
    <?php
    $curr_year = intval( date( 'Y' ) );
    $birth_year = intval( $_REQUEST['birth_year'] );
    for( $i = ($curr_year-110); $i <= $curr_year; $i++ )
    {
    echo ' <option value="', $i, '"';
    if ($i == $birth_year)
    echo ' selected="selected"';
    echo '>', ($curr_year - $i), ' (', $i, ')</option>', "\n";
    }
    ?>
    </select>


    Hilarion
    Hilarion Guest

  20. #20

    Default Re: MySQL Query

    On Tue, 30 Aug 2005 11:52:35 +0200, johan wrote:
     
    >
    >
    > BTW, a field containg an AGE has no real value. What will happen net year.
    > Youd'better take care of the birth day, a fixed data.
    >
    > Johan[/ref]

    This is a response to the OP....
    When I have items that are nullable, I build the list and the values
    sections as I p the page. So if itemx is there, I build the DB_ItemX
    field into the the ongoing column list:
    (DB_ItemA, DB_ItemB, DB_ItemX)
    This is relatively easy using:
    $insertList.= ',DB_ItemX';
    and then it's value in the value list:
    $valueList.= ",'".$formItemX."'";

    So when you get to the point of building the runnable SQL:
    $insertSQL = "INSERT to your.table (";
    $insertSQL.= $insertList ;
    $insertSQL.= ") Values (";
    $insertSQL.= $valueList ;
    $insertSQL.= ")" ;

    Also note that if you are supplying values for all of the columns in the
    table, SQL does not require the column list. The values then need to be
    in the exact same order as defined in the table though!!

    Chris

    Christopher Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. Is this a MySQL Query bug?
    By Carl Anatorian in forum MySQL
    Replies: 4
    Last Post: June 13th, 07:29 AM
  2. Replies: 0
    Last Post: November 4th, 05:38 PM
  3. MYSQL query: AND OR?
    By Bonge Boo! in forum PHP Development
    Replies: 5
    Last Post: July 9th, 11:59 PM
  4. MySql query help
    By luckyrye in forum PHP Development
    Replies: 2
    Last Post: May 1st, 05:25 AM
  5. Query log for mySQL?
    By Kevin Thorpe in forum PHP Development
    Replies: 3
    Last Post: November 27th, 05:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139