Help with ordering and grouping with distinct ...

2002-09-21 Thread Ben Holness
Hi all, I have a table with three columns: Destination varchar(254) SentDatetimestamp(14) UserID varchar(32) I want to be able to get a list of the 100 most recently used numbers, based on the timestamp (SentDate) column. I want to have them ordered such

Help with selecting, grouping and distincting ... May need nested statement?

2002-09-19 Thread Ben Holness
Hi all, I am writing a PHP web page that displays some statistics based on information in a database. The database has a table (stats) with three fields: ID, Sender, Text, UserID, Timestamp I have set it up so that I get basic statistics with the following query: SELECT Text, Count(*) FROM

Indexing question

2002-08-27 Thread Ben Holness
Hi all, I would like to create an index to speed up the following query: SELECT Account, Status, count(*) From MessageStatus WHERE sentDate '(variable)' AND sentDate '(variable)' GROUP BY Account,Status ORDER BY Account sentDate is a timestamp(14), Account and Status are both varchars. The

Indexing Question

2002-05-27 Thread Ben Holness
Hi all, I have developed a system that lets people send messages to each other, with a MySQL database and PHP. Among the tables that exist, one is called Users and contains information such as Username, password (md5'd of course), email address etc. This table has a primary key UserID that is

RE: Scheduling with MySQL

2002-04-27 Thread Ben Holness
However, I would suggest, if you have control of the search tools, that you simply put in an extra timestamp field called hideuntil into your database, and add conditions into your searches that drop rows for which hideuntil is not null and is after the current time. This is a great idea

Newbie: Help with count

2002-04-27 Thread Ben Holness
Hi all, I have a table that has data that looks something like this (fixed font on): UserID MessageID MessageState 1 1 PENDING 1 2 PENDING 2 3 FAILED 3 4 DELIVERED 2

Scheduling with MySQL

2002-04-26 Thread Ben Holness
Hi all, I have a PHP based web interface that interacts with MySQL to let users insert data into a table called Messages. Once an entry is made in this table, other applications read and process the entries, modifying them where appropriate. I would like to add the capability to have this

Timestamps querying question

2002-04-14 Thread Ben Holness
Hi all, I have a database table which includes a timestamp(14) field. I would like to construct an SQL statement that pulls out records between two dates, based on this field (i.e. time is not important, date is). So a user input 01/01/2001 - 01/01/2002 and I want to retrieve all records that

RE: Timestamps querying question

2002-04-14 Thread Ben Holness
It is not apparent from your question, where the problem lies. Why not show us what you have so far? SQL is used to retrieve data from relational databases. Are you really intending to retrieve FROM a web page? Sorry, I meant that I am writing a PHP web page that takes input from a user

RE: Database Design Question

2002-03-28 Thread Ben Holness
Hi Chris/Nick/Scalper, Thanks for the replies. I am not too sure how to implement this in tables, so I will give an example: Let's say I have three lists - a,b and c. List a contains 10,000 entries, list b contains 2,500 entries and list c contains 75,000 entries. I have a table of lists, with

RE: Database Design Question

2002-03-28 Thread Ben Holness
Thanks very much to every who helped me with my MySQL problem! I will probably go with the three table solution as it also eliminates the need for yet another table! Cheers, Ben - Before posting, please check: