Multiple labels with editor

mRender

Here is what I currently have:

if ( !isset($_POST['action']) ) {
   
   $data['projectList'] = $db->query( 'select' )
  ->distinct( true )
  ->table( 'projects' )
  ->get( 'userprojects.projectid as value' )
  ->get( 'projects.projectname as label' )
  ->join( 'userprojects', 'userprojects.projectid = projects.projectid' )
  ->join( 'clients', 'clients.clientid = projects.clientid' )
  ->where( 'userprojects.userid', $userid )
  ->exec()
  ->fetchAll();

This works fine. This is what I really want though:

if ( !isset($_POST['action']) ) {
   
   $data['projectList'] = $db->query( 'select' )
  ->distinct( true )
  ->table( 'projects' )
  ->get( 'userprojects.projectid as value' )
  ->get( 'projects.projectname - clients.clientname as label' ) <<<<<<<<<<<<<<<<<<<<<<<<
  ->join( 'userprojects', 'userprojects.projectid = projects.projectid' )
  ->join( 'clients', 'clients.clientid = projects.clientid' )
  ->where( 'userprojects.userid', $userid )
  ->exec()
  ->fetchAll();

So basically two labels but only display them as one. I tried to do something like this:

    $data['projectList'] = array();
    while ( $row = $data->fetch() ) {
        $data['projectList'][] = array(
            "value" => $row['userprojects.projectid'],
            "label" => $row['projects.projectname'].' '.$row['clients.clientname']
        );
    }

But I couldn't get it to work correctly. Any help would be greatly appreciated.

allan

Your solution of going it in PHP is the way to do it when using the Editor DB library's fluent API as it doesn't current support functions (i.e. you can use concat unfortunately).

What you tried to use doesn't work since $row['userprojects.projectid'] doesn't exist. You aliased it to $row['value'].

You would also need to add individual gets for projects.projectname and clients.clientname.

Allan

mRender

So this code here

if ( !isset($_POST['action']) ) {
   
   $data['projectList'] = $db->query( 'select' )
  ->distinct( true )
  ->table( 'projects' )
  ->get( 'userprojects.projectid' )
  ->get( 'projects.projectname' )
  ->get( 'clients.clientname' )
  ->join( 'userprojects', 'userprojects.projectid = projects.projectid' )
  ->join( 'clients', 'clients.clientid = projects.clientid' )
  ->where( 'userprojects.userid', $userid )
  ->exec()
  ->fetchAll();
  
  
    $data['projectList'] = array();
    while ( $row = $data->fetch() ) {
    $data['projectList'][] = array(
        "value" => $row['userprojects.projectid'],
        "label" => $row['projects.projectname'].' '.$row['clients.clientname']
    );
}

Gives me this error: Call to a member function fetch() on a non-object in /var/www/vhosts/time.mmitco.com/httpdocs/ss_timecard.php on line 74

Line 74 is: while ( $row = $data->fetch() ) {

allan

You've already run fetchAll(); on the result. That give you an array. You probably want to read the result in a result variable ($res) and drop the fetchAll() call.

Allan

mRender

Wow, this works :) Thank you Allan! You da man!

if ( !isset($_POST['action']) ) {
   
   $res = ($data['projectList'] = $db->query( 'select' )
  ->distinct( true )
  ->table( 'projects' )
  ->get( 'userprojects.projectid' )
  ->get( 'projects.projectname' )
  ->get( 'clients.clientname' )
  ->join( 'userprojects', 'userprojects.projectid = projects.projectid' )
  ->join( 'clients', 'clients.clientid = projects.clientid' )
  ->where( 'userprojects.userid', $userid )
  ->exec());
  
  
    $data['projectList'] = array();
    while ( $row = $res->fetch() ) {
    $data['projectList'][] = array(
        "value" => $row['userprojects.projectid'],
        "label" => $row['projects.projectname'].' '.$row['clients.clientname']
    );
}

mRender

Is there a ->order or ->sort type of function to make the labels alphabetical in the list?

allan

order() :-)

Allan