I'm trying to insert some data into a table and update another table as well. I used the "insert record" and "update record" commands using ImpAKT with a Postgresql db and I get error messages when I run the script. I think my problem is that when the php code is written, the MM_*** variables get duplicated and the code stops. Does anyone have any solutions for this. How else can I insert data into one table and update another with data from the web page?
Thanks,
Dave

Unfortunately using the standard controls, you cannot do a multiple update/insert at all.

Quite rightly part of the problem is to do with the variables, the rest of the problem is that neither routine can take into account the myriad of combinations possible.

The only way to do this is with some custom written code.

You'll be happy to hear, though, that I have the code developed for this, and plan to design an extension around it once I have completed current projects.

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>

Do you have any suggestions? Is it possible to get a sample of the custom code or maybe a list of the variables or files that need to be altered, if it's not too difficult to do. I'm going to be working on a project that requires updating or inserting records to multiple tables and I need to understand how UD writes its sql statements.
Thanks for your help.
Dave

To understand how UD creates it's SQL statements you really should consult the ADODB documentation alongside some of the inserted code. You can find the ADODB documentation (called readme.htm) in the adodb folder in your website's root directory.

continued...

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>

Edited by - rawveg on 15 Jan 2002 01:43:52

However, I will try to explain the principle to you right now using an example of entering a hex colour which is added to the 'colors' table, and then also updates the 'themes' table.


Edited by - rawveg on 15 Jan 2002 01:45:08

Say for example, you have a form with a single field:-

&lt; form name="test" method="POST" action="&lt;?php echo $PHP_SELF; ?&gt;"&gt;
Color (Hex)&lt;input type="text" name="color"&gt;
&lt;/form&gt;


Simple enough. Now the tricky part. First of all, you need to ensure that the includes are present on your page, for both adodb and your connection file:-

&lt;?php


// Copyright (c) Interakt Online 2001
// www.interakt.ro/

require("./adodb/adodb.inc.php";
require("./Connections/myConnection.php";
?&gt;

This should be at the very top of the page. Underneath it, we need to process the submitted code from the form. To do this we start immediately after the above with:-

&lt;?php
if (isset($HTTP_POST_VARS["color"])) {

This checks if the POST variable color has a value, if it does, then the form has been submitted. Now we need to create the SQL:-

$insertSQL = "INSERT INTO colors (COLORHEX) VALUES ('" . $HTTP_POST_VARS["color"] . "')";
$updateSQL = "UPDATE themes SET CURRENT_THEME = '1' WHERE COLOR = '" . $HTTP_POST_VARS["color"] . "'";

The $insertSQL creates the SQL necessary to enter the color into the colors table. The $updateSQL sets the CURRENT_THEME marker (0=Not Current, 1=Current) only in the record where the column COLOR matches the HEX value.

The next bit of code executes the SQL Statements:-

$insertRS = $myConnection-&gt;Execute($insertSQL) or DIE($myConnection-&gt;ErrorMsg());
$updateRS = $myConnection-&gt;Execute($updateSQL) or DIE($myConnection-&gt;ErrorMsg());

and then we end the routine:-

}
?&gt;

Whilst this is not a fantastic example, it does illustrate the process, and by adjusting it with the correct tables, forms, columns etc it should work just nicely for you.

Hope this helps <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>

Edited by - rawveg on 15 Jan 2002 01:46:44

Edited by - rawveg on 15 Jan 2002 01:47:42

Edited by - rawveg on 15 Jan 2002 01:48:13

Edited by - rawveg on 15 Jan 2002 01:48:34

Edited by - rawveg on 15 Jan 2002 01:48:57

Sorry for the multi-post reply. The forum decided that in preview mode everything would look ok, then it nicely reformatted everything so it was illegible.

Anyway, it's still not perfect, but as good as it is going to get. Take note, that in the last post the form tag was no properly created. So you will have to do that.

Anyway I hope it fills in the gaps.

All the best

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>

good starting point , not the best php syntax for what i need to do but thanks