JOIN/CONCAT troubles about openjs-grid HOT 34 CLOSED

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.

from openjs-grid.

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.

from openjs-grid.

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

from openjs-grid.

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

from openjs-grid.

Is your grid automatically loading twice?

from openjs-grid.

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

from openjs-grid.

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?

from openjs-grid.

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

from openjs-grid.

So then you would need to have

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

inside of fields

from openjs-grid.

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

from openjs-grid.

Awesome, glad we got it!

from openjs-grid.

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...

from openjs-grid.

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

from openjs-grid.

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"

from openjs-grid.

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.

from openjs-grid.

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.

from openjs-grid.

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?

from openjs-grid.

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...)

from openjs-grid.

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?

from openjs-grid.

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.

from openjs-grid.

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.

from openjs-grid.

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

from openjs-grid.

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'

from openjs-grid.

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'

?>

from openjs-grid.

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>

from openjs-grid.

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"
   ),

from openjs-grid.

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.

from openjs-grid.

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?

from openjs-grid.

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

from openjs-grid.

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??

from openjs-grid.

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=""

from openjs-grid.

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'

     ),     

from openjs-grid.

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

from openjs-grid.

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

from openjs-grid.