Gossamer Forum
Home : General : Databases and SQL :

move data from 1 table to another with PHP

Quote Reply
move data from 1 table to another with PHP
Hello,
Can someone help with this problem I'm having.
I have 2 mySql tables, let say 1 contains cars in it and the other is a user table.
What I'm tring to do is have the user select a car from the list and add to there own record. But I would like the car that the user selected to be removed from the table after they have added it so no one else can add it to their record.

Picture a table on the left of the screen and a table on the right side and 2 submit buttons in the middle that say "ADD" and "Remove". The user can select a car from the table on the left to add to the table on the right or reverse. But I want the record to be moved not copied
Thanks
Quote Reply
Re: [barryb12000] move data from 1 table to another with PHP In reply to
For the user inferface (web form), you'd have to employ javascript codes. For the back-end date processing, you'd have to select the PK from the "cars" table, and then insert the data into the users table.

Although I think there is a major flaw in your database schema/data processing...do you really only want to have ONLY one car associated with each user/member? Seems like it is more logical to HAVE many cars associated with MANY members, don't ya thknk? Thus, a 1->M connection between "CARS" and "USERS" objects would be better.

Visual Example:

Ford Ranger - CarID = 1
Neon SXP - CarID = 3

1 - UserID#100
1 - UserID#2020

3 - UserID#1001
3 - UserID#10301

Basically, I think the logic you are employing with deleting the CAR record is NOT logical at all!
========================================
Buh Bye!

Cheers,
Me
Quote Reply
Re: [Stealth] move data from 1 table to another with PHP In reply to
I just using cars as an example. The real data is NFL players. I have to delete the player from 1 table and insert him into the user table so no one else can select him. I can't have 2 users with the same NFL player.
Quote Reply
Re: [barryb12000] move data from 1 table to another with PHP In reply to
Rather than deleting the NFL player from the table (players), I am assuming you are going to print that player's details somewhere in your scripts, I would recommend that you simply use codes in your scripts to check to see if that "PlayerID" exists in the USERS table, rather than moving the data into the USERS table.

Basically, it is a 1<->1 connection between PLAYERS and USERS based on the PlayerID.

Example:

PLAYERS: (tbl_Players)

PlayerID (INT, AUTO-INCREMENT, PRIMARY)
Player_First_Name (TEXT, NOT NULLl)
Player_Last_Name (TEXT, NOT NULL)

USERS: (tbl_Users)

UserID (INT, AUTO-INCREMENT, PRIMARY)
User_First_Name (TEXT, NOT NULL)
User_Last_Name (TEXT, NOT NULL)
PlayerID (INT) - [FK_KEY from Players table]

Then in your "player" selection menu, all you have to do is JOIN the two tables and ONLY show records from the Players table that ARE not FK_KEYS in the USER table.

Example:

Quote:

SELECT tbl_Players.Player_First_Name, tbl_Players.Player_Last_Name, tbl_Players.PlayerID
FROM tbl_Players
WHERE tbl_Players.PlayerID NOT IN (SELECT PlayerID FROM tbl_Users)
ORDER BY tbl_Players.Player_Last_Name


Of couse, you'll need to be using a DB app that supports sub-queries, but you can also re-write the above SQL statement without the subquery to accomplish the same task.

Then in your HTML select menu codes, depending on what web app or language you are using, all you have to do is output the following columns in the select menu (of course, looping through the query):

tbl_Players.PlayerID
tbl_Players.Player_First_Name
tbl_Players.Player_Last_Name

