JSON encode MySQL results

Question

How do I use the json encode   function with MySQL query results  Do I need to iterate through the rows or can I just apply it to the entire results object

User · Answer

rows   json decode  mysql result true     as simple as that  -

User · Answer

array   array     subArray array     sql results   mysql query  SELECT   FROM  location      while  row   mysql fetch array  sql results          subArray location id   row  location       location id is key and  row  location   is value which come fron database       subArray x   row  x         subArray y   row  y        array       subArray     echo   ProductsData    json encode  array

User · Answer

I have the same requirement  I just want to print a result object into JSON format so I use the code below  I hope you find something in it      Code of Conversion  query    SELECT   FROM products     result   mysqli query  conn    query    if   result    echo   lt  br gt    Results Found       Conversion of result object into JSON format  rows   array    while  temp   mysqli fetch assoc  result          rows      temp    echo   lt  br gt     json encode  rows      else       echo  No Results Found

User · Answer

Sorry  this is extremely long after the question  but    sql    SELECT CONCAT      GROUP CONCAT CONCAT   username    username       CONCAT   email    email                AS json  FROM users    msl   mysql query  sql  print  msl  json       Just basically    SELECT  Select the rows      CONCAT  Returns the string that results from concatenating  joining  all the arguments  GROUP CONCAT  Returns a string with concatenated non-NULL value from a group

User · Answer

http   www php net mysql query says  mysql query   returns a resource    http   www php net json encode says it can encode any value  except a resource    You need to iterate through and collect the database results in an array  then json encode the array

User · Answer

My simple fix to stop it putting speech marks around numeric values     while  r   mysql fetch assoc  rs        while  elm each  r                 if is numeric  r  elm  key                            r  elm  key    intval  r  elm  key                           rows      r

User · Answer

For example  result   mysql query  SELECT   FROM userprofiles where NAME  TESTUSER       1   if  result is only one row    response   mysql fetch array  result   echo json encode  response     2   if  result is more than one row  You need to iterate the rows and save it to an array and return a json with array in it    rows   array    if  mysql num rows  result   gt  0        while  r   mysql fetch assoc  result             id    r  USERID        a column name  ex ID  used to get a value of the single row at at time         rows  id     r    save the fetched row and add it to the array              echo json encode  rows

User · Answer

http   www php net mysql query says  mysql query   returns a resource    http   www php net json encode says it can encode any value  except a resource    You need to iterate through and collect the database results in an array  then json encode the array

User · Answer

I solved like this    stmt- gt bind result  cde  v off  em nm  q id  v m        list array         i 0      while   cresult  stmt- gt fetch                    list  i  0   cde           list  i  1   v off           list  i  2   em nm           list  i  3   q id           list  i  4   v m           i  i 1            echo json encode  list             This will be returned to ajax as result set  and by using json parse in javascript part like this      obj   JSON parse dataX

User · Answer

http   www php net mysql query says  mysql query   returns a resource    http   www php net json encode says it can encode any value  except a resource    You need to iterate through and collect the database results in an array  then json encode the array

User · Answer

For example  result   mysql query  SELECT   FROM userprofiles where NAME  TESTUSER       1   if  result is only one row    response   mysql fetch array  result   echo json encode  response     2   if  result is more than one row  You need to iterate the rows and save it to an array and return a json with array in it    rows   array    if  mysql num rows  result   gt  0        while  r   mysql fetch assoc  result             id    r  USERID        a column name  ex ID  used to get a value of the single row at at time         rows  id     r    save the fetched row and add it to the array              echo json encode  rows

User · Answer

