Checking if column value == TRUE in php and mysql

Question

I'm trying to create a menu that will only populate links based on what the user has subscribed to. Basically I will be using php to check the user against a table that will contain his/her email + registered courses in a boolean format ie:

email          | course 1 | course 2 | course 3|
john@email.com   TRUE       FALSE       TRUE

I want to create an if statement that if course 1 = true WHERE email = john@email.com then echo some html

else check for course 2, etc.

What I'm having trouble with is how would i structure my if statement? Would I use

$sql="Select * FROM courses WHERE user = $user and course1 = TRUE"
$result = mysql_query($sql) or mysql_die($sql)
if($result)
{

echo html

}

or is there another way to do this? Is there a way I can write an if statement to check multiple values for example if($_SESSION['auth_id'] == 1) and if (course1 == TRUE) (i know that the last if statement isnt correct but I'd like to combine the two if possible). Would I need to write it as an if statement within an if statement:

if ($something)
{
   if ($somethingelse)
    {
       do work
     }
}

or an elseif:

if ($something)
{
   elseif ($somethingelse)
    {
       do work
     }
}

Answer 1

Get the row with the desired ID with all three courses. Then use PHP to check each of the course values and output the desired HTML accordingly.

To combine if statements, do this (you were close):

if (condition1 AND condition2)... 
elseif (condition3)... 

Or use && instead of AND, they'll work the same in your case, but they are not the same because of precedence. I'm writing you pseudo language that's a bit easier to read.

You can have as many conditions as you want:

if ( ((c1 OR c2) AND (!c3 AND c4) AND !(c5 AND c6)) OR c7 )... 

And don't forget to stop using the mysql_* functions as they are deprecated. Switch to mysqli or PDO instead.

Answer 2

You're asking how to do multiple conditions in an if in PHP, I see.

The boolean operators && (AND) and || (OR) will be your friends here.

if($cow=='brown'){
    if($cat=='white'){
        doStuff();
        }
    }

is the same as

if($cow=='brown' && $cat=='white'){
    doStuff();
    }

The && in the middle means the condition is only true overall if both parts are true.

see Logical Operators

The second one, with elseif, would be a use for ||. The wexample you gave would not work (it would never reach the elseif if the first one were not true...) but these two are equivalent:

 if($something){
    doStuff();
    }
 elseif($somethingElse){
    doStuff();
    }

and

 if($something || $somethingElse){
    doStuff();
    }

it will doStuff() if either one is true.

Answer 3

About logical operators:

Example ----- Name---Result

$a and $b---And-----TRUE if both $a and $b are TRUE.

$a or $b ----Or-------TRUE if either $a or $b is TRUE.

$a xor $b-- Xor------TRUE if either $a or $b is TRUE, but not both.

! $a-----------Not------TRUE if $a is not TRUE.

$a && $b----And------TRUE if both $a and $b are TRUE.

$a || $b----Or--------TRUE if either $a or $b is TRUE.

---

The returned $result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data

Something like this:

while ($row = mysql_fetch_assoc($result)) {
  $userid=$row["userid"];
  $course1 =$row["course1"];
  if($userid==1 && course1){
    //do something
  } 
}

Get result

As side note: mysql_* functions are deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

mysqli_fetch_assoc()

PDOStatement::fetch(PDO::FETCH_ASSOC)

Answer 4

This is NOT intended as an answer, it's just that it's too big for a comment.

It looks like you are pretty new to all of this. We all were, once, so I wish you luck. Stick with it and it will become clear and easier (and you will build up a good collection of code snippets).

Read a few books. That really got me up to speed with PHP and MySql.

And be sure that you know when to use MySql and when to use Sqlite. I mention this because, although PHP has some slight preference for MySQl, it also happilly supports Sqlite. And, if you think you might ever develop for Android or Ios then it might be better to become an Sqlite guru as both of them support Sqlite, but neither support MySql.

Get a good IDE. I personally love Netbeans. It's coding & debug facilities are excellent. For development on your own PC you need an Apache server and MySql; I don't think you can beat Xampp if you are using Windows (it's even easier with Linux).

But, I digress, I only started this to mention that you might want to consider your database design. It works fine so long as you are 100% certain that you will never have more than 3 courses.

Even then, it is not third normal form.

I would probably start with something like this. You can add more columns as needed and I will leave it to you to sort out the Primary and Foreign Keys.

mysql> show tables;
+------------------------+
| Tables_in_menu_example |
+------------------------+
| course_subscription    |
| courses_description    |
| students               |
+------------------------+
3 rows in set (0.00 sec)

mysql> describe students;
+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| student_id | int(11) | NO   | PRI | NULL    | auto_increment |
| email      | text    | NO   |     | NULL    |                |
+------------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> describe courses_description;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| course_id   | int(11) | NO   | PRI | NULL    | auto_increment |
| course_name | text    | NO   |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> describe course_subscription;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| student_id | int(11) | NO   |     | NULL    |       |
| course_id  | int(11) | NO   |     | NULL    |       |
+------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

I do realize that this hasn't answered your question at all (sorry), but I hope that it will help some. Good luck!!

Answer 5

try to use *mysql_num_rows.*

example:

    $syntax = mysql_num_rows(//your query syntax);
if($syntax >0 )
{
  // do return
}
else
{
  // refresh
}