SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Paging issue

  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Paging issue

    Hi,

    I have the following code that is failing because the field is varchar, but is expecing int..

    Code:
    $query = "SELECT COUNT(*) as Num FROM products WHERE productgroup = 'Some Group'";
    
    	$total_pages = mysql_fetch_array(mysql_query($query));
    	$total_pages = $total_pages[num];
    I was under the assumtion that if I changed:

    SELECT COUNT(*)

    to

    SELECT COUNT(pid) <-- pid field is int

    That it would use that field to count results, not the productgroup field?


    Thanks

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,042
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Is the productgroup column an integer data type?

    If so is it perhaps a foreign key? If it is than you can use a join. Doing so will make it possible to filter based on the "name" column for the referenced table.

    If you want assistance with that post the create table output for the other table which productgroup references.
    The only code I hate more than my own is everyone else's.

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WHERE productgroup = 'Some Group'";
    'productgroup' is not a intinteger.. but 'pid' is..

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,042
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    ok… time to backup

    By "failing" do you mean when you copy the query directly into the MySQL command prompt an error occurs that does not allow it to be executed successfully or… do you mean that query executes successfully but the result set is not as intended?

    The reason I ask is because what has been presented is a perfectly valid query so long as the table, columns and data types are correct.

    That query will return a SINGLE row with the number of rows inside the products table wirh "Some Group" as the value for productgroup column. Essentially the number of products that belong to the group specified.
    The only code I hate more than my own is everyone else's.

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The following works fine as a query directly in mysql..

    SELECT COUNT(pid) as Num FROM products WHERE productgroup = 'Some Group'

    As a whole

    $query = "SELECT COUNT(pid) as Num FROM products WHERE productgroup = 'Some Group'";

    $total_pages = mysql_fetch_array(mysql_query($query));
    $total_pages = $total_pages[num];

    It appears that $total_pages is empty, records display but the paging doesnt appear as it beleaves there are no results..

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Try this, respecting that Case matters and that keys should be quoted:
    PHP Code:
    $total_pages $total_pages['Num']; 

  7. #7
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sweet, that worked.. The initial code posted worked fine untill I changed:

    pid = 1";

    to

    productgroup = 'Some Group'";

    I also got it working using mysql_num_rows as well.. what wouod be the advantage or disadvantage to $total_pages = $total_pages['Num']; vers. mysql_num_rows option?

    Thanks

  8. #8
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    This is only going to return one row;

    SELECT COUNT(*) as Num FROM products WHERE productgroup = 'Some Group'

    'Num' = 15 // or whatever

    This is going to return 15 rows which you can either iterate through, use count() on or leave them and just query mysql's management system to find out how many rows had been returned. mysql_num_rows()

    SELECT id FROM products WHERE productgroup = 'Some Group'

    The former method has the RDBMS doing all the work, and will be much faster as long as your db table is correctly indexed ( as is ever the case ).

    ps if you had used:

    SELECT * FROM products WHERE productgroup = 'Some Group'

    Just to find out how many matching rows there were we would have to send people to your house just to come and shout at you.

  9. #9
    SitePoint Member blogaddition's Avatar
    Join Date
    Sep 2012
    Location
    India
    Posts
    23
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    just use

    $query = mysql_query("SELECT COUNT(*) FROM products WHERE productgroup = 'Some Group'");
    $total_pages = mysql_result($query, 0);
    This will give you total record according to your query.

    thanks


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
  •