CODEWITHSUNDEEP
phpmysql
4ndro1d
4ndro1d

Reputation: 2976

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?

Upvotes: 0

Views: 97

Answers (1)

Olaf Dietsche
Olaf Dietsche

Reputation: 74018

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 ...

Upvotes: 1

Related Questions