How do you fetch data from two tables in mysql using the same form

PHP
1

I have a user table and admin table.

  ini_set('display_errors', '1');
  ini_set('display_startup_errors', '1');
  error_reporting(E_ALL);

  include_once 'config.php'; 
 

  
if ($_SERVER['REQUEST_METHOD'] == 'POST') { 

  //collect form data and store in variables
  
  $username = trim($_POST['username'] );
  $password = md5($_POST['password']);
 

              $sql = $dbh -> prepare ("SELECT * FROM users WHERE username=?");

              $sql->execute([$username]);

              $result = $sql->fetch();

              if($result!=null){

                header('Location:../user/index.php');

              } else{

                $sql = $dbh -> prepare ("SELECT * FROM admin WHERE username=admin");

                $sql->execute([$username]);

                $result = $sql->fetch();

                if($result!=null){

                header('Location:../user/index.php');

              }else{

                $error = "Wrong Password/Username Combination";
              }
            }
  }
2

#1. Dont use md5 as your password hash.
#2: Are the two tables related?

3

They are on the same database.

Noted

4

Being in the same database doesn’t make them related.

I could put a ‘baseball’ table and ‘aliens’ table in my database, it doesn’t make them related.

If, however, I can say that either:
1: The ‘reported_by’ field in the aliens table is tied to the ‘name’ field in the baseball table;
or
2: There is a third table, such as ‘baseball_aliens’, that has a field ‘park’ that is tied to the baseball table, and also has a field ‘encounter_id’ that is tied to the aliens table.

Then I can say there is a relationship between the two tables. In other words, using fields that are tied together, I can draw a line from one table to the other, even if it requires one (or more) ‘hops’ through additional table(s).

5

Understood, so how I am going about it???

6

Well the answer to that is rather dependant on the relationship between the tables, but in general, what you’re referring to would be a JOIN

7

Also it’s unclear to me what you intend this scipt to do.
It appears a bit like some kind of log-in, but then doesn’t make much sense as one, as it just forwards to the same page regardless of input.

8 
$sql = $dbh -> prepare ("SELECT * FROM admin WHERE username=admin");

$sql->execute([$username]);
  1. “admin” should be quoted.
  2. Arguments array should be empty.

Or use it another way…

$sql = $dbh -> prepare ("SELECT * FROM admin WHERE username = ?");

$sql->execute(['admin']);
9

From the OP I see two queries separated with an else

SELECT * FROM users WHERE username=?
...
SELECT * FROM admin WHERE username=admin

And I see two identical header redirects

header('Location:../user/index.php');
.... 
header('Location:../user/index.php');

I admit, the PHP feels off to me. (besides the syntax, is the admin not a user? is it wise to have the admin username admin? why not have a single redirect after the conditionals?)

Any, the question is how to use information from a single form in two different queries. I’m not understanding the problem. eg. a form with a single

<input type="text" name="username" ...

should submit a value that the action file can access with $_POST["username"] and use as many times as it needs to.

10

No…on the same login form if user it directs to user panel while Admin to admin panel

11

Sorry I was suppose to change this…

12

Maybe if I put a “rights” column to determine if the person login in is a user
or an admin. example

13

Please correct me if I’m missing something about what you’re asking.

Based on the form supplied username input:

  • is the user in the user table?
  • is the user in the admin table?
  • redirect to appropriate index page

IIRC, most log in permissions designs I’ve seen have a table with “username”, “password”, “role” fields where the “role” field would specify “user”, “moderator”, “author”, “admin” etc. Permissions are often passed around in SESSION variables. eg.

$_SESSION["logged_in"] = true; 
$_SESSION["is_admin"] = false;

Then, no matter what file is loaded, it can test for the SESSION values and do / not do, display / not display whatever.

Is this something you are just beginning to put together and can change it’s design, or is this something you’re committed to using and need to figure out how to work with it?

14

It would be better to do that, and have a single table IMO. Apart from anything else, it makes it slightly less complex when a new user signs up, as you only have to check in a single table to see if their username / email / whatever you use to identify them is not already in the table.

1 Like
15

I am committed to using an in need figuring how to work with it…so I dont know the way I started it is the right way or using SESSION??

16

Sorry I wasn’t clear. By “something” I meant code outside of your control. By “committed” I meant a need to use someone else’s code, not that you needed login / permission control.

Anyway, if you can change the database design I am fairly certain that in the long run you will find it easier to have a single “user” table with a “permissions” field.

Unless you want to query the database every HTTP request (you don’t), I think SESSIONs are a good choice for passing around the more important values such as admin or not.

17

If I have the permission field, would I still use Sessions ??

18

I would. You will want to securely pass along the “is authorized” from page to page.

This is an older article but still worth reading

19

Thanks for the help…Let me check it out.

20

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.