MYSQL Select query and PHP IF statement to build custom dropdown element

porlhews · Aug 18, 2013

Hey guys,

I'm trying to use a MYSQL select query to look up the current user's rank (from a community builder table) and then create one of two dropdown elements.

Individually all the pieces appear to work;

- when I use my MYSQL select statement in a field element it outputs the correct values.
- when I manually change $result (i.e. not using the MYSQL query) it triggers the IF statement properly
- when I just use the output of the if statement it creates the menus correctly.

As such, I don't think I'm loading the correct $result into the IF statement; I've tried loads and loads of variations, but just can't see where I'm going wrong.

Can anyone help?

Thanks in advance,

Paul Hughes

Code:

$db =& JFactory::getDBO();
$query = "SELECT cb_rank FROM j16bpsa_comprofiler INNER JOIN j16bpsa_users ON j16bpsa_comprofiler.id=j16bpsa_users.id
 
WHERE (
`username` ='{$my->username}'
)
 
";
 
$db->setQuery($query);
$result = $db->loadResult();
 
 
if ($result == "GSM") {
  $opts[] = JHTML::_('select.option', 'alpha', 'alpha');
  $opts[] = JHTML::_('select.option', '2', 'two');
} else {
 
$opts[] = JHTML::_('select.option', 'beta', 'beta');
  $opts[] = JHTML::_('select.option', '3', 'three');
 
}
 
return $opts;

porlhews · Aug 18, 2013

P.S. Using the following:

PHP Version5.4.17
Joomla! VersionJoomla! 2.5.14 Stable [ Ember ] 01-August-2013 14:00 GMT
Joomla! Platform VersionJoomla Platform 11.4.0 Stable [ Brian Kernighan ] 03-Jan-2012 00:00 GMT
Fabrik 3.0.8

porlhews · Aug 19, 2013

cheesegrits rob

Any thoughts?

Thanks,

Paul

Sophist · Aug 20, 2013

Posted in the F3.1 forum - moving to the F3.0 forum.

troester · Aug 20, 2013

For debugging add
var_dump($query,$result);exit;
before the if.

Not sure if a placeholder inside "" is substituted.

Sophist · Aug 20, 2013

The placeholder should definitely be substituted inside " ". If you find it isn't please report it as a bug.

Not sure why you have used an & in "$db =& JFactory::getDBO();". Some documentation sites show code with and some without - most code I have seen (including that in Fabrik) does not have this. Whether this is the cause of your problems, I have no idea.

porlhews · Aug 20, 2013

Hey guys,

Thanks for your help, it's much appreciated.

I've changed the Select statement to include "" instead of '' (code below), however I now get the following error;

Parse error: syntax error, unexpected '{' in /XXXX/components/com_fabrik/models/element.php(3183) : eval()'d code on line 5

Does this mean the placeholder isn't being substituted? Do you think this might be a bug?

Thanks again,

Paul

Code:

$db =& JFactory::getDBO();
$query = "SELECT cb_rank FROM j16bpsa_comprofiler INNER JOIN j16bpsa_users ON j16bpsa_comprofiler.id=j16bpsa_users.id
 
WHERE (
`username` ="{$my->username}"
)
 
";
 
$db->setQuery($query);
$result = $db->loadResult();
 
 
if ($result == GSM) {
  $opts[] = JHTML::_('select.option', 'alpha', 'alpha');
  $opts[] = JHTML::_('select.option', '2', 'two');
} else {
 
$opts[] = JHTML::_('select.option', 'beta', 'beta');
  $opts[] = JHTML::_('select.option', '3', 'three');
 
}
 
return $opts;

porlhews · Aug 20, 2013

I also tried with '' as before and the debugging code suggested by troester (thanks).

It gave me the following error; string(142) "SELECT cb_rank FROM j16bpsa_comprofiler INNER JOIN j16bpsa_users ON j16bpsa_comprofiler.id=j16bpsa_users.id WHERE ( `username` ='' ) " NULL

Again, is this suggesting the placeholder isn't being substituted?

Sophist · Aug 21, 2013

Double quotes around the placeholder will conflict with the double quotes around the select string. Single quotes were correct.

Please put the single quotes back around the placeholder and change the username field in the where clause as shown below and report exactly what error you get. Thx.

