Page 1 of 1

How to do this in MySQL, is it even possible???

Posted: Tue Nov 11, 2003 2:16 pm
by Klaws_wolverine
Hello all,

I have a script in my Extranet Code that allows us to see, in our catalog, which products were viewed how many times, and it gets recorded in our database, the product name, SKU and number of hits.

Is there also a way for me to add a column for ratio? If there are this many hits OVERALL Catalog-Wide, product A had this ratio of hits in contrast to all hits catalog-wide.

I doubt this would be done in PHP, it's probably a MySQL function, I am using MySQL 3.23.58 with PHPmyAdmin.

Thanks
MK

Posted: Tue Nov 11, 2003 2:23 pm
by SBukoski
My initial instinct of how this could be done would be a combination of both MySQL and PHP. I would create one query to retrieve the hits for the specific product, and a second to retrieve a SUM of the hits across the whole table.

Then in PHP it's just a matter of simple math to get the ratio.

Posted: Tue Nov 11, 2003 2:46 pm
by scorphus
Or you could first use a query to retrive the SUM of the hits ($overallHits) and another query like this:

Code: Select all

$sql = "select concat(hits/$overallHist*100, '%') from table_name";
to show the percentage of total hits for each record.

Cheers,
Scorphus.

query

Posted: Tue Nov 11, 2003 3:08 pm
by Klaws_wolverine
Yes that query would work, but only via browser would the data be accessible.

I want a column to be added onto the table, a ratio column.

Isn't there like a MySQL trigger available for this?

Re: query

Posted: Tue Nov 11, 2003 8:19 pm
by scorphus
Klaws_wolverine wrote:Isn't there like a MySQL trigger available for this?
MySQL Manual | 1.7.4.4 Stored Procedures and Triggers wrote:Stored procedures are being implemented in our version 5.0 development tree. (...) Triggers are scheduled for implementation in MySQL version 5.1. (...)
Klaws_wolverine wrote:I want a column to be added onto the table, a ratio column.
Is it really necessary to hold the ratio on the database? You can get it quickly with only 2 simple queries. You won't need to add another (float) column to the table and worry about precision.

And most important: if you choose to add the ratio column you'll have to update it by hand too, with a couple queries for the one record, but don't forget that you will have to update the rest of the records. Supose that: 5000, 10000 or more records to be updated each time a user views a product. Not a good idea.

Regards,
Scorphus.