Giving a variable a value of 1 or 0 based on whether another variable appears in query results

Question

Hello,

I have a query $sqlStr4 that "selects" these fields from a MySQL database:

loginid
username
created

The query $sqlStr4 is limited to 10 rows / results.

I also have the following variable:

$u = $_SESSION['username'];

I would like to assign another variable $topten a value of 1 if $u equals any of the ten username fields returned by the query $sqlStr4, and a value of 0 if it does not.

How can I do this?

Thanks in advance,

John

Answer 1

If you had pasted the query, we'd be able to provide a fixed version. You can do this multiple ways, but one would be to add a column to your query and use a CASE statement.

select *, CASE WHEN username = '$u' THEN 1 ELSE 0 END as topten
from ...

This is just an example.. obviously to prevent SQL injection you should parameterize it, or use mysql_real_escape_string(), or a stored procedure, etc etc...

EDIT: I see you want the variable to be in PHP... so you would need to loop through the array to check each one. What is the problem you're having?...

$topten = 0;
if ($result) {
   while ($record = mysql_fetch_array($result)) {
      if ($record['username'] == $u) $topten = 1;
   }
}

Answer 2

Use:

<?php

$u = $_SESSION['username'];
$topten = 0;

$result = mysql_query($sqlStr4);

while ($row = mysql_fetch_assoc($result)) {
  if ($row['username'] == $u) {
    $topten = 1;
    break;
  }
}

mysql_free_result($result);
?>

Answer 3

Could try something like this...

while ($row = mysql_fetch_assoc($sqlStr4))
{
    $topten = ($u == $row['username']) ? 1 : 0;
    // the rest of your processing code here
}

There may well be better solutions for your situation. Would be easier if you posted some code!