Insert data to 2 tables php

Question

ive looked around here and i see similar questions is already answered but i still cant figure this out (as always, im a total noob and need decent explanations)

I have a users.db (userId, mmbrship, mmbrship_date) a few more values but these are the ones i need to update.

Then i have a payments.db ( id, txnid, payment_amount, payment_status, itemid, createdtime )

In my functions.php i have this code:

function updatePayments($data){ 
    global $link;
    if(is_array($data)){                
       $sql = mysql_query("INSERT INTO `payments` (txnid, payment_amount, payment_status, itemid, createdtime) VALUES (
               '".$data['txn_id']."' ,
               '".$data['payment_amount']."' ,
               '".$data['payment_status']."' ,
             '".$data['item_number']."' ,
                '".date("Y-m-d H:i:s")."' 
    )", $link);

    return mysql_insert_id($link);
    }
}

I need the values 'itemid' and 'createdtime' from payments.db to be inserted into users.db in 'mmbrship', 'mmbrship_date'

And i need the 'userId' from users.db to be inserted to payments.db (to connect the user to its purchase)

This is so i can get info from users.db if a member have paid their membership and as it is now the payments.db insert its own id. So i think if i can just connect these two tables with the userId i might be a step in the right direction...

? Please help :)

did you check **string** instead of **variable** ? Try this: `INSERT INTO payments (txnid) VALUES ('foo');`, and tell me what happens ? — , Jun 22 '15 at 22:57
4 foo 0.00 0000-00-00 00:00:00 This was added into the payments.db (i used the code in phpmyadmin as nothing happened when i edited functions.php :P) — Milla, Jun 22 '15 at 23:16
Is any of these variables coming from user input? If so I recommend checking out this: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — vesan, Jun 23 '15 at 00:09

spencer7593 · Answer 1 · 2015-06-22T23:23:58.787

It looks like your database design is missing a relationship between the user entity and the payment entity.

(In ER modeling terminology, an entity is (roughly) a person, place, thing, concept or event that can be uniquely identified, we need to store information about and is important...)

In order to determine the cardinality of the relationship between the entities, we ask a series of questions, for example:

Can a user be related to more than one payment?
Can a user be related to zero payments?
Can a single payment be related to more than one user?

Based on the answers to those (and similar) questions, we can determine whether the relationship between user and payment is one-to-one, one-to-many, or many-to-many, and whether it's mandatory or optional.

I suspect that in your model, a payment will be related to exactly one user. (That is, a given payment won't be applied to more than one user.) And it will be possible for a given user to have zero, one or more payment.

If that's the case, that's a one-to-many relationship, and the normative pattern is to store the primary key of the user entity as a column on the payment table, and to define a foreign key relationship.

e.g.

  ALTER TABLE payment ADD `userId` INT UNSIGNED COMMENT 'fk ref user' ;

  ALTER TABLE payment ADD CONSTRAINT FK_payment_user 
    FOREIGN KEY (userId) REFERENCES user (userId)
    ON UPDATE CASCADE ON DELETE RESTRICT ;

Then, before you insert a row to the payment table, you need to figure out which user this is related to, and provide a value for the userId column to establish the relationship between this payment, and the user its related to.

If the payment is not related to a user, then store a NULL value in the column. If you want require that a payment be related to a user, then add a NOT NULL constraint on the column.

The datatype of the userId column in payment should match the datatype of the userId column in user. (In the example above, I just took a guess at the datatype of the userId column.)

In a relational database, the relationship between rows in two different tables is established by storing common values in a column of the two tables.

not sure if i actually understood it all LOL but can i change the 'id' in payments.db to 'userId' and then have the members userId inserted instead of the pointless(?) id its now adding whenever someone purchase a membership? A member of my site needs to be logged in to purchase membership so it is a userId connected to the script.. At the page where the users can buy their membership i have this in the top of the page: $id = $_GET['id']; if (!isset($id)) { $id = $_CURRENT_USER->id; } Can i use this with the code in functions.php to insert the users id in payments.db? — Milla, Jun 22 '15 at 23:26
A user can have multiple payments in payments.db but in users.db i just need to have 2 values copied over so i can get this info else on my site — Milla, Jun 22 '15 at 23:27

Insert data to 2 tables php

1 Answers1