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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
