Accessing result of a JOIN

[cju]

If you saw my previous thread regarding a JOIN problem I was having, I think I found the problem.

I have a query that I verified works on the command line (of course I hard coded the variable value that is in it for the command line), it looks like this:

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 );

I want to step through the results in a foreach loop. Before entering the loop, I do the following:

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

Now what I'd normally do at this point is setup a foreach that looks like this:

foreach ( $my_source as $my_row ) {

Do something

}

The problem is, $my_source is screwed up somehow. I seem to be able to access $my_source before entering the foreach loop okay, but inspecting the value of $my_array_count tells me something is worng. For instance, I look at that count value expecting it to have a value of 1 but it has 6 in it.

I'm wondering if I have to do something special when I use a JOIN like this? Will my results look different? Will mysql_fetch_assoc( $result ), not work on a JOIN result?

Thanks for your time.

[volleytotal_ch]

mysql_fetch_assoc is just getting 1 row, not all! so a foreach for one row doesn't make sense, does it?

try this:

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 , $db_conn ) or die("Query failed"); 

$my_array_count = mysql_num_rows( $result ); 

if( $my_array_count > 0 ) {
  while ( $my_row = mysql_fetch_array($result) ) { 
    stripslashes_array( $my_row ); 
    // Do something 
  }
}
else {
// No rows found!
}

hth