Ordering mysql result by loop query value

Question

I am doing the following

$def = $_GET['def']; if (!$def == 0) {$offset = 'OFFSET '.$def.'';}
$data1 = mysql_query(
   "SELECT *, DATE(post_modified) as nicedate 
     FROM (`wp_postmeta`) 
     LEFT JOIN (`wp_posts`) ON wp_postmeta.post_id = wp_posts.ID 
     LEFT JOIN (`wp_top_ten`) ON wp_posts.ID = wp_top_ten.postnumber 
     WHERE meta_value='the_value' 
     ORDER BY nicedate DESC LIMIT 16 ".$offset.""
                   ) or die(mysql_error()); 
   while($info = mysql_fetch_array( $data1 ))

 { 
   $id = $info['ID']; 
   echo "some stuff";
   $data2 = mysql_query("
                 SELECT SUM(vote) as total_vote, COUNT(*) as total_count 
                 FROM (`wp_gdsr_votes_log`) 
                 WHERE id='".$id."' AND vote_type='article'
                    ")  or die(mysql_error()); 
        while($info = mysql_fetch_array( $data2 ))
     {$vote = number_format(
            ($info['total_vote']/$info['total_count'])
                        , 2, '.', ' ');} 
             echo $vote."/5"; echo "some more stuff"; 
 }

How could I order the results in $data1 where 'nicedate' would be the$vote value ?

That's not a subquery. That's doing multiple queries in a loop. — Matti Virkkunen
– Matti Virkkunen, Commented Jan 29, 2011 at 13:51

Damien Pirsy · Accepted Answer · 2011-01-29 14:07:19Z

1

You can create arrays to be populated after each round of the loop. Something like (after you fetched the result of $data1) :

You define an empty array before the $data1.

$newarray = array();

Than, in second loop:

$nicedate = info['nicedate'];

$newarray[$nicedate] = $vote;

You end up having an array('date'=>'vote', 'date2' => 'vote2',..), which you can than sort at will.

answered Jan 29, 2011 at 14:07

Damien Pirsy

25.5k8 gold badges72 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris Pennycuick · Accepted Answer · 2011-01-29 16:12:00Z

Well here is a guess because I can't test it because I don't have your data. However, based on theory you should be able to combine the queries and sort that way.

SELECT *, DATE(post_modified) as nicedate, 
  SUM(vlog.vote) as total_vote, COUNT(vlog.*) as total_count
  (total_vote/total_count) as vote
  FROM wp_postmeta AS meta
  LEFT JOIN wp_posts AS posts  ON meta.post_id = posts.ID 
  LEFT JOIN wp_top_ten AS tten ON posts.ID = tten.postnumber 
  LEFT JOIN wp_gdsr_votes_log AS vlog ON posts.ID = vlog.id
  WHERE meta_value='the_value' 
    AND vlog.vote_type='article'
  GROUP BY vlog.id 
  ORDER BY vote DESC, nicedate DESC 
  LIMIT 16 $offset

Hopefully I am close enough what you want and at least almost works. I apologizes now for any silly mistakes in there.

Feel free to make edits if you see fit. Just make a note of what you changed.

Edits:
1. Gutterball - Added edit note.

Collectives™ on Stack Overflow

Ordering mysql result by loop query value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related