JOIN/CONCAT troubles

[trcharlie]

As I am not a professional programmer I have some troubles using this awesome grid. Specifically I have two seperate issues although they might have the same solution:

First one: Two tables, first is projects, second is user. In the users table name and surname are in seperate columns. in projects there is a column uid for linking with the users table. I watched all videos, read many (solved) issues but wasnt able to create a working code.

This is what i created so far in the ajax.php:

$mysqlhost="localhost"; // MySQL-Host angeben
$mysqluser="wawi"; // MySQL-User angeben
$mysqlpwd="XXXi"; // Passwort angeben
$mysqldb="wawi"; // Gewuenschte Datenbank angeben
$connection=mysql_connect($mysqlhost, $mysqluser, $mysqlpwd) or die("Verbindungsversuch fehlgeschlagen");
mysql_select_db($mysqldb, $connection) or die("Konnte die Datenbank nicht waehlen.");
// require our class
require_once("grid.php");

// load our grid with a table
$grid = new Grid("projekte", array(
    "save"=>true,
    "delete"=>false,
    "adding"=>true,
    "joins"=>array(
        "LEFT JOIN user ON (projekte.uid=user.id)"
        ),
//when doing joins the fields need to be labled and changed to the required filed from the join to show in grid
    "fields"=>array(
        "prename"=>"users.vorname",
        "surname"=>"users.nachname"
        ),
            "select" => 'selectFunction'
    ));

function selectFunction($grid) {

    $selects = array();

    // category select
   $grid->table = "user";
   $grid->fields = array("fullName"=>"CONCAT(prename,' ',surname)");
    $selects["fullname"] = $grid->makeSelect("id","fullname");

    // render data          
    $grid->render($selects);
}

The table on the html looks like this:

    echo "<h2>Bauvorhaben</h2>\n";
            echo '<table class="grid bvh" action="ajaxBVH.php">
            <tr>    
                <th col="id"    width="50">ID</th>
                <th col="name"  width="200" type="text">Bezeichnung</th>
                <th col="aktiv" width="100" type="checkbox">Aktiv</th>
                <th col="temp" width="100"  type="checkbox">Tempor&auml;r</th>
                <th col="created"  width="200" type="text">Erstellt</th>
                <th col="fullname" type="select">Erstellt von</th>
                <th col="zkalkident" type="text">ZKalk DokNummer</th>
            </tr>
        </table>';

This is the MySQL-Log for the query:

130829 15:32:02   290 Connect   wawi@localhost on 
          290 Init DB   wawi
          290 Quit  
          292 Connect   wawi@localhost on 
          292 Init DB   wawi
          292 Query SHOW KEYS FROM `projekte` WHERE Key_name = 'PRIMARY'
          292 Query SELECT `projekte`.`id`,`projekte`.`name`,`projekte`.`aktiv`,`projekte`.`temp`,`projekte`.`created`,`projekte`.`fullname`,`projekte`.`zkalkident`,users.nachname as `surname`
            FROM `projekte`
            LEFT JOIN user ON (projekte.uid=user.id)

            ORDER BY `id` DESC
            LIMIT 0,10
          292 Quit  
          291 Connect   wawi@localhost on 
          291 Init DB   wawi
          291 Query SELECT `user`.`id`,`user`.`fullname`
            FROM `user`
          291 Quit  

FYI this is the js snippet for the grid:

                        $(function() {
                var $grid = $(".bvh").grid({
                    title : "bvh",
                    page : 1,
                    showPager : true,
                    editing : true,
                    deleting : false,
                    nRowsShowing : 10,
                    width: 1000,
                    rowNumbers: false,
                    checkboxes: false,
                    adding: false,
                    orderBy: "id",
                    sort: "DESC"

                }).on("loadComplete",function(e, grid) {
                    console.log("loadComplete", grid);
                }).on("cellClick",function(e, $cell,rowData) {
                    //console.log("cell",$cell,rowData);
                }).on("rowCheck",function(e, $checkbox, rowData) {
                    //console.log("rowCheck",$checkbox, rowData);
                }).on("rowClick",function(e, $rows,rowData) {
                    //console.log("rowClick",$rows,rowData);
                }).on("save",function(e, row, res) {
                    //console.log("save",row,res);
                });

            });

