Wrong database query?

Question

I'm learning and atm writing API with PHP and MySql. It's about tasks and tags exercise. I have this problem - when i read the database where task and tags are connected,JSON shows all data but + id of the tags which i don't need, because are already on the json data.

This is the read method with query.

function read(){

    // select all query
    $query = "SELECT * FROM `task_tag`
    INNER JOIN task ON task_tag.id_task =  task.id
    INNER JOIN tag ON task_tag.id_tag  = tag.id";

    // prepare query statement
    $stmt = $this->conn->prepare($query);

    // execute query
    $stmt->execute();

    return $stmt;
    
}

This is where i call data

// products array
$tasks_arr["task_tag"]=array();
$index = 0;
// retrieve our table contents
// fetch() is faster than fetchAll()
// http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){

    $tasks_arr[$index] = $row;
    $index++;
}

// set response code - 200 OK
http_response_code(200);

// show products data in json format
echo json_encode($tasks_arr);
}

This is only 1 data i'll show. The others have the same id which i don't know where it comes

"task_tag": \[\],
"0": {
**"id": "2",** // This is the id that match "id_tag" in every data showen.
"id_task": "5",
"id_tag": "2",
"task_name": "Buy beer",
"tag_name": "Alcohol drinks",
"color": "#FA8072"
},

Answer 1

You have a column id in more than one table. Your query fetches all of these columns, because you used SELECT *.

An SQL result can have more than one column of the same name. That's no problem.

But a PHP associative array can only store one value for a given key.

As PDO maps the query result into an associative array, it overwrites the id key in the associative array with the id column of the tag table. This is arbitrary, it could have picked one of the other tables, but in this case it happened to pick the tag table.

If you want to fix this, don't use SELECT *. Instead, name each of the columns you want the query to return. If a given column name appears in more than one table, give it an alias so the PDO query result can map it into a distinct key in the associative array.