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

Ye' old general discussion board. Basically, for everything that isn't covered elsewhere. Come here to shoot the breeze, shoot your mouth off, or whatever suits your fancy.
This forum is not for asking programming related questions.

Moderator: General Moderators

Post Reply
phpBever
Forum Commoner
Posts: 42
Joined: Fri Aug 07, 2009 10:23 am

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

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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?
phpBever
Forum Commoner
Posts: 42
Joined: Fri Aug 07, 2009 10:23 am

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

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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);
phpBever
Forum Commoner
Posts: 42
Joined: Fri Aug 07, 2009 10:23 am

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

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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.
phpBever
Forum Commoner
Posts: 42
Joined: Fri Aug 07, 2009 10:23 am

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

Post by phpBever »

Thanks.
Post Reply