I don't know how to go on from this stage as I tried everything I can think of. I am sure the answer is an easy one so please share it with me!

regards, charlie

[optikalefx]

The first thing I notice is that you have your fields as

"prename"=>"users.vorname", "surname"=>"users.nachname"

But your table name is user not users. I'll keep looking.

[optikalefx]

I can't tell if there are other issues, but if you go to chrome, and hit the network tab > ajax > and click on the request > response you should be able to see any mysql errors and post them here for me.

[trcharlie]

Sorry for the typo, i changed so many things to test this out so it must have slipped in. I get the following two errors:

Unknown column 'user.fullname' in 'field list
Unknown column 'projekte.fullname' in 'field list'

Thanks for looking into my problem

[trcharlie]

By the way I get these errors seperate as I get two times the response-page (ajaxBVH.php) as you can see here: http://prntscr.com/1o5y89

[optikalefx]

Is your grid automatically loading twice?

[trcharlie]

no, it is only displayed once, and is not supposed to be loaded twice

[optikalefx]

So for fullname, i don't see you putting user.fullname in the fields list. For projekte.fullname is that actually a real field on projekte?

[trcharlie]

fullname does not exist in any of the tables. it should just be the new field for the grid containing name and surname seperated by a whitespace

[optikalefx]

So then you would need to have

"fullname" => "CONCAT(user.vorname,' ',user.nachename)"

inside of fields

[trcharlie]

Great, now this one is working! Thank you so much. I have a look now into the second grid and contact you if I run into any troubles. Thanks again, charlie

[optikalefx]

Awesome, glad we got it!

[trcharlie]

Now I have one more question: How do I handle multiple selects with different tables? I tried this way, but it didn't work:

    $grid = new Grid("entnahme", array(
        "save"=>true,
        "delete"=>false,
        "adding"=>true,
        "joins" => array("LEFT JOIN material ON (materialid=material.id)",
            "LEFT JOIN materialgruppen ON (material.gruppenid=materialgruppen.id)",
            "LEFT JOIN projekte ON (entnahme.projektid=projekte.id)"),
        "fields" => array("gruppe"=>'materialgruppen.name',
            "bvh"=>'projekte.name'
            ),
        "select" => 'selectFunction',
    ));

        function selectFunction($grid) {

        $selects = array();

        // Mitarbeiter select
        $grid->table = "materialgruppen";
        $grid->fields = array("gruppe"=>'materialgruppen.name');
        $selects["gruppe"] = $grid->makeSelect("id","gruppe");

        $grid->table = "projekte";
        $grid->fields = array("bvh"=>'projekte.name');
        $selects["bvh"] = $grid->makeSelect("id","bvh");

        // render data          
        $grid->render($selects);

    }

I believe the mistake is in the selectFunction() but I can't find it...

[optikalefx]

Yea that should be correct, what errors are you getting?

[trcharlie]

The response looks alright, the grid is also displyed, but the select fields are not set to the corresponding value, so they all show the same value. This is a snippet of the response:

_62: {id:62, time:2013-08-26 20:09:06, gruppe:Bandblech, materialid:6, bvh:Pernitz}
bvh: "Pernitz"
gruppe: "Bandblech"
id: "62"
materialid: "6"
time: "2013-08-26 20:09:06"

[optikalefx]

Make sure that the value in the grid that you want the select box to change to, is the actual real value, not some aliased joined value. For example, if you have shirts and then select box is color, but all the colors have IDs.

Don't let your table have color = 'red'. Make sure that color = '5' so that the select function knows to select value 5, which is display value of red.

[trcharlie]

it looks alright, in the generated div the value is the id of the table:

<option value="16">test4</option>

16 is the id of the table "projekte", test4 the name to be displayed. But nothing is marked as selected. also saving does not work.

[optikalefx]

Yea that part is fine, I'm talking about the parent cell value. Because the parent cell value is the one that tells the drop down which option to pick. If all your values are numbers, like 16, but the cell says test4 not 16, then it won't know how to select 16.

Does that make sense?

[trcharlie]

