CODEWITHSUNDEEP
phpmysql
Tim C
Tim C

Reputation: 5714

mysql_query returning wrong result

Consider the following code

if ( isset( $_SESSION['FBID'] )   ) {
    $uid      = $_SESSION['FBID'];
    $sql      = "SELECT *, count(member_nr) AS notifyMe 
                 FROM poolWinners 
                 WHERE member_nr = '$uid'   AND notification ='1'";
    $result = mysql_query($sql);
    while($row=mysql_fetch_array($result)){
       $notification = $row['notifyMe'];
    }//while
      if ( $notification > 0 ) {
        echo '<span class="badge">' . $notification . '</span>';
    } //if
    var_dump($notification);
} //isset( $_SESSION['FBID'] )

The above script returns how many notifications a member has as you can see in image below enter image description here

My Problem

The script is returning the wrong result (wrong number of notifications). Have a look at the table below, the member number appears 3 times in the table so: $notification = $row['notifyMe'] Should = 3 AND NOT 1

What am I missing or doing wrong here? Thanks for reading

Upvotes: 2

Views: 175

Answers (5)

Pratik Joshi
Pratik Joshi

Reputation: 11693

Use 

$sql      = "SELECT *, count(*) AS notifyMe 
             FROM poolWinners 
             WHERE member_nr = '$uid'   AND notification ='1'";

Notice count(*) , it will fetch how many records are matching criteria.

And initialize $notification = 0; at the start.

Upvotes: 1

Jaydip Kanjariya
Jaydip Kanjariya

Reputation: 46

$sql = "SELECT * FROM poolWinners WHERE member_nr = '$uid'   AND notification ='1'";

if ($result=mysqli_query($con,$sql))
  {
  // Return the number of rows in result set
echo "Toal notification".mysqli_num_rows($result);
  }

mysqli_close($con);
?>

Upvotes: 1

Amir
Amir

Reputation: 4111

$sql      = "SELECT *  
             FROM poolWinners 
             WHERE member_nr = '$uid'   AND notification ='1'";

$result = mysql_query($sql);
$notification = mysql_num_rows($result);

Upvotes: 1

ash__939
ash__939

Reputation: 1599

In your code the notification will be always one, since it will take only the notifyMe field of last row in the result set.

If you want to get number of notifications, try this

if ( isset( $_SESSION['FBID'] )   ) {
    $uid      = $_SESSION['FBID'];
    $sql      = "SELECT *, count(member_nr) AS notifyMe 
                 FROM poolWinners 
                 WHERE member_nr = '$uid'   AND notification ='1'";
    $result = mysql_query($sql);
    $notification = 0;
    while($row=mysql_fetch_array($result)){
       $notification++; 
       /*
       OR $notification += $row['notifyMe'];
       */
    }//while
      if ( $notification > 0 ) {
        echo '<span class="badge">' . $notification . '</span>';
    } //if
    var_dump($notification);
} //isset( $_SESSION['FBID'] )

Upvotes: 1

danidee
danidee

Reputation: 9634

Have you tried approaching it from this angle

$sql = "SELECT * FROM poolWinners WHERE member_nr = '$uid'   AND notification ='1'";

$result = mysql_query($sql);
$notification = array();
    while($row=mysql_fetch_array($result)){
       $notification[] = $row['notifyMe'];
    }
//an array count on notification should give you the number of elements in the array i.e those that matched the query

$total_count = count($notification);

Upvotes: 1

Related Questions