CODEWITHSUNDEEP
phpmysqlarrays
Patrick Moore
Patrick Moore

Reputation: 13354

Ordering / out of sequence when transferring MySQL result set to array

I'm having a peculiar problem with MySQL data and arrays in PHP.

Background: the system is a reporting tool used to export data from a survey system. A user can respond to any survey any number of times. A survey can have any number of questions.

Each set of responses is identified by a column unique_uid, so that the 10 rows for 10 questions all have that same unique_uid (an 18-24 digit string). Each response is stored as a row in table scores. A user might have an account in table accounts, or they may have completed the survey as a guest without registering.

I have two queries built to extract:

(first) a DISTINCT collection of unique_uids and the account's name_first and name_last (if they exist). If the user responded without an account, they are NULL. The results are ordered ASC on name_last, then ASC on name_first. All guests (NULL) are ordered first, followed by all accounts with names.

(secondly) a query to collect all responses to the survey, then pair them with their owner.

The problem: For some reason, my array data is not staying in the order that MySQL returns it. I have two or three accounts with name_first and name_last that are getting interspersed amongst the NULL accounts.

Given that I ordered ascending on name_last then name_first, and the data is coming back from MySQL in that order, and the PHP array is generated in that order, I can't find out why it's losing order. Does PHP re-structure array data based on which key was touched last?

I thought that perhaps my associative keys were being recast as indexed, so I prepended the letter "a" to each unique_uid, but that did not help.

Appreciate any insight you may have, thanks...

**EDIT : I was able to use usort() (code at the bottom of this post) to get the data back in order. But still haven't been able to figure out why it's out of order in the first place :(

$rs = $wpdb->get_results( 'SELECT DISTINCT unique_uid, u.name_first, u.name_last FROM wp_fen_cme_scores s LEFT JOIN wp_fen_cme_accounts u ON u.uid = s.account_uid WHERE s.test_uid = ' . $y . ' ORDER BY u.name_last' );

// ** When I var_dump( $rs ), the data is in the correct order **
$userdata = array();
foreach ( $rs as $row ){
    $userdata[ 'a' . $row->unique_uid ] = array( $row->name_first, $row->name_last, array() );
}

// ** When I var_dump( $userdata ), the data is no longer in the correct order **

$rs = $wpdb->get_results( 'SELECT s.unique_uid, s.question_uid, s.answer, s.correct, s.time, s.lost_focus_count, s.date_created, (q.correct_answer < 1 ) AS open FROM wp_fen_cme_scores s, wp_fen_cme_questions q WHERE s.test_uid = ' . $y . ' AND q.uid = s.question_uid ORDER BY q.sort ASC');
foreach ( $rs as $row ){
    $userdata[ 'a' . $row->unique_uid ][2][ $row->question_uid ] = array( $row->answer, $row->correct, $row->time, $row->lost_focus_count, $row->open, $row->date_created );
}

Some sample data from top query:

[..]
  [46]=>
  object(stdClass)#315 (3) {
    ["unique_uid"]=>
    string(20) "20977191501349809722"
    ["name_first"]=>
    NULL
    ["name_last"]=>
    NULL
  }
  [47]=>
  object(stdClass)#316 (3) {
    ["unique_uid"]=>
    string(19) "6630155101349813205"
    ["name_first"]=>
    NULL
    ["name_last"]=>
    NULL
  }
  [48]=>
  object(stdClass)#317 (3) {
    ["unique_uid"]=>
    string(21) "982542341421349813493"
    ["name_first"]=>
    string(14) "Patrick"
    ["name_last"]=>
    string(15) "Moore"
  }
  [49]=>
  object(stdClass)#318 (3) {
    ["unique_uid"]=>
    string(19) "7589292181349812907"
    ["name_first"]=>
    string(5) "Mallory"
    ["name_last"]=>
    string(9) "Moore"
  }
[..]

Sample data from second query:

  [0]=>
  object(stdClass)#262 (8) {
    ["unique_uid"]=>
    string(20) "16079139101349813111"
    ["question_uid"]=>
    string(2) "41"
    ["answer"]=>
    string(13) "Health Center"
    ["correct"]=>
    string(1) "1"
    ["time"]=>
    string(3) "5.0"
    ["lost_focus_count"]=>
    string(1) "1"
    ["date_created"]=>
    string(19) "2012-10-09 16:05:18"
    ["open"]=>
    string(1) "1"
  }
  [1]=>
  object(stdClass)#261 (8) {
    ["unique_uid"]=>
    string(19) "7491272021349813110"
    ["question_uid"]=>
    string(2) "41"
    ["answer"]=>
    string(28) "Community-Based Organization"
    ["correct"]=>
    string(1) "1"
    ["time"]=>
    string(3) "5.7"
    ["lost_focus_count"]=>
    string(1) "0"
    ["date_created"]=>
    string(19) "2012-10-09 16:05:17"
    ["open"]=>
    string(1) "1"
  }
  [2]=>
  object(stdClass)#260 (8) {
    ["unique_uid"]=>
    string(20) "20879148791349813105"
    ["question_uid"]=>
    string(2) "41"
    ["answer"]=>
    string(13) "Health Center"
    ["correct"]=>
    string(1) "1"
    ["time"]=>
    string(3) "5.3"
    ["lost_focus_count"]=>
    string(1) "1"
    ["date_created"]=>
    string(19) "2012-10-09 16:05:13"
    ["open"]=>
    string(1) "1"
  }
  [3]=>
  object(stdClass)#259 (8) {
    ["unique_uid"]=>
    string(19) "6630155101349813079"
    ["question_uid"]=>
    string(2) "41"
    ["answer"]=>
    string(22) "Other Clinical Setting"
    ["correct"]=>
    string(1) "1"
    ["time"]=>
    string(4) "18.4"
    ["lost_focus_count"]=>
    string(1) "0"
    ["date_created"]=>
    string(19) "2012-10-09 16:04:59"
    ["open"]=>
    string(1) "1"
  }
[..]

Sort function:

function custom_sort( $a, $b ){
    // Compare on `name_last` (the second item in $userdata array)
    return $a[1] > $b[1] ;
}
usort( $userdata, "custom_sort" );

Upvotes: 1

Views: 168

Answers (1)

Nikolaos Dimopoulos
Nikolaos Dimopoulos

Reputation: 11485

This could be because of the length of your keys.

What you can do is either make the keys the same length, say prepend zeros 

// This will prepend zeros before the key and keep only the last
// 24 characters. If your key is 24 characters long it will be 
// intact, otherwise it will end up with a few zeros at the front
$key = substr('00000' . $row->unique_uid, -24);

Upvotes: 2

Related Questions