0

There is probably a very simple solution to this but I just can't work it out.

I am getting some info from the wp database, bypassing WP's built in classes, then echoing the results as encoded JSON data like this :

$db = new PDO('mysql:host=localhost;dbname=dbname', 'dbusername', 'dbpassword');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = 'SELECT user_login AS name, display_name AS tag, user_status AS status
FROM wp_users';

$query = $db->query($sql);
$i = 1;
$data = array();
while($row = $query->fetch(PDO::FETCH_ASSOC)) {
$row['id'] = $i;
$i++;
$data[] = $row;
}
echo json_encode($data);

This outputs the following :

[ 
    {
        "name": "johndoe",
        "tag": "John Doe",
        "status": "0",
        "id": 1
    },
    {
        "name": "janedoe",
        "tag": "Jane Doe",
        "status": "0",
        "id": 2
    }
]

But I need to change the order of the listings for a jquery plug-in that I'm using. I need the "id" tag to render first like this :

[ 
    {
        "id": 1,
        "name": "johndoe",
        "tag": "John Doe",
        "status": "0"
    },
    {
        "id": 2,
        "name": "janedoe",
        "tag": "Jane Doe",
        "status": "0"
    }
]

Can somebody point me in the right direction please?

1 Answer 1

1

Quite a few options you have here but the simplest I suppose would be to change your SQL query like so:

SELECT 
  0 AS id, 
  user_login AS name, 
  display_name AS tag, 
  user_status AS status 
FROM wp_users;

You can see that now we have the needed order and when you will do the $row['id'] = $i; assignment we will use that position instead of creating a new key => value.

Another solution making use of MySQL user variables:

SELECT
  @idx := @idx + 1 AS id,
  user_login AS name, 
  display_name AS tag, 
  user_status AS status
FROM wp_users
CROSS JOIN
  (SELECT @idx := 0) idx;

With the above solution you won't need to add the PHP increment mechanism, you will get the id column already formatted as you need.

2
  • Thanks @webtoure but sadly this won't work as the jQuery script requires all id's to be sequential. This will cause problems if a user deletes their account as the id values would jump. Say user 5 deleted their account, the id's would be "1,2,3,4,6,7 etc". That's why in this instance I'm not actually using the real user ID of the member.
    – Grant
    Aug 26, 2015 at 10:28
  • Sorry @webtoure, just woke up after a late night and misread your solution. I thought you were grabbing the ID from the table which obviously, after a coffee, I can see that you're not. Your solution works perfectly. Thanks ;)
    – Grant
    Aug 26, 2015 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.