Page 1 of 1

Need "select ... as..."?

Posted: Fri Aug 07, 2009 10:45 am
by phpBever
I just spent two hours (I'm a php newbie) trying to get this query to work:

Code: Select all

$sqlAdvisors = "SELECT tblFaculty.fac_name
                FROM tblFaculty 
                RIGHT JOIN tblStudFac ON tblFaculty.fac_id = tblStudFac.fac_id  
                WHERE tblStudFac.stud_id = 4";
without success. :evil: , even though the query worked perfectly well from the MySQL command line.

Finally, just trying anything I could think of, I tried inserting "as facName":

Code: Select all

$sqlAdvisors = "SELECT tblFaculty.fac_name AS facName
                FROM tblFaculty 
                RIGHT JOIN tblStudFac ON tblFaculty.fac_id = tblStudFac.fac_id  
                WHERE tblStudFac.stud_id = 4";
This seemed to work.

My question is this:
Neither Meloni's PHP, MySQL and Apache nor Welling and Thomson, PHP and MySQL Web Development seem to say anything about such a move being necessary. So
--is this move in fact necessary, given that the query works fine from the mysql command line?
--if so, how would one find this out other than by trial and error?
--or did the query actually run right in PHP but there's a trick to extracting the result that I'm missing?

Thanks.

Re: Need "select ... as..."?

Posted: Fri Aug 07, 2009 11:20 am
by Eran
Please post the relevant code - the query execution and following fetching of data. Did you check mysql_error to see if the query is returning any errors?

Re: Need "select ... as..."?

Posted: Fri Aug 07, 2009 11:47 am
by phpBever
Yes, I included an error test. Here's the script with the query that doesn't run.
When I add "AS facName" to the query after "SELECT tblFaculty.fac_name", it runs right.

Code: Select all

<?php
include("include.php");
doDB();
 
//get list of student's assigned advisors from tblStudFac
$sqlAdvisors = "SELECT tblFaculty.fac_name
                FROM tblFaculty 
                RIGHT JOIN tblStudFac ON tblFaculty.fac_id = tblStudFac.fac_id  
                WHERE tblStudFac.stud_id = 4";
            
$resAdvisors = mysqli_query($mysqli, $sqlAdvisors) or die(mysql_error($mysqli));
        
if ($resAdvisors) {
    $number = mysqli_num_rows($resAdvisors);
    printf("Result set had %d rows.<br />\n", $number);
    
    while ($advisors = mysqli_fetch_array($resAdvisors, MYSQLI_ASSOC)) {
        $facName[] = $advisors['tblFaculty.fac_name'];
    }
}
foreach ($facName as $n) {
    echo "$n.<br />";
}
?>
This is the output when I run it without the "AS facName":

Code: Select all

Result set had 3 rows.
.
.
.
When I run it with "AS facName", it gives me the three names.
Thanks.

Re: Need "select ... as..."?

Posted: Fri Aug 07, 2009 3:19 pm
by Eran
you don't need to specify the table association in the associative array (rather, you shouldn't).
so this

Code: Select all

= $advisors['tblFaculty.fac_name'];
Should probably be

Code: Select all

= $advisors['fac_name'];
you can also var_dump the contents of the row to the actual indices and data.

Code: Select all

var_dump($advisors);

Re: Need "select ... as..."?

Posted: Fri Aug 07, 2009 3:47 pm
by phpBever
Thanks.

var_dump() looks useful. I'll play with it.

Regarding specifying the table association in the associative array:

It works when I follow your suggestion.

But:

What if my query has returned, e.g., table1.id and table2.id. Won't I need to specify the table association then? Or is there a rule somewhere that would tell me I had to use and "AS" clause in that case?

Thanks again.

Re: Need "select ... as..."?

Posted: Fri Aug 07, 2009 4:00 pm
by Eran
If there are multiple fields with the same name, only the last one would be returned with its associative index as the field name (others would get overwritten). If you returned the data as numerically indexed array they would all be present.

To prevent such collisions, it's best to assign different aliases to same-named columns with the AS operator.

Re: Need "select ... as..."?

Posted: Fri Aug 07, 2009 4:01 pm
by phpBever
Thanks.