Make WordPress Core

Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#42304 closed defect (bug) (invalid)

Database joins with like field names

Reported by: vinlandmedia's profile 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
7 years 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
7 years 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
7 years 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
7 years 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
7 years ago

  • Milestone Awaiting Review deleted

Glad you got it fixed.

Note: See TracTickets for help on using tickets.