On Tue, 2004-09-07 at 12:38, Jeremy McEntire wrote:
> Clarification.
>
> I'm using modular arithmetic on a table of recently viewed items. My fields
> are:
>
> user_id, pid0, pid1, pid2, pid3, pid4, inc
>
> user_id is the user's unique identification.
> pid* is the product's unique identification.
> inc is a number, modulo 5, corresponding to
> the last pid column updated for this user.
You are not normalizing your data properly. What happens when you want
to keep track of an additional product? Will you alter the table
definition to add a new column? A lot of code will need to be revisited
to ensure that you are taking the correct modulus of the inc column to
get the correct data in that case. It is not hard to do what you want
to accomplish, but because your table is not normalized, the queries are
either impossibly hard or hardly possible.
You should design your table with the following three columns:
userid, productname, lastviewed
Where each user could have multiple rows, like so:
'me', 'pid0', '2004-04-01'
'me', 'pid1', '2004-04-02'
'you', 'pid1', '2004-05-15'
'you', 'pid0', '2004-06-01'
'me', 'pid2', '2004-06-10'
'you', 'pid5', '2004-07-01'
If you only want to keep track of the five most recent, then only ever
select the five most recent:
select * from table where userid = 'me' order by lastviewed desc limit 5
(cleaning out old entries is beyond the scope of this message, but you
should be able to figure it out).
When someone views a product, say I view pid6, issue an insert statement
to record that they viewed it:
insert into table
(userid, productname, lastviewed)
values
('me', 'pid6', now())
If you set up your primary keys correctly, then you can do a REPLACE
INTO statement rather than an INSERT so if they look at the same product
again the lastviewed column will get updated (so the last five product
views will always be unique products, rather than finding out that the
last five products views were the same product viewed five times).
> So, when the user visits a page, I want to verify that they
> don't currently have this product in their "recently viewed"
> list.
Using my sample table above:
select count(1) from table where userid = 'me' and productname = 'pid4'
will return 0 (since I have not viewed pid4). I've viewed five products
other than pid4 more recently than I've viewed pid4 (or I have not
viewed pid4 at all). If you DON'T clean out the table, you can keep a
running list of all the products viewed and when the user viewed them.
> To do so, I'd like to know if the product id is in any of pid0, pid1, pid2,
> pid3, or pid4. To accomplish this, I could use a simple OR statement. But,
> the information I really want is in which column that product id appears.
> Using PHP, I can simply grab the key from the array returned by the query.
> I was hoping MySQL offered this functionality; evidently, it does not.
I serious hope no one adds this "functionality" to MySQL, because it
would only serve to encourage questionable database design.
--
Andy Bakun <[EMAIL PROTECTED]>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
