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

Greg Thomas







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.