CODEWITHSUNDEEP
phpmysql
batch 1999
batch 1999

Reputation: 109

LEFT JOIN on PHP

I have tried the same code below on my c# app and it works fine, but when i tried the same code on php with the tweak of instead of column number on c# i replaced it with column name but the result is different.

$sql ="SELECT 
        `adexposure`.`symbol`, 
        `adexposure`.`netvolume`, 
        `fxexposure`.`netvolume`, 
        `adexposure`.`lastupdate` 
    FROM `adexposure` 
    LEFT JOIN `fxexposure` ON `adexposure`.`symbol` = `fxexposure`.`symbol`";

$result = $conn->query($sql);
if($result->num_rows>0)
{
            echo "<table>";
            echo "<tr>";
            echo "<th>Symbol</th>";
            echo "<th>Net Volume A</th>";
            echo "<th>Net Volume B</th>";
            echo "<th>Last Update</th>";
            echo "</tr>";
            while($row = $result->fetch_assoc())
            {
                            echo "<tr>";
                            echo "<td>" .$row["symbol"]."</td>";
                            echo "<td>" .$row["netvolume"]."</td>";
                            echo "<td>" .$row["netvolume"]."</td>";
                            echo "<td>" .$row["lastupdate"]."</td>";
                            echo "</tr>";                     
            }
            echo "</table>";
 }
 else
 {
            echo "0 results";
 }

Result should be:

<table border=1>
    <tr>
    <th>Symbol</th>
    <th>Net Volume A</th>
    <th>Net Volume B</th>
    <th>Last Update</th>
    </th>
    </tr>
    <tr>
    <td>BITCOIN</td>
    <td>2.5</td>
    <td>3.5</td>
    <td>2018.02.05 10:44</td>
    </tr>
    <tr>
    <td>LITECOIN</td>
    <td>1.5</td>
    <td>5.5</td>
    <td>2018.02.05 10:44</td>
    </tr>
    <tr>
    <td>HASHCOIN</td>
    <td>0.5</td>
    <td>0.5</td>
    <td>2018.02.05 10:44</td>
    </tr>
    </table>

but thats not the case. The result shows both net volume of fxexposure.netvolume.

I hope you can help me with this.

Thanks...

Upvotes: 0

Views: 94

Answers (3)

IMSoP
IMSoP

Reputation: 98005

Read these two lines carefully:

echo "<td>" .$row["netvolume"]."</td>";
echo "<td>" .$row["netvolume"]."</td>";

You are using the same name to mean two different things, so there is no way for PHP to give a different result on those lines.

The solution is to give the values unique column aliases in your SQL:

`adexposure`.`netvolume` as netvolume_as, 
`fxexposure`.`netvolume` as netvolume_fx,

And then those are the names in your PHP:

echo "<td>" .$row["netvolume_as"]."</td>";
echo "<td>" .$row["netvolume_fx"]."</td>";

Upvotes: 0

Professor Abronsius
Professor Abronsius

Reputation: 33823

If you assign an alias to the returned colums you can address them correctly in the php

$sql ="SELECT 
        `adexposure`.`symbol`, 
        `adexposure`.`netvolume` as `netvolume_A`, /* ALIAS */
        `fxexposure`.`netvolume` as `netvolume_B`, 
        `adexposure`.`lastupdate` 
    FROM `adexposure` 
    LEFT JOIN `fxexposure` ON `adexposure`.`symbol` = `fxexposure`.`symbol`";

$result = $conn->query($sql);
if($result->num_rows>0)
{
            echo "<table>";
            echo "<tr>";
            echo "<th>Symbol</th>";
            echo "<th>Net Volume A</th>"; 
            echo "<th>Net Volume B</th>";
            echo "<th>Last Update</th>";
            echo "</tr>";
            while($row = $result->fetch_assoc())
            {
                            echo "<tr>";
                            echo "<td>" .$row["symbol"]."</td>";
                            echo "<td>" .$row["netvolume_A"]."</td>";<!-- /* ALIAS */ -->
                            echo "<td>" .$row["netvolume_B"]."</td>";
                            echo "<td>" .$row["lastupdate"]."</td>";
                            echo "</tr>";                     
            }
            echo "</table>";
 }
 else
 {
            echo "0 results";
 }

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

just a suggestion you have two time netvolumn so you should use alias

sql ="SELECT 
    `adexposure`.`symbol`, 
    `adexposure`.`netvolume`, 
    `fxexposure`.`netvolume` as netvolume2, 
    `adexposure`.`lastupdate` 
FROM `adexposure` 
LEFT JOIN `fxexposure` ON `adexposure`.`symbol` = `fxexposure`.`symbol`";


while($row = $result->fetch_assoc())
        {
                        echo "<tr>";
                        echo "<td>" .$row["symbol"]."</td>";
                        echo "<td>" .$row["netvolume"]."</td>";
                        echo "<td>" .$row["netvolume2"]."</td>";
                        echo "<td>" .$row["lastupdate"]."</td>";
                        echo "</tr>";                     
        }

Upvotes: 3

Related Questions