Custom SQL Insert Update Query

QuestionsCustom SQL Insert Update Query
Alfonso Cabrera asked 4 years ago

Hi Abu!

I have the following database table.

idCotizacion_Encabezado int(11) AUTO_INCREMENT PK
idClientes int(11) NULL
serie varchar(3) utf8_general_ci NULL
fecha datetime NULL
total decimal(7,2) NULL
estatus varchar(12) utf8_general_ci NULL

But I want to organize my columns as follows

serie
idCotizacion_Encabezado PK
idClientes
fecha
total
estatus

As you can see the 1rst column is NOT the primary key, so it fails to show the grid correctly and also the update, insert events don't work. Is there any way to change the default insert update query?

5 Answers
Abu Ghufran answered 4 years ago

You can pick PK 'idCotizacion_Encabezado' as first column and again select this column as third column with different alias. e.g.

SELECT
idCotizacion_Encabezado,
serie,
idCotizacion_Encabezado as idCotizacion,
idClientes,
fecha,
total,
estatus
FROM table

And make the 1st column hidden:true, 3rd column as editable:false

Alfonso Cabrera answered 4 years ago

Thank you, it worked.

Abu Ghufran answered 4 years ago

Set table.field prefix with that column.

$col = array();
$col["title"] = "No"; // caption of column
$col["name"] = "idCotizacion";
$col["dbname"] = "<TABLE>.idCotizacion";

This will be replaced in WHERE clause.

Alfonso Cabrera answered 4 years ago

Hi Abu!

I was using the autofilter in the 3rd column but I get the following error:

Couldn't execute query. Unknown column 'idCotizacion' in 'where clause' – SELECT count(*) as c FROM (SELECT idCotizacion_Encabezado, serie, idCotizacion_Encabezado AS idCotizacion, idClientes, fecha, total, estatus FROM cotizacion_encabezado WHERE 1=1 AND ( `idCotizacion` LIKE '%5%' )) pg_tmp

Source code:

$col = array();
$col["title"] = "Id"; // caption of column
$col["name"] = "idCotizacion_Encabezado"; // grid column name, must be exactly same as returned column-name from sql (tablefield or field-alias)
$col["width"] = 60;
$col["search"]= true;
$col["hidden"] = true;
$col["editable"] = false;
$cols[] = $col;

$col = array();
$col["title"] = "Serie"; // caption of column
$col["name"] = "serie"; // grid column name, must be exactly same as returned column-name from sql (tablefield or field-alias)
$col["editable"] = true;
$col["width"] = 40;
$col["align"] = "center";
$col["edittype"] = "select"; // render as select
$str2 = "A:A;B:B";
$col["editoptions"] = array("value" => $str2);
$col["formatter"] = "select"; // display label, not value
$cols[] = $col;

$col = array();
$col["title"] = "No"; // caption of column
$col["name"] = "idCotizacion"; // grid column name, must be exactly same as returned column-name from sql (tablefield or field-alias)
$col["width"] = 60;
$col["align"] = "center";
$col["editable"] = false;
$col["search"]= true;
$cols[] = $col;

Select command:

$g->select_command = "SELECT idCotizacion_Encabezado, serie, idCotizacion_Encabezado AS idCotizacion, idClientes, fecha, total, estatus FROM cotizacion_encabezado";

Why I'm getting this error?

Alfonso Cabrera answered 4 years ago

Thank you Abu!

It worked, I only modified the following line of your code becuase the real name of the ID in the table is idCotizacion_Encabezado.

$col["dbname"] = "<TABLE>.idCotizacion_Encabezado";

I put it as you told me and I got the same error but it was caused by the incorrect column name idCotizacion doesn´t exist.

Thank again!

Your Answer

20 + 19 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating / 5. Vote count:

We are sorry that this post was not useful for you!

Let us improve this post!