MySQL Database like bank accounts daily transactions

Question

I have a Database with two tables: users and payments

The users table is populated with

name
address
age
plan

The payment table is populated with

name, address
age, plan
paidamount, transactionid
paiddate, receivedby
updatedby, authorisedby

Users table contains about 1000 rows; they are fixed, and they regularly pay in a certain amount for savings.

I need to get user data from the users table into a webpage with a form, while inserting their payment information into it, and posting the entire data to the payment table where I used the code below, but I can't post the user table data with this form.

INSERT INTO Payments
    (ampaid, rcamo, userchar, totamo) VALUES 
    ('@ampaid', '@rcamo', '@userchar', '@totamo'),
    ('$_POST[ampaid]','$_POST[rcamo]','$_POST[userchar]','$_POST[totamo]');
 (SELECT name, ero, srno, address FROM Users WHERE srno='@srno'));

But I am not able to fetch from the users table when I update payments

Answer 1

Firstly

Never include $_POST values in your queries for this reason. Parameterize or prepare them, using PDO or MySQLi. (Also, that syntax is invalid for including in a string, and hence you are trying to add the literal string '$_POST[var]' to an INT field)

Your real issue here is that you are not using MySQL as it should be used (an RDBMS. See the next section:

---

Relationships

Look up foreign keys. They link your tables together, and hence you should never be inserting the contents of a table into another on every update. You should insert the ID of the row in the user's table to do this, and then use JOIN.

---

The Actual Insertion

To insert multiple values at once, use the syntax:

INSERT INTO
    `table` (`col1`, `col2`) VALUES
    (:val1, :val2), (:val3, :val4)

and create this array:

Array(
    ":val1" => $val1,
    ":val2" => $val2, // etc.
);

This is using PDO. Look it up, learn to love it, etc. There are plenty of resources on StackOverflow with examples and the occasional pitfall.

Getting Started

$params = array(
    ":ampaid" => $_POST["ampaid"], // tip: use quotes for array keys...
    ":rcamo" => $_POST["rcamo"], 
    ":userchar" => $_POST["userchar"],
    ":totamo" => $_POST["totamo"]
);

$db = new PDO($connection_info, $user, $pass); // from a config file
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // throw errors

$sql = "INSERT INTO `payment` (`ampaid`, `rcamo`, `userchar`, `totamo`) " .
       "VALUES (:ampaid, :rcamo, :userchar, :totamo)";

$prepare = $db->prepare($sql); // prepare query
$prepare->execute($params); // execute query using bound parameters

I'll leave the error handling up to you as homework. (tip: try-catch)

You cannot use the result of a SELECT query to populate an INSERT query in the same call (correct me if i'm wrong, but it's still a terrible idea).

---

Lastly

Never insert unsanitized or unprepared user input. If this is a real banking application (which I kind of doubt... hopefully), I'd be extremely scared to trust my money with this. In fact, I'd much rather make money through the inevitable lawsuits, so be careful and sanitize absolutely any user input you ever receive, period.

Answer 2

what i get from above discussion is @user2226832 want to insert multiple values in table, So you can do like this-

INSERT INTO payment 
    (ampaid, rcamo, userchar, totamo) VALUES 
    ('@ampaid', '@rcamo', '@userchar', '@totamo'),
    ('$_POST[ampaid]','$_POST[rcamo]','$_POST[userchar]','$_POST[totamo]');

Note: Code is not tested, but syntax is correct just take care of your single quotes.