When using PDO Use fetchAll   to fetch all rows as an associative array   stmt    pdo- gt query  SELECT   FROM article     rows    stmt- gt fetchAll PDO  FETCH ASSOC   echo json encode  rows    When your SQL has parameters   stmt    pdo- gt prepare  SELECT   FROM article WHERE id       stmt- gt execute  1     rows    stmt- gt fetchAll PDO  FETCH ASSOC   echo json encode  rows    When you need to rekey the table you can use foreach loop and build the array manually   stmt    pdo- gt prepare  SELECT   FROM article WHERE id       stmt- gt execute  1      rows       foreach   stmt as  row         rows                newID    gt   row  id             Description    gt   row  text              echo json encode  rows    When using mysqli Use fetch all   to fetch all rows as an associative array   res    mysqli- gt query  SELECT   FROM article     rows    res- gt fetch all MYSQLI ASSOC   echo json encode  rows    When your SQL has parameters you need to perform prepare bind execute get result   id   1   stmt    mysqli- gt prepare  SELECT   FROM article WHERE id       stmt- gt bind param  s    id      binding by reference  Only use variables  not literals  stmt- gt execute     res    stmt- gt get result       returns mysqli result same as mysqli  query    rows    res- gt fetch all MYSQLI ASSOC   echo json encode  rows    When you need to rekey the table you can use foreach loop and build the array manually   stmt    mysqli- gt prepare  SELECT   FROM article WHERE id       stmt- gt bind param  s    id    stmt- gt execute     res    stmt- gt get result      rows       foreach   res as  row         rows                newID    gt   row  id             Description    gt   row  text              echo json encode  rows    When using mysql   API Please  upgrade as soon as possible to a supported PHP version  Please take it seriously  If you need a solution using the old API  this is how it could be done   res   mysql query  quot SELECT   FROM article quot      rows       while   row   mysql fetch assoc  res          rows      row     echo json encode  rows

User · Answer

I solved like this    stmt- gt bind result  cde  v off  em nm  q id  v m        list array         i 0      while   cresult  stmt- gt fetch                    list  i  0   cde           list  i  1   v off           list  i  2   em nm           list  i  3   q id           list  i  4   v m           i  i 1            echo json encode  list             This will be returned to ajax as result set  and by using json parse in javascript part like this      obj   JSON parse dataX

User · Answer

we could simplify Paolo Bergantino answer like this   sth   mysql query  SELECT        print json encode mysql fetch assoc  sth

User · Answer

Sorry  this is extremely long after the question  but    sql    SELECT CONCAT      GROUP CONCAT CONCAT   username    username       CONCAT   email    email                AS json  FROM users    msl   mysql query  sql  print  msl  json       Just basically    SELECT  Select the rows      CONCAT  Returns the string that results from concatenating  joining  all the arguments  GROUP CONCAT  Returns a string with concatenated non-NULL value from a group

User · Answer

I have the same requirement  I just want to print a result object into JSON format so I use the code below  I hope you find something in it      Code of Conversion  query    SELECT   FROM products     result   mysqli query  conn    query    if   result    echo   lt  br gt    Results Found       Conversion of result object into JSON format  rows   array    while  temp   mysqli fetch assoc  result          rows      temp    echo   lt  br gt     json encode  rows      else       echo  No Results Found

User · Answer

rows   json decode  mysql result true     as simple as that  -

User · Answer

Thanks   my answer goes   if   result- gt num rows  gt  0                  code                 arr                    inc   0              while   row    result- gt fetch assoc                        code                     jsonArrayObject    array  lat    gt   row  lat     lon    gt   row  lon     addr    gt   row  address                       arr  inc     jsonArrayObject                   inc                               json array   json encode  arr               echo  json array                    else              echo  0 results

User · Answer

we could simplify Paolo Bergantino answer like this   sth   mysql query  SELECT        print json encode mysql fetch assoc  sth

User · Answer

http   www php net mysql query says  mysql query   returns a resource    http   www php net json encode says it can encode any value  except a resource    You need to iterate through and collect the database results in an array  then json encode the array

User · Answer

The above will not work  in my experience  before you name the root-element in the array to something  I have not been able to access anything in the final json before that   sth   mysql query  quot SELECT     quot     rows   array    while  r   mysql fetch assoc  sth          rows  root name      r    print json encode  rows    That should do the trick

