Page 1 of 1

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

Posted: Fri May 29, 2009 12:41 am
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>
        ";
    }
 

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

Posted: Fri May 29, 2009 2:07 am
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.

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

Posted: Fri May 29, 2009 2:30 am
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.

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

Posted: Fri May 29, 2009 2:08 pm
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!