4ndro1d 4ndro1d - 3 months ago 9
PHP Question

PHP MySQLi get columns from foreign key

I have the following tables:

'auktionen'

id|uid|typ(FK)|min|max|menge|...


'typen'

id|typ
1|Hack
2|Schredder


where
typ
in
typen
is just a textutal representation, which I would like to get.

$prep_stmt = "SELECT anzeigentyp, typ, holzart,
qualitaet, rinde, min, max, menge FROM auktionen WHERE uid = ?";

$stmt = $mysqli->prepare($prep_stmt);
$stmt->bind_param('i', $user_id);
$stmt->execute();
$stmt->bind_result($anzeigentyp, $typ, $holzart, $qualitaet, $rinde, $min, $max, $menge);
$stmt->store_result();


So when fetching all my results I want to get "Hack" instead of the referencing id (in case it is 1). I guess it needs some JOIN to be achieved and I tried it like this without success:

$prep_stmt = "SELECT anzeigentyp, typen.typ, holzart,
qualitaet, rinde, min, max, menge FROM auktionen WHERE uid = ?
JOIN typen ON auktionen.typ = typen.typ";


What is the proper way to do it?

Answer

You must move the where clause to the end and join the corresponding columns typen.id and auktionen.typ

select a.anzeigentyp, t.typ, a.holzart, a.qualitaet, a.rinde, a.`min`, a.`max`, a.menge
from auktionen a
join typen t on t.id = a.typ
where ...