[Zope] Newbie: How to UPDATE records in MySQL?

[Alexander Staubo]

Here's a small example.

The DTML Method to view your table should look something like this::

...
<!--#in AllRecordsSql-->
  <!--#if sequence-start-->
  <table>
  <tr>
    <th>Name</th>
    <th>Age</th>
  </tr>
  <!--#/if-->
  <tr>
    <td><a href="ViewRecord?id:int=<!--#var recid-->"><!--#var
name--></a></td>
    <td><!--#var age--></td>
  </tr>
  <!--#if sequence-end-->
  </table>
  <!--#/if-->
<!--#else-->
  No records.
<!--#/in-->
...

where AllRecordsSql is an SQL Method that returns the whole table, with
columns "recid", "name", and "age".

Your ViewRecord DTML Method should look like so::

<!--#in "FindRecordSql(match_id = REQUEST.id)" size="1"-->
  <form action="<!--#var URL1-->" method="post">
  <input type="hidden" name="id:int" value="<!--#var id-->">
  <table>
    <tr>
      <th align="right">Name</th>
      <td><input type="text" name="name" value="<!--#var name-->"></td>
    </tr>
    <tr>
      <th align="right">Age</th>
      <td><input type="text" name="age:int" value="<!--#var
age-->"></td>
    </tr>
  </table>
  <input type="submit" name="SaveRecord:method" value="OK">
  <input type="submit" name="index_html:method" value="Cancel">
  </form>
<!--#else-->
  Record not found.
<!--#/in-->

where FindRecordSql is an SQL Method that does something like

  select recid, name, age from people
  where recid = <!--#sqlvar match_id type=int-->

This form uses a Zope trick that allows you to have multiple submit
buttons, each triggering a different method on the parent folder. Also
note how I use things like ":int" to indicate the type of form
variables; otherwise Zope will just stringize everything.

Your SaveRecord DTML Method could look like this::

<!--#call "UpdateRecordSql(match_id = REQUEST.id, name = REQUEST.name,
  age = REQUEST.age)"-->

Or it could look like this::

<!--#call "UpdateRecordSql(match_id = REQUEST.id, REQUEST)"-->

Or like something else; I do prefer myself to pass SQL arguments
explicitly, though, rather than just pushing the complete REQUEST
namespace. You can also use _.namespace() to pass your arguments.
Finally, UpdateRecordSql is an SQL Method that says something like:

  update people
  set name = <!--#var name type=string-->,
  age = <!--#var age type=int-->
  where recid = <!--#sqlvar match_id type=int-->

With me so far?

--
Alexander Staubo             http://www.mop.no/~alex/
"What the hell, he thought, you're only young once, and threw
himself out of the window. That would at least keep the element of
surprise on his side."
--Douglas Adams, _The Hitchhiker's Guide to the Galaxy_

>-----Original Message-----
>From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of
>piotrk@ibb.waw.pl
>Sent: 7. juli 1999 04:08
>To: zope@zope.org
>Subject: [Zope] Newbie: How to UPDATE records in MySQL?
>
>
>I have MySQL database connected to ZOPE 1.10.3. I can search
>and INSERT new records, but I don't know how to edit and
>UPDATE single records.
>
>
>I think that a single record in a database can be updated in
>the following way:
>
>1. Search the database and display the results in tabular
>format (it is already working).
>
>2. Select a single record to edit - I do not know how to make
>a link from tabular search result to a single record (i.e.
>hyperlink from a record first field to page with forms
>containing data preloaded from fields to be edited.
>
>4. POST the changed form and UPDATE the database record.
>
>
>Could you please send me some sample code? Are there any
>on-line documentation I can read? I have found plenty of
>information about searching and inserting data into internet
>accessible databases but no information about updating
>database records.
>
>TIA
>
>
>
>_______________________________________________
>Zope maillist  -  Zope@zope.org
>http://www.zope.org/mailman/listinfo/zope
>
>(For developer-specific issues, use the companion list,
>zope-dev@zope.org - http://www.zope.org/mailman/listinfo/zope-dev )
>