PHP/MYSQL - Data retrieval

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.

I'd recommand you to use PDO instead of mysql_connect http://stackoverflow.com/questions/1402017/php-pdo-vs-normal-mysql-connect — Patiphan Mahamat, Jun 16 '15 at 09:57
Hello All - Thank you for your guidance. PDO doesn't help either. I've reverted to using nested queries instead. Thank you again. — User12345, Jun 16 '15 at 12:26
Use `print_r($row)` in your while loop to take a look at the row data that is being returned and check that you've got your array keys correct. — Eborbob, Jun 17 '15 at 08:08

User12345 · Accepted Answer · 2015-06-17T05:49:38.053

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);

PHP/MYSQL - Data retrieval

1 Answers1