MySQL Query Question

6 posts by 2 authors in: Forums > CMS Builder
Last Post: June 16, 2011   (RSS)

By gkornbluth - June 16, 2011

Hi All,

Quick Question,

I’m trying to set up a list that gets it’s options from the “title” field of another table (event_titles), using the “get options from mySQL query” option.

I’d like to restrict the options that a user can see to only those where they are the author.

I’m not at all sure how to write the ‘where”.

Here’s where I started:

SELECT title, title

FROM `<?php echo $TABLE_PREFIX ?>event_titles`


I tried adding something like this. I know that the syntax is incorrect, but it’s the direction I want to go in.

'where' => ['createdByUserNum'] = @$CURRENT_USER['num']

Can anyone offer some guidance?

Thanks

Jerry Kornbluth
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

Re: [zip222] MySQL Query Question

By gkornbluth - June 16, 2011

Thanks Zip222,

I'll plug that in and give it a try.

As to the admins...

Good point.

Could I somehow add something like this (probably not correct syntax)?

orWhere $CURRENT_USER['isAdmin'] = 1
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

Re: [gkornbluth] MySQL Query Question

By zip222 - June 16, 2011

I think this should work...

<?php
if ($CURRENT_USER['isAdmin'] {
$where="";
}
else {
$where="WHERE createdByUserNum='".$CURRENT_USER['num']."'";
}
?>


I just recently learned this technique and it can be incredibly useful.

Re: [zip222] MySQL Query Question

By gkornbluth - June 16, 2011

Thank you again.

Jerry
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

Re: [zip222] MySQL Query Question

By gkornbluth - June 16, 2011

After a bit of playing around, this is the final that works in case anyone else can benefit from it.

[code

]SELECT title, title
FROM <?php echo $TABLE_PREFIX ?>event_titles_silver
<?php if (!$CURRENT_USER['isAdmin']): ?>
<?php $where="WHERE createdByUserNum='".$CURRENT_USER['num']."'"; ?>
<?php echo $where ?>
<?php endif ?>
Thanks again zip222,

Jerry Kornbluth
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php