How do I make column alias to be in integer?

Question

I had this line of query

$sql = " SELECT c.name, IF(a.addressid IS NOT NULL,0,1) AS checked
FROM customers c
LEFT JOIN addresses a 
ON c.customerid = a.customerid
WHERE customerid = 123 ";

$array     = array();
$GetResult = $Obj->FetchData($sql, $Obj->DEFAULT_PDO_CONNECTIONS);
while ($row = $GetResult->fetch()) {
    $array[] = $row;
}
header("Content-type: application/json");
$result = json_encode($array);
echo $result;

when I echo in JSON format it show my checked output as a string. And I wonder by default what data type for alias checked? And how can I change into integer data type?

A column alias will acquire the data type from the underlying data , I cannot remember if this is by scanning data or from the table definition- if in doubt force it by using cast or convert. — P.Salmon, Aug 01 '19 at 10:04

score 1 · Answer 1 · answered Aug 02 '19 at 00:55

Thanks for your answer, yes by default it already in data type number, the problem in json format. It produce my checked as string. So I found the answer here PHP json_encode encoding numbers as strings.

$result = json_encode($array, JSON_NUMERIC_CHECK);

Ryan Tan · Answer 2 · 2019-08-01T09:00:50.313

0

i have added a convert function of that column to represent an integer type. hopefully that will translate to your desired result

SELECT c.name, Convert(IF(a.addressid IS NOT NULL,0,1), unsigned integer) AS checked
FROM customers c
LEFT JOIN addresses a 
ON c.customerid = a.customerid
WHERE customerid = 123

edited Aug 01 '19 at 09:00

answered Aug 01 '19 at 08:54

Ryan Tan

344
2
11

score 0 · Answer 3 · answered Aug 01 '19 at 09:00

0

Use cast or convert, full documentation.

So it would be similar to:

SELECT c.name, cast(if(a.addressid is not null, 0, 1) as unsigned) as checked

answered Aug 01 '19 at 09:00

cjb110

1,310
14
30

How do I make column alias to be in integer?

3 Answers3