EXAMPLE (with Perl template parser, like GT's):

Quote:

<select name="PlayerID" size="1">
<option value="">Choose a Player</option>
<option value="<%PlayerID%>"<%Player_First_Name%> <%Player_Last_Name%></option>
</select>


Of course, I've left out the loop codes since they can be written and applied in quite a few ways.

Hope this makes sense and helps.
========================================
Buh Bye!

Cheers,
Me

Last edited by:

Stealth: Jun 17, 2002, 10:07 PM
Quote Reply
Re: [Stealth] move data from 1 table to another with PHP In reply to
Thanks man, It makes a lot of sense.

You are correct, that is a better database design with the use of the PlayerID field. Thanks. I will try that and let you know how it comes out. By the way, I'm using PHP and a mySql database. I know PHP than Perl but I can always give Perl a try since I did almost the same thing with Perl last year but with a Flat File database.
Quote Reply
Re: [barryb12000] move data from 1 table to another with PHP In reply to
You're welcome.

Well, MySQL (v.3.23, I believe) supports subqueries like the one I wrote above, so you should be able to get the query to work. And doesn't really matter what scripting language you use, all you have to do is use SQL that works with whatever database storage app you are using.

And you really have to get away from the flat file model, and think more "relationally" between objects in your dataset.

BTW: For future development of your system and if at some point you want to allow your web users to have multiple players, the better thing to do is create an intersection table (to allow multiple players to be associated with multiple users), like the following:

tbl_User_Players

UserID (UNIQUE, INDEX)
PlayerID (UNIQUE, INDEX)

You can still use codes to restrict the number of players like the following:

Quote:

SELECT tbl_Players.Player_First_Name, tbl_Players.Player_Last_Name, tbl_Players.PlayerID
FROM tbl_Players
WHERE tbl_Players.PlayerID NOT IN (SELECT PlayerID FROM tbl_User_Players)


Using M<->M connections with intersection tables will allow more flexibility for future growth and development of your system. Rather than having to switch from 1<->1 and edit your USERS table constantly to fit the new schema that you apply later on. Of course, the more intersection tables you add, the more JOINS you have to apply, which takes more time to process in terms of CPU cycles and Memory, but the time you save in configuring your system in a flexible manner outweighs the extra ms (milliseconds) of data processing, IMO.
========================================
Buh Bye!

Cheers,
Me

Last edited by:

Stealth: Jun 18, 2002, 8:54 PM
Quote Reply
Re: [Stealth] move data from 1 table to another with PHP In reply to
Thanks for all your help man.

What my application needs to do is

1. allowe the users to add or drop any player that want.

2. If a player does not belong to anyone than any user can add him.

3. Once a user drops a player then he is available to anyone.

4. When a user signs in I would like to show him all of his players and then show him all the available players he can add.

5. Each user can only have 16 players and no two users can have the same player.

If I understand you correctly than I should be able to do this by using your sample code. Is that correct.

I will use 1 table to hold all the players with a field the has "Userid" and "PlayerID" and I can update the "Userid" field if a user selects that player. Than have a second table with only "Userid" and "PlayerID" to display all the players a given user has.

If this is correct than I think I can do this with some hard work.



Thanks again
Quote Reply
Re: [barryb12000] move data from 1 table to another with PHP In reply to
I don't think you are getting the drift of my messages.

Here is a summary again....

You would want to have three tables (to allow future flexibility of your application/system).

One table contains all the PLAYERS and all their attributes with a Primary Key (PK) of PLAYERID.

The second table would contain all the USERS and their attributes with a Primary Key (PK) of USERID.

Then in an intersection table, you would use PLAYERID and USERID as Foreign Keys (FK) from the above mentioned tables.

Please re-read my previous replies since I have outlined a few approaches that you can use as well as sample SQL statements that you can use to CONTROL the number of PLAYERS associated with USERS and vica versa.

Does this make sense????

If not, may be a quick tutorial of SQL would help you. Check out the links in the SQL Resources thread in this forum.

Also, I have attached a MS Access database schema that includes a few attributes in the primary tables and also constraints in the intersection table that illustrates that you can both use constraints in the backend DB and also through coding you can restrict the number of players per users. To change the schema, all you would have to do is change or remove the constraints on the UserID/PlayerID. And also edit the SQL statements.

Hope this helps and makes better sense.
========================================
Buh Bye!

Cheers,
Me

Last edited by:

Stealth: Jun 23, 2002, 9:41 PM
Quote Reply
Re: [Stealth] move data from 1 table to another with PHP In reply to
Hello Stealth

I'm having some problems with the information you gave me. It looks like the version of mySql that I have does not support sub queries.

Also I'm having some problems creating the HTML Form that will show the user information and the players information.

Can you help me some more? I'll provide the the tables with some data in them.

The player table is called (Free_agents) the Users Table is called (test_fantasy_team)

Thanks you for your help if you can.
Quote Reply
Re: [barryb12000] move data from 1 table to another with PHP In reply to
You can re-write the query to not use sub-queries like the following:

Quote:

SELECT P.Player_First_Name, P.Player_Last_Name, P.PlayerID
FROM tbl_Players P tbl_Players P2
WHERE (P.PlayerID <> P2.PlayerID)


Just use "table aliases" and the <> or IS NOT operators in the WHERE clause(s).

Good luck!
========================================
Buh Bye!

Cheers,
Me

Last edited by:

Stealth: Jul 18, 2002, 2:05 PM