Login   Register  
PHP Classes
elePHPant
Icontem

Using 'AS FIELDNAME' in sql fails

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us

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