Re: MySQL / PHP Database Help

Jonathan, [have put the cc back to the list] > Is it possible to make BOTH the date and venue into a single unique index? =Why not? Like a good woman, treat her right, and SQL will do almost anything for you: 6.5.3 CREATE TABLE Syntax CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] create_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [reference_definition] or PRIMARY KEY (index_col_name,...) or KEY [index_name] (index_col_name,...) or INDEX [index_name] (index_col_name,...) ... Note the ellipses (...) at the end of that last line - many people are used to writing [PRIMARY] KEY or INDEX immediately after field name and definition, forgetting that if it is a separate clause of the CREATE stmt, multiple columns may be specified! > (Not that this works for me.) But I'm qcurious about this. I understand > where this could be useful as a single unique index.. (as opposed to two > unique indexes) Is this possible? How so? =yes it is possible, as above. =the short answer is: wherever you find yourself doing SELECT...WHERE field/index-condition1 AND field/index-condition2 =If only the first field/column is indexed, then obviously the SELECT will be faster than when accessing an unindexed table. However if there is a large fan-out between the two fields columns, (ie there are a large number of different values in field/column2 which share the same value in field/column1) then it may pay to combine the two fields into a single index for even faster results. Of course, the smaller your table, the harder it is to 'see' any return on the investment! (In my case multiple entries are > ok, just as long as I can run a report to spot them, and then edit them > which usually requires human interaction.) =If your system's data-entry stage is time-constrained then I would be tempted to agree. Otherwise conventional wisdom suggests that it is better to prevent 'dirty' data entering the system or data integrity issues creeping in, than it is to develop a strategy to 'clean' the db post-fact. Usually the person entering the data knows most about it - or has the best opportunity to ask the 'data source' for clarification! > Your second suggestion worked rather well... although its not quite > generating the output that would be best suited to me. The MySQL docs on > Group By and Count are quite weak.. do you have something else you could > send me / can you explain these commands. I was sure there is / was a way > to do it in MySQL my SQL just isn't what it should be. =if you post the code you've developed thus far, and some sample source data and results, together with some specific criticism, we might be able to help with issues like "best suited", or tweak the code I sent earlier to provide for situations that may not have been evident (at least to me) in your first post. =GROUP BY and COUNT() can be combined in many different ways, so what seems straightforward on the surface can yield enormous power when you start to tinker under the hood. I assume what you mean is that the manual is not really a tutorial. =Apart from the manual, I use books (I've picked up a few over the years - some probably now out of print; Paul DuBois' MySQL is current and the most specific - and has a PHP interface chapter, plus other more-PHP books, eg Welling & Thomson) and there are a number of tutorial web sites either covering SQL generally or MySQL in particular (start at the MySQL site or any search engine). =Regards, =dn