Search for two MySQL tables in php [closed]

Navigation
1

I have two tables that are:

  • Registration

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

  • Classification

    • Location (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 fine without the place, just selecting the data from the record table but that is not working. Does anyone know why?

    
asked by anonymous 18.06.2015 / 12:40

2 answers

1

Use Inner Join, Left Join, or Right Join for table relationships 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 you are in this table. And put a or die(mysqli_error()) at the end of your query, if you're making a mistake.

Example:

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

    
18.06.2015 / 13:35
1

I do not have much to add in your query, Diego has already improved it. I'm creating this answer just to show what 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;
}
    
18.06.2015 / 15:13
Relationship between data with combobox Query executes FilterRecord only once