User · Answer

sth   mysqli query  conn   quot SELECT     quot     rows   array    while  r   mysqli fetch assoc  sth          rows      r    print json encode  rows    The function json encode needs PHP  gt   5 2 and the php-json package - as mentioned here NOTE  mysql is deprecated as of PHP 5 5 0  use mysqli extension instead http   php net manual en migration55 deprecated php

User · Answer

One more option using FOR loop     sth   mysql query  SELECT         for  rows   array     row   mysql fetch assoc  sth    rows      row    print json encode  rows     The only disadvantage is that loop for is slower then e g  while or especially foreach

User · Answer

Try this  this will create your object properly    result   mysql query  SELECT          rows   array       while  r   mysql fetch assoc  result           rows  object name        r         print json encode  rows

User · Answer

Code    rows   array     while  r   mysqli fetch array  result MYSQL ASSOC        row array  result      r     array push  rows  row array      here we push every iteration to an array otherwise you will get only last iteration value    echo json encode  rows

User · Answer

When using PDO Use fetchAll   to fetch all rows as an associative array   stmt    pdo- gt query  SELECT   FROM article     rows    stmt- gt fetchAll PDO  FETCH ASSOC   echo json encode  rows    When your SQL has parameters   stmt    pdo- gt prepare  SELECT   FROM article WHERE id       stmt- gt execute  1     rows    stmt- gt fetchAll PDO  FETCH ASSOC   echo json encode  rows    When you need to rekey the table you can use foreach loop and build the array manually   stmt    pdo- gt prepare  SELECT   FROM article WHERE id       stmt- gt execute  1      rows       foreach   stmt as  row         rows                newID    gt   row  id             Description    gt   row  text              echo json encode  rows    When using mysqli Use fetch all   to fetch all rows as an associative array   res    mysqli- gt query  SELECT   FROM article     rows    res- gt fetch all MYSQLI ASSOC   echo json encode  rows    When your SQL has parameters you need to perform prepare bind execute get result   id   1   stmt    mysqli- gt prepare  SELECT   FROM article WHERE id       stmt- gt bind param  s    id      binding by reference  Only use variables  not literals  stmt- gt execute     res    stmt- gt get result       returns mysqli result same as mysqli  query    rows    res- gt fetch all MYSQLI ASSOC   echo json encode  rows    When you need to rekey the table you can use foreach loop and build the array manually   stmt    mysqli- gt prepare  SELECT   FROM article WHERE id       stmt- gt bind param  s    id    stmt- gt execute     res    stmt- gt get result      rows       foreach   res as  row         rows                newID    gt   row  id             Description    gt   row  text              echo json encode  rows    When using mysql   API Please  upgrade as soon as possible to a supported PHP version  Please take it seriously  If you need a solution using the old API  this is how it could be done   res   mysql query  quot SELECT   FROM article quot      rows       while   row   mysql fetch assoc  res          rows      row     echo json encode  rows

User · Answer

The above will not work  in my experience  before you name the root-element in the array to something  I have not been able to access anything in the final json before that   sth   mysql query  quot SELECT     quot     rows   array    while  r   mysql fetch assoc  sth          rows  root name      r    print json encode  rows    That should do the trick

User · Answer

One more option using FOR loop     sth   mysql query  SELECT         for  rows   array     row   mysql fetch assoc  sth    rows      row    print json encode  rows     The only disadvantage is that loop for is slower then e g  while or especially foreach

User · Answer

sth   mysqli query  conn   quot SELECT     quot     rows   array    while  r   mysqli fetch assoc  sth          rows      r    print json encode  rows    The function json encode needs PHP  gt   5 2 and the php-json package - as mentioned here NOTE  mysql is deprecated as of PHP 5 5 0  use mysqli extension instead http   php net manual en migration55 deprecated php

User · Answer

