Thread: Pulling from multiple tables...

Now this question is a bit from left field and I somehow doubt it will be possible, but here's what I'd like to do:

I have two tables in my database; a log table and a users table. In the log table, I have four fields I retreive:
Log - user,campaign,status,seconds

In the users table, I only need one field:
Users - full_name

However, the users table has a 'user' field that is the same data as it is in the log. What I'm wanting to do is compare the user field in the log to the user field in the users table to pull the full_name for that user into my array. For example:

PHP Code:

sql="SELECT l.user,l.campaign,l.status,l.seconds,u.full_name FROM log l,users u WHERE l.user=u.user AND <date range or other filters>";
$result=mysql_query($sql, $link);
while($row=mysql_fetch_array($result, MYSQL_NUM)){
    $array[]=$row;
    if(!in_array($row[4], $user_array){
        $user_array[]=array$row[4];
    }
} 


Now this is an abbreviated version of what I have going on, but for this example, it'll suffice. This works in that it appends the full_name to the end of each entry the way I was hoping it would. HOWEVER! This method only works when l.user=u.user. There are also system events in the log that are logged as a special 'user' that doesn't appear in the users table. Ergo, when the query checks the log and sees these entries (where l.user<>u.user) it skips them. Problem is, I need them and adding the system 'user' to the users table isn't an available option (the user field is numeric and the system 'user' is alphanumeric).

Is there some way to include the system log entries having them entered into $array with $row[5] being left blank?

What you're dealing with here is fundamental, ground level relational database operations. When you have related data in several tables, you absolutely MUST have primary and foreign key fields. Then you can use Joins and do other wonderful things. Try not to confuse unrelated matters, like whether or not you want to display something as output. The only things that matter are that a primary key must be UNIQUE within its table, and there must ALWAYS be a matching primary key for EVERY foreign key.

In your case, it seems like you ALMOST have these conditions, the part that's missing is this 'special' user that doesn't have a matching user record. That's pretty simple to fix. You will have to reconcile the data type differences, but that's not really much of an issue. One easy solution would be to change the numeric user code to be alphabetic, then you can include the 'special' user in the user table (even if there's no other data there but the ID). That won't work, of course, if the user ID is an auto-increment number. If so, you'll have to do something like assign some number as the 'special user' ID and fudge some code in a few places to use it instead of the alphabetic user identification now being used. These are no more than annoyances, they're not serious issues.

Please understand this: when working with a relational database, you absolutely MUST follow the rules that apply, you can't "cheat" and do little workarounds. You have to THINK in relational database terms. When you do, everything will work beautifully and you will be able to expand your database in the future, if needed.

Once you have your primary and foreign keys fixed, you can use either of the SQL syntax forms:

Code:

SELECT * FROM table1 AS a, table2 AS b WHERE a.ID = b.ID

or

SELECT * FROM table1 LEFT JOIN table2 ON a.ID = b.ID

Do read the W3Schools reference link that JMH provided for you.