0

I've been writing a script to display the names of users based on whether they are assigned an even or odd comment id. It calls up data from 2 different tables in the same database. Here is the table information:

Table 'comments' has the columns commentid, tutorialid, name, date: Table 'winners' has the columns pool, pool2, pool3, pool4, pool5, pool6, pool7. Table 'comments' has multiple rows that are updated through user input. Table 'winners' has only 1 row with numbers that are randomly generated daily.

The first part of the script that displays "Result 1" and "Result 2" is working properly. The part that isn't working is the part that calls up the usernames. I only want to display the usernames that corralate with the result that is displayed IE if Result 1 is chosen then I only want the usernames with even 'commentid's displayed.

<?php
$db = mysql_connect('localhost', 'username', 'pass') or die("Database error");
mysql_select_db('dbname', $db);

$query = "SELECT pool FROM winners";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result))
    if ($row['pool'] % 2) {
        echo "<h4>Result 1</h4>";
        $names = get_names(1);
        foreach($names as $name) {
            echo $name . "<br/>";
        }
    } else {
        echo "<h4>Result 2</h4>";
        $names = get_names(0);
        foreach($names as $name) {
            echo $name . "<br/>";
        }
    }

function get_names($pool_result)
{
    $name_array = array();

    $query = "SELECT * FROM comments where mod('commentid',2) = $pool_result";
    $result = mysql_query($query);

    while ($row = mysql_fetch_array($result)) {
        array_push($name_array, $row['name']);
    }

    return $name_array;
}
?>

Can anyone figure out why this isn't working?

user1206214
  • 63
  • 1
  • 7

2 Answers2

1

The SELECT statement with the mod is not referencing the field. Should be backticks instead of single quotes. Single quotes indicate a string constant, which would result in a constant result set (mod('commentid',2) appears to have a result of 0). It should be something like this:

$query = "SELECT * FROM comments where mod(`commentid`,2) = $pool_result";
Mark Wilkins
  • 40,729
  • 5
  • 57
  • 110
  • Hmm, I just tried your suggestion putting backticks around just about every combination possible on that line and I still either get no output or errors – user1206214 Feb 13 '12 at 23:34
  • @user1206214: If you run that query independently (e.g., in the command line client), do you get the expected result? – Mark Wilkins Feb 13 '12 at 23:41
0

Adding quotes around commentid treats it as a string, and you can't mod a string by an integer. Try the following instead:

$query = "SELECT * FROM comments WHERE commentid % 2 = $pool_result";

This was taken from the following Stack question: select row if the "value" % 2 = 1. MOD()

Community
  • 1
  • 1
Graham Swan
  • 4,818
  • 2
  • 30
  • 39