Member Avatar for ElegantElephant

I have a small problem with a MYSQL select query involving an INNER JOIN. Both tables that I'm joining have a field that have identical names. So when I use

$array = mysqli_fetch_array($result,1);
$field = array['fieldname'];

it returns the wrong value from the wrong table.

Is there any way around this?

  • 2 Contributors
  • 3 Replies
  • 119 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by JRM

Dani AI

Generated

The behaviour you saw is normal: when you fetch a row as an associative array, PHP uses column names as keys, so duplicate names from a JOIN will collide and the later column wins. That explains the “wrong” value seen by — the resultset column order (and thus which duplicate overwrites the other) follows how the query expands columns. Swapping table order (as suggested) can change which value wins, but it is brittle and hard to maintain.

A reliable fix is to explicitly list and alias the columns you need so each associative key is unique. For example:

SELECT p.id   AS project_id,
       p.status AS project_status,
       u.id   AS user_id,
       u.status AS user_status
FROM projects p
INNER JOIN users u ON p.user_id = u.id;

With that, fetching an associative row gives distinct keys like project_status and user_status, avoiding overwrites.

Alternatives and cautions:

  • You can mix * with explicit aliases, but still avoid leaving duplicate names — alias them uniquely if you must. See MySQL SELECT/alias rules: SELECT syntax and aliases.
  • Using numeric fetches (numeric indexes) avoids the name collision but is less readable and fragile if column order changes. See PHP fetch docs: mysqli_result::fetch_assoc and mysqli_result::fetch_array.
  • Also double-check you are accessing the correct PHP variable when reading the row (a wrong variable name will give unexpected values).

In short: stop relying on column order, explicitly list/alias fields, and prefer clear, unique names for joined columns.

Recommended Answers

All 3 Replies

Member Avatar for JRM

not really a fix but, how about swapping the order of the tables in the query?
When you have conflicts like that, all kinds of confusion can happen.
change the conflicting name if possible, otherwise do two queries in tandem.

Member Avatar for ElegantElephant

Thanks for your reply, which is the correct order to select the tables in the query?

And also does anyone know if it is possible to combine SELECT * with an AS query like:

SELECT *, project.status AS pstatus

Edited by ElegantElephant because: n/a
Member Avatar for JRM

Thanks for your reply, which is the correct order to select the tables in the query?

And also does anyone know if it is possible to combine SELECT * with an AS query like:

SELECT *, project.status AS pstatus

you never posted the query, but the order depends on the objective and which table needs data from the other to perform the join.

the second question is too vaugue but if :
project = database name and status = table , yes you can alias a table.
the columns are referenced like pstatus.date.
Usually you work omn tables within a database which is already set, so
you just do the alias of the table. i like them short like in the MySql docs.
my_big_table_name AS t1, etc...

Speaking of MySql docs, all of this is there for the reading in much more depth.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.