SQL:

WHERE j16bpsa_users.username = '{$my->username}'

S

troester · Aug 21, 2013

...WHERE ( `username` ='' ) "

As you can see the placeholder is substituted (if you are using single quotes) but it's empty.
Are you logged in?

Sophist · Aug 21, 2013

Troester has a good point - you should not assume that the placeholder contains a name - I would suggest that you add some code at the beginning to return a default value if the placeholder is empty e.g.:

PHP:

if ('{$my->username}' == '') return '';

porlhews · Aug 21, 2013

Hi guys,

Thanks for the help. I'm at work at the moment and will have a go tonight.

However, I can confirm that the user is logged in when accessing the form and I have fields working which look up the users name (etc) and load it into a field element.

Paul

porlhews · Aug 21, 2013

Hmmmm, got nowhere with this one I'm afraid - like I mentioned earlier today, the user is logged in and definitely has a username which should be looked up.

Any ideas?

Sophist · Aug 22, 2013

So what are your current symptoms?

troester · Aug 22, 2013

when I use my MYSQL select statement in a field element it outputs the correct values

Where do you have this code? Field default eval? calc element? Custom form template?

Sophist · Aug 22, 2013

I have looked at the placeholder replacement code in parent.php and it would seem that it should work. username is definitely being passed by Joomla (on my system at least), and the placeholder looks like it was written correctly. And as Troester has pointed out, it is being substituted, so it must be correctly specified.

So why it is being substituted with a blank is a mystery.

S

porlhews · Aug 22, 2013

troester said:
Where do you have this code? Field default eval? calc element? Custom form template?

It's a Field default eval, the code I used is:

Code:

$db =& JFactory::getDBO();
$query = "SELECT cb_scoutgroup FROM j16bpsa_comprofiler INNER JOIN j16bpsa_users ON j16bpsa_comprofiler.id=j16bpsa_users.id
 
WHERE (
`username` ='{$my->username}'
)
 
";
 
$db->setQuery($query);
$result = $db->loadResult();
return $result;

porlhews · Aug 22, 2013

Sophist said:
So what are your current symptoms?

Basically the same place as the first post.

Here's my current code:

Code:

$db =& JFactory::getDBO();
$query = "SELECT cb_rank FROM j16bpsa_comprofiler INNER JOIN j16bpsa_users ON j16bpsa_comprofiler.id=j16bpsa_users.id
 
WHERE (
`username` ='{$my->username}'
)
 
";
 
$db->setQuery($query);
$result = $db->loadResult();
 
if ($result == GSM) {
  $opts[] = JHTML::_('select.option', 'alpha', 'alpha');
  $opts[] = JHTML::_('select.option', '2', 'two');
} else {
 
$opts[] = JHTML::_('select.option', 'beta', 'beta');
  $opts[] = JHTML::_('select.option', '3', 'three');
 
}
 
return $opts;

porlhews · Aug 22, 2013

I've just hardcoded in a username.

When I edit the profile for that username (i.e. change cb_rank to contain GSM or not), the dropdown is populated as appropriate.

I can only conclude that the {$my->username} placeholder isn't working?

porlhews · Aug 22, 2013

Finally got it working!

For anyone else needing help, I used the following;

Code:

$user =& JFactory::getUser();
$name = $user->username;
$db =& JFactory::getDBO();
 
$query = "SELECT cb_rank FROM j16bpsa_comprofiler INNER JOIN j16bpsa_users ON j16bpsa_comprofiler.id=j16bpsa_users.id
 
WHERE (
`username` ='$name'
)
 
";
 
$db->setQuery($query);
$result = $db->loadResult();
 
if ($result == GSM) {
  $opts[] = JHTML::_('select.option', 'alpha', 'alpha');
  $opts[] = JHTML::_('select.option', '2', 'two');
} else {
 
$opts[] = JHTML::_('select.option', 'beta', 'beta');
  $opts[] = JHTML::_('select.option', '3', 'three');
 
}
 
return $opts;

MYSQL Select query and PHP IF statement to build custom dropdown element

Member

Member

Member

Guest

Administrator

Guest

Member

Member

Guest

Administrator

Guest

Member

Member

Guest

Administrator

Guest

Member

Member

Member

Member

Useful links