PHP/SQL help with function

Question

Help my write a function.

function reportThread() {
 global $id;

$result = mysql_query("SELECT is_checked FROM reports WHERE url = '?threadID=$id'");
$r = mysql_fetch_assoc($result);

if ($r['is_checked'] == 0) {

echo "<script type=\"text/javascript\">alert(\"A moderator has been notified.\");</script>";

mysql_query("INSERT INTO reports (url, userID) VALUES ('?threadID=$id', 1)");

} else echo "<script type=\"text/javascript\">alert(\"Theres already a pending report on this thread.\");</script>";

}

What I want to achive is that if theres is a URL that the user reports in the table with is_checked = 0 i want it to say error else i want it to insert!

This will not work for me. It does always insert.

reports:
id, url, is_checked(default 0), userID

i have records...

Answer 1

Your logic is askew. That code inserts if is_checked is equal to 0. Try this:

function reportThread() {
 global $id;

$result = mysql_query("SELECT is_checked FROM reports WHERE url = '?threadID=$id'");
$r = mysql_fetch_assoc($result);

if ($r['is_checked'] == 0) 
{
    echo "<script type=\"text/javascript\">alert(\"Theres already a pending report on this thread.\");</script>";
}
else
{
    mysql_query("INSERT INTO reports (url, userID) VALUES ('?threadID=$id', 1)");
    echo "<script type=\"text/javascript\">alert(\"A moderator has been notified.\");</script>";
}

Answer 2

Right now, you're code is inserting if ($r['is_checked'] == 0), which is the opposite of what you want. But that's not the only problem.

You are also using the equality operator (==) instead of the identity operator (===).

In PHP, 0 is falsy value. So are the following:

• "" (an empty string)
• 0 (0 as an integer)
• "0" (0 as a string)
• NULL
• FALSE
• array() (an empty array)
• var $var; (a variable declared, but without a value in a class)

So if there is no result (will cause a NULL) or is_checked is any of those values, using the equality operator will make your comparison true.

PHP: Comparison Operators

What you really want to do is check if the value is really equal to 0, as such:

if (strval($r['is_checked']) !== '0') {
  echo "<script type=\"text/javascript\">alert(\"A moderator has been notified.\");</script>";
  mysql_query("INSERT INTO reports (url, userID) VALUES ('?threadID=$id', 1)");
} else {
  echo "<script type=\"text/javascript\">alert(\"Theres already a pending report on this thread.\");</script>";
}

But since you are doing that, why not do the operation directly in your query?

$result = mysql_query("SELECT is_checked FROM reports WHERE url = '?threadID=$id' AND is_checked = '0'");

// No Result? Continue
if(mysql_num_rows($result) === 0) {
  echo "<script type=\"text/javascript\">alert(\"A moderator has been notified.\");</script>";
  mysql_query("INSERT INTO reports (url, userID) VALUES ('?threadID=$id', 1)");
} else {
  echo "<script type=\"text/javascript\">alert(\"Theres already a pending report on this thread.\");</script>";
}

Answer 3

You could eliminate the need for checking the number for rows altogether if all you want is for the query to act as a boolean operator. Use an aggregate function instead of the is_checked column alone, and that way the query will always return a single row and it will always be at least 0. And if you remove the is_checked column and remove the url part of the user_id column (just have that output in another query when you need it), you get something much lighter:

Example:

<script type="text/javascript">
alert("
<?php
$results = mysql_query("SELECT COUNT(threadID) AS counter FROM reports 
           WHERE threadID = '$id'");

while($count = mysql_fetch_assoc($results)) {
       $checked = ($count > 0) ? TRUE : FALSE;
 }

 if($checked) {
     echo "There is already a pending report on this thread.";
 }
 else {
     mysql_query("INSERT INTO reports (threadID, userID) VALUES ('$id', 1)");
     echo "Theres already a pending report on this thread.";
 }
 ?>
 ");

Notice that the above example assumes you have already escaped out of PHP, output the javascript (thus removing the need for the redundant echo out of the script tags), and then you simply start the php where the alert needs to be filled in.