Populating with all contacts & cutomers.

classic12

I have a customers & contacts table. Both tables have unique field custID. contacts table have multiple contacts from one company

I am trying to get the following result

customers.custID | contacts.firstName | contacts.firstName

500              |  John              |   Paul

ie I need them in separate fields.

Is this possible in a MYSQL Query?

If so what is the syntax to use with the editor.

The idea is I can use responsive extension and show the company details and then have all the contacts show on expanding the row.

Or is there another approach?

Cheers

Steve Warby

allan

You can do this with a one-to-many join. Because the data for the names will come back as an array, if you want names in different columns you could do something like:

{
  data: 'contacts.0.firstName',
  defaultContent: ''
},
{
  data: 'contacts.1.firstName',
  defaultContent: ''
}
,

That assumes you have a restricted number of the join entries, since you need a column for each. The other option is to display them in a column like the example I linked to above and then use a renderer to display them.

Allan

classic12

I may have no explained correctly.

customers.custID | contacts.firstName(contID12) | contacts.firstName(contID13)
 
500                        |  John                                       |   Paul

So in the datatable I only have one row for each customer.

The PHP is

include( "DataTables.php" );


// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'customers' , 'custID')
    ->fields(
        Field::inst( 'customers.custID' ),
        Field::inst( 'customers.companyName' ),
        Field::inst( 'customers.address1' ),
        Field::inst( 'customers.address2' ),
        Field::inst( 'customers.address3' ),
        Field::inst( 'customers.Post_Code' ),
        Field::inst( 'customers.Mobile' ),
        Field::inst( 'customers.Telephone' ),
        
        Field::inst( 'contacts.custID' ),
        Field::inst( 'contacts.FirstName' )
            )

    ->leftJoin( 'contacts', 'customers.custID', '=', 'contacts.custID' )    
    //->where('customers.custID', '501', '=')
    ->process( $_POST )
    ->json();
    ```

and the first row of data is returned as 

{
"data": [
{
"DT_RowId": "row_501",
"customers": {
"custID": "501",
"companyName": "Crystal Architectural Aluminium Ltd",
"address1": "Unit 1 Mackeson Road",
"address2": "Ashton Under Lyne",
"address3": "Lancashire",
"Post_Code": "OL6 8HZ",
"Mobile": "xxx",
"Telephone": "xxxx"
},
"contacts": {
"custID": "501",
"FirstName": "Andy"
}
},
{
"DT_RowId": "row_501",
"customers": {
"custID": "501",
"companyName": "Crystal Architectural Aluminium Ltd",
"address1": "Unit 1 Mackeson Road",
"address2": "Ashton Under Lyne",
"address3": "Lancashire",
"Post_Code": "OL6 8HZ",
"Mobile": "xxxx",
"Telephone": "xxxxx"
},
"contacts": {
"custID": "501",
"FirstName": "Greg"
}
}
],
"options": [],
"files": []
}
```

I am seeing

What I need to see is

one row for custID501

with

CustNo - Company - firstName(1) - firstName(2)

The reason for this is I can enter a company name or first name in the search field and quickly find a customer.

Hope I'm making sense

Cheers

Steve Warby

allan

Is customers.custID unique and the primary key in the customer's table? I'm not sure why it would be showing two rows if so - could you add ->debug( true ) before the ->process(...) call and show me the data returned from the server if that is the case?

If it isn't the primary key, and it isn't unique, it can't be used as the primary key (third parameter in the Editor constructor).

Allan

classic12

Hi Allan,

customers.custID is unique

  "debugSql": [
    {
      "query": "SELECT  `customers`.`custID` as 'customers.custID', `customers`.`companyName` as 'customers.companyName', `customers`.`companyType` as 'customers.companyType', `customers`.`address1` as 'customers.address1', `customers`.`address2` as 'customers.address2', `customers`.`address3` as 'customers.address3', `customers`.`Post_Code` as 'customers.Post_Code', `customers`.`Mobile` as 'customers.Mobile', `customers`.`Telephone` as 'customers.Telephone', `customers`.`Email` as 'customers.Email', `contacts`.`custID` as 'contacts.custID', `contacts`.`FirstName` as 'contacts.FirstName', `contacts`.`Surname` as 'contacts.Surname', `contacts`.`Mobile` as 'contacts.Mobile', `contacts`.`Email` as 'contacts.Email' FROM  `customers` LEFT JOIN `contacts` ON `customers`.`custID` = `contacts`.`custID`  LEFT JOIN `companyTypes` ON `customers`.`companyType` = `companyTypes`.`value` ",
      "bindings": []
    },
    {
      "query": "SELECT DISTINCT  `value` as 'value', `label` as 'label' FROM  `companyTypes` ",
      "bindings": []
    }
  ]
}

