Accessing rows from a mysqli join query in php

问题:

I have the following code:

// db connection info set up earlier
$sql= "SELECT `TABLE_1.ID`, `TABLE_2.ID`, `POTATO` FROM `TABLE_1.ID` LEFT JOIN `TABLE_2` ON `TABLE_1`.`ID` = `TABLE_2`.`ID_OF_OTHER_TABLE`;";
$rows = mysqli_query($connection, $sql);
foreach ($rows as $row){
    $potato = $row["POTATO"];
    $id = $row["TABLE_2.ID"];
}

I can't get TABLE_2.ID. I've tried to doing a print_r to get the proper format, but it says it's a mysqli object and I don't get much more info than that. However, I can get potato. So I'm guessing it's a calling syntax issue, but I've searched multiple sites (stack and google included) and not found a clear answer. So, what do I need to do instead of

$id = $row["TABLE_2.ID"];

?

回答1:

Assign aliases to the columns with the same name.

$sql= "SELECT `TABLE_1`.`ID` AS t1_id, `TABLE_2`.`ID` AS t2_id, `POTATO` 
       FROM `TABLE_1.ID` 
       LEFT JOIN `TABLE_2` ON `TABLE_2`.`ID_OF_OTHER_TABLE` = `TABLE_1`.`ID`;";
$rows = mysqli_query($connection, $sql);
foreach ($rows as $row){
    $potato = $row["POTATO"];
    $id = $row["t2_id"];
}

回答2:

You can't left join a table with itself, you also cannot have a table that isn't joined = something from another table that cannot be joined to itself.

This will work:

  // db connection info set up earlier
$sql= "SELECT TABLE_1.ID, TABLE_2.ID, POTATO
        FROM 
        TABLE_1.ID
        LEFT JOIN TABLE_2 ON TABLE_1.ID = TABLE_2.ID";
$rows = mysqli_query($connection, $sql);


while ($row = mysqli_fetch_assoc($rows)) {

echo ($row['ID']);

}
mysql_free_result($rows);