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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Klaws_wolverine
Forum Commoner
Posts: 32
Joined: Mon Sep 29, 2003 10:11 am

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

Post 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
SBukoski
Forum Contributor
Posts: 128
Joined: Wed May 21, 2003 10:39 pm
Location: Worcester, MA

Post 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.
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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.
Klaws_wolverine
Forum Commoner
Posts: 32
Joined: Mon Sep 29, 2003 10:11 am

query

Post 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?
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Re: query

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