SelectQuery-Talk

Select Query Talk page

SelectQuery for the documentation.

This looks promising (I have been thinking that PmWiki could become a great front-end for MySQL). Thank you for taking this on and publishing your recipe. My initial reaction is that you should strive to find ways to make better use of the things that PmWiki already does. Not merely to avoid re-inventing the wheel, but because you want to be able to ride along on the advancements that PmWiki continues to introduce. I undertsand that it might be initially seem harder and more frustraiting to get PmWiki to recognize what you are trying to do (judging from your comments about conditionals), but if you give up and create your own conditionals then you will never be able to benefit directly and automatically from improvements to PmWiki's conditionals. From what I have seen during this past year or so, Pm seems to give an awful lot of attention and coding to help developers solve the basic types of getting-pmwiki-to-recognize-this issues. And then, once a recipe is hooked into PmWiki code, he seems to go out of his way to make sure that your recipe is not broken or left behind when improvements are introduced (or, at least, he will help find and write the fix, whether it is to the core, or your recipe). By contrast, he does not seem at all supportive when recipes go off and create their own alternative markup. I would reach our directly to Pm, on or off the list, for help on getting your recipe to make the necessary connections to the core.

So, anyway, to get to the point that I wanted to comment about, it appears that formatting multi-record results will become a big issue for this recipe (judging from the documentation references to formatting single record results and your plans for 2.0 that mention "custom markup of queries that return more than one row, using an (:endquery:) tag to mark the end of the markup to be repeated for each row." All that makes me think that you should be looking more closely at pagelists and pagelist templates, which are really wonderful, powerful and flexible features that seem destined for even greater improvements and support. See PData and this thread. If there was any way to make use of pagelists, or at least link up with the supporting code, you should. At the very least, however, you should think about making use of the type of approaches and solutions that are being used. For instance, using pages to store formatting markup, and code to distinguish first and last from the middle, which ends up being useful for incorporating table formating in templates.

Ok, now, to go off the deep end, something just occurred to me and I'll just say it, even though it may make no sense. What if you could create a recipe that fooled PmWiki into treating your MySQL structures as PmWiki's own structures? A database might be a wiki (or Wiki Field), a table a group, a record a page, and columns could be PageVariables or these new definition structures that Pm is working on using to create some kind of on-the-fly variables that can be used like PageVariables in pagelists.

Of course, MySQL can be used as the storage backend for PmWiki, but I'm not talking about replacing PmWiki's flat-file system, I'm talkning about supplementing it. (Though perhaps there are valuable things to be learned from recipes that use MySQL to replace PmWiki's backend). How can the two systems exist side by side? Maybe you can look to the intermap markup and code for ideas? For example, suppose I have two PmWikis, one regular and another that used MySQL as the backend. I can use intermap links to connect pages between the two wikis even though one uses flat-file with pages stored as files and the other uses MySQL. Maybe something in all this rambling creates a spark that helps you brainstorm and identify possibilities to explore. I hope so. Otherwise, thanks for reading along this far and for all the work you are doing on this. Pico August 31, 2006, at 07:56 PM

Thanks for the comments, Pico. The only reason I went off and did my own thing was that I was under deadline at the time -- I didn't have an immediate need for PageLists, couldn't get the variables to work the way the documentation suggested they would, and didn't have time to keep up with the amount of traffic on the list. I certainly hope I haven't burnt any bridges by creating new markup, and I definitely plan to work with Pm and the other folks on the list while developing DataQuery. Ben Stallings September 01, 2006, at 09:22 AM

Cool. I totally understand. That was great that you included us by publishing along the way. I think this will be the most exciting thing to happen with PmWiki since the last most exciting thing. ;-) Pico September 01, 2006, at 12:20 PM

GhostRider asked on the UpdateForm page how to make an edit link in a SelectQuery that links to an UpdateForm. The simplest way to do this is to use SelectQuery's "link" parameter with one of the columns of your query, for example link=username,EditUser,userid. But if you want the link to say "edit" instead, how to do that?

The answer is to make a column that just says "edit". For example (:selectquery columns=username,'edit' tables=users link=edit,EditUser,userid:)

Simple! --Ben Stallings March 04, 2007, at 10:53 AM

I've modified the code to allow the linked column to support the 'as' clause in SQL. This allows more flexibility when writing links - you are not stuck with the column name as parameter to a link. selectquery_v1_4.php Δ

Guy Moreau March 25, 2007 at 2:49 PM

I've just made another release of select query: Version 2.0 Beta 1. Here is the list of changes for this release. Please note, if you use fully qualified names always make all fields and links fully qualified names. This will prevent any potential conflicts.

Added display options:
norowcount: do not display the number of selected rows
noheaders: do not display the header row
div: display using divs instead of tables (example of use is with single column data)
debug: allows to output of a) the link data, b) the select string for debug purposes, c) the output string
Converted to database standard
Fixed a bug with fields that were both in the columns list and linked fields not showing
Current TODO:
Currently cannot handle some fully qualified and some not fully qualified when dealing with links

selectquery_v2_beta1.php Δ

Guy Moreau March 29, 2007 at 9:21 PM EST

That's great, Guy! Please consider yourself the new maintainer of this recipe, and update the documentation above to reflect the setup and features of the new version. Thanks!!

Ben Stallings March 30, 2007, at 08:39 AM

Greetings, while using SelectQuery to throw pull things from db and put it on pmwiki, I've hit against a wall I cannot jump over by myself. I've set up a private project, that uses mysql to gather some data. Table contains 4 columns and few hundreds of rows, now the problem: Sometimes members want to see all stuff we've gathered so far. So they point a page, that contains dbquery. The table containing 800 rows in hard to read, so I'd split the result into several pages. Put something like 'limit 30' somehow, and manipulate offset automatically provigind valid links to other 'limit 30,<number>'. Is there a native way in SelectQeury to do this or do I have to hack sources ;) ? Thank you.

And another one, if it possible to pass more than one argument to link= ? I might be wrong, but only first argument is beeing passed, others are ignored.

GhostRider, March 31, 2007 at 00:28 CEST

GhostRider: Currently, SelectQuery does not support pagination. I don't suspect it would be too hard to add such a feature. I'm thinking a page action such as ?SQPage= format might do this nicely or maybe new parameters to selectquery 'Start=' and 'Count=' (limit is a SQL statement and is not appropriate for this function) . Then, SQ could add links to Previous Page and Next Page (and possibly First as Last as well). I will look into it this weekend.

As for the link parameter, make sure you put a semicolon separating the links from each other.

Example: link='column1,link,column2;column3,link2,colunm4'

If you did that and it still doesn't work as expected, please provide me with your select query statement for me to debug.

Guy Moreau March 31,2007 at 3:41 PM EST

The problems seems that I'm trying to send multiple values to one page, like this:

(:selectquery tables="tab" columns="one,two,three,flag,'edit'" match="one,two,three,flag" 
  link="edit,UpdateEntries,one;edit,UpdateEntries,two;edit, 
  UpdateEntries,three;edit,UpdateEntries,flag" 
  against=searchfor order by one DESC:)

Table is simple, it only contains 4 rows, which are all varchar().

And now, I expect 'edit' link to be generated that looks like this:

http://test.org/Main/UpdateEntries?one=blah?two=bleh?three=foo?flag=D

Sending multiple variables to another page does not work. Link= would generate only agrument (that is e.g. ?flag=<sth>), ignoring any other variables I'm trying to send. Correct me if I'm wrong. I'd like to have it possible, while it's the most comfortable way to edit a record, when all previous values are already pre-filled. Thank you for time spent. Btw., I'm waiting impatientely for pagination feature :)

EDIT: I've just found out, that if I only pass one value, the others belonging to the same record appear automagically, so forget this. ;)

Anyway, it would be nice to pass more than one argument. Example use for it would be to provide moderation support for specific records. One could be able to generate 'moderate this' links, that would pass 'hidden' values and e.g. change the moderation flag. What do you think about it?

GhostRider, April 2,2007 at 21:47 CEST

Interesting idea GhostRider. The trick would be a simple way to tell the difference between multiple links and multiple variables in a single link. I think the simplest way would be to add extra commas to the link argument for each extra variable. It should not be too difficult to do.

A quick update on the pagination feature. Most of the code is complete - just can't change pages yet. I got hung up by trying to get the backwards compatibility code working and a nagging timeout issue. With the timeout issue being soled - I can move forward with pagination. Backwards compatibility may need to way to a later version.

Guy Moreau April 2, 2007 at 9:42 EST

I've just updated SelectQuery to beta 2, adding the pagination feature. To use pages, simply specify the count parameter to specify the number of rows to display by page. Navigation links are automatically added. You can specify any page by adding ?SCPage= to the url of the page. Also in the version is the ability to quickly migrate to version 2.0 if you already have the database standard installed on your wiki.

Guy Moreau April 9, 2007 at 11:34 EST

Where's the download link dude ? ;) Thanks. Additionaly I move to adodb stuff, and found out, that beta1 displays no results when querying multiple tables. Here's my query:

(:selectquery tables="table,type" 
columns="table.what,table.comment,table.date,type.comment" 
where="id>0" limit="10" :)

It just shows: 10 rows selected, but displays blank page below. Timeout occurs when there's no 'limit' statement. It tries to wait more than 30 secs, and quits with a message. It worked just fine when using selectquery 1.4, and it works fine when querying only one table. Thanks,

EDIT: also link= functionality has been lost in beta1 :-/

GhostRider, April 9, 2007 at 20:21 CEST

The download link is at the top of the page, i just pasted over the old link. I will look into those issues. The link parameter should work - one caveat is if you use fully qualified names in the column selectors, you should use fully qualified names in the link parameters as well. But it could very well be a bug. As for the timeout, it seems like it's getting stuck in a loop.

Guy Moreau April 10, 2007 at 11:17 AM EST

Thanks for taking on this project, Guy! I really appreciate it. Here are a few other changes I'd suggest for the next version:

Change the definition of $SQofflimits to an SDV function, so that people can define it in their config.php rather than editing the recipe file.
Get rid of the custom markup for {`variables`} and (conditionals), using $FmtPV and $Conditionals instead. I wrote SelectQuery 1.0 under deadline and wasn't able to figure out how to do it properly, but my kludge has now been perpetuated into version 2.0. It's particularly important to phase out the (conditional) markup now that Pm has said he's going to use that markup in the core for a different purpose.

Thanks again, and keep up the good work!

Ben Stallings April 10, 2007, at 10:38 AM

@GhostRider: Using Beta 2, could you please provide me with with the details of the display=debug option. This will give me an idea of what is happening on your side. EDIT: Please use this with the your broken 'link=' query. I've got a good idea that all of this is related to fully qualified field names.

@Ben Stallings: Both things are now on my to do list with this recipe. It's starting to get interesting.

Guy Moreau April 10, 2007 at 3:12 PM EST

This took longer than I expected, but after a few delays, here is beta 3. selectquery_v2_beta3.php Δ

A lot of work has gone into beta 3 to ensure that fully qualified names work properly. This version also uses strait HTML instead of wiki formating. This makes the process a little bit quicker by not having to interpret the table formating. Other added features is the ability for a link to have unlimited parameters (i.e. ?t=1?n=2 etc.). Just keep adding commas for more parameters. And finally, as per Ben's request, $SQofflimits is now a SDVA array that can be configures via config.php.

There is still work to do, such as making the conditionals be standard and putting a proper format for the pagination options.

Guy Moreau April 23, 2007 at 7:34 PM EST

Works like a charm, I see no problems, pagination is just great. Good work!

EDIT: is it possible to use non-existing column to be shown ? Like I wrote on UpdateForm comments? With beta3 I'm able to use link but only on an existing column. Of course I can have any column to be visible as hyperlink pointing to other page with UpdateForm, but I'd rather keep it as it was before: separate column that says 'edit this'.

GhostRider May 1, 2007 at 22:46 CEST

Sorry for the late reply, been ill. I will look into it. I should be fairly simple since SQL allows you to use string literals as fields. Perhaps all you need to do is add it to the list of columns as a string literal.

Guy Moreau May 5, 2007 at 11:02 AM EST

Think I've hit a bug here. I moved my database to postgres-8, changed adodb settings, and get this instead of wikipage with query specified:

Fatal error: Call to undefined function mysql_error() in /wiki/cookbook/selectquery_v2_beta3.php on line 548

Seems strange. Line 548 says:

$out = "$query\\\\\n".mysql_error();

If I'm correct, script get to this point after unsuccessfull query to db. Will hunt this to provide more data.

GhostRider, May 17, 2007 at 00:01 CEST

Hi, GhostRider. That's a bug in the program -- obviously a postgres database isn't going to have any mysql_errors! To support database types other than MySQL, the function should be $DB[$SelectQuerySettings['database']]->ErrorMsg(), I believe. Guy, would you double-check that?

Ben Stallings May 18, 2007, at 08:40 AM

Hi guys, I knew I can always count on you :) Thanks for great work. According to what you said Ben, if I change the following line $out = "$query\\\\\n".mysql_error(); to this: $out = "$query\\\\\n".$DB[$SelectQuerySettings['database']]->ErrorMsg()

I'm actually able to perform a query. Frankly, after I first run this, I saw error: permision denied for relation blabla this let me know, what was wrong with my db and fix it by granting neccessary rights to db user.

Digging in selectquery beta3 I found out another mysql stuff. Line 130:

                        if (!is_numeric($value)) {
-> here                        $value = "'" . mysql_real_escape_string($value) . "'";
                        }

I'm not sure what it does, but I'll surely hit this bug sooner or later ;) Again thanks for a good work and your support.

EDIT: PHP manual explains what this function does, and I find it quite usefull. Is there any possibility to convert it to adodb and not only mysql ? Thanks.

GhostRider, May 18, 2007 at 22:54 CEST

Thanks for finding those issues, they are remnants from the conversion to DBStandard/ADODB. That string is not 100% correct because it assumes that the recipe is using the DB array. Since beta 2, the recipe will automaticly upgrade the db constants from 1.x to DB standard. In short: $DBToUse->ErrorMsg() is the string to use. QUICK EDIT #2: This does not seem to work... FINAL EDIT: Ok, here is the string to use: $DB[$DBToUse]->ErrorMsg(); This also exposes another bug in the recipe! In the execute statement, it would use the db array and not the determined correct connection object.

As for the second mysql statement, i don't see an equivalent for ADODB. I could be wrong, but this might need some more research and possibly an extension to ADODB. Alternatively, there may be a pure PHP solution to that one. QUICK EDIT: I believe this code is attempting to prevent SQL Inject - pure PHP should be able to handle this.

I'm still working on that and the string literal in link issue.

Guy Moreau May 22, 2007 at 8:09 PM EST

And again, your favourite GhostRider with another issue in beta3 ;) Here it is. I've got a form, that is supposed to perform full text search against given pattern. The code is below.

(:input form:)
(:input text searchfor:)   (:input submit value="Seek":) 
(:selectquery display=debug tables="table,types" columns="table.who,table.what,types.commenten,table.comment,table.flaga" 
match="table.who,table.what,types.commenten,table.comment,table.flaga" 
link="table.what,UpdateEntries,what" against=searchfor where="table.typ = types.typ" order by who DESC:) 
(:input end:)
[[SubmitEntries | Submit ]]

So far it's simple. Now the problem. If I enter pattern beggining with a '*' e.g. '*blah*' all is fine. If I don't - here what it spits out in me (I'm using 'blah*' pattern).

SELECT table.who, table.what, types.commentpl, table.comment, table.flaga, what
FROM table,types WHERE MATCH (table.who,table.what,types.commentpl,table.comment,table.flaga)
AGAINST ('blah*' IN BOOLEAN MODE) AND table.typ = types.typ
ERROR: syntax error at or near "AGAINST" at character 190 \\

When using display=debug, it also spits out this:

Warning: Cannot modify header information - headers already sent by (output started at (dir 
removed)cookbook/selectquery_v2_beta3.php:105) in /var/www/wiki/pmwiki.php on line 884

GhostRider, May 26, 2007 at 23:30 CEST

Hello again.

I will have to look that one up, I have not used the forms method of this recipe and I'm not familiar with that part of SQL. AS for the warning, that is due to the way that debug messages are displayed. They use the print statement, and result in the warning. If debug is not used, that warning will not show. So, in short - ignore the warning with debug!

On a status update - I have fixed in my dev version the string literals in link statements. I hope to have a new version out by the end of the week (hockey playoff notwithstanding).

Guy Moreau May 28, 2007 at 08:15 AM EST

Long time no post!

I am putting up what I currently have - we'll call it beta 4. (selectquery.php Δ) A couple of bug fixes in this release - but nothing on that MATCH / AGAINST issue since I could not find any documentation on that SQL statement.

Notting really major here:

Removed one of the two mysql specific statements (Fixes the proper output of error messages)
Fixed a bug with string literals

I still need to look at fixing the variables and conditionals to be standard with pmwiki.

Guy Moreau July 06, 2007 at 08:32 PM EST

Hi, Guy. The documentation on MATCH/AGAINST is at http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html

Keep up the good work!!
Ben Stallings July 06, 2007, at 10:50 PM

Hello,

I am attempting to use SelectQuery and UpdateForm to maintain a MySQL table that has a field that is required to be unique. I start by collecting just the unique field using a simple form to populate a custom PageVariable and link to the page with the SelectQuery. I would like to use the PageVariable as the selection criteria for the SelectQuery and then either put up an empty UpdateForm if there is no current entry or a populated UpdateForm if the unique field is already in a current entry. I don't really want the results of the SelectQuery to do anything except let me know if the entry for the unique field is already in the database. Is this a reasonable expectation for these Cookbooks? Any pointers to examples that might help me understand how to create interactions between SelectQuery and UpdateForm.

Thanks,

Rick Cook January 07, 2008, at 00:32

Hum, good question. I don't think that it can be done directly, but I'm not very familiar with UpdateForm. Then again, maybe with statements, you might be able to pull it off. I guess if record count is 0, display update form else display something else.

The record count is not directly stored in a page variable, only records - and not properly.

I hope this helps.

On other notes, I'm trying to discover and fix a nagging blank results page error some people have been getting. So far, every thing I try seems to fail somehow. It is very hard when I can't reproduce the problem on my Dev web page.

Guy Moreau January 2008

Minor update: I've fixed the bug that some were experiencing with missing query results. I will be releasing an update shorty.

Guy Moreau January 22, 2008, at 8:11 EST

Beta 5 is now online! Look at the top for the most recent version. What new? Version 2.0 Beta 5, January 27th, 2008 by Guy Moerau <gmyx@gpws.ca>

Bug Fix: 'as' statement was not being honered in column headers
Clean up some code in the query row section - redundent access to field data
Bug Fix: On some installs, results were always blank.

The biggest highlight in this version is that it should fix the nagging missing content bug for eveyone. I'm still looking into migrating to page variables or alternatively (: :) style command.

Guy Moreau January 27, 2008, at 13:27 EST

Hi Guy,

I've got a rather simple question: I'd like to implement a mysql-statement like this: "SELECT name FROM db WHERE name LIKE '%foo%';" I already got this far:

 (:selectquery columns="name" tables=db:)
 (:input form:)
 (:input text where value="name LIKE '%foo%'":)
 (:input submit value="Search":)
 (:input end:)

Although this works fine, I have the rather confusing "name LIKE" in the text-field (I am very sure my users can't handle this). How may I get rid of this? I can't use the "MATCH-AGAINST"-algorithm, because the database does not support fulltext search on this column and i am not the db-administrator.

Thanks in advance

Tobias, February 29, 2008

Hello Tobias,

As far as I know, this ain't possible, yet. I will look more into it and add it if required.

Guy Moreau March 3, 2008, at 11:51 EST

Hello all,

A new version of Select Query is available. I've decided to eliminate the 'beta' tag since it's just ongoing development. I consider this version to be stable, but still requiring some work.

The new feature of this build is the addition of the 'type' parameter that has been added to the match / against to allow the use of like statements. Further more, if there is no leading wild card, the "like" operator will automatically add a leading and trailing wild card making the functionality more user friendly.

Guy Moreau March 18, 2008, at 20:19 EST

Is there still a SelectQuery that does not need/require ADOdb (or Database Standard?) to be installed?
Or, better yet, is there some manual somewhere on how to get this installed properly? I'm completely lost. I only need SelectQuery and UpdateForm for my purposes, and I had UpdateForm working fine, but when adding SelectQuery and following some instructions (there aren't many here on it, to be honest), it seems ADOdb does not work. Could someone provide functional installation instructions for SelectQuery and Database Standard? It seems to go wrong as soon as I add


ADOdbConnect('some_name');

to local/config.php This is the error I get:


Warning: mysql_connect() [function.mysql-connect]:
Unknown MySQL Server Host 'my_db_username' (1) in
/home/www/pmwiki/cookbook/adodb/drivers/adodb-mysql.inc.php on line 365

Fatal error: Call to a member function on a non-object in
/home/www/pmwiki/cookbook/adodb-connect.php on line 94

Thanks in advance!

~Someone.

Hello Someone...

You asked "Is there still a SelectQuery that does not need/require ADOdb (or Database Standard?) to be installed?\\" The answer is yes: Version 1.4 does not use ADOdb. I does not contain any of the new features added to version 2.0 of course.

Have you looked at the "New Installation" section of the SelectQuery Page? You just need to add the connection information. You should not connect directly to ADOdb. Let the recipes do that for you.

Let me know if you have any problem with the installation instructions and I will try to clarify them for you.

Guy Moreau April 17, 2008, at 19:49 EST

Still does not work. It needs to be described somewhere how to use both UpdateForm and SelectQuery. Both connect with the database, and it seems they conflict while doing that. ~Cees

Hi there,

I allready installed the cookbook and everythink is running okay. In the explanation you are talk about using the JOIN clause. Is that possible? I would like to join 3tables..

Daniel Nov 24, 2008, at 22:11 met

Hi, Daniel. Yes, you can join your three tables in one of two ways. The easy but denigrated way: (:selectquery columns=tableA.colA,tableB.colB,tableC.colC tables=tableA,tableB,tableC where="tableA.colA = tableB.colA and tableB.colB = tableC.colB":)

or the more correct but less easy way: (:selectquery columns=tableA.colA,tableB.colB,tableC.colC tables="tableA join tableB on tableA.colA = tableB.colA join tableC on tableB.colB = tableC.colB":)

Hope this helps. Ben Stallings November 25, 2008, at 09:01 AM

That is correct. The difference is using the where clause is good for a couple of tables but can easily get run-away with multiple tables. Using the join clause is more powerful but requires knowledge of the SQL in use.

If you use the Join clause and plan on using some of the other features such as sort, make sure to use the AS clause in the table name.

Example: (:selectquery columns=tableA.colA,tableB.colB,tableC.colC tables="tableA as tbla join tableB on tbla .colA = tableB.colA join tableC on tableB.colB = tableC.colB" order=tbla.cola :)

Guy Moreau November 26, 2008, at 10:31 EST

I have successfully configured SelectQuery and have a question about some customizations. I've read everything int the SelectQuery docs and in this discussion but did not discover a way to override the field name as the column header name. For instance the field name is 'mvdate' and I would like the display to show 'Date' in the column header.

Kerry Townsend December 13, 2008, at 10:55 MST

Kerry,

Use the 'AS' clause in the columns parameter.

Example: (:selectquery columns="mvdate as Date" ... :)

Guy Moreau December 16, 2008, at 10:27 EST

Guy,

I'm working on the Same Project as Kerry Townsend. Thank you for the 'as' help above. Now I am tackling a new issue. 'Character' is a reserved word it seems so I'm using `Character` as my option since that is the field I'm searching by and also in the SELECT statement. When not using custom display, the `Character` field is NULL for some reason when it should be showing the Name of the character (which is what is in the `Character` field). When I use display=custom I can not retrieve any of the fields using {PlayerName`}, {`Level`}, ect. Using {``Character``} seems to echo the POST value of my input called `Character`. I have tried creating an Alias of `Character` but without luck. My syntax for that looks like this: columns="PlayerName,*other fields...*,`Character` as Name" The Alias seems to work because the printout of the row has the last field as Name rather than `Character`, but still it does not echo the value of the field. Is this a bug with reserved names? If so, what besides changing the name of the field can I do to get this working?

Brian December 16, 2008, at 9:36 MST

Brian,

Interesting question. I will have to research it more but I think you are right that it a bug with a reserved word.

Guy Moreau December 17, 2008, at 19:43 EST

Guy,

I figured out a solution by using tablename.fieldname (Characters.Character) instead of `Characters`. Then I gave the field an alias and it began displaying in the print out of my results. Please still look into that bug though I you could.
I am still having difficulties using display=custom. Can you please extend your instructions about it?

Brian December 17, 2008, at 22:58 MST

Brain and Kerry,

I know what is causing the problem - the back tick. Now to fix the code. As for custom - I didn't write that part, so I don't know exactly how it works. I think that custom ignores the as clause since the value is assigned before the as clause is parsed by the php.

Guy Moreau December 20, 2008, at 11:20 EST

Guy & Brian,

Do you have any examples of display=DIV?

Kerry Townsend December 26, 2008, at 9:57 MST

Hello All, a new version with minor changes:

Version 2.0 build 7, December 21, 2008 by Guy Moreau <gmyx@gpws.ca>
* New variables:
* * $SQFieldId incase the DB uses something else than '`'
* Bug Fix: backtics causes the parser to ignore fields names

Kerry and Brian: This should fix your bug. Thank you for finding it!

As for a div example: I use (:selectquery columns='topic_title' tables=phpbb_topics where='forum_id=4' link='topic_title,http://.../forums/viewtopic.php,topic_id as t' limit=5 display='norowcount,noheader,div' order='topic_time desc':) (the real address is removed) to display the last 5 posts from a forum.

Guy Moreau December 27, 2008, at 11:56 EST

Guy,

Thanks for the bug fix, Kerry or I will implement this fix sometime in the near future. Thanks again for the awesome add on.

Brian January 09, 2009, at 11:15 MST

A new release with some minor bug fixes and new features:

2009-06-30: Version 2.0 Build 8:

Changed Variables introduced in b7 into the $SelectQuerySettings[] array since it didn't work
* $SQDelim becomes 'Seperator', defaults to '.'
* $SQFieldID becomes 'FieldId', defaults to '`'
Added Variable 'TableId' to $SelectQuerySettings[], default none
By Request: New parameter to select run-time the connection for multiple connections
* connection = "" selects the array with the same name in config.php to use
Bug Fix: Empty FieldID causes warnings has been fixed.

Noting major here except the ability to use different connections without special php code.

Guy Moreau June 30, 2009, at 10:57 EST

A small improvement that may be useful.

In a "div" display, it's impossible to make all cells from a same column to display differently from other cells.

To fix this, you just change line 590 from : $out .= '<div class="selectquerycell">';

to : $out .= '<div class="selectquerycell col'.$i.'">';

This way, cells will take style from ".selectquerycell" class AND ".col0" (col1, col2,...) classes (0 = first column, 1 = second, ...).

You could also write something like this : $out .= '<div class="selectquerycell '.$columns[$intcount]['field']['name'].'">';

This way, every cell will use a fieldname CSS class if it exists (for instance : "name" cells will use the ".name" class).

bye,

François (12/10/09)

Tried to use this recipe for an intranetwiki. If i use just one column, it works fine, but when i use two columns, just the headers and the rowcounter are shown.

I also fixed a bug. The 'connection'-markup can't work up to this point, because the else-statement is on the wrong place. you have to change it to

// check for run-time override
	if ($params['connection'] == '') 
	{// 1.If

	 	//check for backwards compatible upgrade to DBStandard
  		if ($SelectQuerySettings['database'] == '') 
		{ //2.if
  			// check if the constants are defined
  			if (defined('DB_SERVER')) 
			{ //3.if
  				// first constant defined - try to map to the db standards	    			
  				foreach ($Databases as $DBSName => $DBSItem)
				{ //4.if
  					//check it's properties
  					if 
					( //5.if	
						$DBSItem['hostname'] == DB_SERVER && 
  						$DBSItem['database'] == DB_NAME &&
  						$DBSItem['username'] == DB_USER &&
  						$DBSItem['password'] == DB_PASS) {
  						// we've got a match!
  						$DBToUse = $DBSName;
  					} //5.if-end
  			} //4.if-end

  			//if all constantd difined and still no match - we can add it ourselves
  			if ($DBSItem = '' && defined(DB_NAME) && defined(DB_USER) && defined(DB_PASS)) 
			{ //4.if
  				// we can go ahead and add it to the standard
  				$Databases['SelectQueryContantsConverted'] = array(
  					'driver' => 'mysql',
   					'hostname' => DB_SERVER,
   					'database' => DB_NAME,
   					'username' => DB_USER,
   					'password' => DB_PASS);
  				$DBToUse ='SelectQueryContantsConverted';
  			} //4.if-end
  		} //3.if-end

  	} //2.if-end
	else
	{ //else für 2.if
  		//print 'DBS name: '.$SelectQuerySettings['database'].'<br />';
  		$DBToUse = $SelectQuerySettings['database'];
  	} //else für 2.if end

  } //1.if-end
  else 
  { //else für 1.if
    //connection override was on the wrong place, also the variable was false.	
    $DBToUse = $params['connection'];
  } //else für 1.if end

Sorry for the long source, but it is easier to see and understand, where the else`s has to be placed.

Hopefully, someone can help me with my output-problem. The relevant debug-text is this. </br><br /><table class="selectquery"><tr><th>Header a</th><th>Header b</th></tr><tr><td></td><td></td></tr></table>

Sincerly Pr0saek

Thanks Pr0saek for the database selection bug fix. I have not had the problems you describe, except when the query returns no answers. There seems to be little traffic on this page, so I don't know when you posted this. It appears that the fix you mentioned has been applied, but there is a typo on approximately line 106. It says if ($param['connection'] when of course it should be if ($params['connection']

Today is 2010-12-19. I have added a few things, and am working on adding some page variables. If you still have problems, perhaps add a line to this.

    Vince

I'm new here and have been trying to check out whether selectquery or dataquery will fit in my application best. While doing so with selectquery I was unable to get variable substitution for fieldnames to work (with display-custom). It looks like there's a problem with //create page variables and conditionals that can be used in the page

	SDVA($SQdata,$queryd->GetAssoc());

[that's around line 500]

Looking back to in V1.4 where it works MYSQL's mysql_fetch_assoc() is used and returns the current row as a one dimensional associative array.

in V2.8 (using ADODB) ADODB's GetAssoc() is used and returns a two dimensional array with the top level index being the value of the first column (and the column name and value absent from the second level arrays). That results in the values of the first column, not the column names, being added as indices in $SQdata.

What it seems to need is SDVA($SQdata,$queryd->FetchRow()); As the mode has already been set, $DB[$DBToUse]->SetFetchMode(ADODB_FETCH_ASSOC);, that FetchRow roduces an associative array.

 - - - - - - -

I also found that I had to put an include_once for adodb-connect.php into my config.php, otherwise I got "Warning: include_once(adodb.inc.php) [function.include-once]: failed to open stream: No such file or directory in ......\adodb-connect.php on line 59". That's possibly because of scope of the $ADOdbLocation variable, but easily overcome by adding the include_once to config. I appreciate the work put in by the authors and hope my comment is helpful - of course I may have simply misunderstood/misconfigured things.

Thanks, Michael T. 01 Jan 2011

Sorry to be slow in responding. I would like to update this recipe, with my changes, but I could not solve the problem you mentioned. Vince

Added Sept 26 2013 The new version adds a display=pmtable option to allow use of PM's SimpleTable format, instead of straight html. Also a parameter nodata=message, for a customizable, per query message to be displayed if the query is empty (and there is no error). For Michael, and anyone who would like the query to set page variables, or page text variables, it will not be easy. You can of course set them in the recipe, but they will not show up on the screen, or at least I could not make it work. Some of the remnants are left in the source code. The problem seems to be that page and page text variables are set before the is executed. And actually you want this so you can use these as part of the query. One way around this is to set session variables, or cookies, that can be read later, perhaps in a page pointed to by one of the links created in the query.

Nevertheless, this recipe has been enormously useful to us, and I really thank Ben Stallings and Guy Moreau.

    Vince G

 - - - - - - -

July 20 2015

Around line 781 is an 'echo "Output is $SQdata[$fieldname]";' that shouldn't be in production code.

Around line 484 is some $qcolumns variable assignment that has a typo. $SelectQuerySettings['FieldId'] instead of $SelectQuerySettings['FieldID'] (the latter being the correct version).

Append: Vince: for what Michael T. is referring to regards the 'SDVA($SQdata,$queryd->FetchRow());' is that display=custom does not set page variables without it. Using FetchRow(), page variables correctly get set for display=custom markup and {`column_name`} page variable access.

    unfy

I had trouble to display UTF-8 characters from database with SelectQuery4.0. My solution was to add the following command when opening the database connection:

    // Connect to database
    try {
      $db = $Databases[$this->database];
      $this->dbh = new PDO($db['driver'] .':host='. $db['hostname'] .';dbname='.
      ], $db['password'], array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
    } catch (PDOException $e) {
      $this->error = $e->getMessage();
    }

Uwe (29/06/2018)

I needed to select fields that I do not want displayed, for sorting and to pass via the link (namely the primary key) so I modified the code a bit.
I'm betting others would like this feature too so here it is:

within the function query($params) {

...
  if ($where == '') return FALSE;\\

      if ($params['includes'] > '') 
      {$include =",".$params['includes'];}
      else
      {$include ="";}
    $sql = 'SELECT '. $params['columns'] .$include.' FROM '. $this->TableID . $params['tables'] . $this->TableID .' WHERE '. $where;

...

This allows me use the Markup like this:
(:selectquery columns='lastname,firstname,phone,email' includes=primarykey tables=my_table where=1 order=lastname link=lastname,Members/Detail,primarykey display=custom; :)

Kirk Siqveland 2019-12-14

Taking that idea one step further, I now generate wikiGroup/wikiPage links from the Query results:

The link part of the markup can be literal or query-result. So for our Scout Troop, I can have Patrols as Groupnames with a page for each Scout:
so the link part of the Markup looks something like this:
... link=scoutname,[patrolname]/[scoutname]
the square brackets designate a query result, but you can mix and match so these also work:
... link=scoutname,Members/[scoutname] or
... link=scoutname,[patrolname]/PatrolRoster
Here's the code:


// Turn the field into a link.
  // If the destination includes [fieldnames], use them to build the link
  if(strpos( $dest, "[" ) >0 ){  
      $peg = strpos( $dest,"/" ) ;
      $GroupName = substr($dest, 0, $peg) ;
        if( strpos( $GroupName, "[" ) !== false){
            $fieldname = trim($GroupName, "[ ]") ;
            $GroupName = $row[$fieldname] ; 
        }

      $PageName = substr($dest,$peg + 1) ;
        if( strpos( $PageName, "[" ) !== false){
            $fieldname = trim($PageName, "[ ]") ;
            $PageName = $row[$fieldname] ; 
        }

      $row[$field] = '[['. $GroupName . '/' . $PageName .' | '. $row[$field] . ']]' ;
  }else{

Kirk Siqveland 2021-03-12

Talk page for the SelectQuery recipe (users).