Problem with parenthesis in sql

Archived from the Xataface Users forum.

Sten — Fri Jun 15, 2007 6:40 am

Hello Steve,

I am using a calculated field with 7.1.

Here is my statement
__sql__=”select *, (quantite/nbre_poses)*nbre_pages as nbre_feuilles from fichestravaux”
all fields are on 1 as default and I tested to be sure the query is OK.

Although I have this error message
Cannot use a scalar value as an array in /var/www/html/dataface-0.7.1/Dataface/Table.php on line 1793
It is about the value of $data[‘columns’]
Looking into the source I saw that there is a problme with parenthesis inside the query.

Please do you have any hunch ?

Kind regards
Jean

shannah — Fri Jun 15, 2007 10:53 am

Hi Jean,

Thanks for posting this.ÃŠ The SQL parser class that I am using (based on the PEAR SQL_Parser class) doesn’t handle arithmetic (e.g. (quantite/nbre_poses)*nbre_pages), unfortunately.ÃŠ I have improved the class a great deal to make it handle most cases, but this one is remaining.

I would like to add this support but it will require a an evening of very fancy stepping to make it work.
For now, you can “cheat” by creating a dummy field, then implementing the %fieldname%__display() method to override the display value of the field.
e.g.
__sql__=”select *, ‘’ as nbre_feuilles from fichestravaux”
Then in your nbr_feuilles__display(&$record) method you would have:
(quantite/nbre_poses)*nbre_pages
function nbr_feuilles__display(&$record){
return ($record->val(‘quantite’)/$record->val(‘nbre_poses’))*$record->val(‘nbre_pages’));
}
Hope this helps.
Steve

meikel — Tue Jun 19, 2007 12:35 pm

Hi Steve,
I could only get this to work in using an existing field in the table (i.e. the ID number of every entry/row in the table) as a “dummy field”. After that everything went as expected. With the “empty” query field (‘’ – 2 Apostrophes) I only got an error messages in ../Dataface/Table.php on line 1789…

Kind regards
meikel

PS: Very powerful framework!!

shannah — Tue Jun 19, 2007 12:53 pm

Thanks for posting this Meikel.ÃŠ Evidently there are some more things that need to be touched up in the SQL parser - but your solution looks like a good one in the mean time.

-Steve