Posted by Norman Peelman on 01/27/07 14:32

Szymon Bobek wrote:
> Hi!
>
> I have databese with a few tables named like that: films_triller,
> films_horror, films_action etc.
> I decided to create many tables instead of creting just one big table
> containing all sort of films. Is it a good idea? (I think, that it boost
> search time much - am I right?)
> Now - to find a film (when only title is known) I have to search in all
> that tables. But I have a problem with that.. How to do that? SELECT * FROM
> REGEXP 'film_*' WHERE title='the film' does not work. The same is when I
> want to list all tables (to see what groups of films I have) - if I try do
> join SHOW TABLES with REGEXP I get an syntax error too.
>
> Where is the mistake and how to correct it?
>
> Thanks in advance
> Szymon Bobek
>
>

Your structure is fine... the problem is your method of searching. I'm
not really sure you can use REGEX in that context:

1) You can obtain your TABLE names with 'SHOW TABLES'
2) You could keep a list of film types in an array
3) populate your SELECT with the TABLE names

example:

$tables = '';
$columns = '';
$query = "SHOW TABLES";
$result = mysql_query($query,$dbc);
while ($row = mysql_fetch_array($result))
{
$tables. = "$row,";
$columns .= "$row.title,";
}
$tables = substr($tables,0,-1); // strip trailing commas
$columns = substr($columns,0,-1);

$query = "SELECT * FROM $tables WHERE $columns.title LIKE "$film_name";
// or "%$film_name%", play with the % (wildcard matching) this should be
used as you could have films like 'GODZILA vs MOTHRA', 'GODZILA vs
MEGAZILA' or 'KING KONG', 'KING KONG (2002)', etc.
// echo "$query<br>"; // uncomment to see what your query looks like
---

You need to have an index on the film_name columns to make it real fast.

Norm

[Back to original message]