CODEWITHSUNDEEP
phpmysqlsql
user1424394
user1424394

Reputation: 116

Not able to get the value out of query

I am running this below code but it returns an error as: "undefined index: value"

$two_tier= mysql_query("
    SELECT Count(1)
    FROM(
        SELECT `login_id`
        FROM `data`
        WHERE Year(`start_at`) = Year(Date_sub(Now(), INTERVAL 1 month))
        AND Month(`start_at`) = Month(Date_sub(Now(), INTERVAL 1 month)) 
        AND end_at > Date_add(start_at, INTERVAL 5 minute) 
        GROUP  BY `login_id`
        HAVING Count(`login_id`) > 1
    ) AS Value
");

$two_cnt = mysql_fetch_assoc($two_tier);
echo $two_cnt['value'];

I am trying to get the "value". Little help, please.

Upvotes: 1

Views: 100

Answers (2)

hjpotter92
hjpotter92

Reputation: 80649

Since the exterior most value in SELECT statement is SELECT Count(1), so you are actually fetching Count(1) as Count(1) instead of value. To fetch it as value, you'll need to do it as:

SELECT Count(1) AS value ....

and then the code will work fine. So, the final statement will be:

$two_tier= mysql_query("
    SELECT Count(1) AS value
    FROM(
        SELECT `login_id`
        FROM `data`
        WHERE Year(`start_at`) = Year(Date_sub(Now(), INTERVAL 1 month))
        AND Month(`start_at`) = Month(Date_sub(Now(), INTERVAL 1 month)) 
        AND end_at > Date_add(start_at, INTERVAL 5 minute) 
        GROUP  BY `login_id`
        HAVING Count(`login_id`) > 1
    ) AS Value
");

Upvotes: 3

Constantin
Constantin

Reputation: 2318

SELECT Count(1) as my_value
FROM   (SELECT `login_id` 
        FROM   `data` 
        WHERE  ( Year(`start_at`) = Year(Date_sub(Now(), INTERVAL 1 month)) 
                 AND Month(`start_at`) = Month(Date_sub(Now(), INTERVAL 1 month) 
                                         ) ) 
               AND ( end_at > Date_add(start_at, INTERVAL 5 minute) ) 
        GROUP  BY `login_id` 
        HAVING Count(`login_id`) > 1) AS Value


$two_cnt = mysql_fetch_assoc($two_tier);
echo $two_cnt['my_value'];

you select your FROM as Value, but result of first select isn't in that Value, you need one more AS

Upvotes: 5

Related Questions