update multiple tables

[adzie]

Hi Guys,

 

working on a membership database system, the question is if someone amends a record and then commits those changes to the database is it possible to also uipdate another database to record the change and date of change.

 

for example

 

members database

name - text

address - text

level - drop down options

loudness -text

 

levelchange database

prev level

new level

date

 

what I'd like to do is when the when they choose a new option from level it updates the members database  but also puts a entry into the levelchange database showing previous level the new option selected and the date actioned

 

What i'm concerned about is that everytime they submit changes to their profile it might put an entry into that levelchange table even if the level field does not change.

 

is this possible to be done?

 

thanks for any advice

[amites]

I wouldn't go at it with a new database so much as a new table,

 

in your 2nd table you'd want 2 more columns than you have,

 

1: ID

2: user_ID

3: prev_lvl

4: new_lvl

5: timestamp

 

might be able to do it in a single query though that's beyond my mySQL knowledge,

doing it in 2 queries would be simple enough

[adzie]

sorry should have said tables not database, so its possible to do this? as I say I need the levelchange table to only show an entry where a change has been made from the existing level in the table

[tgavin]

amites is correct. you need additional columns in your levelchange table. an id (primary key) and the member id (foreign key).

 

This is off the top of my head - the code is not complete but should get you going in the right direction

 

<?php
// get the member id from the form
$id = $_POST['member_id'];

// get the values from the db. Select the columns you need
$sql = mysql_query("SELECT prev_level FROM members WHERE id = {$id}");
$row_members = mysql_fetch_array($sql);

// check to make sure the level change was made
if($_POST['level'] != $row_members['level']) {
    // a change was made
    // update the members table
    $sql = mysql_query("UPDATE members SET level = $_POST['level'] WHERE member_id = {$id}");

    // update the levelchange table
    $sql = mysql_query("UPDATE levelchange SET new_level = $_POST['level'], prev_level = SELECT level FROM members WHERE member_id = {$id}");
}
?>

[tgavin]

I found a couple of mistakes in my earlier post. I wasn't able to edit them though

 

amites is correct. you need additional columns in your levelchange table. an id (primary key) and the member id (foreign key).

 

This is off the top of my head - the code is not complete but should get you going in the right direction

 

<?php
// get the member id from the form
$id = $_POST['member_id'];

// get the values from the db. Select the columns you need
$sql = mysql_query("SELECT level FROM members WHERE id = {$id}");
$row_members = mysql_fetch_array($sql);

// check to make sure the level change was made
if($_POST['level'] != $row_members['level']) {
    // a change was made
    // update the members table
    $sql = mysql_query("UPDATE members SET level = $_POST['level'] WHERE member_id = {$id}");

    // update the levelchange table
    $sql = mysql_query("UPDATE levelchange SET new_level = $_POST['level'], prev_level = $row_members['level'] WHERE member_id = {$id}");
}
?>