WordPress.org

Make WordPress Core

Opened 22 months ago

Closed 22 months ago

Last modified 22 months ago

#42304 closed defect (bug) (invalid)

Database joins with like field names

Reported by: vinlandmedia Owned by:
Milestone: Priority: normal
Severity: normal Version: 4.8.2
Component: Database Keywords: close reporter-feedback
Focuses: Cc:

Description

Using custom tables, and in this case I have a parent-child relationship. The following query works fine interactively:

SELECT wp_books.name, wp_chapters.name FROM wp_books, wp_chapters WHERE wp_books.id = wp_chapters.book_id;

In Wordpress (using $wpdb->get_results) the query only returns a single column, the latter (wp_chapters.name).

If I use any other column combination (i.e. not two "name" fields) the query works in Wordpress.

Wondering if this might be a bug.

Change History (5)

#1 @birgire
22 months ago

  • Keywords close added

Thanks for the ticket and welcome to trac.

I just did a little testing and this seems to be the case.

But this isn't specific to WordPress.

It's e.g. mentioned in a comment by user NUNTIUS, in the PHP docs on mysqli_query()

When running joins in SQL you may encounter a problem if you are trying to pull two columns with the same name. mysqli returns the last in the query when called by name. So to get what you need you can use an alias. ..

The workaround mentioned there is to use an alias.

So you could e.g. use:

SELECT wp_books.name AS book_name, wp_chapters.name AS chapter_name FROM ...

#2 @johnbillion
22 months ago

  • Keywords reporter-feedback added

@vinlandmedia Is this query a result of a WP_Query call? Or a manually constructed SQL query?

If it's the latter, then as @birgire said this is something not specific to WordPress. If it's the former, can you let us know the query parameters please?

#3 @vinlandmedia
22 months ago

Thank you for the responses.

@birgire - that seems reasonable, except when I tried an exact copy of the query interactively (using Adminer -- this is from a Local by Flywheel test instance) it worked fine. However, your workaround seems like it would solve my issue, and there is certainly nothing wrong with having more thorough qualifications in the queries.

@johnbillion - this is a new plugin I've been working on, and the SQL is built in the plugin based on criteria. The statement executed in the code is:

$query_results = $wpdb->get_results($mysql_query);

...where $mysql_query is variations based on the previously-posted statement.

I will try adding the "as field_name" suggested by @birgire.

#4 @vinlandmedia
22 months ago

  • Resolution set to invalid
  • Status changed from new to closed

Adding the additional qualification resolved the issue.

Again, thank you both for your responses.

#5 @johnbillion
22 months ago

  • Milestone Awaiting Review deleted

Glad you got it fixed.

Note: See TracTickets for help on using tickets.