PHP while loop within a while loop works once

Question

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);

Can you show the queries? Might be easier to do this with a join — WayneC
– WayneC, Commented Oct 14, 2012 at 10:20
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
– eggyal, Commented Oct 14, 2012 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
– Andrew Walker, Commented Oct 14, 2012 at 10:38
Firstly, it's worth noting that sometimes questions take more than a day to be answered here. I have open questions from over a month ago, to which I am still hoping to receive an answer. Secondly, if "nobody could help" it's likely that your question was unclear or difficult to understand: work on clarifying your requirements in those previous questions and you may well get a prompt and helpful answer. Thirdly, it's extremely unclear from this question exactly what it is that you're trying to accomplish and there's almost certainly a much better way than what you're doing. — eggyal
– eggyal, Commented Oct 14, 2012 at 10:44
Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — vascowhite
– vascowhite, Commented Oct 14, 2012 at 10:55

Alvarez · Accepted Answer · 2012-10-14 10:29:51Z

1

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

answered Oct 14, 2012 at 10:29

Alvarez

1,3292 gold badges10 silver badges29 bronze badges

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

Comments

Ravindra Bagale · Accepted Answer · 2012-10-14 10:21:07Z

0

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;
    }

}

edited Oct 14, 2012 at 10:21

answered Oct 14, 2012 at 10:03

Ravindra Bagale

17.7k9 gold badges46 silver badges70 bronze badges

11 Comments

WayneC Over a year ago

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

Andrew Walker Over a year ago

If I use == on the while, I get no results at all? And on the IF it still did not correct the problem?

Ravindra Bagale Over a year ago

@AndrewWalker: yes ,if your $allvotes not equal to return value of mysql_fetch_array($result)

WayneC Over a year ago

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

Andrew Walker Over a year ago

@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.

|

WayneC · Accepted Answer · 2012-10-14 10:41:57Z

0

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.

edited Oct 14, 2012 at 10:41

answered Oct 14, 2012 at 10:20

WayneC

5,7402 gold badges35 silver badges44 bronze badges

2 Comments

Andrew Walker Over a year ago

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

WayneC Over a year ago

Go with the sql one if you can.

Collectives™ on Stack Overflow

PHP while loop within a while loop works once

3 Answers 3

Comments

11 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

11 Comments

2 Comments

Linked

Related