Search for two Mysql tables in php

Asked

Viewed 90 times

1

I have two tables that are:

  • Registration

    • Dorsal(id auto_increment Primary key);
    • Name;
    • Team;

  • Classification

    • Place(id auto_increment Primary key);
    • Dorsal (Foreign key);

and the following code in PHP:

$result = mysqli_query($con,"SELECT c.lugar, c.dorsal, r.nome, r.equipa from classificacao  c, registo r where c.dorsal = r.dorsal and r.categoria = '45'");
while($row = mysqli_fetch_array($result)){
    echo "<tr>";
        echo "<td>" . $row['lugar'] . "</td>";
        echo "<td>" . $row['dorsal'] . "</td>";
        echo "<td>" . $row['nome'] . "</td>";
        echo "<td>" . $row['equipa'] . "</td>";
    echo "</tr>";
}

This works well without the place, just by selecting the data from the record table but so it is not working. Does anyone know why?

php mysql select

  • The table where the place field is called "Place"?

  • @Hélder No, I’m sorry it’s already mended and thank you

  • 2

    Can you put a sample of the data from the two tables? And are you sure there is a record in the record table with category = 45?

2 answers

1


Use Inner Join, Left Join or Right Join for relationships on tables in your query. Visually it’s more beautiful too.

SELECT 
    c.lugar, 
    c.dorsal, 
    r.nome, 
    r.equipa 
FROM 
    classificacao c
INNER JOIN
    registo r on r.dorsal = c.dorsal 
WHERE 
   r.categoria = 45

Check the column name, if it’s even in this table. And put a or die(mysqli_error()) at the end of your query, in case you are making a mistake.

Example:

mysqli_query($con, "Consulta SQL") or die(mysqli_error());

1

I don’t have much to add to your query. I’m creating that answer just to show you what it is Heredocs and how to interpolate variables in it.

<?php

$sql = <<<SQL
SELECT 
    c.lugar,
    c.dorsal,
    r.nome,
    r.equipa
FROM classificacao as c
INNER JOIN registo as r ON r.dorsal = c.dorsal
WHERE c.dorsal = r.dorsal AND r.categoria = 45
SQL;

$result = mysqli_query($con, $sql) or die(mysqli_error());

while($row = mysqli_fetch_array($result)){
    echo <<<HTML
<tr>
    <td>{$row['lugar']}</td>
    <td>{$row['dorsal']}</td>
    <td>{$row['nome']}</td>
    <td>{$row['equipa']}</td>
</tr>
HTML;
}

Browser other questions tagged php mysql select

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