How to run multiple query in multiple table in single line

Question

I have two different table in database: (1) userreg (2)admin. i want to run a query which will first check in userreg table and then in admin table. if the user is found in userreg table then it will redirect to index.php page if the user found in admin table then the user will redirect to admin.php page. can anyone help me to do this?

 $result = $conn->query("select * from userreg where email='$email' AND password = '$password'")||query("select * from admin where email='$email' AND password = '$password'");
    $row = $result->fetch_array(MYSQLI_BOTH);
    if($row)
    {
      /// session_start();
      $_SESSION["fullname"] = $row['fullname'];
      $_SESSION["email"] = $email;
      $_SESSION["cellno"]= $row['cellno'];
      $_SESSION["gender"]=$row['gender'];
      $_SESSION["uid"]=$row['uid'];
      $_SESSION['logged_in'] = true;
      //header('Location: index.php');
      $URL="./index.php";
        echo '<META HTTP-EQUIV="refresh" content="0;URL=' . $URL . '">';

    }

also, can a user also be an admin? Be mindfull that in its current state, your code is open to SQL injection too. — Takarii, Mar 01 '16 at 10:22
Run the query, check the result, if the result is empty, run the other query in the other table. There's no magic syntax shortcut for that. — deceze, Mar 01 '16 at 10:24
Two table with same fields ? use Union http://www.w3schools.com/sql/sql_union.asp or make only one table with a field to determine if this user is an admin — Benjamin Poignant, Mar 01 '16 at 10:25

score 0 · Answer 1 · edited May 23 '17 at 12:15

Simplest way to handle this would be a flag in your usertable that allows registered users to be flagged as admins. However, assuming you cannot modify the tables, this code should help.

You can use this as a single query that would return the data you need

IF (SELECT Count(*) FROM userreg WHERE email='$email' AND password='$password') = 1

    THEN
        SELECT *,1 as 'flag' FROM userreg where email='$email' AND password='$password'
ELSEIF (SELECT Count(*) FROM userreg WHERE email='$email' AND password='$password') = 0
    THEN
        IF (SELECT Count(*) FROM admin WHERE email='$email' AND password='$password') = 1
            THEN
                SELECT *,2 as 'flag' FROM admin where email='$email' AND password='$password'
        ENDIF
ELSE SELECT 0
ENDIF

Then a small modification to your php code

 $result = $conn->query(); 
    $row = $result->fetch_array(MYSQLI_BOTH);
    if($row)
    {
      if ($row['flag'] = 0)
      {
        ///echo an error
      }
      else
      {
        /// session_start();
          $_SESSION["fullname"] = $row['fullname'];
          $_SESSION["email"] = $email;
          $_SESSION["cellno"]= $row['cellno'];
          $_SESSION["gender"]=$row['gender'];
          $_SESSION["uid"]=$row['uid'];
          $_SESSION['logged_in'] = true;
          //header('Location: index.php');
          if ($row['flag'] = 2)
          {
              $URL="./admin.php";              

          }
          else
          {
              $URL="./index.php";
          }
          echo '<META HTTP-EQUIV="refresh" content="0;URL=' . $URL . '">';

    }

You need to be fully aware that this does not protect against SQL injection attacks. You need to parameterise the query - Refer to this SO post for information on how to do that How can I prevent SQL injection in PHP?

Reference : MySQL Docs

Thanks for your kind comment, but there is some syntex error in the first part of your code. and i can't understand what it is. Thanks anyway. — Nazmul, Mar 02 '16 at 04:53

How to run multiple query in multiple table in single line

1 Answers1