从open api导入数据并使用循环结合mysql循环

Question

I'm using an openapi json source that includes id:s that I could possibly convert to a human readable format by using a database in mysql containing the same id:s and a matching names to those id:s.

Openapi communication is handled by a composer package from where it is easy enough to just foreach through the array that then shows all the id's currently in use.

Then I can't figure out a solution to get that mysql part right to transfer all of the id:s for a name, i have thought and tried some double loops but they havent worked. while inside a foreach and the other way around.

Separately I can get things to work out but not combined.

$sql = "SELECT activityName, activityID FROM Activities";
$sqlresult = $conn_->query($sql);
if ($sqlresult->num_rows > 0) {
      while($row = $sqlresult->fetch_assoc()){
                foreach($_jobs as $obj)
                        {
                            echo $obj->activity_id . " Type id: " . $obj->origin_type_id . " Status: " . $obj->status . "<br>";
                        }
}} else {
    echo "0 results";
}

echo json_last_error() . PHP_EOL . "<br>";
echo json_last_error_msg() . PHP_EOL . "<br>";

$conn->close();

Currently it just echoes them openapi results 10 times as is the amount of rows in the mysql database.

5 type id: 1115 Status: active
4 type id: 11891 Status: active
4 type id: 11890 Status: active
5 type id: 1115 Status: active
4 type id: 11891 Status: active
4 type id: 11890 Status: active
5 type id: 1115 Status: active
4 type id: 11891 Status: active
4 type id: 11890 Status: active
5 type id: 1115 Status: active
4 type id: 11891 Status: active
4 type id: 11890 Status: active
5 type id: 1115 Status: active
4 type id: 11891 Status: active
4 type id: 11890 Status: active
5 type id: 1115 Status: active
4 type id: 11891 Status: active
4 type id: 11890 Status: active
5 type id: 1115 Status: active
4 type id: 11891 Status: active
4 type id: 11890 Status: active
5 type id: 1115 Status: active
4 type id: 11891 Status: active
4 type id: 11890 Status: active
5 type id: 1115 Status: active
4 type id: 11891 Status: active
4 type id: 11890 Status: active
5 type id: 1115 Status: active
4 type id: 11891 Status: active
4 type id: 11890 Status: active

I want to be able to use that openapi activity_id to fetch a matching name from the mysql database. Can i maybe put the loops other way around with no errors? Or is the approach wrong all together?

Answer 1

At the moment the code doesn't seem to make any attempt to print the database details. However what it does do, due to the nested loops, is print all the API data again for each database row returned.

You need to separate these two things. First get all the database data out into an associative array. Make the key of the array the activity ID. That way, when you come to print the API data and combine it with the database data, you can simply use the activity ID to reference the correct item in the associative array.

This should do the job (untested):

$activities = array();
$sql = "SELECT activityName, activityID FROM Activities";
$sqlresult = $conn_->query($sql);

if ($sqlresult->num_rows > 0) 
{
  while($row = $sqlresult->fetch_assoc())
  {
    $activities[$row["activityID"]] = $row["activityName"];
  }

  foreach($_jobs as $obj)
  {
    echo $obj->activity_id . " Type id: " . $obj->origin_type_id . " Status: " . $obj->status . " Name: ". $activities[$obj->activity_id] . "<br>";
  }
}
else 
{
  echo "0 results";
}

$conn->close();