Justtechjobs.com Find a programming school near you






Online Campus Both


php3-list | 2000051

Re: [PHP3] Admin type script From: Doug Semig (dougslist <email protected>)
Date: 05/01/00

I believe it is a good idea to build your UPDATE and INSERT SQL in this
manner, and I do it quite often. But I have personally never done it where
the names of the fields are unknown at run time.

How would you map the field names to the values the user wants to put in
those fields? I can see it very easily done if you're writing something
like phpMyAdmin, because you would just show the user the field names and
they would (hopefully) fill in the appropriate values for update/insert. I
take it from your examples, though, that you are not making a general
purpose table browser. In a super-dynamic application, the only way I can
see to do it is to have the administrator of the application set up a field
map (which can be stored in a .inc for inclusion, a database for
selection, or wherever you want to that you can get to it at runtime).

For example, a database table could look like this:

CREATE TABLE fieldmap (
  formid int,
  fieldid int,
  sqlfieldname varchar(48),
  formfieldname varchar(48),
  quotes int
);

And the application administrator would assign each field (presumably in
nice web-based configuration screens). So some records could look like this:

formid fieldid sqlfieldname formfieldname quotes
----- ----- ----- ----- _____
1 1 auth_no authnumber 0
1 2 did_no didnumber 0
1 3 acct_no accountnumber 0
1 4 c_name companyname 1
1 5 f_name firstname 1
1 6 l_name lastname 1
1 7 uname username 1

Once you have this map, which would be set up by the administrator of the
application, you can select them out/read them in and process them
dynamically. Also, I've included a formid so you can use the EXACT SAME
CODE that appears below for UPDATEs and INSERTs for different forms.

To build an insert query for this example form, you would:

/* somewhere up above you connect to the mysql database */
/* NOTE: I do not work with MySQL at all...and this is UNTESTED code */
/* I made it up right here in my e-mail program...it will almost certainly
contain errors. */
/* Please use it as a guideline of the principles I'm trying to convey */

$result = mysql_query(
      "SELECT fieldname, formfieldname, quotes FROM fieldmap WHERE formid =
1");
if (!$result) {
  echo "Error querying database."
  exit;
}
if (mysql_num_rows($result) <= 0) {
  echo "Form #1 isn't defined by the administrator yet. Please check back
later.";
  exit;
}
/* if we get here, we know that there's something mapped by the admin! */
$needcomma = 0; $fieldlist = ""; $valuelist = "";
while ($data = mysql_fetch_array($result)) {
  if ($needcomma) $fieldlist .= ",";
  else $needcomma = 1;
  $fieldlist .= " " . $data["fieldname"];
  if ($needcomma) $valuelist .= ",";
  $valuelist .= " ";
  if ($data["quotes"]) $valuelist .= "'";
  $valuelist .= ${$data["formfieldname"]};
  /* note that if there's a possibility of the field containing characters
    with special meaning to the database (quotes and apostrophes),
    one would do addslashes(${$data["formfieldname"]}); instead */
  if ($data["quotes"]) $valuelist .= "'";
}
mysql_free_result($result);
/* you said you already have a $tblname */
$sql = "INSERT INTO " . $tblname . " (" . $fieldlist . ") VALUES (" .
$valuelist . ")";
$result = mysql_query($sql);
if (!$result) {
  echo "Error inserting record into database. The SQL was [" . $sql . "]";
  exit;
}

The UPDATE would be even easier, actually...

...
$needcomma = 0; $updatelist = "";
while ($data = mysql_fetch_array($result)) {
  if ($needcomma) $fieldlist .= ",";
  else $needcomma = 1;
  $updatelist .= " " . $data["fieldname"] . " = ";
  if ($data["quotes"]) $updatelist .= "'";
  $updatelist .= ${$data["formfieldname"]};
  if ($data["quotes"]) $updatelist .= "'";
}
mysql_free_result($result);
/* You get to figure out the WHERE clause, because it wasn't specified in
   the example */
$sql = "UPDATE " . $tblname . " SET" . $updatelist . " WHERE something =
something";
...

All of the above depends upon having the web application administrator
enter in the correct values into the fieldmap table! So don't try it
without a fieldmap table. So you need a couple of fancy screens in the
administration portion of your website for this, but once the admin sets
these, they wouldn't change unless the underlying database schema was
changed. Even then, it's just a matter of entering in the new values into
the fieldmap table. No changes to code necessary.

This methodology would work, I suppose, when you need your app to run in
various places where the table and field names will vary from site to site,
and you need a place to tell your app where to find the data it needs. It
could also play a part of a generic form-to-database web application
generator.

Is this what you were asking?

Doug

Browsehere.com was heard at 10:14 PM 4/30/00 -0400 to say:
>Okay. I've looked high and low but can't seem to find anything relative.
>
>I am writing a custom script, but am trying to do so with duplication in
>mind.
>
>The issue I am having is this:
>For each client, they have unique fields in a table.
>I can define the db name, table name, etc in a config.inc file, but the
>problem comes to the fields in the tables.
>
>I want to create the add string, modify string by querying the database.
>Instead of
>$add_string = "$auth_no, "
>."$did_no, "
>."$acct_no, "
>."$c_name, "
>."$f_name, "
>."$l_name, "
>."$uname, "
>."$pwd "
>.")" ;
>
>Can't I somehow query the database with a loop? Something like this:
>$add_string = "" ;
>for ( $c = 0; $c < $num_fields; $c++) {
> $add_string .= mysql_fieldname($result, $c ) ;
>} // end of for-loop
>
>Of course I'd have to make sure the last field wouldn't have a comma after
>it and all, but it's just the above I can't figure.
>
>The real problem comes with the modify part when you have to go 'UPDATE
>$tbname SET (field=$field, field2=$field2 etc)
>
>Can a loop be made for this?
>The trouble is having the $mod_string look like the above.
>How do you do: mysql_fieldname($result, $c ) = $ mysql_fieldname($result,
>$c ) ??? and get it to actually look like above without it passing
>variables not set from the form that you want the variables to be?
>
>I hope I'm being clear enough.
>
>Thank you,
>Derek Bruce
>dbruce <email protected>
>www.browsehere.com
>v. 603.880.8117
> 

-- 
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe <email protected>
To subscribe to the digest, e-mail: php3-digest-subscribe <email protected>
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin <email protected>