Query works in phpmyadmin but not with $wpdb

  • Hello !

    I am trying to retrieve all user meta (in columns) for each users (in rows).

    I build (not without pain) this query that is working in phpmyadmin :

    SET group_concat_max_len=16384;
SET @sql = NULL;
SELECT
  GROUP_CONCAT( DISTINCT
	CONCAT(
	  'MAX( IF ( M.meta_key = ''',
	  meta_key,
	  ''', M.meta_value, NULL)) AS `',
	  meta_key, '`'
	)
  ) INTO @sql
FROM wp_usermeta;

SET @sql =
CONCAT( ' SELECT *, ', @sql,
' FROM wp_users as U, wp_usermeta as M
WHERE U.id = M.user_id
GROUP BY U.id ' );

PREPARE stmt FROM @sql;
EXECUTE stmt;

    But when I execute it with $wpdb->get_results, it returns an empty array.

    Anyone can help ?

Viewing 1 replies (of 1 total)
  • Thread Starter Yoan Cutillas

    (@cuti)

    I have found a workaround, with three queries…

    It seems that wordpress doesn’t supports SQL variables and that group_concat_max_len needs to be set in its own query.

    $wpdb->query( 'SET SESSION group_concat_max_len = 1000000' );

$select_usermeta_columns_query = " SELECT
				GROUP_CONCAT( DISTINCT
				  CONCAT(
					'MAX( IF ( M.meta_key = ''',
					meta_key,
					''', M.meta_value, NULL)) AS `',
					meta_key, '`'
				  )
				) as select_usermeta
				FROM wp_usermeta as M;";

$select_usermeta_columns = $wpdb->get_var( $select_usermeta_columns_query );

$users_info_query =	" SELECT *, " . $select_usermeta_columns . "
			FROM wp_users as U, wp_usermeta as M
			WHERE U.id = M.user_id
			GROUP BY U.id ; ";

$users_info = $wpdb->get_results( $users_info_query, OBJECT );

    But I don’t like this solution because it uses 3 queries instead of 1, and I need to pass $select_usermeta_columns directly in the query ($wpdb->prepare doesn’t work for column names).

    Still looking for proper solutions ??

Viewing 1 replies (of 1 total)
  • The topic ‘Query works in phpmyadmin but not with $wpdb’ is closed to new replies.