Inner Join in PHP with email field function

[Tina_5]

This is a bit complex. I have two tables bus_status and routes
routes has a foriegn key look up from bus_status called UnitAssigned in routes table and it is pulled from the BusNumber field in the bus_status table.

Right now when I send an email and include the field in the message data it only provide the Bus number like this:

Bus Number: 0114

This is actually the record ID number of this bus number and not the real bus number which is AAOH_221

I need it to show the actual. When I am creating reports I do this with an inner join.. but I am not sure how to do this withing the code for the message below..

    function routes_after_update($data, $memberInfo, &$args){
$fields_to_send = array('UnitAssigned', 'RouteNo', 'DriverAssigned', 'DriverAssignedpm', 'County', 'Monitor', 'Notes', 'SubAssigned1', 'SubAssigned2',);
foreach($fields_to_send as $field) {
    $messageData2 .= "$field: {$data[$field]} \n";
}

Any Suggestions?

Thanks, Tina

[iamthwee]

If you're unsure of writing joins in SQL use an SQL builder.

Top of my list is still SQLeo which is java based. If you make a small donation you get the full thing, you only need to donate a dollar.

Others are navicat but they have a trial not sure if it is only available to windows either.

[Tina_5]

I actually do fine in sql.. i write reports all the time.. just need the info for this particular way to write it.. not like a normal query..
Just not sure where to put the query lets say.. I am interested in sqleo.. I will certainly check it out.. I love time savers.. any suggestions on this question?
Thanks, T

[iamthwee]

You do realise you have not written any sql in that above example you have posted. It might be an idea to one, tell us your db structure and what you are expecting from your output query. This would make it easier to provide a solution.

BTW writing the reports (crystal reports) is much different from writing hand written sql. Drag and drop linking tables conveniently hides complex SQL.