Page 1 of 1
How do I create this SQL?
Posted: Thu Nov 06, 2003 12:14 pm
by Klaws_wolverine
Hello all,
I have a question... obviously. If I have a site with products, and I want to know which products are being viewed the most, how do I create an SQL that does this.
pseudocode:
select productName from productStats
if productName currently being viewed in catalog is not in productStats table, add it and add hits = hits + 1
if productName is in productStats table, increment the hits to 1.
I have alot of productNames, instead of doing data entry with all the product names and put them in this table, just do it the above way.
Anyone know some good SQL for this?
Thanks
Posted: Thu Nov 06, 2003 12:22 pm
by JAM
Looks like you almost answered your own question. Depending on how it actuall works, you are on the right path (imho).
If you as example use links to display product information...
Code: Select all
<a href="info.php?id=1">Product 1</a>
<a href="info.php?id=2">Product 2</a>
...you likely would use the id, to actually find the one product to fetch info for.
For fetching the info, you use a...
Code: Select all
$result = mysql_query("select * from table where id = '1'");
...most likely.
Just after that query you could add another mysql_query() that does just what you said in your original post.
Code: Select all
mysql_query("update table set hits = hits +1 where id = '1'");
For that you only really need a new field in the database, preferably a INT-type named 'hits', that you use to track clicks with.
Perhaps I just explained something obvious. Or perhaps I didn't fully understand the question.
query
Posted: Thu Nov 06, 2003 12:37 pm
by Klaws_wolverine
Yes exactly, but what if the ProductName is already NOT in the DB.
ok, here we go.
My query string contains the productID. From that ProductID, I do a:
Select productName from ProductTable where productID = $productID
Ok, so now I have the productName.
In my productStats table:
select productName from ProductStats where productName = $row["productName"]
The above is fine, however what if productName is not in the productStats table? so:
If I am on the web page viewing a product called lemmy.
if lemmy is not in the productName field in productStats, do a table scan to check it's existance, if it's not there, then insert into and hit =1, if it does already exist, then hits++.
How do I do this?
Thanks
Posted: Thu Nov 06, 2003 12:46 pm
by Weirdan
Code: Select all
mysql_query("update product_stats set hits=hits+1 where product_name=' {$row["productName"]}'");
if(!mysql_affected_rows())
mysql_query("insert into product_stats set hits=1, product_name='{$row["productName"]}'");
ps: don't forget to add error checking...
Query
Posted: Wed Nov 12, 2003 1:05 pm
by Klaws_wolverine
Hi,
I understand the code, but where does Ratio(%) come in? The code doesn't update or insert the ratio of the product...
Thanks
Posted: Wed Nov 12, 2003 1:42 pm
by JAM
Explain. It only updates hits. % of something is another question, if I understood you correct.
query
Posted: Wed Nov 12, 2003 1:56 pm
by Klaws_wolverine
Oh, I apologize, I had clicked submit on another thread, i guess it didn't get posted.
Here is my question, I have productName, and Hits, but I also want to add a Ratio column, I don't know whether this would be a mySQL function or done in PHP, but how many hits a product got is useless information without knowing how many hits it got in contrast to hits of other products, so this is why I would like to add a ratio column.
Is this possible? Select count(*) as ratioNumber, and number of hits/RatioNumber, something like this I guess.
Thanks
Posted: Wed Nov 12, 2003 5:56 pm
by JAM
MySQL's sum() would be handly. I wont do it for you, but I can give some pointers...
If you divide the hits of article 'foo' and divide that with sum(hits) of all articles, you would get the %-age of 'foo'.
Posted: Thu Nov 13, 2003 10:55 am
by Weirdan
You can do it in one query:
Code: Select all
create table tst (id int not null auto_increment, name char(20), hits int,primary key(id));
insert into tst values(NULL,'asd',100);
insert into tst values(NULL,'sdf',200);
insert into tst values(NULL,'qwe',300);
select t1.id,t1.name,t1.hits,t1.hits/sum(t2.hits) as ratio from tst t1,tst t2 group by t1.id;
+----+------+------+-------+
| id | name | hits | ratio |
+----+------+------+-------+
| 1 | asd | 100 | 0.17 |
| 2 | sdf | 200 | 0.33 |
| 3 | qwe | 300 | 0.50 |
+----+------+------+-------+
But be aware that this query does
full "self-cross-join", so
do not use it if your table is large (>500 rows, I suppose).
Posted: Thu Nov 13, 2003 11:27 am
by scorphus
This topic was also oppened on this thread:
viewtopic.php?t=14508. Please some mod bind/lock them...
Cheers,
Scorphus.