How do I create this SQL?
Moderator: General Moderators
-
Klaws_wolverine
- Forum Commoner
- Posts: 32
- Joined: Mon Sep 29, 2003 10:11 am
How do I create this SQL?
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
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
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...
...you likely would use the id, to actually find the one product to fetch info for.
For fetching the info, you use a......most likely.
Just after that query you could add another mysql_query() that does just what you said in your original post.
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.
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>For fetching the info, you use a...
Code: Select all
$result = mysql_query("select * from table where id = '1'");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'");Perhaps I just explained something obvious. Or perhaps I didn't fully understand the question.
-
Klaws_wolverine
- Forum Commoner
- Posts: 32
- Joined: Mon Sep 29, 2003 10:11 am
query
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
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
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"]}'");-
Klaws_wolverine
- Forum Commoner
- Posts: 32
- Joined: Mon Sep 29, 2003 10:11 am
Query
Hi,
I understand the code, but where does Ratio(%) come in? The code doesn't update or insert the ratio of the product...
Thanks
I understand the code, but where does Ratio(%) come in? The code doesn't update or insert the ratio of the product...
Thanks
-
Klaws_wolverine
- Forum Commoner
- Posts: 32
- Joined: Mon Sep 29, 2003 10:11 am
query
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
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
You can do it in one query:
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).
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 |
+----+------+------+-------+- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
This topic was also oppened on this thread: viewtopic.php?t=14508. Please some mod bind/lock them...
Cheers,
Scorphus.
Cheers,
Scorphus.