allan

It looks like you have two leftJoin statements, which isn't shown above:

SELECT `customers`.`custID` AS 'customers.custID',
       `customers`.`companyName` AS 'customers.companyName',
       `customers`.`companyType` AS 'customers.companyType',
       `customers`.`address1` AS 'customers.address1',
       `customers`.`address2` AS 'customers.address2',
       `customers`.`address3` AS 'customers.address3',
       `customers`.`Post_Code` AS 'customers.Post_Code',
       `customers`.`Mobile` AS 'customers.Mobile',
       `customers`.`Telephone` AS 'customers.Telephone',
       `customers`.`Email` AS 'customers.Email',
       `contacts`.`custID` AS 'contacts.custID',
       `contacts`.`FirstName` AS 'contacts.FirstName',
       `contacts`.`Surname` AS 'contacts.Surname',
       `contacts`.`Mobile` AS 'contacts.Mobile',
       `contacts`.`Email` AS 'contacts.Email'
FROM `customers`
LEFT JOIN `contacts` ON `customers`.`custID` = `contacts`.`custID`
LEFT JOIN `companyTypes` ON `customers`.`companyType` = `companyTypes`.`value`

There is no select from the companyTypes table, which is what is causing the issue. if you don't want anything from that table, remove that leftJoin.

Allan

classic12

Sorry I was playing around.

I get the same result without the second join.

  "query": "SELECT  `customers`.`custID` as 'customers.custID', `customers`.`companyName` as 'customers.companyName', `customers`.`companyType` as 'customers.companyType', `customers`.`address1` as 'customers.address1', `customers`.`address2` as 'customers.address2', `customers`.`address3` as 'customers.address3', `customers`.`Post_Code` as 'customers.Post_Code', `customers`.`Mobile` as 'customers.Mobile', `customers`.`Telephone` as 'customers.Telephone', `customers`.`Email` as 'customers.Email', `customers`.`Notes` as 'customers.Notes', `contacts`.`custID` as 'contacts.custID', `contacts`.`FirstName` as 'contacts.FirstName', `contacts`.`Surname` as 'contacts.Surname', `contacts`.`Mobile` as 'contacts.Mobile', `contacts`.`Email` as 'contacts.Email' FROM  `customers` LEFT JOIN `contacts` ON `customers`.`custID` = `contacts`.`custID` ",
      "bindings": []
    },
    {
      "query": "SELECT DISTINCT  `value` as 'value', `label` as 'label' FROM  `companyTypes` ",
      "bindings": []
    }
  ]
}


Run Request

Display Code

Close

allan

Formatted it looks like this:

SELECT `customers`.`custID` AS 'customers.custID',
       `customers`.`companyName` AS 'customers.companyName',
       `customers`.`companyType` AS 'customers.companyType',
       `customers`.`address1` AS 'customers.address1',
       `customers`.`address2` AS 'customers.address2',
       `customers`.`address3` AS 'customers.address3',
       `customers`.`Post_Code` AS 'customers.Post_Code',
       `customers`.`Mobile` AS 'customers.Mobile',
       `customers`.`Telephone` AS 'customers.Telephone',
       `customers`.`Email` AS 'customers.Email',
       `customers`.`Notes` AS 'customers.Notes',
       `contacts`.`custID` AS 'contacts.custID',
       `contacts`.`FirstName` AS 'contacts.FirstName',
       `contacts`.`Surname` AS 'contacts.Surname',
       `contacts`.`Mobile` AS 'contacts.Mobile',
       `contacts`.`Email` AS 'contacts.Email'
FROM `customers`
LEFT JOIN `contacts` ON `customers`.`custID` = `contacts`.`custID`

That looks like it should work to me. If you run it directly against your database, do you only get a single row for each customer ID?

Allan

classic12

I get the same ie if a company has 5 contacts I get 5 rows.

allan

Ah - I see what you mean, sorry I hadn't quite got it before. Yes, that is how a left join would operate in these circumstances. If the joined table has multiple records, it would fill in the details for the remainder of the row for each entry.

What you want here is an Mjoin rather than a left join (since the relationship is one-to-many). Ideally there would also be a link table in between the two tables so that you can change the links if needed.

Allan

classic12

Hi allan got it working ( nearly).

include( "DataTables.php" );


// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'customers' , 'custID')
    ->field(
        Field::inst( 'customers.custID' )->set(false),
        Field::inst( 'companyName' ),
        Field::inst( 'address1' ),
        Field::inst( 'address2' ),
        Field::inst( 'address3' ),
        Field::inst( 'Post_Code' ),
        Field::inst( 'Mobile' ),
        Field::inst( 'Telephone' ),
        Field::inst( 'Email' ),
        Field::inst( 'Notes' )
        
        )

        ->join(
        Mjoin::inst( 'contacts' )
            ->link( 'customers.custID', 'contacts.custID' )
        ->fields(
        Field::inst( 'custID' )->set(false),    
        Field::inst( 'FirstName' ),
        Field::inst( 'FirstName' ),
        Field::inst( 'Mobile' ),
        Field::inst( 'Email' )

            )
            
            )

    //->leftJoin( 'contacts', 'customers.custID', '=', 'contacts.custID' )  
    //->leftJoin( 'companyTypes', 'customers.companyType', '=', 'companyTypes.value' )
    //->where('customers.custID', '501', '=')
    //->debug( true )
    ->process( $_POST )
    ->json();

What would work is the following:

{ data: "contacts" , title : 'MOBTest', 
             render: '[</br> ].Mobile'
             },

but concat the fields and make the mobile number into an sms

Can't work out the syntax but this

render: '[</br> ].FirstName' + '<a href="sms:' + [].Mobile + '">' + [</br> ].Mobile + '</a>'

so the output would be

John 07861xxxxx
Paul 07861yyyyy
Andrew 07861zzzzzz

Where the mobile numbers are SMS links.

Cheers

Steve Warby

allan

You'd need to use a function with a loop since you want to build a more complex string:

render: function ( data, type row ) {
  var str = [];
  for ( ... ) {
    str.push( '<a ...'> ... );
  }

  return str.join('<br>');
}

Allan

classic12

Got it working as follows

        columns: [
            { data: "customers.custID" , responsivePriority: 1, width :10 ,  title : 'Cust No'},
            { data: "companyName" , responsivePriority: 2, width :50 , title : 'Company'},
            { data: null , title : 'Contacts', 
            // render: '[-].Mobile'
             render: function ( data, type, row ) {
                      var str = [];
                      var dataNew = [JSON.stringify(row)];
                      var contactsNew = row.contacts;
                      for (i = 0; i < contactsNew.length; i++) { 
                          str.push( contactsNew[i].FirstName + '  '+ contactsNew[i].Surname );
                          }
            return str.join('<br>');
                          }
            },
             { data: null , title : 'Mobile',
             render: function ( data, type, row ) {
                      var str = [];
                      var dataNew = [JSON.stringify(row)];
                      var contactsNew = row.contacts;
                      for (i = 0; i < contactsNew.length; i++) { 
                          str.push( contactsNew[i].FirstName + '  ' + contactsNew[i].FirstName+ '  ' +  '<a href="tel:' + contactsNew[i].Mobile + '">' +  '  <i class="fa fa-phone"></i>' +   '</a>' + '   ' + contactsNew[i].Mobile +'   '+ '<a href="sms:' + contactsNew[i].Mobile + '">' +  '<i class="fa fa-commenting"></i>' + '  </a>'  );
                          }
          
              str.unshift('<br>');
              return str.join('<br>');
                  }
             },
            { data: null , title : 'Email',
             render: function ( data, type, row ) {
                      var str = [];
                      var dataNew = [JSON.stringify(row)];
                      var contactsNew = row.contacts;
                      for (i = 0; i < contactsNew.length; i++) {              
                          str.push( contactsNew[i].FirstName + '  ' + contactsNew[i].Surname+ '   '+ '<a href="mailto:' + contactsNew[i].Email + '">' + contactsNew[i].Email + '  </a>' );
                          }
                        str.unshift('<br>');
              return str.join('<br>');
}
             },

            {data: null, title :'Work Email',
                                render: function (data, type, row) {
                                    return '<a href="mailto:' + data.Email + '?Subject=Hi!">' + data.Email + '</a>';
                                }
                                },
             {data: null, title :'Work Landline',
                                render: function (data, type, row) {
                                return  '  </a>' + '<a href="tel:' + data.Telephone + '">' + data.Telephone + '</a>';
                                }
                                },
            { data: "address1" , title : 'Add1'},
            { data: "address2" ,  title : 'Add2'},
            { data: "address3" ,  title : 'Add3'},
            { data: "Town" ,  title : 'Town'},
            { data: "Post_Code" ,  title : 'Post Code'},
            { data: "Notes" ,  title : 'Notes'}

            
            
           
        ]

Once again thanks for all the help Allan.

Cheers

Steve Warby