Ashirogi Muto Ashirogi Muto - 6 months ago 8
SQL Question

How can I select two tables in mysql using join based on the input given by the user

I have two tables. One is with user details and other is a table with some offer details. Both the tables have user ID as the primary key. I want to access all the details from both tables based on the ID provided by the user. How can I do that?
User table:-

---------------------------------------------
id name email password acc_conf
----------------------------------------------


Offer table:-

----------------------------------------------
id user_id offer_code quantity
----------------------------------------------


script:-

<?php
$id = $_POST['id'];
?>

Answer

You can do as follows:

$sql = "SELECT a.id AS user_id, a.name, a.email, a.password, a.acc_conf,
b.id AS offer_id, b.offer_code, b.quantity 
FROM user_table AS a, offer_table AS b
WHERE a.id = b.user_id AND a.id = {$id}";

WARNING!: If your using your above code as it stands, you are wide open to SQL Injection. The previous answer was just a proof of concept that the code would provide the right results, however I recommend you prepare the statement and bind your parameters like so:

$sql = "SELECT a.id AS user_id, a.name, a.email, a.password, a.acc_conf,
b.id AS offer_id, b.offer_code, b.quantity 
FROM user_table AS a, offer_table AS b
WHERE a.id = b.user_id AND a.id = :id";

$stmt = $conn->prepare($sql);
$stmt->bindParam(':id', $id);
$stmt->execute();

I used the names user_table and offer_table. I wasn't sure if you actually had spaces between the names you places in your question. If you actually did have spaces, just replace the names in my code with 'user table' and 'offer table'. When you have spaces in columns and tables, quotations are necessary for SQL to understand those are spaces and not other commands in the SQL statement.

Comments