$wpdb->get_results not behaving as expected with JOIN query

  • Hi everyone. I have a custom SQL query of custom tables in a custom WP plugin which was working fine under the old mysql_query() functions and works if entered under phpMyAdmin but now I’ve converted it to use $wpdb no longer works as expected.

    The query is a preset CSV download of 2 tables, (it’s actually a list of artists and their exhibition venues, which may or may not be known) :

    $SQL = "SELECT	a.forename, a.surname, a.address_1,
                a.address_2, a.address_3,
	        a.postcode, a.phone, a.phone2, a.email, a.website_url,
        	v.address_1, v.address_2, v.address_3, v.postcode,
	        a.wbat_artist_id, a.reg_date
                FROM wbat_artist a
                LEFT JOIN wbat_venue v ON ( a.venue_id = v.venue_id )
WHERE a.is_current_exhibitor = 1
ORDER BY reg_date, wbat_artist_id";

$results = $wpdb->get_results( $SQL , ARRAY_N);
$i = 16;
foreach ($results as $row)
{
    for ($j=0;$j<$i;$j++)
    {
	$csv_output .= '"'.str_replace('"','',stripslashes($row[$j])).'",';
    }
}

    The $wpdb->get_results method doesn’t include columns from the second table (aliased ‘v’) even where they do exist, but issues notices of undefined indexes 12,13,14,15.

    Is there something wrong with the equality operator in the JOIN part? It’s as though it never comes up true, though both columns are int(11)s and the condition is met for most of the rows. I have read in other posts of ‘limitations’ with $wpdb on custom queries but no clarification of what these limitations are.

    Thanks for any help.

Viewing 2 replies - 1 through 2 (of 2 total)
  • Moderator Marius L. J.

    (@clorith)

    Hi,

    Using $wpdb->get_results() is essentially the same as running a mysql_query directly followed by a mysql_fetch_object() so that shouldn’t be a factor here.

    Do you think you’d be able to set up a demo schema along with your query at https://sqlfiddle.com/ for us, as it’s a bit hard to spot SQL errors without having the schemas and some test data to work with?

    Thread Starter vyvyanhs

    (@vyvyanhs)

    Thanks, Marius, for your interest, but I have now solved (or rather side-stepped) the problem by using mysqli_query() for this particular function. I do think there may be a problem with the wpdb code as I have not had to change the SQL at all, and it was working perfectly OK for over a year under the now-deprecated mysql_query() and now works with mysqli_query().

    The db schema is very simple, representing artists in an annual art trail who may exhibit at just one venue, which could be their own home, or elsewhere, or they don’t yet know the venue yet. Further simplified it is:

    //venue

venue_id int (primary key)
address_1 varchar
address_2 varchar
address_3 varchar
[... other text fields ...]

//artist

artist_id int            // primary key
venue_id  int            // may be null
name      varchar
email     varchar
is_current_exhibitor int //flag to show they're taking part
reg_date  date           //date the artist signed up)
[... other text fields ...]

    The query delivers a list of all artists who are taking part this year together with the address of the venue they’re using, if known, as a CSV download.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘$wpdb->get_results not behaving as expected with JOIN query’ is closed to new replies.