My simple fix to stop it putting speech marks around numeric values     while  r   mysql fetch assoc  rs        while  elm each  r                 if is numeric  r  elm  key                            r  elm  key    intval  r  elm  key                           rows      r

User · Answer

Thanks   my answer goes   if   result- gt num rows  gt  0                  code                 arr                    inc   0              while   row    result- gt fetch assoc                        code                     jsonArrayObject    array  lat    gt   row  lat     lon    gt   row  lon     addr    gt   row  address                       arr  inc     jsonArrayObject                   inc                               json array   json encode  arr               echo  json array                    else              echo  0 results

User · Answer

sth   mysqli query  conn   quot SELECT     quot     rows   array    while  r   mysqli fetch assoc  sth          rows      r    print json encode  rows    The function json encode needs PHP  gt   5 2 and the php-json package - as mentioned here NOTE  mysql is deprecated as of PHP 5 5 0  use mysqli extension instead http   php net manual en migration55 deprecated php

User · Answer

lt  php define  HOST   localhost    define  USER   root    define  PASS       define  DB   dishant      con   mysqli connect HOST USER PASS DB       if  mysqli connect errno           echo  Failed to connect to MySQL      mysqli connect error           sql    select   from demo       sth   mysqli query  con  sql     rows   array     while  r   mysqli fetch array  sth MYSQL ASSOC        row array  id      r         array push  rows  row array       echo json encode  rows    mysqli close  con     gt    aarray push  rows  row array   help to build array otherwise it give last value in the while loop  this work like append method of StringBuilder in java

User · Answer

The code below works fine here    lt  php     con mysqli connect  localhost   username  password databaseName         Check connection   if  mysqli connect errno           echo  Failed to connect to MySQL      mysqli connect error            query    the query here       result   mysqli query  con  query       rows   array      while  r   mysqli fetch array  result          rows      r        echo json encode  rows      mysqli close  con     gt

User · Answer

array   array     subArray array     sql results   mysql query  SELECT   FROM  location      while  row   mysql fetch array  sql results          subArray location id   row  location       location id is key and  row  location   is value which come fron database       subArray x   row  x         subArray y   row  y        array       subArray     echo   ProductsData    json encode  array

User · Answer

Code    rows   array     while  r   mysqli fetch array  result MYSQL ASSOC        row array  result      r     array push  rows  row array      here we push every iteration to an array otherwise you will get only last iteration value    echo json encode  rows

User · Answer

The code below works fine here    lt  php     con mysqli connect  localhost   username  password databaseName         Check connection   if  mysqli connect errno           echo  Failed to connect to MySQL      mysqli connect error            query    the query here       result   mysqli query  con  query       rows   array      while  r   mysqli fetch array  result          rows      r        echo json encode  rows      mysqli close  con     gt

User · Answer

sth   mysqli query  conn   quot SELECT     quot     rows   array    while  r   mysqli fetch assoc  sth          rows      r    print json encode  rows    The function json encode needs PHP  gt   5 2 and the php-json package - as mentioned here NOTE  mysql is deprecated as of PHP 5 5 0  use mysqli extension instead http   php net manual en migration55 deprecated php

User · Answer

Try this  this will create your object properly    result   mysql query  SELECT          rows   array       while  r   mysql fetch assoc  result           rows  object name        r         print json encode  rows

User · Answer

lt  php define  HOST   localhost    define  USER   root    define  PASS       define  DB   dishant      con   mysqli connect HOST USER PASS DB       if  mysqli connect errno           echo  Failed to connect to MySQL      mysqli connect error           sql    select   from demo       sth   mysqli query  con  sql     rows   array     while  r   mysqli fetch array  sth MYSQL ASSOC        row array  id      r         array push  rows  row array       echo json encode  rows    mysqli close  con     gt    aarray push  rows  row array   help to build array otherwise it give last value in the while loop  this work like append method of StringBuilder in java

[php] JSON encode MySQL results

Examples related to php

Examples related to mysql

Examples related to json