Paging issue

[_matrix_]

Hi,

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

$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

[oddz]

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.

[_matrix_]

WHERE productgroup = 'Some Group'";

'productgroup' is not a intinteger.. but 'pid' is..

[oddz]

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.

[_matrix_]

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..

[Cups]

Try this, respecting that Case matters and that keys should be quoted:

$total_pages = $total_pages['Num']; 

[_matrix_]

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

[Cups]

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.

[blogaddition]

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