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