Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Jul 2012
    Posts
    67
    Thanks
    14
    Thanked 6 Times in 6 Posts

    Pulling data from an executed query in php?

    So, I've been trying to wrap my head around something, and I haven't had much like finding guidance on the subject online.

    Let's say you have a really basic MySQL query like below:

    Code:
    $query = "SELECT * FROM infocall";
    $result = mysql_query($query);
    At this point in time, presumably, all your data from your query should exist in the $result variable, right? If that's correct, how do you go about extracting additional queries from that data?

    Let's say for instance that I have a column entitled "caller" in the "infocall" database. If I wanted to get the number of rows that have a specific entry in the caller column (say "customer"), can I query the results now stored in the $result variable instead of making another query like...

    Code:
    SELECT * FROM infocall WHERE caller LIKE 'customer'
    ...and then using mysql_num_rows to total the result?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    You can, but if you want just a count it'd probably be more efficient to use the COUNT() aggregate (assuming proper indexing in use).
    Pulling data is a matter of using mysql_fetch_array or any derivative such as mysql_fetch_assoc, mysql_fetch_row or mysql_fetch_object. Then you can index the record based on query field order. These are always incremental per call, so you can use a while($row = mysql_fetch_assoc($result)) loop to fetch each record.

    Also, mysql library is now officially deprecated. If your starting on something new,it'd be much wiser to use the MySQLi or PDO libraries instead.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    superwookie (05-24-2013)

  • #3
    New Coder
    Join Date
    Jul 2012
    Posts
    67
    Thanks
    14
    Thanked 6 Times in 6 Posts
    Thanks for the reply, that makes a bit more sense to me.

    Incidentally, I normally use mysqli. I've been working on a project for the last couple of months using mysql instead, and it's made my brain a bit mush when it comes to remember the extra i.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    Quote Originally Posted by Fou-Lu View Post
    You can, but if you want just a count it'd probably be more efficient to use the COUNT() aggregate (assuming proper indexing in use).
    Even without indexing, it's going to be a lot faster to use COUNT().

    Code:
    SELECT COUNT(*) FROM infocall WHERE caller = 'customer'
    By the by, do *NOT* use LIKE unless you have wild card character[s] in the target.

    If your caller field is indexed, then that is going to execute lightning fast: MySQL will simply look in the index, find matches, and count them.

    But even if that field is not indexed, or even if you do have to use a LIKE, such as[code]
    Code:
    SELECT COUNT(*) FROM infocall WHERE caller LIKE '%customer%'
    MySQL will indeed have to scan all the records for matches, but it only has to send ONE number back to your PHP code.

    If you ask for all the rows, using
    Code:
    SELECT * FROM infocall WHERE caller LIKE '%customer%'
    and there are, say, 10378 matches, then *ALL* the data in *ALL* 10378 rows has to be sent from MySQL to PHP. And that is a very expensive process.

    Remember, MySQL is running in its own process. PHP is running in its own process (probably shared with the web server). So to pass data back and forth, they must use inter-process transfer mechanisms, which are never inexpensive in terms of either memory or performance.

    The less data you can send back and forth from PHP to MySQL and vice versa, the better.

    If you really *NEED* (say) all the phone numbers from customer calls, then do *NOT* do
    Code:
    SELECT * FROM infocall WHERE caller LIKE '%customer%'
    Instead do
    Code:
    SELECT phonenumber FROM infocall WHERE caller LIKE '%customer%'
    SELECT the bare minimum number of fields you REALLY need. *NEVER* use just SELECT * unless you *know* you will be getting back only one or perhaps a very few records.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    One thing I should ask now that there's a count in question here as well and note of its efficiency. I believe with myisam that the count was a part of the metadata, but I believe is not the case in the use of innodb. Is there ever a point in time where the use of count becomes inefficient to the degree where keeping a separate counter would be warranted (using INNODB)?
    I ask since I'm considering something I'm working on. Whilst optimistically I'd be looking at needing to do aggregate count, min/max, avg, etc on grouped by data in the counts of hundreds of millions, realistically I'd be looking at hundreds of thousands or maybe tens of million. Since I'll always build based on optimistic potential over more realistic potential, would it be better to count separate data or use simple addition over that of using counts?
    All and all I would presume that the addition will always be faster, but its not that I prefer that approach (since I make use of more than just counting and whatnots). I'm more curious if I'd be looking at extreme degradation of using properly indexed records in the tens of millions versus a simple select query of an existing count.
    BTW, I would need to keep the records regardless of if I run a separate counter or not. Just curious if you think there would ever be a need for concern.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    Well, personally, I would probably *NOT* choose to use INNODB for a table with millions of records. But I suppose it depends on what your data access patterns are.

    I've never tried to do things such as COUNT(*) based on an index in INNODB with such a large table. (Do it all the time with tables with thousand and tens of thousands of records, though.) So I don't really know how bad the performance might be.

    I would comment, though, that worst case is that INNODB has to scan the index, which clearly has to be better no matter what than scanning the records.

    ANYWAY...

    I do have a similar situation (happens to be using MyISAM, but it involves joining a pair of tables and so joining indexes, which even with MyISAM is not super fast), and my solution was to build a cache table.

    Somewhat like your suggestion of creating a separate table of counts but with the difference that the table starts empty. Then I use a Stored Procedure to find a given count. If I find it in the cache, then I'm done. If not, then I run the query to get the count from the join and I store that result in the cache table of counts, so it is there next time. This happens to be data that depends upon city and state, so the most popular (and populous) cities will be requested most often. Only the first request, then, for a particular city and state has to do the expensive query. From then on, the data is in the cached table. It's really simple to do and well worth the effort. You don't have to guess which data is "important"; the natural action of queries will soon build the cache with the most important values.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    Like this (with some names changed to protect the not so innocent).

    Note that I am actually caching many records per city/state, because I am also looking at category names
    Code:
    DELIMITER //
    
    CREATE PROCEDURE getCityStateCounts( 
        _state varchar(2), 
        _city varchar(28) 
    )
    BEGIN
        DECLARE _found INT;
    
        SELECT COUNT(*) INTO _found
        FROM CacheCounts
        WHERE state = _state AND city = _city;
    
        IF _found = 0 THEN
           INSERT INTO CacheCounts( state, city, category, ccount )
           SELECT _state AS thestate, _city AS thecity, category, COUNT(*)
           FROM ...joined tables not shown...
           GROUP BY thestate, thecity, category;
        END IF;
    
        SELECT category, ccount
        FROM CacheCounts
        WHERE state = _state AND city = _city
        ORDER BY ccount DESC;
    END 
    //
    
    DELIMITER ;
    By the by, there's no primary key on the CacheCounts table. There is a compound key on (state,city).
    Last edited by Old Pedant; 05-25-2013 at 12:55 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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