PHP Classes

Using 'AS FIELDNAME' in sql fails

Recommend this page to a friend!

      phpMyDataGrid - AJAX Enabled Datagrid  >  All threads  >  Using 'AS FIELDNAME' in sql fails  >  (Un) Subscribe thread alerts  
Subject:Using 'AS FIELDNAME' in sql fails
Summary:Using "select concat(fld1,fld2,fld3) as fld4 from..." fails
Messages:5
Author:Paul Dubinsky
Date:2013-06-17 12:04:06
Update:2014-03-02 02:24:15
 

  1. Using 'AS FIELDNAME' in sql fails   Reply   Report abuse  
Picture of Paul Dubinsky Paul Dubinsky - 2013-06-17 12:04:06
I have an sql that uses this snippet:

CONCAT(last_name, ', ', IF(prefix > '',CONCAT(prefix,' '),''),first_name,' ',IF(mi > '',mi,''),IF(suffix > '', CONCAT(', ',suffix),'')) AS fName, "

when I run the sql I get this error:

SQL Error found in query: SELECT account_number,temp_acct_num,fName,city,state,zip,zip4,ext1,email,status,trx FROM accounts WHERE last_name > '' ORDER BY last_name ASC LIMIT 0, 30

Error Message: Unknown column 'fName' in 'field list'

Is there a work around that I can use to run this sql?

TIA,
Paul Dubinsky

  2. Re: Using 'AS FIELDNAME' in sql fails   Reply   Report abuse  
Picture of Gustavo Arcila Gustavo Arcila - 2013-06-17 16:58:23 - In reply to message 1 from Paul Dubinsky
Hi,

are you using $objGrid -> sqlstatement to define The SQL?

  3. Re: Using 'AS FIELDNAME' in sql fails   Reply   Report abuse  
Picture of Paul Dubinsky Paul Dubinsky - 2013-06-17 17:12:01 - In reply to message 2 from Gustavo Arcila
Yes, I am.

But, I have found a work around which is to create a temporary MySQL table with the fields that I want from the first sql and then to build the phpmdg grid from the temporary table. That way the phpmdg grid sees the "as fName" as a legitimate table column and never sees the complex CONCAT statement.

Paul

  4. Re: Using 'AS FIELDNAME' in sql fails   Reply   Report abuse  
Picture of Dean Wen Dean Wen - 2014-03-01 23:27:37 - In reply to message 2 from Gustavo Arcila
$objGrid->FormatColumn("Personnel", "Personnel", 100, 100, 0, "100", "center","SELECT
* FROM Personnel");

doesn't work and I got this error

Warning: Invalid argument supplied for foreach() in /Applications/XAMPP/xamppfiles/htdocs/stem/phpmydatagrid.class.php on line 1079

in your website, it states the free version has the ability to select field to dynamic make a dropdown menu.

Please let me know. Thanks

  5. Re: Using 'AS FIELDNAME' in sql fails   Reply   Report abuse  
Picture of Gustavo Arcila Gustavo Arcila - 2014-03-02 02:24:15 - In reply to message 4 from Dean Wen
you are using a wrong format the correct one should be:

$objGrid->FormatColumn("Personnel", "Personnel", 100, 100, 0, "100", "center","select:SELECT field1, field2 FROM Personnel");