Results 1 to 14 of 14
  1. #1
    More Cheesier Than Ever Cheesehead's Avatar
    Join Date
    January 18th, 2005
    Location
    Land of The NFL Champs!
    Posts
    2,942
    Search multiple databases/tables?
    I would like to be able to search multiple tables from a mysql database (or from different databases) using PHP. All tables have the same structure.

    Is this possible? Thanks in advance.
    This World is Not My Home
    We're gonna go inside, we're gonna go outside, inside and outside. . . And then we're gonna go go go and we're not gonna stop til we get across that goalline! Quotes from the movie Rudy, 1993

  2. #2
    Full Member
    Join Date
    October 22nd, 2006
    Posts
    200
    Selecting data from 2 different tables within the same database is no problem

    Just use the mysql_query with the different table names

    PHP Code:
    $result1=mysql_query("SELECT * FROM table1"); 
    or
    $result2=mysql_query("SELECT * FROM table2"); 
    Using two different databases you will have to connect to both databases:

    PHP Code:
     $link1 mysql_connect("DB_SERVER""DB_USERNAME","DB_PASSWORD")
       or die(
    "Could not connect : " mysql_error());
      
    mysql_select_db("DB_DATABASE1",$link1);
     
    $link2 mysql_connect("DB_SERVER""DB_USERNAME2","DB_PASSWORD2")
       or die(
    "Could not connect : " mysql_error());
      
    mysql_select_db("DB_DATABASE2",$link2); 
    Then in the query you will have indicate which database you are using

    PHP Code:
    $result1=mysql_query("SELECT * FROM table1",$link1); 
    or
    $result2=mysql_query("SELECT * FROM table2",$link2); 

  3. #3
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    If they all have the same structure, it would probably be better to have them all in the same table with an extra column for whatever is unique about them. For instance, if the tables are products from datafeeds and you're thinking about a separate table for each merchant, just create a single product table and add a merchant field.

    If you're trying to combine data from multiple, normalized tabled, you'll want to look at the JOIN command.
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  4. #4
    More Cheesier Than Ever Cheesehead's Avatar
    Join Date
    January 18th, 2005
    Location
    Land of The NFL Champs!
    Posts
    2,942
    Thanks guys! It sounds like there are several good options.
    This World is Not My Home
    We're gonna go inside, we're gonna go outside, inside and outside. . . And then we're gonna go go go and we're not gonna stop til we get across that goalline! Quotes from the movie Rudy, 1993

  5. #5
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    I agree with Michael. It's definitely best to put all the data in a single table. You could write a PHP script that will combine all your tables into a single table. This script can execute nightly after all of your individual tables are updated. That way your front end isn't affected by your back end updates. I keep my raw datafeed data separate from my front end, searchable, optimized data.

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  6. #6
    More Cheesier Than Ever Cheesehead's Avatar
    Join Date
    January 18th, 2005
    Location
    Land of The NFL Champs!
    Posts
    2,942
    [QUOTE=SnibThat way your front end isn't affected by your back end updates. I keep my raw datafeed data separate from my front end, searchable, optimized data.

    - Scott[/QUOTE]

    So the raw datafeed is the "back end"? Is the "front end" another datafeed table that has been optimized?

    How could your "front end" be affected by some other type of update?

    I assume you use some sort of cron job to automatically revise the large table made up of other tables?
    This World is Not My Home
    We're gonna go inside, we're gonna go outside, inside and outside. . . And then we're gonna go go go and we're not gonna stop til we get across that goalline! Quotes from the movie Rudy, 1993

  7. #7
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    Quote Originally Posted by Cheesehead
    So the raw datafeed is the "back end"? Is the "front end" another datafeed table that has been optimized?

    How could your "front end" be affected by some other type of update?

    I assume you use some sort of cron job to automatically revise the large table made up of other tables?
    I've actually got two tables. One table holds every product from every datafeed while the other holds every in-stock, unique product. So the raw datafeed table might have 20 rows for a particular product from 20 different merchants, but the front end table only has one row for that product because it's only one unique product. Then I use the raw datafeed table to show all the prices from all the merchants when a user views that particular product.

    The raw datafeed table is optimized for product imports while the front end table is optimized for product searching and browsing. And yes I've got a number of crontab scripts running to keep everything current.

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  8. #8
    More Cheesier Than Ever Cheesehead's Avatar
    Join Date
    January 18th, 2005
    Location
    Land of The NFL Champs!
    Posts
    2,942
    Thanks Scott! Slowly, this is all starting to make sense. Slowly.

    I have a ways to go.
    This World is Not My Home
    We're gonna go inside, we're gonna go outside, inside and outside. . . And then we're gonna go go go and we're not gonna stop til we get across that goalline! Quotes from the movie Rudy, 1993

  9. #9
    Action Jackson - King of the World
    Join Date
    January 18th, 2005
    Posts
    2,201
    The problem for me is if I use one table only then it kills my server load LOL

  10. #10
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    Quote Originally Posted by jackson992
    The problem for me is if I use one table only then it kills my server load LOL
    It shouldn't if you index your tables properly. A properly indexed table should be able to handle upwards of 1 million rows with at least 1gb of ram.

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  11. #11
    Action Jackson - King of the World
    Join Date
    January 18th, 2005
    Posts
    2,201
    I have indexes on both the name and category fields, the two fields I usually call up the most

  12. #12
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    A MySQL database, if properly configured, indexed, and designed, can handle a tremendous amount of data and queries quite quickly. I have multiple tables with millions of records, and usually have hundreds of database accesses per second, all without the load being excessive.
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  13. #13
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    Quote Originally Posted by jackson992
    I have indexes on both the name and category fields, the two fields I usually call up the most
    If you're experiencing high loads because of a large number or rows, you're most likely missing some necessary indexes.

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  14. #14
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    Are you experiencing high loads from database queries, or database updates. Indexes help queries, but updates are a different beast altogether.
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  15. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Where, oh where did my databases go......
    By bettylou in forum Midnight Cafe'
    Replies: 16
    Last Post: October 7th, 2008, 08:32 PM
  2. 50% off in all databases
    By pachecus in forum Midnight Cafe'
    Replies: 1
    Last Post: June 12th, 2006, 10:54 AM
  3. Search and multiple sub categories
    By JoyUnltd in forum GoldenCAN
    Replies: 2
    Last Post: November 26th, 2005, 12:28 PM
  4. 2 databases on one page
    By Gordon in forum Programming / Datafeeds / Tools
    Replies: 6
    Last Post: July 25th, 2005, 09:15 PM
  5. Query Multiple tables... help!!
    By Doug247 in forum Programming / Datafeeds / Tools
    Replies: 1
    Last Post: June 4th, 2004, 04:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •