[pmwiki-users] Interacting with MySQL

[Ben Stallings]

Rick Cook wrote,
> I am attempting to connect a PmWiki form (or forms) to a MySQL database. I was 
> able to quickly get UpdateForm to allow me to enter new records into the 
> database including enforcing some "Required" fields and having one field that 
> is required to be unique across all rows. I could also update an existing 
> record if I included a "where=UniqueField" clause in the "(:update 
> form...:)". In that mode, I could not create new entries in the database.
> 
> With those two results, I thought "Okay, I just have the unique field entered 
> in a simple form, use SelectQuery to see if that value exists in the database 
> and put up a populated UpdateForm for existing entries or an unpopulated 
> UpdateFrom (except for the unique field). After several hours attempting to 
> get the data from the simple input form using $_POST, I was successfully able 
> to get the SelectQuery populated by creating a custom PageVariable and 
> populating it from the POST data.
> 
> Now I am stuck. I have the successful SelectQuery results and can see the 
> record if I don't use "display=custom". I can't seem to grab any of the data 
> from the query to use in any decision process or to populate the form - the 
> {`fieldname`} syntax does not seem to be working (or maybe I don't know how 
> to make it work).

Rick, it sounds like you're trying to use UpdateForm with a table whose 
primary key field is not an auto-incrementing integer.  UpdateForm is 
designed to be used with tables that have integer, auto-increment key 
fields.  The only way that it knows it's supposed to create a new record 
is if the key value is *not specified* -- in that case, a new record is 
created with the next key value.  If the key value is specified -- even 
if that key value doesn't yet exist in the table -- UpdateForm attempts 
to use an update rather than an insert statement.

(The good news is, you only need one form, not two like you're 
describing.  If you use it correctly, the same form will handle both 
inserts and updates.)

If I'm right, you have a couple of choices:
1) change your table so that it has an auto-increment key field
2) use DataQuery instead of SelectQuery and UpdateForm
3) change your copy of UpdateForm so that it can handle other kinds of 
key fields, and then contribute it back to the recipe page when you've 
got it working

I regret that I am not available at this time to do more development on 
UpdateForm myself, but I'm happy to answer other questions you may have. 
  Sorry the documentation was unclear -- I'll fix it now!  --Ben S.