Databases
StackOverflow2013
Postsdbo
POOrdering / out of sequence when transferring MySQL result set to array
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOrdering / out of sequence when transferring MySQL result set to array
    primarykey
    data
    text
    <p>I'm having a peculiar problem with MySQL data and arrays in PHP.</p> <p><strong>Background:</strong> 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.</p> <p>Each set of responses is identified by a column <code>unique_uid</code>, so that the 10 rows for 10 questions all have that same <code>unique_uid</code> (an 18-24 digit string). Each response is stored as a row in table <code>scores</code>. A user might have an account in table <code>accounts</code>, or they may have completed the survey as a guest without registering.</p> <p>I have two queries built to extract:</p> <p>(first) a DISTINCT collection of <code>unique_uid</code>s and the account's <code>name_first</code> and <code>name_last</code> (if they exist). If the user responded without an account, they are <code>NULL</code>. The results are ordered ASC on <code>name_last</code>, then ASC on <code>name_first</code>. All guests (<code>NULL</code>) are ordered first, followed by all accounts with names.</p> <p>(secondly) a query to collect all responses to the survey, then pair them with their owner.</p> <p><strong>The problem:</strong> For some reason, my array data is not staying in the order that MySQL returns it. I have two or three accounts with <code>name_first</code> and <code>name_last</code> that are getting interspersed amongst the <code>NULL</code> accounts. </p> <p>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? </p> <p>I thought that perhaps my associative keys were being recast as indexed, so I prepended the letter "a" to each <code>unique_uid</code>, but that did not help.</p> <p>Appreciate any insight you may have, thanks...</p> <p>*<strong>*EDIT :</strong> I was able to use <code>usort()</code> (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 :(</p> <pre><code>$rs = $wpdb-&gt;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-&gt;unique_uid ] = array( $row-&gt;name_first, $row-&gt;name_last, array() ); } // ** When I var_dump( $userdata ), the data is no longer in the correct order ** $rs = $wpdb-&gt;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 &lt; 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-&gt;unique_uid ][2][ $row-&gt;question_uid ] = array( $row-&gt;answer, $row-&gt;correct, $row-&gt;time, $row-&gt;lost_focus_count, $row-&gt;open, $row-&gt;date_created ); } </code></pre> <p>Some sample data from top query:</p> <pre><code>[..] [46]=&gt; object(stdClass)#315 (3) { ["unique_uid"]=&gt; string(20) "20977191501349809722" ["name_first"]=&gt; NULL ["name_last"]=&gt; NULL } [47]=&gt; object(stdClass)#316 (3) { ["unique_uid"]=&gt; string(19) "6630155101349813205" ["name_first"]=&gt; NULL ["name_last"]=&gt; NULL } [48]=&gt; object(stdClass)#317 (3) { ["unique_uid"]=&gt; string(21) "982542341421349813493" ["name_first"]=&gt; string(14) "Patrick" ["name_last"]=&gt; string(15) "Moore" } [49]=&gt; object(stdClass)#318 (3) { ["unique_uid"]=&gt; string(19) "7589292181349812907" ["name_first"]=&gt; string(5) "Mallory" ["name_last"]=&gt; string(9) "Moore" } [..] </code></pre> <p>Sample data from second query:</p> <pre><code> [0]=&gt; object(stdClass)#262 (8) { ["unique_uid"]=&gt; string(20) "16079139101349813111" ["question_uid"]=&gt; string(2) "41" ["answer"]=&gt; string(13) "Health Center" ["correct"]=&gt; string(1) "1" ["time"]=&gt; string(3) "5.0" ["lost_focus_count"]=&gt; string(1) "1" ["date_created"]=&gt; string(19) "2012-10-09 16:05:18" ["open"]=&gt; string(1) "1" } [1]=&gt; object(stdClass)#261 (8) { ["unique_uid"]=&gt; string(19) "7491272021349813110" ["question_uid"]=&gt; string(2) "41" ["answer"]=&gt; string(28) "Community-Based Organization" ["correct"]=&gt; string(1) "1" ["time"]=&gt; string(3) "5.7" ["lost_focus_count"]=&gt; string(1) "0" ["date_created"]=&gt; string(19) "2012-10-09 16:05:17" ["open"]=&gt; string(1) "1" } [2]=&gt; object(stdClass)#260 (8) { ["unique_uid"]=&gt; string(20) "20879148791349813105" ["question_uid"]=&gt; string(2) "41" ["answer"]=&gt; string(13) "Health Center" ["correct"]=&gt; string(1) "1" ["time"]=&gt; string(3) "5.3" ["lost_focus_count"]=&gt; string(1) "1" ["date_created"]=&gt; string(19) "2012-10-09 16:05:13" ["open"]=&gt; string(1) "1" } [3]=&gt; object(stdClass)#259 (8) { ["unique_uid"]=&gt; string(19) "6630155101349813079" ["question_uid"]=&gt; string(2) "41" ["answer"]=&gt; string(22) "Other Clinical Setting" ["correct"]=&gt; string(1) "1" ["time"]=&gt; string(4) "18.4" ["lost_focus_count"]=&gt; string(1) "0" ["date_created"]=&gt; string(19) "2012-10-09 16:04:59" ["open"]=&gt; string(1) "1" } [..] </code></pre> <p>Sort function:</p> <pre><code>function custom_sort( $a, $b ){ // Compare on `name_last` (the second item in $userdata array) return $a[1] &gt; $b[1] ; } usort( $userdata, "custom_sort" ); </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPatrick Moore
    1. USPatrick Moore
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOrdering / out of sequence when transferring MySQL result set to array
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIs there any overlap between the unique IDs returned by the two queries? Array internal order is the order that the elements were added to the array. If the second query updates an element from the first one, it will be in the first query's position.
      singulars
      1. POOrdering / out of sequence when transferring MySQL result set to array
      1. USBarmar
    2. COFYI, index arrays remember the order the elements were created, they don't get reordered unlesss you sort it.
      singulars
      1. POOrdering / out of sequence when transferring MySQL result set to array
      1. USBarmar
    3. CO@Barmar, that's what's so puzzling.. there is no sorting happening here. Both queries pull from `scores`, so every `unique_uid` returned in the second query, would already exist as array key from the first query.
      singulars
      1. POOrdering / out of sequence when transferring MySQL result set to array
      1. USPatrick Moore
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload