Multiple left joins, how to output in php

Question

I have 3 tables I need to join. The contracts table is the main table, the 'jobs' and 'companies' table are extra info that can be associated to the contracts table.

so, since I want all entries from my 'contracts' table, and the 'jobs' and 'companies' data only if it exists, I wrote the query like this....

$sql = "SELECT * FROM contracts
        LEFT JOIN jobs ON contracts.job_id = jobs.id
        LEFT JOIN companies ON contracts.company_id = companies.id
        ORDER BY contracts.end_date";

Now how would I output this in PHP? I tried this but kept getting an undefined error "Notice: Undefined index: contracts.id"...

$sql_result = mysql_query($sql,$connection) or die ("Fail.");

if(mysql_num_rows($sql_result) > 0){

    while($row = mysql_fetch_array($sql_result))
    {
       $contract_id = stripslashes($row['contracts.id']);
       $job_number = stripslashes($row['jobs.job_number']);
       $company_name = stripslashes($row['companies.name']);
    ?>
        <tr id="<?=$contract_id?>">
           <td><?=$job_number?></td>
           <td><?=$company_name?></td>
        </tr>
    <?  
    }
}else{
    echo "No records found";
}

Any help is appreciated.

Answer 1

The column names will not be prefixed like this - and with each table having a column called "id" you could be in trouble. You should explicitly identify the columns you want returned rather than using "select *", and you then just retrieve the column by name un prefixed (e.g. $row['job_number']).

Answer 2

The below would solve you problem.

$sql = "SELECT contracts.id AS contract_id, jobs.job_number, companies.name FROM contracts
        LEFT JOIN jobs ON contracts.job_id = jobs.id
        LEFT JOIN companies ON contracts.company_id = companies.id
        ORDER BY contracts.end_date";

Your problem is likely to be realted to the fact you are using two tables with the field id this is why you should select them as an alias using the mysql as clause.

You may also want to look into using a naming convention for your fields and sticking with it. For example, check out the theory of Hungarian Notation, this would stop issues like this from arrissing.