Hello all, thanks for reading my little post...My question is can i merge and SELECT and a SELECT COUNT () AS Num.

I'm wondering if I can do something as simple as this

SELECT , COUNT() AS Num
But then how do I access count?

Curently I'm using

This is the Queries

      $Sql = "SELECT * FROM `Blog` ORDER BY `Date`";
        $SqlCnt = "SELECT count(*) AS `num` FROM `Blog`";

This is the db

         $sql = $Sql ." {$SqlAdd} LIMIT $From, $MaxRowsPP";
         $getlist = db_res( $sql );            

        $Paginate['RowList'] = $getlist;      

        $Paginate['TESTSQL'] = $sql;

Then later on I do a count

        $getcount = db_res( $SqlCnt );             

        $total_results = mysql_result($getcount, 0) - 1;   

        $TotalPages = ceil( $total_results / $MaxRowsPP );

db_res is a custom function that returns

mysql_query( $Query, $MySQL->link );

My goal here is to combine if possible

        $getcount = db_res( $SqlCnt );             

        $total_results = mysql_result($getcount, 0) - 1;  

AND
         $sql = $Sql ." {$SqlAdd} LIMIT $From, $MaxRowsPP";
         $getlist = db_res( $sql );

Into ONE db_res i.e. one mysql_query and get rid of mysql_result($getcount, 0) - 1; ...Is there away?

    Obviously it's not possible the eay way

    #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

    But I don't think that that additional query takes too much time.

      If you don't have a limit or anything it is possible to use [man]mysql_num_rows[/man], then you don't have to do a second query. As wilky says it is not possible to use both select and count() at the same time, but I think that this would work:

      SELECT *, (SELECT COUNT(*) FROM table) as c FROM table

      But I don't see any reason to do such things, one extra query is nothing to worry about.

        Well, "one extra query" should be noted as "one extra query per page request," so you'd have to multiply that "one" extra query by average number of page requests to get some idea of what kind of load you're talking about.

        Either way, seems simpler to just use [man]mysql_num_rows/man after running the first query.

        Also, unless you plan on working with every column in the table, it's unwise to use a "SELECT *" statement compared to SELECT'ing only the columns you are going to use.

          Ok...I'll give that a try....I don't think there will be limits because this is for doing pagination stuff and I'm trying to save everywhere I can...

          Thanks so much I'll give this a try and see if I can come up with something to reduce it even more...

          Oh I just realized that it won't work because I am using limits for pages ..:eek:

            You guys got me thinking about how I was doing this, I did not eliminate the extra query like I wanted but I did Eliminate and extra variable I was having to pass to my function. Which is just as good because it was making the function confusing to use.

            The key was doing the mysql_num_rows on the $Sql and Not the $sql which is the same thing as $Sql but $sql has the limits put in for the pages...

            $TotalResults = mysql_num_rows( db_res( $Sql ) );

            So thise means I do not need to pass $SqlCnt to my function, for me this is just as good as elimating the extra query...

            Thanks you guys are a good resource.

              Sorry to say, but that is not a good idea. If you should use the whole result anyway mysql_num_rows is good, but not if you do a query just to get the number of rows. If you do it that way then the database have to return everything to PHP and it gets counted after that. Instead use SELECT COUNT(*), it is much better.

                Write a Reply...