PHP / MYSQL - 数据检索

Question

Going by the advice that join are better than nested queries, I've converted all my nested queries to join. However, upon converting to join, I'm unable to retrieve data into my array from the SQL result.

Here are my queries :

Without join

$a="SELECT F_DATE, COUNT(F_DATE) as COUNT_F 
    from FWH 
    where FI_NAME IN 
    ( 
       SELECT I_NAME from INS_W WHERE INSTANCE_ID IN 
       (
         SELECT I_MAP_ID FROM T_MAP where T_MAP_ID = 
         (
           SELECT T_ID FROM TWY WHERE T_NAME = 'abc'
          )
        )
    ) 
    AND F_DATE between '$S_D' AND '$E_D'
    GROUP BY F_DATE";

With join

$a="SELECT t1.F_DATE AS DATE_F, COUNT(t1.F_DATE) as COUNT_F
    from FWH t1 
    JOIN INS_W t2 ON(t1.FI_NAME = t2.I_NAME) 
    JOIN T_MAP t3 ON(t2.INSTANCE_ID = t3.I_MAP_ID) 
    JOIN TWY t4 ON(t3.T_MAP_ID = t4.T_ID) 
    WHERE t4.T_NAME = 'abc' AND
    t1.F_DATE BETWEEN '$S_D' AND 'E_D'GROUP BY t1.F_DATE";

Here's the PHP code to retrieve data

$link = mysql_connect("ip", "user", "passs");
$dbcheck = mysql_select_db("db");   
if ($dbcheck) {
    $chart_array_1[] = "['F DATE','F COUNT']";
    $result = mysql_query($a);
    if (mysql_num_rows($result) > 0) {
        while ($row = mysql_fetch_assoc($result)) {
            $f_date=$row["DATE_F"];
            $f_count=$row["COUNT_F"];
            $chart_array_1[]="['".$f_date."',".$f_count."]";
        }
    }
}
mysqli_close($link);

The SQL queries themselves run fine when tested directly on MySQL DB.

Answer 1

For some reason, when I use joins, I'm forced to use row[0], row[1] etc instead of fetching values using the name of column. I do not understand the reason behind this. However, this is the only way out in my case. Code below for those who may get stuck in a similar situation as me.

$link = mysql_connect("ip", "user", "passs");
$dbcheck = mysql_select_db("db");   
if ($dbcheck) {
    $chart_array_1[] = "['F DATE','F COUNT']";
    $result = mysql_query($a);
    if (mysql_num_rows($result) > 0) {
        while ($row = mysql_fetch_assoc($result)) {
            $chart_array_1[]="['".$row[0]."',".$row[1]."]";
        }
    }
}
mysqli_close($link);