Pete asked 4 months ago
I saw another thread 10 years ago in here about resolving this issue. However, I am not sure exactly where to put the code in the answer I reviewed at this link:
Search in grid not working when i'm using inner join in query
So below is my SQL-Select statement . . .
$g->table = “drvbids”;
$g->select_command = “SELECT drvbids.id, drvbids.drvFullName, drvbids.drvMobilePhone, drvbids.bidPrice, drvbids.bidDateTime,
CONCAT(deliveries.pkupHouseNo, ‘ ‘,deliveries.pkupStreet) AS Address FROM drvbids INNER JOIN deliveries
ON deliveries.id = drvbids.delvpkID WHERE drvbids.isPaid = ‘No’ AND drvbids.cuspkID = $cusPkVal”;
The Concatenation is used to form address information in one column in the grid.
CONCAT(deliveries.pkupHouseNo, ‘ ‘,deliveries.pkupStreet) AS Address
The select statement is good and works in the grid showing the address information and also works in mySQL WorkBench performing the select statement.
However, when I attempt a Search from the Search box at the top right of the grid I get a SQL Select error as follows . . .
Couldn’t execute query. Column ‘id’ in where clause is ambiguous – SELECT count(*) as c FROM (SELECT drvbids.id, drvbids.drvFullName, drvbids.drvMobilePhone, drvbids.bidPrice, drvbids.bidDateTime, CONCAT(deliveries.pkupHouseNo, ‘ ‘,deliveries.pkupStreet) AS Address FROM drvbids INNER JOIN deliveries ON deliveries.id = drvbids.delvpkID WHERE drvbids.isPaid = ‘No’ AND drvbids.cuspkID = 288 AND ( (`id` LIKE ‘%a%’ OR `drvFullName` LIKE ‘%a%’ OR `drvMobilePhone` LIKE ‘%a%’ OR `Address` LIKE ‘%a%’ OR `bidDateTime` LIKE ‘%a%’ OR `bidPrice` LIKE ‘%a%’) )) pg_tmp
I am assuming from the suggested fix 10-years ago the column for Address information should be . . .
$col = array();
$col[“title”] = “Address”;
//$col[“name”] = “Address”;
$col[“dbname”] = “CONCAT(deliveries.pkupHouseNo, ‘ ‘,deliveries.pkupStreet)”;
$col[“width”] = 62;
$col[“fixed”] = true;
$col[“sortable”] = true;
$col[“editable”] = false;
$cols[] = $col;
However, when I try this in place of . . .
$col[“name”] = “Address”;
I the address information is now blank in the grid column and if I search I still get an error as shown below.
Error
Couldn’t execute query. Column ‘id’ in where clause is ambiguous – SELECT count(*) as c FROM (SELECT drvbids.id, drvbids.drvFullName, drvbids.drvMobilePhone, drvbids.bidPrice, drvbids.bidDateTime, CONCAT(deliveries.pkupHouseNo, ‘ ‘,deliveries.pkupStreet) AS Address FROM drvbids INNER JOIN deliveries ON deliveries.id = drvbids.delvpkID WHERE drvbids.isPaid = ‘No’ AND drvbids.cuspkID = 288 AND ( (`id` LIKE ‘%aa%’ OR `drvFullName` LIKE ‘%aa%’ OR `drvMobilePhone` LIKE ‘%aa%’ OR CONCAT(deliveries.pkupHouseNo, ‘ ‘,deliveries.pkupStreet) LIKE ‘%aa%’ OR `bidDateTime` LIKE ‘%aa%’ OR `bidPrice` LIKE ‘%aa%’) )) pg_tmp
I’m not sure of where to put the . . .
$col[“dbname”] = “CONCAT(deliveries.pkupHouseNo, ‘ ‘,deliveries.pkupStreet)”;
code line above, I assumed as part of the column itself?
Been on this now going into day-2 and I have tired every combination I can think of and yet I cannot figure out what to do?
I looked though all the help info and as well many of the code examples that come with the Grid 4 PHP demo and did not see any example case where the $col[“dbname”] is being used in a demo example.
At this point I’m stuck!
Please any advise or suggestions would be greatly appreciated!
Pete answered 4 months ago
Ok on day-3 I found what I had to do to resolve the issues with the search errors when I was using a JOIN statement in my select statement.
I had to use the dbname reference for every grid column for both primary and secondary tables involved in the JOIN
Below is my select statement . . .
$g->select_command = “SELECT b.id, b.drvFullName,
b.drvMobilePhone, b.bidPrice, b.bidDateTime,
CONCAT(d.pkupHouseNo, ‘ ‘,d.pkupStreet) AS Address
FROM drvbids b INNER JOIN deliveries d ON d.id = b.delvpkID
WHERE b.isPaid = ‘No’ AND b.cuspkID = $cusPkVal”;
If you review my $col() settings shown below you will see in bold where I had to add in the dbname column setting for each and every column in the grid. . . .
$col = array();
$col[“title”] = “Driver”;
$col[“name”] = “drvFullName”;
$col[“dbname”] = “b.drvFullName”;
$col[“width”] = 56;
$col[“fixed”] = true;
$col[“sortable”] = true;
$col[“editable”] = false;
$cols[] = $col;
$col = array();
$col[“title”] = “Cell #”;
$col[“name”] = “drvMobilePhone”;
$col[“dbname”] = “b.drvMobilePhone”;
$col[“width”] = 58;
$col[“fixed”] = true;
$col[“sortable”] = true;
$col[“editable”] = false;
$cols[] = $col;
$col = array();
$col[“title”] = “Address”;
$col[“name”] = “Address”;
$col[“dbname”] = “CONCAT(d.pkupHouseNo, ‘ ‘,d.pkupStreet)”;
$col[“width”] = 62;
$col[“fixed”] = true;
$col[“sortable”] = true;
$col[“editable”] = false;
$cols[] = $col;
$col = array();
$col[“title”] = “Bid Date”;
$col[“name”] = “bidDateTime”;
$col[“dbname”] = “b.bidDateTime”;
$col[“width”] = “70”;
$col[“editable”] = true; // this column is editable
$col[“editoptions”] = array(“size”=>20); // with default display of textbox with size 20
$col[“editrules”] = array(“required”=>true); // required:true(false), number:true(false), minValue:val, maxValue:val
$col[“formatter”] = “date”; // format as date
// – PMS: Force the dates into ANSI format so when sorting up and down the date follow the
// correct cronological ordering.
$col[“formatoptions”] = array(“srcformat”=>’Y-m-d’,”newformat”=>’Y-m-d’); // http://docs.jquery.com/UI/Datepicker/formatDate
$cols[] = $col;
$col = array();
$col[“title”] = “Bid $”;
$col[“name”] = “bidPrice”;
$col[“dbname”] = “b.bidPrice”;
$col[“width”] = 58;
$col[“fixed”] = true;
$col[“sortable”] = false;
$col[“editable”] = false;
$cols[] = $col;
I am not sure if this is normal behavior or not as I did not find a reference that states:
If using aJOIN statement in your SQL-Select you need to apply the dbname option for each and every column in the grid.
I assumed you only needed this for the join fields, but in my case, I had to apply to every column setting and I’m running Grid 4 PHP v.2.9
Good news is after adding in the dbname to each column I now have a grid displaying the data as expected and no more errors when typing into the global seach box at the top-right of the grid headers.
