How do you select data from two tables with one query?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
WithHisStripes
Forum Contributor
Posts: 131
Joined: Tue Sep 13, 2005 7:48 pm

How do you select data from two tables with one query?

Post by WithHisStripes »

Heya,
So I pull projects data into my portfolio page from a table called 'project-data'. This has their business name, live site url, feedback, and a few others things. But the clients first and last name are stored in `client-data`. I'm not sure how I write a query that will pull both segments of data for my loop. Can anyone help? Thanks!

Code: Select all

 
    $get_portfolio_data = mysql_query("SELECT * FROM `project-data` JOIN `client-data` WHERE `project-status` = 'published' ORDER BY `date-completed` ASC");
        
    while ($show_portfolio_data = mysql_fetch_array($get_portfolio_data, MYSQL_ASSOC)) {
        echo "
            <table>
                <tr valign='top'>
                    <td><img src='" . $root . "images/project-thumbnails/" . $show_portfolio_data['business-name'] . "-" . $show_portfolio_data['project-name'] . ".png' /></td>
                    <td>
                        <h1 style='text-transform: capitalize;'>" . ereg_replace('-', ' ', $show_portfolio_data['business-name']) . "</h1>
                        <p><span style='color: #333;'>Our Contact:</span> " . $show_portfolio_data['client-first-name'] . " " . $show_portfolio_data['client-last-name'] . "</p>
                        <p><a target='_blank' href='" . $show_portfolio_data['live-site'] . "'>Live Site</a></p>
                        <p><span style='color: #333;'>Their overall rating of us:</span> " . $show_portfolio_data['rating-overall'] . " out of 10</p>
                        <p><span style='color: #333;'>Their overall comments of us:</span> " . $show_portfolio_data['comments-overall'] . "</p>
                    </td>
                </tr>
            </table>
        ";
    }
 
Last edited by Benjamin on Fri May 29, 2009 10:28 am, edited 1 time in total.
Reason: Changed code type from text to php.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do you select data from two tables with one query?

Post by requinix »

Almost there.

What you have now JOINs the two tables together on each and every row. In both. If project-data has 100 rows and client-data has 30 then you'll get 3000 rows.

Code: Select all

JOIN table [USING (field) | ON condition]
field can be the name of a field that is present in both tables; condition is some condition (like "project-data.field1 = client-data.field2").

Since you did a SELECT * then you'll get every field from all tables used in the query.
Griven
Forum Contributor
Posts: 165
Joined: Sat May 09, 2009 8:23 pm

Re: How do you select data from two tables with one query?

Post by Griven »

If you're going to do a join, make sure you're using the right type (inner, outer, left, left outer, etc, etc). In addition to that, when joining two tables of related data, you'll probably want to join them using a foreign key.
WithHisStripes
Forum Contributor
Posts: 131
Joined: Tue Sep 13, 2005 7:48 pm

Re: How do you select data from two tables with one query?

Post by WithHisStripes »

Hey tasairis - yeah that was the exact effect I was getting. Things repeating.

When you're talking about fields, my problem is that client-data only holds information about my client, while project-data only holds information about the project. The only similarity is the both use the business-name and client-name. But even those aren't labelled the same (which was my mistake - hindsight is always 20/20 right?)

All I need to get from client-data is the client's first name (client-first-name), last name (client-last-name) and their business name (client-business-name). That has to be assigned to the project where client-business-name = business-name.

So basically, either I don't understand what you were suggesting, haha, or I don't think that is quiet the right solution? Any thoughts? THANKS!
Post Reply