mysql_query returning wrong result

Question

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

sum up the values in the notification column? as they look to be 1 for each, like this select sumNot from poolWinners where member_nr = '$uid' AND notification = 1; no need to loop over the results, get the sum straight — Alex Andrei
– Alex Andrei, Commented Jun 27, 2015 at 5:54
or do select count(member_nr) from poolWinners where member_nr= '$uid' and notification = 1; and use the count instead of the sum, to avoid notification values bigger than 1. again take the full value straight, no need to loop over the result set — Alex Andrei
– Alex Andrei, Commented Jun 27, 2015 at 5:55

Pratik Joshi · Accepted Answer · 2015-06-27 08:08:09Z

1

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.

edited Jun 27, 2015 at 8:08

answered Jun 27, 2015 at 4:53

Pratik Joshi

11.7k7 gold badges44 silver badges73 bronze badges

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

7 Comments

Tim C Over a year ago

Thanks mate but im getting same problem

Tim C Over a year ago

Thank you for your effort, but I cant for the live of me figure out why but it is still returning one even with the edit which I thought would solve it, a var_dump confirms $notification value is one (1)......any other suggestions

Pratik Joshi Over a year ago

Define $notification = 0 before while()

Tim C Over a year ago

Same problem...:-( guess its back to the drawing board for me

Pratik Joshi Over a year ago

Edit question and paste SQL for your table structure

|

danidee · Accepted Answer · 2015-06-27 05:20:49Z

1

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

answered Jun 27, 2015 at 5:20

danidee

9,6342 gold badges39 silver badges58 bronze badges

1 Comment

danidee Over a year ago

if you're dealing with a small amount of rows you could try mysql_num_rows to see how many rows were returned

ash__939 · Accepted Answer · 2015-06-27 05:34:24Z

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'] )

Amir · Accepted Answer · 2015-06-27 05:45:15Z

1

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

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

answered Jun 27, 2015 at 5:45

Amir

4,1314 gold badges18 silver badges28 bronze badges

Comments

Jaydip Kanjariya · Accepted Answer · 2015-06-27 05:46:55Z

1

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

answered Jun 27, 2015 at 5:46

Jaydip Kanjariya

463 bronze badges

Collectives™ on Stack Overflow

mysql_query returning wrong result

5 Answers 5

7 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related