0

I have a query that gives me the number of occurrences using a count, see below:

$query = "SELECT COUNT(a.market_id) AS winners, a.winner, 
a.twitter_pubstatus, a.market, a.racetime, a.racecourse, b.course, b.horse, 
b.type, b.racetime FROM results a INNER JOIN bets b ON a.racecourse = b.course 
WHERE a.twitter_pubstatus = 0 AND a.market = '$win' AND 
b.type = '$userwin' AND a.winner = b.horse;";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$checkWinners = $row['winners'];

echo $checkWinners;

This for example will return 11. I now have an IF statement:

<?php if ($checkWinners > 1) { ?>

DO THIS

<? } else { ?>

DO THIS INSTEAD

<? } ?>

Now it runs through this once and that's fine. I want it to loop through so, 11 then goes through the first bit then need it will be 10 and go through until it hits 0 and then stop.

How to do this? While Loop? Help Appreciated

andy
  • 459
  • 2
  • 6
  • 26

3 Answers3

1

Yes you can do this with a while loop. The basic logic is this:

$c = 10;
while ($c > 0) {
  // do something
  $c--;
}

But if I had to take a guess, I think maybe what you really want is this:

$query = "SELECT COUNT(a.market_id) AS winners, a.winner, 
a.twitter_pubstatus, a.market, a.racetime, a.racecourse, b.course, b.horse, 
b.type, b.racetime FROM results a INNER JOIN bets b ON a.racecourse = b.course 
WHERE a.twitter_pubstatus = 0 AND a.market = '$win' AND 
b.type = '$userwin' AND a.winner = b.horse;";
$result = mysql_query($query) or die(mysql_error());

while ( $row = mysql_fetch_array($result) ) {
  $checkWinners = $row['winners'];
  echo $checkWinners . '<br/>';
}

This loops through all the rows returned from the query.

CrayonViolent
  • 32,111
  • 5
  • 56
  • 79
0
while($checkWinners >0){

 echo $checkWinners;
 $checkWinners--;
}
akr
  • 739
  • 4
  • 15
0

An even simpler way is to loop through normally, and have MySQL order them as you need, to ensure they are in the right order.

$query = "SELECT COUNT(a.market_id) AS winners, a.winner, 
    a.twitter_pubstatus, a.market, a.racetime, a.racecourse, b.course, b.horse, 
    b.type, b.racetime FROM results AS a INNER JOIN bets AS b ON a.racecourse = b.course 
    WHERE a.twitter_pubstatus = 0 AND a.market = '$win' AND 
    b.type = '$userwin' AND a.winner = b.horse
    ORDER BY winners DESC;"; //<--- ORDER BY 
$result = mysql_query($query) or die(mysql_error());

if( mysql_num_rows( $result ) ) { //Check if there's any results
    while ( $row = mysql_fetch_array($result) ) { //Iterate results until empty
        $checkWinners = $row['winners'];
        //Tweet winners code here
        echo $checkWinners . '<br/>';
    }
    //Update database to reflect the updated tweets
    $query = "UPDATE results AS a INNER JOIN bets AS b ON a.racecourse = b.course 
        WHERE a.twitter_pubstatus = 0 AND a.market = '$win' AND 
        b.type = '$userwin' AND a.winner = b.horse
        SET a.twitter_pubstatus = 1;" //Update all of them at once
    mysql_query($query) or die(mysql_error()); //Send query, unless fail
} else {
    //If no results from initial query
    echo "No Winners! :(";
}

Also, you should consider using mysqli over mysql, as mysql extension for PHP has been deprecated. See: Why shouldn't I use mysql_* functions in PHP?.

Community
  • 1
  • 1
Sunny Patel
  • 7,830
  • 2
  • 31
  • 46
  • Within while loop I have another statement that updates twitter_pubstatus = 1 so once this has changed all the winners twitter_pubstatus = 1 will it then go to the else section? That's what I need it to do. – andy Mar 27 '14 at 18:39
  • So is `twitter_pubstatus` a column inside your database, and you need to update it within the database? Or is that its own variable inside PHP? Please clarify your question by including more code in the detail. – Sunny Patel Mar 27 '14 at 18:42
  • yes, twitter_pubstatus is a column within the database. Basically I have a script that tweets out the row in the database and then updates the twitter_pubstatus = 1. Sometimes there may be 4 or 5 rows that need updating so I need it to loop through these until all the 0's have been converted to 1's then the initial query above will return 0 rows and therefore move on to the else section. – andy Mar 27 '14 at 18:46
  • Haha, it's right there in the query, sorry @andy. You'll have to perform another query within the if statement then. This is one reason you should convert to using **mysqli**, so that you can take advantage of [Prepared Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php)! Anyway, I'll update my answer in a few minutes. – Sunny Patel Mar 27 '14 at 18:51
  • Struggling at the minute, it's not really doing what I want it to :\ It recognises the winner but it doesn't do the update and I'm currently getting an error mysql_fetch_array() expects parameter 1 to be resource – andy Mar 27 '14 at 20:19
  • Are you modifying `$result` inside the *while* loop? – Sunny Patel Mar 27 '14 at 20:21
  • I've now renamed result in the query and the error has gone. However the first SELECT COUNT is returning a figure of one when it shouldn't as there is nothing in the bets table so it should return 0. Pulling my hair out here :\ – andy Mar 27 '14 at 20:30
  • I made some silent edits to your code in my answer, by adding the "AS" clause between `results a` and `bets b`. – Sunny Patel Mar 27 '14 at 20:38
  • I have this `if(mysql_num_rows( $result25 )) { //Check if there's any results while ($row = mysql_fetch_array($result25)) { //Iterate results until empty $checkWinners = $row['winners']; $numberWinners = (mysql_num_rows( $result25 ));` It returns a number when the statement is correct but when the join returns nothing it is just returns nothing, no zero, no characters at all. If I wanted to do a if($numberWinners = 0) how do I get a zero in that statement when the join doesnt return anything – andy Mar 27 '14 at 21:02
  • I'm not sure if I'm following your logic. If the initial `$result` from the `SELECT` statement returns 0 rows, then it will skip that if statement and move onto the else, and execute whatever you have in the lines surrounding `"No Winners :("`. – Sunny Patel Mar 27 '14 at 21:17
  • Yes, exactly. That's what I would have thought to but it doesn't return a zero. Instead it just returns nothing, not a number just blank as I have the result in an echo. When there are results that meet the SELECT criteria it returns the correct number. Strange. – andy Mar 28 '14 at 08:08
  • Are you saying that `$result == ''`? What happens when you do a `var_dump( $result ); var_dump( mysql_num_rows( $result ) );` just before the if statement? See if those responses make sense. – Sunny Patel Mar 28 '14 at 20:46
  • ok the statement in the initial post works when there is 1 result it either does the update in the first section or the update in the else. I just need to to pop back up to the top if there is more than one result and check. Each update (first section and else) changes the twitter_pubstatus to 1 or 2 so I just need it to go back to the start until the 0's have gone. – andy Mar 28 '14 at 21:43
  • The very first `SELECT` statement was designed to accept multiple results. If you're not getting multiple results when you have them, then you need to adjust that `SELECT` statement accordingly as well as the `WHERE` clause in the `UPDATE` statement that follows. Are you saying you have an `UPDATE` statement inside the else where I have listed "**No Winners! :(**"? If so, you need to mention the purpose of that. – Sunny Patel Mar 28 '14 at 21:57