Another JOIN question...

cju

I have two tables, a users table and a sources table. In sources, you have any number of records that are linked to the users table by an ID (in this case "emp_id"). This field exists in both tables and is named "emp_id".

So ... what I need is to list out sources either all at once, or by last name, or by owner. In all cases I need to output the owner name from the users table with the source.

So I have some fields that look like this:

users.emp_id
users.f_name
users.l_name

sources.emp_id
sources.f_name
sources.l_name

It's important to note that the f_name/l_name in each table are not the same. In users this is the name of the owner, who may have 1 or 1000 records in the sources table linked to his "emp_id". In essence, it's a simple contact manager, so think of the sources table as a list of contacts that are owned by the person in users table.

What I need to do is output a lists of sources based on a user defined request, you know like show all sources, show my sources, show other people's sources, etc.. What I need is a query that can output sources and then also fetch the owner name from the users table.

What I thought would work okay is something like this:

		
$sql = "SELECT s.f_name, s.l_name, u.f_name, u.l_name
FROM src_main s INNER JOIN users u ON s.emp_id = u.emp_id" AND s.l_name LIKE '$alphasearch%'
		$result = mysql_query($sql);

This does not work. You see in this example I'm trying to say show me all sources that have last names that start with this letter, and also give me the owner f_name and l_name from the users table where the emp_id matches the emp_id in the sources table.

I certainly could do two queries, one where I lookup the sources by l_name letter (or whatever the search criteria happens to be), then loop on the results and do another select inside the loop that looks up the f_name / l_name in users on each trip through the loop - but that's what I want to avoid, it seems I could do this in a much more efficient way.

I wonder if the fact that I have a f_name/l_name in each table is the problem? Since they aren't supposed to match even when emp_id matches does this cause a problem? Is SQL going to assume in a Join that fields witht he same names will always matchup?? Do you see what I'm saying?

I know this may be rambling, but I wanted to provide all the information I could to assist anybody replying.

Thanks in advance!

siwis

if its mysql, you'd use a JOIN (simple) along the following lines:

SELECT a.field1, b.field1 FROM a LEFT JOIN b ON a.b_id = b.id WHERE a.field1 = rule ORDER BY b.field1 DESC

if its not mysql, I dunno!

I'd therefore assume your sql to be:

SELECT sources.f_name, sources.l_name, users.f_name, users.l_name FROM sources LEFT JOIN users ON sources.emp_id = users.emp_id WHERE sources.l_name LIKE '$alphasearch%'

edited to remove stray double quote

cju

Okay, thanks very much for your reply. I am using MySQL. Here is the query I came up with, added some minor changes to your example - I did try your example as-is but it didn't seem to work but that is probably because of something else I'll discuss in a minute..

 		$sql = "SELECT s.f_name as source_first, s.l_name as source_last, s.src_id, u.f_name as owner_first, u.l_name as owner_last 
 				FROM src_main s LEFT JOIN users u ON s.emp_id = u.emp_id WHERE s.l_name LIKE '$alphasearch%'";

Now please check out the following PHP code. If I inspect or try to display data from this query result before entering a foreach loop, it seems to work okay. However, inside the loop, the data comes back as groups of matching letters, like owner_name might be "HH" or "CC", but using an simple echo right before entering the loop would return a real name. I fell like an idiot, I can't figure this out... Here's my PHP code with a couple comments added that might help illustrate what I'm rambling on about.

		

$sql = "SELECT s.f_name as source_first, s.l_name as source_last, s.src_id, u.f_name as owner_first, u.l_name as owner_last 
 				FROM src_main s LEFT JOIN users u ON s.emp_id = u.emp_id WHERE s.l_name LIKE '$alphasearch%'";
$result = mysql_query($sql);			

$my_source = mysql_fetch_array($result);			
stripslashes_array( $my_source );
$my_array_count = count( $my_source );

echo "Owner: $my_source[owner_first] ID: $my_source[emp_id]"; exit; 
# If I leave this line uncommented it will  stop here and display a real first name from the database, going on into the foreach loop this would display is something goofy like HH or GG or CC

# Now dynamically create the meat of the table that displays the available sources
foreach ( $my_source as $my_row ) {  
# If I look at variable in the loop as: $my_row[source_first], or something like that - it returns garbage

siwis

In the first instance....

would you not be better served using a while () if you are simply looping through the results of an sql?

Secondly, I don't quite see the need to shorten the table names, but I doubt that this has anything to do with why your code is stumbling.

Here's what I'd do anyway for starters to check that I'd got a query that was working:

<?PHP
$sql = "SELECT src_main.f_name AS source_first, src_main.l_name AS source_last, src_main.src_id, users.f_name AS owner_first, users.l_name AS owner_last 
                 FROM src_main LEFT JOIN users ON src_main.emp_id = users.emp_id WHERE src_main.l_name LIKE '$alphasearch%'";
$result = mysql_query($sql);            

 while ($my_source = mysql_fetch_array($result))	{
	$source_first = $my_source['source_first'];
	$source_last = $my_source['source_last'];
	$src_id = $my_source['src_id'];
	$owner_first = $my_source['owner_first'];
	$owner_last = $my_source['owner_last'];
	echo $source_first . " " . $source_last . " " . $src_id . " " . $owner_first . " " . $owner_last . "<br>";
 };            

?>

Once you know your query is sound, you can then set about displaying it.

cju

You rock! The foreach statement was the causing a problem of some kind, I wish I knew what - but it works so I don't care! 🙂

I love the Internet. I have a problem and kind people help out within minutes of asking.

Have a great weekend!

siwis

This is a pure guess, but I doubt it was the actual foreach that was causing the problem.

I'd guess the problem was related to this line:

$my_source = mysql_fetch_array($result);

You assigned the sql output array to a variable NOT to an array. Therefore you would likely have had nothing meaningful left to loop through.
$my_source[] =
would have assigned the array ready for your foreach.