5 replies to this topic

[jon]

I'm trying to find the best way to, from within a Snippet, determine all of the Column Names for a Table in an External Database.

Background:
With that information, I want to build a generic snippet that will get all the data off a form, by looking for all fields on the form that match column names in whatever table the Form is supposed to update. And, before that, pre-populate the form with existing values if the specified Row already exists, i.e. - a combo Insert/Update form.

Any help would be appreciated. Feel free to point me to another forum topic, as I couldn't think of a relevant Search criteria to find such a topic on my own.

[Cris D.]

http://au.php.net/mysql_field_name will collect the field names if you know the offset. You could do a count and collect 'foreach' fieldname. However, I have found the easiest way to collect a record is by the id, use getIntTableRows to collect the $rs based on the $where='id=n' (or any other criteria for collecting the row), use mergeCodeVariables with a form chunk to pre-fill in the data with the $rs, then <on submit> getFormVariables to either updIntTableRows if the id is filled or putIntTabaleRows if the id is blank. The FormHandler snippet does a lot of this latter table entry already.

Another way of handling this is to use a snippet similar to this one that I wrote that allows you to identify the form fields in the snippet call, therefore programming the table fields into the snippet as variables instead trying to read them with new code.

Edited by Cris D., 04 December 2007 - 05:26 AM.

[jon]

Thank you, Chris. I also will be trying dbExtQuery with SHOW COLUMNS FROM table-name, which I just found in a MySQL forum.

I'll respond with what works the best for me.

My goal is to create a very generic set of Snippets that can be used by people with HTML and phpMyAdmin skills, but no PHP.

Edited by jon, 04 December 2007 - 05:53 AM.

[Cris D.]

Quote

My goal is to create a very generic set of Snippets that can be used by people with HTML and phpMyAdmin skills, but no PHP.

The original FormHandler snippet will send data off easily to an internal or external table to the correct fields if they are names correctly and have a prefix prepended (see the in-snippet documentation). The only thing it won't do is pre-fill data. That will be the hardest part of what you are trying to do while steering away from PHP. Good luck

[Ralph]

One method that I use, which seems to work for my needs, without resorting to extra database hits, is to do the following... Like CrisD, I make extensive use of the getIntTableRows() and getExtTableRows() API function calls - as well as their complimentary upd (update) and put (insert) counterparts... Considering how the resultset can be returned as a multi-dimensional array, I simply look through $rs[0] using foreach($rs[0] as $key=>$value) and use the keys which are the column names... I also make use of the getFormVars() API function which can retrieve form fields from $_POST or $_GET based on variable name prefix... With this function I can grab just frm_ or user_ related data, or I can use btn_ to pull in submit button results if I am using multiple submit controls...

[jon]

As always, Ralph (I've read a lot of your forum posts over the last month), you have given me the "perfect" solution to my problem, as I will already have done a getExtTableRows with fields="*" before this. I'm new to php, my only past experience a little phpBB customization, so Array handling is something I've been learning over the last week.

Thank you, both, for your help.