Skip navigation
Currently Being Moderated

mysql query (search multiple tables in database)

Sep 16, 2013 1:58 AM

I have 12 tables in a database - january through to december.

 

I need to search all 12 tables for 'keyworrd' phrases submitted by the user through a search form.

 

Must be a more streamlined way of doing it than below using 'UNION'. I have incorporated 2 tables in the below query but  I need a more 'condensed' query for all 12 tables?

 

$sql = ('SELECT * FROM january WHERE tourTitle = "'.$keyword.'" UNION SELECT * FROM february WHERE tourTitle = "'.$keyword.'"');

 

Cheers

 

Os

 
Replies
  • Currently Being Moderated
    Sep 16, 2013 6:18 AM   in reply to osgood_

    Sorry Os, I would have tackled the tables issue in a different manner. I would have had all of the data in one table with a column for the month.

     

    The query would have been very simple as would the rest of the code to insert new data.

     
    |
    Mark as:
  • Currently Being Moderated
    Sep 16, 2013 9:38 AM   in reply to osgood_

    >That's what I did last year but  thought I'd break it down this year into 12 easier to work with tables.

     

    No, Ben is correct. Using 1 table for each month is absolutely the wrong way. It violates basic rules of normalization and causes all sorts of problems.

     

    >Breaking it down appeals to be more so I can keep all the relevant months

    >together instead of potentially becoming scattered throught-out one table.

     

    That's what you use the Order By clause for.

     

    >If by any chance the client says they want to update x, y or z I can go

    >straight to the month in question without the necessity to flip through

    >dozens of pages in phpMyAdmin as there is no real CMS management in place for this process.

     

    Not sure what you are saying. Performing inserts, updates and queries is much simpler using a single table.

    Whenever someone asks for a way to search through multiple tables, it tells me that the data structure is not designed well.

     
    |
    Mark as:
  • Currently Being Moderated
    Sep 16, 2013 10:50 AM   in reply to osgood_

    >Arrgh you see IT WAS SO SIMPLE:

     

    It sure is

     

    >When I did this job last year there was about 60 pages created in phpMyAdmin.

     

    I'm still confused about this statement, possible because I never use MySQL. I though that tool was simply a front end for MySQL administration. What type of pages are you talking about here?

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points