TIP: Use Markdown or, <pre> for multi line code blocks / <code> for inline code.
Database query for MAX()
  • samsoirsamsoir

    Hi,

    I am doing a query to find the maximum value of a value in a column using the standard "SELECT MAX(column) FROM table WHERE 1" database query which is working.

    However I am trying to get the value out of the result using:

    $myResult = $db->query( "SELECT MAX(column) FROM table WHERE 1" ); $maxColumnValue = $myResult[0]->MAX(column);

    I know the second row is wrong. What I want to know is what is the correct syntax for getting the MAX(column) result out of the database result object created from my query on the first line.

    All suggestions much appreciated, thanks in advance

    Kind regards,

    Sam Clark

  • zomborzombor
    Try:

    $maxColumnValue = $db->query('SELECT MAX(column) as max FROM table')->current()->max;
  • samsoirsamsoir

    I've fixed it....

    I'm such a dullard... of course MySQL can do this for me :-)

    $myResults = $db->query( "SELECT MAX(column) as maxColumn FROM table WHERE 1" );

    Well, there you go for anyone else stuck

  • samsoirsamsoir
    Posted By: zombor

    Try:

    $maxColumnValue = $db->query('SELECT MAX(column) as max FROM table')->current()->max;


    Thanks zombor, I got there myself after looking at it properly rather than just being lazing and asking you guys. Still, nice to know there is always a quick response.

    :-)
  • shadowhandshadowhand

    I never understood the point of MAX(), when this works just as well:

    SELECT value FROM table ORDER BY value DESC LIMIT 0, 1

    Said syntax also works with query builder:

    $max = $this->db->select('value')->from('table')->orderby('value', 'desc')->limit(1)->get()->current()->value;
  • samsoirsamsoir
    I guess it's whichever method you prefer? I (obviously) am less of an expert with MySQL as PHP, so I would hazard a guess that maybe MAX() is optimised on some table types (maybe?). Else it may just be there to keep users from other systems happy when they use MAX()?
  • dleavittdleavitt
    Any interest in integrating MAX, MIN, etc into the core DB library? These are pretty standard active record methods imo.
  • zomborzombor
    Posted By: dleavitt

    Any interest in integrating MAX, MIN, etc into the core DB library? These are pretty standard active record methods imo.



    All it would be doing is running the query shadowhand referenced above. We have no interest in MAX() function support, since it's database specific.
  • dleavittdleavitt
    I think you may be a little hasty in your dismissal! There's a reason most frameworks include wrappers for these functions - in addition to being difficult to parse at first glance, shadowhand's method is much slower on unindexed columns than using MAX() would be. That said, I agree with your basic point that this is probably not a deal breaker.

    Also: neat framework! I have been porting a project from CI, and Kohana seems like a pretty substantial improvement.
  • zomborzombor
    Posted By: dleavitt

    I think you may be a little hasty in your dismissal! There's a reason most frameworks include wrappers for these functions - in addition to being difficult to parse at first glance, shadowhand's method is much slower on unindexed columns than using MAX() would be.



    Again, if it were standard SQL, it would be supported. We do not support database specific functionality. The query builder is mainly intended for database agnostic code and query escaping, and supporting MAX() breaks both of these rules.
  • asdf007asdf007
    There should *definitely* be a way to turn off the escaping though, just like CI's library does. I personally use the query builder for only some queries (those where I need to have conditional selects/where/joins etc), and not being able to use function such as MAX() in those (MAX is standard as far as I know) is a real pain in the ass. (I made a fix for that by overloading the DB library.)

    I agree that it shouldn't support database specific stuff, but forcing the escaping actually prevents developers from making some perfectly standard SQL statements through the builder.
  • zomborzombor
    You can globally turn off escaping in SVN, it is a 2.2 feature. Use at your own risk.
  • edamedam
    Posted By: zombor

    You can globally turn off escaping in SVN, it is a 2.2 feature. Use at your own risk.



    This is no good for day to day use though. Quoting SQL values is an essential feature of the query builder - making quoting the default seriously helps prevent SQL injection. Having to disable the entire quoting system just to be able to use a couple of values unquoted isn't much of a solution! :o(

    I suggested an idea here that I use to get round this problem. It's not the most intuitive solution, but it works well from a development point of view.

    I think we need some more idea on how to solve this one! Anyone got any more?
  • asdf007asdf007
    What about CI's way?

    $db->select('MAX(`age`)', false);
  • edamedam
    Ok, take the following example:
    $data = array( 'id' => 4, 'name' => 'edam', 'count' => 'count + 1');
    $this->db->update( 'people', $data );

    How would that be done in the CI way?

    Would it be something like:
    $data = array( 'id' => 4, 'name' => 'edam', 'count' => array( 'count + 1', false ) );
    $this->db->update( 'people', $data );

    ?
  • zomborzombor
    The 2.1 DB library is very limiting, so adding features without making it feel like a hack is very hard. This is why it's a global option.

    Actually, I misspoke about that being a 2.2 feature. It's in SVN, but won't be released because of the new DB rewrite, which will have the ability per-query to turn off escaping. The new modular interface will make it very simple to do that.
  • edamedam
    @zombor: intriguing! Can you give us a quick example of usage of the new interface?
  • zomborzombor
    Posted By: edam

    @zombor: intriguing! Can you give us a quick example of usage of the new interface?



    I can't, only because it's not done yet and I don't want to commit to anything. All I'll say is that they query builder won't directly be a part of Database. You can find an example of where it is going here:

    http://trac.kohanaphp.com/browser/branches/2.2-database/system/libraries/Database_Where.php

    There will be similar libraries for SELECT, INSERT and UPDATE.

    The where class would be used like:

    $subwhere = new Database_Where();
    echo $subwhere->where(array('title' => 'foobar', 'username' => 'test'))->build(); // echoes (`title` = 'foobar' AND `username` = 'test')
    $where = new Database_Where();
    echo $where->where(array('filename' => 'foobar'))->orwhere($subwhere)->build() // echoes (`filename` = 'foobar' OR (`title` = 'foobar' AND `username` = 'test'))

    Note that you would probably only need to use the where object for queries involving parenthesis and subqueries. So you could build your complicated where clause with this object, and pass them to the select object: $select->where($where)

    Basic functionality would be provided in the select object.
Add a Comment