[Resolved] Query multiple columns through URL variable?

Makkentass

I have a theatre database of performer names, where they played, characters, head shots, biographies, etc:

So I have columns ...fname, lname, source, picture, bio, additionalinfo... Then I have char1, prod1, date1

Most of the performers have multiple productions and characters that I wanted to show on the listing so I added an additional 15 columns to the database to account for:

char2...6, prod2...6, date2...6

Now, I used the search function and got examples of most of the code that I need--examples of the alternate table colors, alphabar and the query to select items by first letter which I modified to be applicable to a variable cat:

$result = mysql_query("SELECT * FROM $table WHERE $cat like '$name%'");

Problem 1: Is it possible to get the program to select from six different tables at once? I.e. if I want to display items from fields char1~char6 with the first letter 'c'...

I tried:

But it returns neither mySQL errors nor results.

Problem2: Some times they played multiple characters in the same production. Rather than add more columns is it possible to search with in a record for a partial match?

I.e. for this record:

VALUES('NULL','Aruj','Silvia','','','','','Mary / Sue-Ellen, Billie-Joe-Bob','Broadway','1997 / 1998','Barbara','Argentina','1993','','','','','','','','','','','','');

If I wanted to return a list of everyone with Sue-Ellen in the name field? Even if it didn't start with that name?

Thanks in advance for answering. 😃

Pig

I think the problem your getting is from a funky design and your data is not normalized. It would be more effective to have a seperate table for all of the productions, rather than multiple productions in the one table.

Have one table hold the actors information, including their ID.

In the second table, have all of the productions and use the ID from the actors table to tie them together. That way each actor can have 1 production, or 100.

Searching is much easier because you are just querying the production table, and use a join to get the actors information.

Makkentass

Yup. It is a funkily designed database. I knew that when I was adding on the extra 15 catagories for display and my data isn't standardized (and will never be.)

After tyring to convert to a two table method, and failing miserably, I decided to do it a bit differently. Drop those 15 categories and use a textarea for adding the cast, production, and date information. Displays the same way as the old stuff plus I don't have to do as much data convertion or adding extra columns later on.

Thanks anyway for the help... 😃