How to Pass Value of a Variable in a MySQL Query

Hi @mwambembela,

Your query should look something like this:

SELECT first_name, last_name, phone_mobile, postcode, trade_type FROM customer WHERE (area_id = '$value_id') AND (trade_id = '$value_tradeid')

Let us know if you have more questions!

Hi @kimmyx i tried my below codes as you explained in your above codes but too bad it did not work out.

Here is my query below
SELECT P.ID, MAX(IF(PM.meta_key = 'first_name', PM.meta_value, NULL)) AS Name, MAX(IF(PM.meta_key = 'last_name', PM.meta_value, NULL)) AS Last, MAX(IF(PM.meta_key = 'Gender', PM.meta_value, NULL)) AS Gender, MAX(IF(PM.meta_key = 'Department', PM.meta_value, NULL)) AS Department, MAX(IF(PM.meta_key = 'position_title', PM.meta_value, NULL)) AS Dpt, MAX(IF(PM.meta_key = 'region', PM.meta_value, NULL)) AS Region, MAX(IF(PM.meta_key = 'district_dar', PM.meta_value, NULL)) AS District, MAX(IF(PM.meta_key = 'status_av', PM.meta_value, NULL)) AS Status FROM wp_users AS P LEFT JOIN wp_usermeta AS PM on PM.user_id = P.ID WHERE P.ID in (select user_id from wp_usermeta where meta_key='Department' and (meta_value = '$dept')) and P.ID in (select user_id from wp_usermeta where meta_key='status_av' and meta_value = 'on') group by P.ID

Variable name is $dept in meta value below.

And this is how i declared it within function.php so it can be accessible on the site.

global $current_user;
      get_currentuserinfo();
      $dept = $current_user->Department;
      

Where do you think i’m doing wrong??
$dept value from functions is supposed to be used in query when listing datas.

Thanks.

• This reply was modified 2 years ago by mwambembela.
• This reply was modified 2 years ago by mwambembela.
• This reply was modified 2 years ago by mwambembela.

Hi @mwambembela,

It is not possible to use a PHP variable like $dept directly in a query. Your variable $dept is available in PHP but not in the database where your query is executed.

There are a number of ways to solve this issue. One way is to make your variable available in the database as a session variable. In that case your variable name will change to @dept. You can find an example on this page:
https://wpdataaccess.com/2022/08/18/wordpress-user-id-in-sql/
See section: Need more WordPress values in SQL?

Another way would be to use a shortcode parameter. This features is explained here:
https://wpdataaccess.com/docs/data-publisher/static-filters/
Please be aware that you need to create a view for your query to use static filters. This kind of filter requires you to provide the column name for the filter to be added.

You can also use URL parameter as explained here:
https://wpdataaccess.com/docs/data-publisher/url-parameters/
This features also requires you to save your query as a view.

Does this help?

Thanks,
Peter

Thanks @peterschulznl let me get into it and see how it will come out

Hi @peterschulznl here I am again

I tried to use Session Variable Method,
So in my database, I have a table named wp_users, I executed this query select * from wp_users it listed all table columns with their value, then I added the WHERE Clause the query read like this select * from wp_users where ID = @wpda_wp_user_id but it’s strange when I add the WHERE Clause no any data is output as seen in the picture I attached in the link below. Where might be the problem? I thought maybe it’s a WordPress naming scheme so I tried to change this @wpda_wp_user_id to @wp_wpda_user_id but nothing changed.

https://prnt.sc/hL9isdtXtumZ

Looking forward to hearing from you.

And This is how i assigned Department Value to @$dept Session Variable, but even if i use the Session Variable @$dept query it return nothing.

add_action(
	'wpda_dbinit',
	function( $wpdadb ) {
		if ( null !== $wpdadb ) {
			$suppress_errors = $wpdadb->suppress_errors( true );
			$current_user    = wp_get_current_user();
			$wpdadb->query( 'set @dept = "' . $current_user->Department . '"' );
			$wpdadb->suppress_errors( $suppress_errors );
		}
	},
	10,
	1
);

Hi @mwambembela,

What response do you get in the Query Builder for these queries:
select * from wp_users where ID = @wpda_wp_user_id
select @wpda_wp_user_id

The $current_user object does not contain a Department variable. So your action will not work as $current_user->Department does not exist. In your case you need to get that value from another source.

Maybe you should fix the first issue first. What DBMS version are you on?

Thanks,
Peter

The response I get when I run this query

select * from wp_users where ID = @wpda_wp_user_id
select @wpda_wp_user_id

in Query Builder, it says ERROR: Invalid query.

If I need to get the value of $current_user from another source how can accomplish that please advise.

I’m using localhost with MySQL version 5.2 if I’m not wrong.

Hi @mwambembela,

MySQL 5.2 is very old! Is it possible for you to update? I’m not sure if version 5.2 supports session variables? Can anybody reading this confirm? Maybe my database friends from Canada…? ??

Thanks,
Peter

LOL

Let me try to update it.

Hi @mwambembela,

Cool! I think session variables are available from version 5.6.

Let us know how it goes after you’ve done the update. ??

I can’t find 5.2 documentation. Switch to 5.7 or 8.0, for your own sanity.