How to resolve ambiguous column names when retrieving results?

You can set aliases for the columns that you are selecting:

$query = 'SELECT news.id AS newsId, user.id AS userId, [OTHER FIELDS HERE] FROM news JOIN users ON news.user = user.id'

You can either use the numerical indices ($row[0]) or better, use AS in the MySQL:

SELECT *, user.id AS user_id FROM ...

You can do something like

SELECT news.id as news_id, user.id as user_id ....

And then $row['news_id'] will be the news id and $row['user_id'] will be the user id

When using PDO for a database interacttion, one can use the PDO::FETCH_NAMED fetch mode that could help to resolve the issue:

$sql = "SELECT * FROM news JOIN users ON news.user = user.id";
$data = $pdo->query($sql)->fetchAll(PDO::FETCH_NAMED);
foreach ($data as $row) {
    echo $row['column-name'][0]; // from the news table
    echo $row['column-name'][1]; // from the users table
    echo $row['other-column']; // any unique column name
}

If you don't feel like aliassing you can also just prefix the tablenames.

This way you can better automate generation of your queries. Also, it's a best-practice to not use select * (it is obviously slower than just selecting the fields you need Furthermore, only explicitly name the fields you want to have.

SELECT
    news.id, news.title, news.author, news.posted, 
    users.id, users.name, users.registered 
FROM 
    news 
LEFT JOIN 
    users 
ON 
    news.user = user.id

Here's an answer to the above, that's both simple and also works with JSON results being returned. While the SQL query will automatically prefix table names to each instance of identical field names when you use SELECT *, JSON encoding of the result to send back to the webpage, ignores the values of those fields with a duplicate name and instead returns a NULL value.

Precisely what it does is include the first instance of the duplicated field name, but makes its value NULL. And the second instance of the field name (in the other table) is omitted entirely, both field name and value. But, when you test the query directly on the database (such as using Navicat), all fields are returned in the result set. It's only when you next do JSON encoding of that result, do they have NULL values and subsequent duplicate names are omitted entirely.

So, an easy way to fix that problem is to first do a SELECT *, then follow with aliased fields for the duplicates. Here's an example, where both tables have identically named site_name fields.

SELECT *, w.site_name AS wo_site_name FROM ws_work_orders w JOIN ws_inspections i WHERE w.hma_num NOT IN(SELECT hma_number FROM ws_inspections) ORDER BY CAST(w.hma_num AS UNSIGNED);

Now in the decoded JSON, you can use the field wo_site_name and it has a value. In this case, site names have special characters such as apostrophes and single quotes, hence the encoding when originally saving, and the decoding when using the result from the database.

...decHTMLifEnc(decodeURIComponent( jsArrInspections[x]["wo_site_name"]))

You must always put the * first in the SELECT statement, but after it you can include as many named and aliased columns as you want, as repeatedly selecting a column causes no problem.