2 tables in 1 query PHP

jmoney3457

so I have the code below and in the query I have it going to TWO tables (customers & employees) as there logging into a site using the explode function so they have to use their first and last name but for some reason it won't let me pull their login info from single query..is there way to do this?[PHP]<?
include 'connect.inc.php';

$login=explode(" ", $_POST[name]);
$query="SELECT * FROM employees,customers where fname='$login[0]' and lname='$login[1]' and password=password";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
session_start();
$id=mysql_result($result,$i,"id");
$vid=mysql_result($result,$i,"vid");
$cid=mysql_result($result,$i,"cid");
$fname=mysql_result($result,$i,"fname");
$lname=mysql_result($result,$i,"lname");
$level=mysql_result($result,$i,"level");
$year=mysql_result($result,$i,"year");
$make=mysql_result($result,$i,"make");
$model=mysql_result($result,$i,"model");
$price=mysql_result($result,$i,"price");
$image=mysql_result($result,$i,"image");


$i++;
$_SESSION[auth]="yes";

$_SESSION[id]=$id;
$_SESSION[vid]=$vid;
$_SESSION[cid]=$cid;
$_SESSION[level]=$level;
$_SESSION[fname]=$fname;
$_SESSION[lname]=$lname;
$_SESSION[year]=$year;
$_SESSION[make]=$make;
$_SESSION[model]=$model;
$_SESSION[price]=$price;
$_SESSION[image]=$image;






if ($level==1){
header('Location:admin.php');
} else if ($level==2){
header('Location:salesman.php');
}
else if ($level==3) {
header('Location:display.php');
}
else { header('Location:index.php');
}
?>[/PHP]

Khaos

Uh, without running your code or seeing the layout of your tables, I would make the following points:

1. Your comparison of the first and last name is currently case sensitive, which is a possible source of errors when attempting to locate the correct rows. You could fix this by changing it to, e.g. WHERE fname LIKE 'login[0]'

2. It's kind of hokey to try to parse a string of multiple words to determine a login. It would be a lot better to modify your system so that each user gets an actual user name to login with. This could be first initial and last name, or last name and first few letters of their first name (For example, at work I login with GoerssRo). Any single login word/name is going to be better than parsing a full name. How you structure it depends on the number of unique users you're supporting.

3. What if someone is listed in both customers and employees? Why not simply have a USERS table and denote the type of USER based on a field, rather than have two separate tables? This would seem to me to make more sense based on the way you are using this data.

4. AND password=password -- if password is a POSTed field, you need to access it like a variable... $password. If you just changed this when you quoted the code, nevermind...

5. If fields in customers and employees collide, then you need to qualify which table you want the field from when you retrieve from your result. I have no idea what your tables look like, but your results may be indexed like this.... customer.fname, customer.lname, etc. This is another reason why I feel it is better to have a single users table instead of multiples. It would greatly simplify things for you.

6. Your query might fail the way it is written if the fname and lname are not found in both the customers AND employees tables.

---

Do the following...

Create a users table that is indexed on a unique ID key. This table should store any fields that are the same between the customers and employees, including a login and password along with a timestamp of when the user was created. Use the timestamp as a key along with a programmatic key to encrypt and decrypt the password -- this isn't really that secure, but at least the password isn't stored as plain text where any prying eyes can simply read it.

Change your customers and employees tables so that they store special customer/employee information about a particular user and link the tables to your users table by the unique ID. Create a relationship that cascades deletions from users to employees/customers.

That should make your queries a lot easier. Now, for the login page you would query your users table and, depending on the type of user, direct them to the next page. The following pages would know to look in the customers or employees tables for any additional information they need based on the assumption that they deal with a particular type of user -- so you just give them the unique user ID and they go get whatever they need.

jmoney3457

thanks i got it fixed now:)