How do I create this SQL?

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

Moderator: General Moderators

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

How do I create this SQL?

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

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

query

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

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

Query

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Explain. It only updates hits. % of something is another question, if I understood you correct.
Klaws_wolverine
Forum Commoner
Posts: 32
Joined: Mon Sep 29, 2003 10:11 am

query

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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'.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

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

Post by scorphus »

This topic was also oppened on this thread: viewtopic.php?t=14508. Please some mod bind/lock them...

Cheers,
Scorphus.
Locked