I see what you mean, and yes, that makes sense. But, where do I define the parent cell value? (sure a dumb question, but it's quite late here so please forgive me...)

[optikalefx]

It's not a dumb question, this particular part is tough to get.

The parent cell value comes from the first main table select. So in that very first <th col="" where you say what column you want that guy to be, make sure in the PHP for that column you aren't aliasing that field to something else.

The best way to test that you have it right, is to get rid of the select boxes for a second. Just comment that part out. Then load your grid. If in that column you see a bunch of numbers, like 16, then you're good. If you see test4 then your not good. If you see test4 then you need to take out that column in the fields array, because it's aliasing it.

Does that make any sense?

[trcharlie]

Perfect, I think I got it. It works now on most of my select boxes but on one. Maybe this isn't possible at all but I'll explain anyway what I want to do.

I have three tables, tbl.A. tbl.B and tbl.C tbl.A.bid points to tbl.B.id nad tbl.B.cid point to tbl.C.id

now i want to have a selecbox like this:

<option value="tbl.A.id">tbl.C.desc & tbl.B.desc</option>

I joined all three tables in the ajax.php and added a new field to fields:

    $grid = new Grid("tbl.A", array(
        "save"=>true,
        "delete"=>false,
        "adding"=>true,
        "joins" => array("LEFT JOIN tbl.B ON (tbl.A.bid=tbl.B.id)",
            "LEFT JOIN tbl.Cn ON (tbl.B.cid=tbl.C.id)",
                 "),
        "fields" => array(
            "fulldesc"=>"CONCAT(tbl.C.desc,' & ',tbl.B.desc)",
            ),
        "select" => 'selectFunction'
    ));

This seems to work, as I get the fulldesc in the response. Also I created the select in the function:

function selectFunction($grid) { 
        $selects = array();
    $grid->table = "tbl.B";
    $grid->fields = array("fulldesc"=>"CONCAT(tbl.C.desc,' & ',tbl.B.desc)");
        $selects["tblAid"] = $grid->makeSelect("tbl.A.id","fulldesc");

        // render data          
        $grid->render($selects);

    }   

So first, is this even possible? If so, what table do I have to specify in the selectFunction? Where could be a mistake in the code?

At the moment the grid is loading without any error message but all select boxes are empty. In the response I can see the created field "fulldesc" which contains the correct descriptions. Also I see the tbl.A.id. But in the created code the is empty.

Thanks for your great help, everything else works fine now.

[optikalefx]

so you don't need to do the CONCAT in the original statement, only in the select function. But you need to do the joins in the select function.

So all your missing are the joins in your select function, and then it should work.

[trcharlie]

Great, everything works now! Thank you so much for the help!

[sambaf]

please can some one help me? i started tested your grid and find it very awsome but i kind if felt in thesame promblems of joining two tables. i have a table kunden and another one konten, now i can display the kunden table on the grid with no problems, but when i try to join another table i get and error saying
Unknown column 'konten.Rechnungsnr' in 'field list'

[sambaf]

my ajax.php looks like this ?php // connect to db mysql_connect("localhost","root","root"); mysql_select_db("mydb");

// require our class
require_once("grid.php");

// load our grid with a table
$grid = new Grid("kunden", array(
        "joins"=>array(
       "LEFT JOIN konten c ON ( kunden.kundennummer=konten.Rechnungsnr)"
       ),

       "fields"=>array(
           "Rechnungsnummer"=>"konten.Rechnungsnr"
       ),
    "select" => 'selectFunction'
));
    function selectFunction($grid) {

$selects = array();

// category select

$grid->table = "konten"; $grid->fields = array("Rechnungsnr"=>"Rechnungsnr"); $selects["Rechnungsnr"] = $grid->makeSelect("Rechnungsnr","Rechnungsnr");

// render data          
$grid->render($selects);

}

// drop down function
// if you have anonymous function support, then you can just put this function in place of
// 'selectFunction'

?>

[sambaf]

and my html table looks like this

                   <th  col="Rechnungsnummer" type="select">Rechnungsnr</th>
                     <th col="Vorname"type="text" >Vorname</th>

                     <th col="Nachname" width="50" type="text">Nachname</th>
                     <th col="Adresse" type="text">Adresse</th>
                     <th col="PLZOrt" type="text">PLZOrt</th>
             <th col="email" type="text">email</th>

[optikalefx]

You've got an alias problem. In your PHP you're doing this

LEFT JOIN konten c ON ( kunden.kundennummer=konten.Rechnungsnr)

Which aliases the table "konten" to "c" Therefor everywhere you have "konten" (after this point) needs to be "c"

LEFT JOIN konten c ON ( kunden.kundennummer=c.Rechnungsnr)

"fields"=>array(
       "Rechnungsnummer"=>"c.Rechnungsnr"
   ),

[optikalefx]

More importantly actually, you are joining on a field that you are trying to use in a select

<th  col="Rechnungsnummer" type="select">Rechnungsnr</th>

This field "Rechnungsnummer" needs to be the raw number from the database, not a joined value. Why? Because your select function needs the raw number to look up against the entire table of values.

But your making your "select" box with the name "Rechnungsnr" but in your HTML you have

 <th  col="Rechnungsnummer" type="select">Rechnungsnr</th>

So your select is not referring the field that you are selecting from the PHP. I don't know what those words are so I can't speculate what relationships you want.

[sambaf]

oh thanks for the quick response, just to clarify it again, Rechnungsnr is the primary key of the table i m referencing, and kundennnumer is the primary key of the actual table ,so it seems i m doing it completely wrong because the i dont want to use the field as select rather i want to just get the data and display it on the grid? so is it wrong with the selectiong function? how should i go about that please?

[optikalefx]

The select function is for making drop down boxes, hence the name select after the HTML tag

What you want is more like this

require_once("grid.php");

// load our grid with a table
$grid = new Grid("kunden", array(
    "joins"=>array(
       "LEFT JOIN konten ON ( kunden.kundennummer=konten.Rechnungsnr)"
    ),
   "fields"=>array(
       "Rechnungsnummer"=>"konten.Rechnungsnr"
   )
));

Then in your HTML

<th  col="Rechnungsnummer">Rechnungsnr</th>
<th col="Vorname">Vorname</th>
<th col="Nachname">Nachname</th>
<th col="Adresse">Adresse</th>
<th col="PLZOrt">PLZOrt</th>
<th col="email">email</th>

This assumes that

• konten.Rechnungsnr exists
• kunden.kundennummer is a foreign key that can join to konten.Rechnungsnr

sambaf commented 10 years ago

thanks bro, big up with this great work you are doing. it worked like magic, just one last question before you go please say i want to get more variables from the table i m referencing how should i do it??

optikalefx commented 10 years ago

I'm never going anywhere, don't worry.

With the current code you have access to every single field in the kunden table. If you want more fields from the konten table, you need to list them as you already are in the "fields=>array() part.

You can also get fields from other tables too, just add another LEFT JOIN in your "joins" part, and then add more "fields".

Once you do that, all those fields that you defined in "fields" will be available to use in <th col=""

sambaf commented 10 years ago

Now i listed the fields as shown below, i can see that its returning the right values but i cant see it on the grid. so i tried adding names to the table column but i got an error instead. "fields"=>array( "Rechnungsnummer"=>"konten.Rechnungsnr", "Monumsatz"=>'konten.Mon_umsatz', "Bank"=>'konten.Bank'

     ),     

optikalefx commented 10 years ago

To have these new columns show in the grid you would add <th col="Monumsatz">

sambaf commented 10 years ago

again bomb like magic. i m so greatfull not just for the work you have done and put out there but for the time you take to answer questions. be blessed thanks

• © Githubissues.
• Githubissues is a development platform for aggregating issues.

[sambaf]

thanks bro, big up with this great work you are doing. it worked like magic, just one last question before you go please say i want to get more variables from the table i m referencing how should i do it??

[optikalefx]

I'm never going anywhere, don't worry.

With the current code you have access to every single field in the kunden table. If you want more fields from the konten table, you need to list them as you already are in the "fields=>array() part.

You can also get fields from other tables too, just add another LEFT JOIN in your "joins" part, and then add more "fields".

Once you do that, all those fields that you defined in "fields" will be available to use in <th col=""

[sambaf]

Now i listed the fields as shown below, i can see that its returning the right values but i cant see it on the grid. so i tried adding names to the table column but i got an error instead. "fields"=>array( "Rechnungsnummer"=>"konten.Rechnungsnr", "Monumsatz"=>'konten.Mon_umsatz', "Bank"=>'konten.Bank'

     ),     

[optikalefx]

To have these new columns show in the grid you would add <th col="Monumsatz">

[sambaf]

again bomb like magic. i m so greatfull not just for the work you have done and put out there but for the time you take to answer questions. be blessed thanks