mysql select multiple tables

Posted on 2014-08-08
Last Modified: 2014-08-12

I want to make a search function on a website and i would like to create a sql query that searches all of my preferred tables in my sql database and see if anything matches. i was thinking that i would have to use wildcards but im not sure how i could accomplish this or even if its possible

the list of my tables are
Home Log out Query window  phpMyAdmin documentation  Documentation
- bushbase (21)

thanks in advance
Question by:M. Jayme Nagy
    LVL 47

    Expert Comment

    This is way too broad to answer in my view.

    a typical SQL query requires field names

    SELECT field1, field2 FROM anytable WHERE field1 LIKE 'something%' OR field2 LIKE 'something%'

    Perhaps you have implemented some full text indexes?

    SELECT * FROM anytable  WHERE MATCH (title,body) AGAINST ('something')

    {edit: it is MySQL}
    LVL 11

    Assisted Solution

    > This is way too broad to answer in my view.
    Agreed.  Perhaps the asker is wanting a way-too-broad answer.  Allow me:

        Yes, it is possible.

    Comforting, ain't it?
    Sorry, I wasn't able to go into detail with column names, etc, because I don't know them.
    Glad to be of service.
    Please don't give me all the points.  Portlet deserves some, too.
    LVL 107

    Expert Comment

    by:Ray Paseur
    When it comes to "search" Google kind of owns the space.  They can build a custom search engine for your site.

    I have used Atomz, Freefind, and Wrensoft "Zoom" search with good results, too.  My point here is that you do not need to go back in time a dozen years to reinvent the future of web search as it was back then, before modern technology took over.  Just use the inventions of others!  Many are free and open source and all of them have at least a dozen years (and hundreds of millions of dollars) head-start on anything you can begin building today.
    LVL 6

    Author Comment

    by:M. Jayme Nagy

    Thanks for your responses

    1) i have used zoom before but i always have to update it and adjust some of the settings which i do not like.
    2) i have looked into google but i was unable to  update the styling  so i did not like it

    i just want to know how i would select multiple tables:

    SELECT * FROM table1, table2... WHERE column1, column2, column3 LIKE 'something%' OR field2 LIKE 'something%'
    LVL 107

    Assisted Solution

    by:Ray Paseur
    I think you're pretty close with the SELECT you posted.  Have you tried that yet?  Maybe this variant would be more on point.  You may have to disambiguate the table+column combinations if there are columns in two tables with the same names.

    SELECT column1, column2
    FROM table1, table2
    WHERE column1 LIKE 'something%'
    OR    column2 LIKE 'something%'
    OR    column3 LIKE 'something%'
    OR    field2  LIKE 'something%'
    LVL 11

    Assisted Solution

    ...and you may get more output row combinations than you want, unless you add a condition that links the tables by foreign keys (if applicable), e.g.
        SELECT column1, column2
        FROM table1, table2, table3
   = table2.table1_id AND
   = table3.table1_id AND
            (column1 LIKE 'something%' OR
            column2 LIKE 'something%' OR
            column3 LIKE 'something%' OR
            field2  LIKE 'something%')
    LVL 107

    Expert Comment

    by:Ray Paseur
    @tel2: Good point.  A GROUP BY clause may be helpful, too.
    LVL 6

    Author Comment

    by:M. Jayme Nagy

    So i made the following SQL
    SELECT * FROM blog, bucket_list, destinations, drinks, events, games, help, music, news, pages, photos
    	    WHERE  status='active' AND (
    		title LIKE (:title) OR image LIKE (:image) OR photographer LIKE (:photo) OR date LIKE (:date) OR
    		author LIKE (:author) OR content LIKE (:content) OR tags LIKE (:tags) OR tagline LIKE (:tagline) OR 
    		category LIKE (:cat) OR location LIKE (:loc) OR city LIKE (:city) OR country LIKE (:country) OR latitude LIKE (:lat) OR
    		longitude LIKE (:long) OR start_date LIKE (:start) OR fin_date LIKE (:end) OR ingredients LIKE (:item) OR
    		difficulty LIKE (:bitch) OR occasion LIKE (:occ) OR type LIKE (:type) required_items LIKE (:required) OR
    		url_link LIKE (:url) OR name LIKE (:name) OR genre LIKE (:genre) OR artist LIKE (:artist) OR personal_desc LIKE 
    		(:desc) OR source_uploader LIKE (:uploader) OR source_description LIKE (:src_desc) OR
    		header1 LIKE (:header1) OR header2 LIKE (:header2) OR header3 LIKE (:header3) OR header4 LIKE (:header4) OR
    		header5 LIKE (:header5) OR content_title1 LIKE (:title1) OR content_title2 LIKE (:title2) OR link LIKE (:link) OR
    		trip LIKE (:trip) )

    Open in new window

    and im not yielding any results

        [:title] => %$search_item%
        [:image] => %$search_item%
        [:photo] => %$search_item%
        [:date] => %$search_item%
        [:author] => %$search_item%
        [:content] => %$search_item%
        [:tags] => %$search_item%
        [:tagline] => %$search_item%
        [:cat] => %$search_item%
        [:loc] => %$search_item%
        [:city] => %$search_item%
        [:country] => %$search_item%
        [:lat] => %$search_item%
        [:long] => %$search_item%
        [:start] => %$search_item%
        [:end] => %$search_item%
        [:item] => %$search_item%
        [:bitch] => %$search_item%
        [:occ] => %$search_item%
        [:type] => %$search_item%
        [:required] => %$search_item%
        [:url] => %$search_item%
        [:name] => %$search_item%
        [:genre] => %$search_item%
        [:artist] => %$search_item%
        [:desc] => %$search_item%
        [:uploader] => %$search_item%
        [:src_desc] => %$search_item%
        [:header1] => %$search_item%
        [:header2] => %$search_item%
        [:header3] => %$search_item%
        [:header4] => %$search_item%
        [:header5] => %$search_item%
        [:title1] => %$search_item%
        [:title2] => %$search_item%
        [:link] => %$search_item%
        [:trip] => %$search_item%

    Open in new window

    LVL 11

    Assisted Solution

    Hi M. Jayme Nagy,

    I'm not familar with your "(:title)" syntax, as I'm not a PHP expert, but I suggest you start with something very simple, and get that working, and then make it more complex until you get what you want.  Start with something short, that you know should work (i.e. there is data in the table that matches the conditions).

    SELECT * FROM blog, bucket_list, destinations, drinks, events, games, help, music, news, pages, photos
              WHERE status = 'active' AND (
                title LIKE "%something-that-is-in-this-field%" )

    When you get that working, try using your variable, like this:

    SELECT * FROM blog, bucket_list, destinations, drinks, events, games, help, music, news, pages, photos
              WHERE status = 'active' AND (
                title LIKE (:title) )

    If that works, expand it further.

    And I suggest you use this "don't try to run until you can crawl" method for the rest of your life.  Well, you can try to run first, but if you fall over (i.e. the code falls over), then go back to crawling for a while, again.
    LVL 107

    Accepted Solution

    Completely agree with @tel2.  Start with a simple (as in "Drop-dead Simple") example that uses one table and one column. Then begin adding to the problem definition.  You may find that test-driven development is a useful concept.  The article here is not about crafting MySQL queries, but it shows the thought processes and design patterns that professionals follow when confronted by unique and unknown problems.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Suggested Solutions

    Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
    Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
    Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now