A table related to itself / advanced mysql select question
4 posts by 2 authors in: Forums > CMS Builder
Last Post: May 30, 2013 (RSS)
By mizrahi - May 29, 2013 - edited: May 29, 2013
I have a table (portfolio_projects) that consists of a large number of portfolio projects. and I am trying to setup a fields of "Related Projects". this will be a multiselect list of the exact same table so the user can select other projects that area similar. But I want this list to show every project except for the one currently being edited. I am trying to use the advanced mysql query (see below), but it's not filtering out the current project.
SELECT num, CONCAT(client,' / ',title)
FROM `<?php echo $TABLE_PREFIX ?>portfolio_projects`
WHERE hidden=0 AND num!="<?php echo $ESCAPED_FILTER_VALUE['num'] ?>"
I am sure the answer lies in my misuse of "<?php echo $ESCAPED_FILTER_VALUE['num'] ?>" but i am not sure how to resolve this.
Help?
By gregThomas - May 29, 2013
Hi,
This should work:
<?php $numValue = (@$ESCAPED_FILTER_VALUE )? $ESCAPED_FILTER_VALUE : '0'; ?>
SELECT num, CONCAT(client,' / ',title)
FROM `<?php echo $TABLE_PREFIX ?>portfolio_projects`
WHERE hidden = 0 AND num != <?php echo $numValue; ?>
Then below the text area where you entered this there should be a drop down list called Advanced Filter, you need to select num field from the drop down list.
The escaped value will get the current value from the num field. If the value isn't set because you're creating a new record then the num value will be set to zero in the where statement and all records will be shown.
Let me know if you have any questions.
Thanks!
Greg
PHP Programmer - interactivetools.com
By mizrahi - May 29, 2013
Exactly what I needed.
I have a question though, can you explain the first line of your code? I have never seen/used something like the second half of that statement and I am not clear on what it's doing.
thanks.
By gregThomas - May 30, 2013
Glad you've got it working.
The first line of code is a way to create a simplified if statement, if you wrote it using if/else it would look like this:
if(@$ESCAPED_FILTER_VALUE){
$numValue = $ESCAPED_FILTER_VALUE
}else{
$numValue = '0'
}
But it can be shorted to:
<?php $numValue = (@$ESCAPED_FILTER_VALUE)? $ESCAPED_FILTER_VALUE : '0'; ?>
So $numValue will be equal to $ESCAPED_FILTER_VALUE if it exists, else it will be equal to zero. But you could put any normal if statement in the brackets, eg:
<?php $weatherString = ($temperature > 30)? 'Hot' : 'Cold'; ?>
Thanks!
Greg
PHP Programmer - interactivetools.com