Shortcode with Where clause default value

Hi @lbtdev

Sorry, the where param cannot be wrapped in any form of “if” logic.
You’ll have to go with PHP templates in order to do this.

Cheers, Jory

Hi Jory. Thanks for thre quick response. I figured this would be the case, but thought I’d check as the same concept in something like .net where the where clause would read …meta_value = ” would yield all results instead of no results.

Anyway

I have now created a page template for this and it’s running fine, until I add the where clause and then I get a SQL syntax error. If I include t. before the field name then I get an unknown column error, so I think the column name is correct and as far as I can see everything else should be fine. I have tried swicthing the ” and ‘ around as well as using ` but nothing seems to work.

My code is below:

    $var = "S"; // Placeholder for the moment. Replace with querystring val when working
    $params = array(        
		'limit'   => -1, 
		'where'   => 'workshop_category.meta_value?= "' . $var . '"' //Works with this line taken out
	);

    $PWpod = pods('primary-workshop',$params);

    if ( $PWpod->total() > 0 ) {...

This works when I take out the where clause (S is a known valid value). I’ve looked through several posts and tried several different itterations of this including with and with out the ‘t.’ and using LIKE but I still get the same error.

My shortcode this is dervied from is: [pods name=”primary-workshop” template=”Primary Workshops” where=”workshop_category.meta_value = ‘{@get.STEMCat}'”]

Please advise.

Hello @lbtdev

Just to be sure, since you named the field workshop_category. Is it an actual category (taxonomy) or a plain meta value?

Cheers, Jory

It’s a plain meta value. Setup as a field in the POD:
Field Type: Relationship
Related To: Simple
Custom Defined Types: S|Science
T|Technology
E|Engineering
M|Maths

It’s a singular dropdown list and I am using the value to evaluate against the querystring (?STEMCat=x)

if it helps, my updated code is:

	$selectedCat = $_GET["STEMCat"];

	if($selectedCat){
		$params = array(        
			'where'   => "workshop_category.meta_value?= '" . $selectedCat . "'",
			'limit'   => -1, 
		);
	} else {
		$params = array(        
			'limit'   => -1, 
		);
	}

    $PWpod = pods('primary-workshop')->find($params);

Thanks in advance

Hi @lbtdev
I might be missing something but I’d say that should work.
Could you check the SQL by adding ?pods_debug_sql=1 to your URL.
Cheers, Jory

@keraweb thank you. Yes, I can’t see any issue with it and I’ve tried several different itterations with no success.

The debug trace came back with:

Database Error; SQL: SELECT DISTINCT t.* FROM vrw_posts AS t LEFT JOIN vrw_postmeta AS workshop_category ON workshop_category.meta_key = ‘workshop_category’ AND workshop_category.post_id = t.ID WHERE ( ( workshop_category.meta_value = ‘E’ ) AND ( t.post_type = “primary-workshop” ) AND ( t.post_status IN ( “publish” ) ) ) ORDER BY t.menu_order, t.post_title, t.post_date; Response: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ = ‘E’ ) AND ( t.post_type = “primary-workshop” ) AND ( t.post_status I’ at line 11

Adding t. before workshop_category give me an unknown coulmn error. So it looks as though I’m looking in the right place. I have tried escaping the string via \ as well as using the PHP functions to escape the strings, I have also tried string interpolation but always get this syntax error

• This reply was modified 4 years, 4 months ago by Tony Law.

Hi @lbtdev
Correct, the workshop_category shouldn’t be prefixed.
I tested this SQL locally without errors so I think there must be some wrong characters within your SQL where statement.
Are you sure you are using the correct quotes? (WP forum converts these characters so I can’t see). For strings these need to be simple single quotes.
Cheers, Jory

@keraweb Thanks for your help.

I have run this directly against the SQL database and hacked around with the query. Turns out that there are spaces in the built query which my DB version didn’t like. Removing these fixed the issue!

I amended the following params to remove the spaces between and it now works.

	if($selectedCat){
		$params = array(        
			'where' => 'workshop_category.meta_value="' . $_GET["STEMCat"] . '"',
			'limit' => -1, 
		);
	} else {
		$params = array(        
			'limit' => -1, 
		);
	}

Hi @lbtdev

Ah ok! Weird issue, just curious, what type of SQL database are you running and what version?

Cheers, Jory

As far as I know it’s the latest version of MySql.

You can see the SQL info in Pods > Settings to be sure!
I find it such a weird issue that I’d like to verify and reproduce locally.

@keraweb No problem. Yes it does seem very strange!

MySQL Version: 5.7.25