Recent Changes - Search:
Cookbook /

DataQuery-Talk

I've decided to split this over-ambitious project into three smaller pieces. Here's the way I hope to roll it out in the coming weeks:

  1. DataQuery
    • DataQuery 0.1 beta is a stripped-down version of DataQuery 0.1 alpha that just handles the I/O of getting page data into and out of a database table. It accepts the read, write, ls, and exists methods of PageStore and acts just like the wiki.d PageStore object. It handles incoming data from ZAP forms and standard PmWiki edit forms as well as custom EditForms with one input field per table field (like those produced by DataQuery 0.1 alpha).
    • DataQuery 0.2 provides a customization page at DataQuery.Groupname to allow the query at Groupname to use a different key field, join multiple tables, etc.
    • DataQuery 0.3 provides the ability to delete records, and accommodates tables with underscores and mixed caps in their names.
    • DataQuery 0.4 provides a simpler method for specifying calculated fields and joins, including joins to multiple copies of the same table. Additional functionality to tie in with DataPlates.
  2. DataPlates
    • DataPlates 0.1 will be a stripped-down version of DataQuery 0.1 that just handles the automatic creation of edit forms, search forms, pagelist templates, and query configuration forms. This will be a separate recipe because it will require ZAP.
  3. FormExtensions
    • FormExtensions 0.1 will provide client-side input masks via JavaScript (for entering dates, phone numbers, etc.) and support for the HTML <label>, <fieldset>, and <legend> tags.

DataQuery 1.0 will be a single package (.zip file) incorporating all three of these recipes plus the Database Standard recipe and ADOdb, to facilitate new installations. The DataStore recipe will still be available in case people want to use it separately from DataQuery.

Comments welcome!
Ben Stallings January 06, 2007, at 04:51 PM

Many thanks to Kathryn Andersen for beta-testing DataQuery with SQLite!

Ben Stallings January 18, 2007, at 05:36 AM

Many thanks to Gary Berger for beta-testing with MS SQL Server!

Ben Stallings January 23, 2007, at 05:43 PM

I can't seem to get this working with phpBB. This seems to not know what to do about the underscores in the table and field names. I've tried all of your demo site, but cant get the underscores to work properly.

Guy Moreau February 11, at 10:49 AM EST

Sorry for the delay in getting back to you, Guy -- I'll contact you by email!

I've just discovered an incompatibility with the newest beta of PmWiki. I'll release a patch of DataQuery soon, but meanwhile here's the fix: change line 202 from 

 $group = FmtPageName('$Group',trim($pat));

to 

 $group = FmtPageName('$Group',trim($pat,", "));

Ben Stallings March 06, 2007, at 06:28 PM

Version 0.4 beta includes some major changes to the query configuration variables, in anticipation of DataPlates. The "match" variable is gone, replaced by a [var] syntax that allows you to match any value submitted by get, post, or cookies in your "where" variable. Also the separate variables for calculated fields are gone; you now list calculated fields alongside regular fields in the "display" variable. The "display" variable now must include AS clauses for each field name and have a linebreak after each one. Sorry for the inconvenience, but it'll be worth it...

Ben Stallings March 16, 2007, at 03:49 PM

Is there a way to see the query SQL text for debugging purposes? I wonder if a flag could be set in the DataQuery.php file so that it would appear on the DataQuery list page. Should we add a $sql string var to the DataQuery->config array? This could hold the SQL query text. It could be displayed at the bottom of the configuration page?

Matt May 12, 2007

Has anyone else got this error when "listing" a query?

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near...

Matt May 12, 2007

Hi, Matt. Yes, if you edit the dataquery.php code, you'll find a number of places where you can uncomment lines to display the SQL queries. For the trouble you're having, find the DataQuery::ls() function (about 9/10 of the way down the file) and you'll see two lines that say 

 //Abort($sql); //uncomment to debug

There are two because the ls() function runs two queries: the first retrieves and caches all information from the entire query and the second pulls only the info necessary for a pagelist. If you remove the first pair of //s from one of those lines, and then do a pagelist of a DataQuery group, PmWiki will display the SQL query. Be sure to comment the line out again when you're done debugging.

Ben Stallings May 13, 2007, at 12:07 PM

I've discovered a bug in ADOdb that prevents its Replace method from working with MS Access tables that have spaces in their names: it tries to use the first word of the table name instead, and of course that doesn't work. I've reported the problem to the ADOdb community, but there's no guarantee they'll fix it, and I'm not comfortable enough with the software to fix it, so I may just need to change DataQuery to not use the Replace method.

(The Replace method allows you to avoid writing SQL insert and update statements; instead you just send it the table name and the array of values to update, and it writes the SQL for you. It first tries to update, and if the record doesn't exist, it inserts a new record.)

Since no one had yet claimed to have gotten DataQuery working with Access, this is not really news, but it does mean that if you were planning to try to get it working, you should be prepared for a struggle. Also, when I have gotten ADOdb working with Access, I've had to use the PConnect function rather than the Connect function that DataQuery uses, so that's another obstacle. Just part of the fun of using other people's software!

Ben Stallings September 10, 2007, at 04:28 PM

Ben,

Your demo site has SQL syntax errors reported in the header of the ListRecordsInAQuery page.

Pico September 11, 2007, at 12:13 PM

New lines in text area fields appear as [[<<]] in edit field using dataplates with dataquery and ZAP.

Is this a bug? Could it not be hidden from the user by replacing them with proper carriage returns back and forth when editing and saving the form?

Here are the steps to reproduce -

  • Create a Database fields of type text, medium text or long text. It results in a textarea in the Edit page for the record.
  • Edit this entry, enter a few lines of text each terminated by a carriage return and save
  • Return to the edit page again
  • You will notice that all the carriage returns you entered appear as [[<<]] .

How do I get around this issue?

farhad Mar 21, 2009

Hi, Farhad. Believe it or not, this is a feature, though it's been so long I don't remember why I did it this way. I suggest you disable it and find out whether or not it works better.

To disable it, edit the dataquery.php file and go to line 200, which should read 

 $page['text'] .= "$fore$key: ".str_replace("\n","[[<<]]",$value)

Change it to 

 $page['text'] .= "$fore$key: ".$value

Let me know how it goes.

Ben Stallings March 22, 2009, at 08:45 AM

Hi Ben,

That didn't help. I changed this line on line#205 in dataquery2.php. I also removed all occurences of str_replace with
in dataquery2.php and dataplates.php, that didn't help either.

Thanks,

Farhad

Actually it worked. It did remove the new line control code but now the field does not display properly unless in edit mode.

farhad March 23, 2009, at 10:41 PM

Hi,

What can I do ?

''Warning: First argument to array_keys() should be an array in /home/psycho-oncologie/html/WikiPsyOnco/cookbook/dataquery.php on line 23 No databases specified in $DQglobals["databases"]''

Thanks a lot Jeanduri 4 mai 2009

Hi,

I installed Data Query as well as Data Plates. Everything works fine - great work! There's just one thing that doesn't work: Making a search with the {$Group}.Search-Template, the search results don't show up in the desired way as they do on your demo page, but the show up in the standard search result format - on my default homepage instead of the group's page. Have I got any wrong settings?
Thanks for your help!
Michael June 29, 2009, at 11:44 AM

Hello,

I installed everything as described above and the other related pages. However, I get the following message when trying to load my page:

Parse error: syntax error, unexpected T_STATIC, expecting T_OLD_FUNCTION or T_FUNCTION or T_VAR or '}' in /homepages/5/d29011413/htdocs/elfgestirn/pmwiki/cookbook/adodb/adodb.inc.php on line 434

Did I do anything wrong? Thanks in advace for your support,

Marcel

Talk page for the DataQuery recipe (users?).