Page 1 of 1

I'm trying to move the last record into a new table

Posted: Sun Jun 28, 2009 4:57 pm
by PHP_Newbie_1
Can any one help me please? I am a newbie and I prefer to solve my own problems but this one has me beat.

I have been working on this problem for months now and I just can't get it to work properly.

let me explain.

I want to move the last record only from a table called users into a new table called members. The new table called members has two additional fields called parent_id and child_count. child_count is incremented by 1 until it reaches 8. When child_count = 8 the parent_id is incremented by 1.

I like to usually work out my own problems. But I have to finally admit now that I am well and truly stuck.

I have tried all kinds of combination's. I need someone to put me out of my misery.

The code is:

Code: Select all

$q = "INSERT INTO ".USERS." 
            VALUES ('$username', '$password', '0', $ulevel, '$email', $time)";
      return mysql_query($q, $this->connection);
     $sql = "SELECT username,child_count FROM" .members.  
     $sql =  "INSERT INTO".matrix_members. "VALUES (‘‘, username, email) SELECT username, email 
            FROM" .TBL_USERS. "ORDER BY timestamp DESC LIMIT 1";
            child_count = child_count + 1
            return mysql_query($sql, $this->connection);     
     $sql = "UPDATE " .MEMBERS. "SET child_count 0, parent_id = parent_id + 1 
     WHERE  SELECT username,child_count FROM" .members.  
     "ORDER BY timestamp DESC LIMIT 1"
Thanks for your help in advance

Re: I'm trying to move the last record into a new table

Posted: Sun Jun 28, 2009 5:08 pm
by McInfo
It seems like this is a problem that should be solved with triggers, but I'm still a little uncertain about the what and why of your situation.

MySQL Manual: Using Triggers

Edit: This post was recovered from search engine cache.

Re: I'm trying to move the last record into a new table

Posted: Tue Jul 07, 2009 8:05 am
by PHP_Newbie_1
Since I am new to PHP I don't know how to use triggers.

What I want to do is as follows.

1)A new person registers on to my database (This works 100%)
2)I then move that members details to another table where they will be provided with either a child_id or parent_id.
3)The information in the new table is processed in a particular way which is unique to my business. (This I can get to work 100%)

My main problem is simply moving the last record, from the main database table into a new table.
I have decided to create a function since this seems to be a better option but it still doesn't work.

function addMem($username, $email){
$q = "INSERT INTO".TBL_MEMBERS. "VALUES (‘‘, $username, $email) SELECT username, email
FROM" .TBL_USERS. "ORDER BY timestamp DESC LIMIT 1";
return mysql_query($q,$this->connection);

I thought that this should work.
Thanks in advance for your help.