在一对多关系中获取字符串值而不是外部id

Question

I have a query that will display the fields/values of a table that is related to other table.Using foreach loop, I can display those fields/values in html.But I want to display the the 'foreign_name' instead of 'foreign_id'.

<?php foreach ($table_related as $table): ?>
<?php echo $table['id']; ?>
<?php echo $table['foreign_id']; ?>//the foreign key, it should be foreign_name
<?php echo $table['name'];?>
<?php endforeach; ?>

//classeManageService.php

function showAllServices()
{

    $query = $this->link->query("SELECT * FROM services ORDER BY id DESC");

    $rowcount = $query->rowCount();

    if($rowcount > 0)
    {
        $result = $query->fetchAll();
        return $result;
    }    
    else
    {
        return $rowcount;
    }
    return $result; 
 }

//show_all.php

<?php
include_once('../../classes/class.ManageServices.php');

$init = new ManageServices();
    $show_all_services = $init->showAllServices();

?>

'my_table' has columns id,my_name,desc,foreign_id and 'other_table' has columns foreign_id,foreign_name,foreign_desc

I can display all those values in html.But how about displaying the other fields instead of its id from the other table?Do I have to change my query?Any Ideas?

Answer 1

You have to do a query with a JOIN:

Since you didn't provide the other table name, please consider otherTable as it, and service_id as foreign key.

Query example:

$query = $this->link->query("SELECT * FROM services s, otherTable o  WHERE s.id = o.service_id ORDER BY s.id DESC");

now in the result set you will have also all the fields of otherTable from which you will be able to get the name as desired.