The forums have permanently moved to forum.kirupa.com. This forum will be kept around in read-only mode for archival purposes. To learn how to continue using your existing account on the new forums, check out this thread.


Results 1 to 5 of 5

Thread: moving data from 1 table to another...

  1. #1

    moving data from 1 table to another...

    Hey there,
    ok, it took me a while, but I successfully wrote a script that will move data from 1 table to a second, identical table. Good for me.

    However, I have a lot of NULL's in the original table that I need to maintain when it gets moved to the new table. Right now, my script puts 0's in. Can't have that.

    Here is my query string:
    PHP Code:
    $query = ("INSERT INTO $table2 (Year, Event, EventType, Division, Handler, Dog, Club, CR1, D1, I1, S1, C1, deduct1, FS1, CR2, D2, I2, S2, C2, Total) VALUES ('$Year', '$Event', '$Type', '$Division', '$Handler', '$Dog', '$Club', '$CR1', '$D1', '$I1', '$S1', '$C1', '$deduct1', '$FS1', '$CR2', '$D2', '$I2', '$S2', '$C2', '$Total') "); 
    As I said, that script works fine, except that anything that is NULL becomes '0'.
    Now, I have another script that I use to directly input data into a table, and if any of the fields are blank, they stay as NULL. Here is what it looks like:
    PHP Code:
    $query = ("UPDATE {$table} SET cr1=IF('$CR1'='',NULL,'$CR1'), D1=IF('$D1'='',NULL,'$D1'), I1=IF('$I1'='',NULL,'$I1'), S1=IF('$S1'='',NULL,'$S1'), C1=IF('$C1'='',NULL,'$C1'), Total='$Total' WHERE row_id=$rowid"); 
    This also works. (yes, the fields are different-they are two different scripts...)
    So obviously the difference is one is an INSERT statement, and one is an UPDATE statement. Is there any way to put that 'IF' logic into the INSERT statement?
    It would be terribly handy!
    I have beat my head on this one (including trial error and searches) for a while now...
    Any would be greatly appreciated!

  2. #2
    jwilliam's Avatar
    476
    posts
    An intellectual carrot!
    I know I'm not answering your question... but there is a utility called mysqldump that you can use to do this. There's no need to write your own script. In fact, there are a few utilities out there for doing this, though I can't remember the names of the other ones.

  3. #3
    Hey...
    Quote Originally Posted by jwilliam View Post
    I know I'm not answering your question... but there is a utility called mysqldump that you can use to do this. There's no need to write your own script. In fact, there are a few utilities out there for doing this, though I can't remember the names of the other ones.
    ok, sounds good-I will google. The thing is, these are 2 tables in the same database, hosted at a website, and my php scripts access them remotely.

    I hadn't even thought of using my db tools, though...I have navicate, sequel pro and a couple others too...hmmm...

    Though a script would do just, only and exactly what I want, without the overhead of an app...

    Chris

  4. #4
    Check to make sure you don't nave NOT NULL set in the database for the fields in table2

    and it helps to set the default value to NULL directly.

    Twitter / The Human Conditions

    biznuge: "that doesn't grammatical sense..."

  5. #5

    solution...ugly, but it works...

    Quote Originally Posted by birdwing View Post
    Check to make sure you don't nave NOT NULL set in the database for the fields in table2.
    Yup, I have that...

    But I managed to get it to work. I think this is inelegant, but it seems to work:
    This INSERT is taking place in a WHILE loop. it iterates through each person in the first table and adds all their data into the second table. So what I do is, whithin the WHILE loop, I break up the data.
    First, I do an INSERT with just the basics, Year, Event, Division, Handler, Dog. All that data is present on every row, and it is completely unique for each row.
    Next, still in the same while loop, I do another query, this time to the second table, and I grab the row_id of that new row I just added. This is an autoincrementing field in the second table that gets populated as soon as a row is added, of course.

    And finally, still within the WHILE loop, I use that new row_id to simply do an UPDATE of the fields, which allows me to use the UPDATE line I mentioned before. It is capable of checking to see if there is data. If so, update. If not, leave NULL.

    So, as I said, it is a bit ugly, and unweildly, and I wasn't sure I could add a row to a table and turn around and do a query on that same new row in the same script, much less the same loop, but apparently you can!

    late.

    Chris
    Last edited by eyebum; October 30th, 2009 at 02:50 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012