CODEWITHSUNDEEP
phpmysql
Al Kasih
Al Kasih

Reputation: 886

Insert to join table

I have a table named member_registry which holds the member information and its columns contain of:

-id -member_name -member_email -member_password -member_phone

And I have a table named estock which function to get the posted data of the product they want to buy. Its columns contain of:

-member_id -product_name -product_quantity -product_price

And then I have a table named products which its columns contain of:

-product_id -product_name -product_price -product_image

What I want is when the user submit the product they want to buy to the table of estock

I want to be able to get their name (member_registry.member_name) that will be posted to the estock table in the column estock.member_id.

One thing I've got to remember is that the username (member_registry.member_name) can be gotten from the session_username on the page.

And vice versa, after inserting the form data to the estock table,

I want to be able to get the data from the estock that will also containt information from member_registry table: `

Please, help me. this is my first time in joining table. I hope anyone understand what I want to achieve.

Upvotes: 2

Views: 64

Answers (2)

FortMauris
FortMauris

Reputation: 167

I don't think you can execute both SELECT and INSERT at the same time? I'm no expert at SQL but i will probably do something like this:

$name = $_SESSION['session_username'];
$query = "SELECT id FROM member_registry WHERE member_name = '$name'";
$result = mysqli_query($link, $query) or die(mysqli_error($link));
$memberRow = mysqli_fetch_array($result);
$member_id = $memberRow['id'];

$query = "INSERT INTO estock (member_id, product_name, product_quantity, product_price)
         VALUES ('$member_id',[value], [value], [value])";
mysqli_query($link, $query) or die(mysqli_error($link));

I hope this helps.

Edit:

If you mean that you want to get information from both estock and member_registry table, you can try this:

$query = "SELECT * FROM estock AS e, member_registry AS m WHERE e.member_id = m.id";

Upvotes: 1

Keshav jha
Keshav jha

Reputation: 1364

@klaudia i think this will help you

SELECT * FROM member_registry
INNER JOIN estock ON member_registry.id = estock.member_id
WHERE your where statement

This will return eveything form both table based on your where statement. if you want to get specific column then mention column name instead of * in SELECT statemet

Upvotes: 2

Related Questions