Using JOIN to display data in a table

Question

Thanks for reading my question

i am trying to make *clients_id* from the table repair_jobs appear as the name from the table contacts

but i am having no luck i have got 2 sql query's is this wrong?

the 1st

$query = "select * from repair_jobs";

this helps me display the information i need regarding the fields from repair_jobs and works

this is the 2nd

$query = "SELECT repair_jobs.client_id, contacts.name
FROM repair_jobs
INNER JOIN contacts
ON repair_jobs.client_id=contacts.name";

under that i have this to try to display the name of the client

echo "<td>{$client_id}</td>";

but it is only displaying the number and not the data (clients name) that i need

am i missing something?

Additional information

The client_id (repair_jobs) is a number and is the same as id (contacts) but wanting to display the name (contacts)

CLIENTS

Id – name – surname – phone – address

REPAIRS

Id – clients_id (same as id in clients) – unit – date – price

current code

<?php
//include database connection
include 'db_connect.php';

//query all records from the database
$query = "select * from repair_jobs";

//execute the query
$result = $mysqli->query( $query );

//get number of rows returned
$num_results = $result->num_rows;

//this will link us to our add.php to create new record
if( $num_results > 0){ //it means there's already a database record

    //start table
    //creating our table heading
    echo " <table class='table_basic'>";
    echo "<thead><tr>";
        echo "<th>Job #</th>";
        echo "<th>Name Of Unit</th>";
        echo "<th>Client</th>";
        echo "<th>Estimated Value</th>";
    echo "</thead></tr><tbody><tr>";

    //loop to show each records
    while( $row = $result->fetch_assoc() ){
            //extract row
            //this will make $row['firstname'] to
            //just $firstname only
            extract($row);

            //creating new table row per record
            echo "<tr>";
                echo "<td width='40px'><a href='rdetails.php?id={$id}'># {$id}</a></td>";
                echo "<td>{$rmake} {$rmodel}</td>";

$query = "SELECT rj.client_id, c.name AS client_name FROM repair_jobs rj INNER JOIN contacts c ON rj.client_id=c.id";
echo "<td>{$client_name}</td>";

echo '<td align="center"><span class="badge badge-success">£';
$lhours = $labour;
$repaircosts = $ourcosts;
$labourpay = $labourcharge;
$sum_total = $repaircosts +($lhours * $labourpay);




print ($sum_total);
echo '</span></td>';
echo "</td>";
echo "";
    }

    echo "</tr></table>";//end table

}else{
    //if database table is empty
    echo "No records found.";
}

//disconnect from database
$result->free();
$mysqli->close();

?>

We'll need to see the schema for the tables. Does contacts.name actually equal to repair_jobs.client_id? Or is there a contacts.id that might be more appropriate? — DevlshOne
– DevlshOne, Commented Aug 25, 2013 at 15:01
the two rows are named client_id (in the repair_jobs table) and name (in the contacts table) — Rick Skeels
– Rick Skeels, Commented Aug 25, 2013 at 15:03
That was not the question. Is this client_id actually an ID, or is it the name of the contact? Is this contacts.name and ID, or is it the name of the contact? If the answer to both questions isn't the same, you're doing something wrong by joining on the wrong criteria. And if they are the same, you're still doing something wrong because your naming scheme sucks. — Jeroen van den Broek
– Jeroen van den Broek, Commented Aug 25, 2013 at 15:07
sorry client_id is a number and name is the name of the client — Rick Skeels
– Rick Skeels, Commented Aug 25, 2013 at 15:10
i have updated my question with additional information of what you might be asking — Rick Skeels
– Rick Skeels, Commented Aug 25, 2013 at 15:16

Sean · Accepted Answer · 2013-08-25 15:33:58Z

2

Change your 1st query to you join query, as there is no reason to do a 2nd query in the middle of your code. (also you never executed that query anyway).

//query all records from the database
$query = "SELECT repair_jobs.*, contacts.name as client_name
FROM repair_jobs
INNER JOIN contacts
ON repair_jobs.client_id=contacts.id";

Then in your table keep the $client_name

echo "<td>{$client_name}</td>";

edited Aug 25, 2013 at 15:33

answered Aug 25, 2013 at 15:23

Sean

12.4k3 gold badges31 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DevlshOne Over a year ago

He stated he wanted the client name to appear there. **but it is only displaying the number and not the data (clients name) that i need **

Sean Over a year ago

See my updated answer. Just use your join query as your 1st query.

DevlshOne · Accepted Answer · 2013-08-25 15:46:38Z

1

<?php
include 'db_connect.php';
$query = "SELECT rj.Id AS job_number, rj.unit, rj.make, rj.model, c.name AS client_name, rj.price FROM repair_jobs rj INNER JOIN contacts c ON rj.clients_id = c.id ORDER BY c.date";
$result = $mysqli->query( $query );
$num_results = $result->num_rows;
if( $num_results > 0){ //it means there's already a database record
    echo " <table class='table_basic'>";
    echo "<thead><tr>";
    echo "<th>Job #</th>";
    echo "<th>Name Of Unit</th>";
    echo "<th>Client</th>";
    echo "<th>Estimated Value</th>";
    echo "</tr></thead><tbody>";
    while( $row = $result->fetch_assoc() ){
        extract($row);
        echo "<tr>";
        echo "<td width='40px'><a href='rdetails.php?id={$job_number}'>#{$job_number}</a></td>";
        echo "<td>{$make} {$model}</td>";
        echo "<td>{$client_name}</td>";
        echo "<td align='center'><span class='badge badge-success'>£";
        $lhours = $labour;
        $repaircosts = $ourcosts;
        $labourpay = $labourcharge;
        $sum_total = $repaircosts +($lhours * $labourpay);
        echo $sum_total;
        echo '</span></td>';
        echo "</td>";
        echo "</tr>";
    }
    echo "</tbody></table>";
} else {
    echo "No records found.";
}
$result->free();
$mysqli->close();
?>

edited Aug 25, 2013 at 15:46

answered Aug 25, 2013 at 15:05

DevlshOne

8,4671 gold badge31 silver badges38 bronze badges

4 Comments

DevlshOne Over a year ago

What are you using to fetch the rows?

Rick Skeels Over a year ago

thx for your help but now i am getting syntax error on that code

Sean Over a year ago

@DevlshOne The OP is using mysqli object oriented style, so it does not make sense to use mysql procedural style code/examples

DevlshOne Over a year ago

Here's an all new set of code for you... I've never used extract, so you'll have to double check me on those. And the rmake and rmodel fields were never mentioned in your repair_jobs table before. So, it may need some tweaking but it is error free.

Collectives™ on Stack Overflow

Using JOIN to display data in a table

Additional information

current code

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Additional information

current code

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related