Daniel Daniel - 2 months ago 5
MySQL Question

Return data into input fields for update operation using inner joins

What I'm trying to do its a system to update data inside the database from a frontend which already fills in the inputs with the information that it finds in the database so that you will be able to see how it is right now and then input the new values.

Problem is that when I'm returning values like name,email,phone it works. But when I try to join my tables so that for city I wont have in the input field a value like 159 which its the key value for a specific city in my database.

It brakes. Printing out this error.

SQL Error



Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous' in ....PATH TO FILE......: PDOStatement->execute(Array) #1 {main} thrown in .....PATH TO FILE....... on line 98


Broken Code



Here you have the code that I`m running in that part of the script.

else {
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT
Users.id,
Users.firstname,
Users.lastname,
Users.email,
Users.age,
Users.phone,
Universities.univ AS university,
Internship.internship_type AS Internship,
Orase.oras AS city,
Interes.interes AS interest,
User_status.user_status AS userstatus,
Users.filename,
Users.account,
Users.reg_date
FROM
Users
INNER JOIN
Orase ON Users.city = Orase.cheie
INNER JOIN
Universities ON Users.university = Universities.id
INNER JOIN
Interes ON Users.interest = Interes.cheie
INNER JOIN
Internship ON Users.Internship_type = Internship.cheie
INNER JOIN
User_status ON Users.user_status = User_status.id
WHERE id = ?";
$q = $pdo->prepare($sql);
$q->execute(array($id));
$data = $q->fetch(PDO::FETCH_ASSOC);
$name = $data['firstname'];
$lastname = $data['lastname'];
$email = $data['email'];
$age = $data['age'];
$mobile = $data['phone'];
$city = $data['city'];
Database::disconnect();
}


Code that actually works



Also here is the same script working without inner joins so that my city returns a numeric value.

else {
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT * FROM Users where id = ?";
$q = $pdo->prepare($sql);
$q->execute(array($id));
$data = $q->fetch(PDO::FETCH_ASSOC);
$name = $data['firstname'];
$lastname = $data['lastname'];
$email = $data['email'];
$age = $data['age'];
$mobile = $data['phone'];
$city = $data['city'];
Database::disconnect();
}

?>


Image with the form using working code



Image for context so that you understand why I'm trying to use inner joins for the name of the city
Image for context so that you understand why I'm trying to use inner joins for the name of the city

Answer

Just replace WHERE id = ? for WHERE Users.id = ?

Comments