up vote 2 down vote favorite

I have two queries sent to a database bring back posts (op_ideas 16 cols) followed by another which holds the votes per post (op_idea_vote cols 4) with matching idea_id's

Example of Data:

Query: op_ideas:

[{"idea_id":"2211","author_id":"100000", "date":"2012-09-06 10:02:28","idea_title":"Another test","4" etc etc

Query: op_idea_votes:

idea_id = 2211, agree=3, disagree=1, abstain=0

The code below ought to look at op_ideas, and then cycle over op_ideas_vote until it finds a match under 'idea_id'. Then it goes to the next record under op_ideas, and again using that idea_id search for it within the op_idea_vote list, find a match, and add it to the array.

This works for only the first record, not for the other three. I am testing, so I have 3 rows in each that match idea_id with different results in the op_idea_vote.

$votes = mysql_query($commentVotes);

$result = mysql_query($gl_query);

while ($gce_result = mysql_fetch_array($result)) {
    $voteid = $gce_result['idea_id'];

    while($allvotes= mysql_fetch_array($votes)) {
        if($voteid = $allvotes['idea_id']) 
        {
        //echo $voteid . " main idea and the votes: " . $allvotes;
            $gce_result["agree"] = $allvotes['agree'];
            $gce_result["disagree"] = $allvotes['disagree'];
            $gce_result["abstain"] = $allvotes['obstain'];
        } 
        else 
        {
            $gce_result["agree"] = 0;
            $gce_result["disagree"] = 0;
            $gce_result["abstain"] = 0;
        }
        //print_r($gce_result);
    }
    $data_result[] = $gce_result;
}

echo json_encode($data_result);

If I use print_f(&gce_result) it works fine in phpfiddle. But when i use the code above, it works for the first record, but it's complete missing the second two. It seems to be missing the second while, as it does not even give me the 0 0 0 results.

Query for op_ideas:

$gl_query = "SELECT DISTINCT * FROM heroku_056eb661631f253.op_ideas INNER JOIN op_organs ORDER BY date ASC;";
if (!mysql_query($gl_query)) {
    die('Error: ' . $gl_query . " " . mysql_error());
}
$result = mysql_query($gl_query);

Query For op_idea_vote :

$commentVotes = "SELECT v.idea_id, COUNT(v.agree = 1 or null) as agree, COUNT(v.disagree =   1 or null) as disagree, COUNT(v.obstain = 1 or null) as obstain FROM op_idea_vote v GROUP BY  v.idea_id";
if (!mysql_query($commentVotes)) {
    die('Error: ' . $commentVotes . " " . mysql_error());
}
$votes = mysql_query($commentVotes);
share|improve this question
1  
Can you show the queries? Might be easier to do this with a join – WayneC Oct 14 '12 at 10:20
Added as requested :) – Andrew Walker Oct 14 '12 at 10:29
1  
Are you really intending to perform a cross join between op_ideas and op_organs? Despite INNER JOIN keywords, you've not provided a join condition (so this actually performs a CROSS JOIN in MySQL). – eggyal Oct 14 '12 at 10:33
@eggyal I tried the SQL query yesterday, with no prival. I could not make it work and I put up two different types of code on here, but nobody could help. So instead I have done two queries, and added them server side. This works, other than the While stops after the first iteration. I tired mysql_data_seek($votes, 0) but that did not work, only copied the first result. Something is stopping the second while, as it does not even get to the second IF statement after the first row. – Andrew Walker Oct 14 '12 at 10:38
1  
If you can avoid it, please, don't use mysql_* functions to write new code. They are no longer maintained and the community has begun the deprecation process. See the red box? Instead you should learn about prepared statements and use either PDO or MySQLi. If you can't decide which, this article will help you. If you pick PDO, here is good tutorial. Also see Why shouldn't I use mysql functions in PHP? – vascowhite Oct 14 '12 at 10:55
show 3 more comments

3 Answers

up vote 1 down vote

You can scan a resource only once. So the inner while will be run only one time.

share|improve this answer
up vote 0 down vote

use == instead of = for checking condition of if & while in the while loop ,you have to assign the value of $allvotes ,but you never assigned,

while ($gce_result == mysql_fetch_array($result)) {
    $voteid = $gce_result['idea_id'];

    while($allvotes== mysql_fetch_array($votes)) {
        if($voteid == $allvotes['idea_id']) 
        {
        //echo $voteid . " main idea and the votes: " . $allvotes;
            $gce_result["agree"] = $allvotes['agree'];
            $gce_result["disagree"] = $allvotes['disagree'];
            $gce_result["abstain"] = $allvotes['obstain'];
        } 
        else 
        {
            $gce_result["agree"] = 0;
            $gce_result["disagree"] = 0;
            $gce_result["abstain"] = 0;
        }
        $data_result[] = $gce_result;
    }

}
share|improve this answer
how can that work, $allvotes wont get assigned the result of the mysql_fetch, so the rest of the code becomes pointless, same with $gce_result – WayneC Oct 14 '12 at 10:11
If I use == on the while, I get no results at all? And on the IF it still did not correct the problem? – Andrew Walker Oct 14 '12 at 10:15
@AndrewWalker: yes ,if your $allvotes not equal to return value of mysql_fetch_array($result) – Ravindra Bagale Oct 14 '12 at 10:17
What he was doing is a common pattern for looping over mysql results. The while loop is not checking if $allvotes its equal, it just needs to know that it is "truthy", Ie, mysql_fetch_array is returning a new row, if it is false, there are no more rows to fetch – WayneC Oct 14 '12 at 10:20
@Ravindrabagale They are different results. $result brings back all the posts and $allvotes ($votes) only brings back votes. If I use == on the first 'while' it does not give any results, so it cannot get to the second while. That what I mean. – Andrew Walker Oct 14 '12 at 10:21
show 6 more comments
up vote 0 down vote

Your problem is trying to scan over the $votes result more than once.

You should store the result of that query first.

Eg.

while ($vote = mysql_fetch_array($votes)) {
    $allvotes['idea_id'] = $vote;
}

while ($gce_result = mysql_fetch_array($result)) {
    $voteid = $gce_result['idea_id'];
    if (array_key_exists[$voteid, $allvotes]) {
       //assign results
    } else {
       //default
    }
}

Another option would be to do the query with a join, so you can do everything in one query. Then just loop over that result.

share|improve this answer
Thank you. I have tried the SQL approach here: [link]stackoverflow.com/questions/12874647/… I will try your suggestion and get back in a mo – Andrew Walker Oct 14 '12 at 11:08
Go with the sql one if you can. – WayneC Oct 14 '12 at 11:10

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

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