JOIN 3 different tables display list

Asked

Viewed 2,951 times

1

Good Morning Everyone.

I’m trying to join 3 tables and display in a list, but I’m not getting along. I drew (WITH LESS LINES) the tables in the database and an idea of how I’m trying to list the fields.

Tabelas no banco de dados e listagem

I believe that for this I must do 3 joins, but it is not working.

$i=0;

$result = mysql_query("SELECT * FROM payment 
LEFT JOIN booking ON payment.booking_id = booking.id 
LEFT JOIN customer ON booking.customer_id = customer.id
WHERE (date >= '$datainicialsql' AND date <= '$datafinalsql') ORDER BY date") or die(mysql_error());

while ($linha = mysql_fetch_array($result)) {
    $contador = $i;
    $vcustomer = $linha["customer.name"];
    $vtype = $linha["type"];
    $vdate = date("d-m-Y", strtotime($linha["date"]));

    $vvalue = $linha["amount"];

    echo $i . $customer . " Name: $vcustomer" . " - " . " Date: $vdate" . " Type: $vtype" . " Date: $vdate" . " Amount: $vvalue" .  "<br>";
    $i++;
}
php mysql join

1 answer

5


If I understand correctly what you want what’s missing is to add to SELECT the column name table Customer 

$result = mysql_query("SELECT customer.name, type, date, amount FROM payment 
LEFT JOIN booking ON payment.booking_id = booking.id 
LEFT JOIN customer ON booking.customer_id = customer.id
WHERE (date >= '$datainicialsql' AND date <= '$datafinalsql') ORDER BY date") or die(mysql_error());

  • That’s exactly what SQL @ramaral says. Running phpmyadmin is perfect. I will have to change the method I use to display the data from while to foreach as well. Correct?

  • Davirrossini, there is no need to switch to foreach, just change the result variable present in your code to the one informed by @ramaral. http://php.net/manual/en/function.mysql-query.php

  • This is already in the domain of PHP which is something I don’t master.

  • Thanks again, I already added the variable and it was perfect the solution. Now I will put in a table to look more beautiful visually.

Browser other questions tagged php mysql join

You are not signed in. Login or sign up in order to post.