CODEWITHSUNDEEP
mysqlsql
faz
faz

Reputation: 453

Selecting multiple columns with the same name in MySQL

I wanted to access two tables in mysql and want to use the fields in both tables. I used a innerjoin to match both tables, but wondering how to fetch the field in both tables, coz both tables are having same field name and different values. Here is my query...

$query1 = mysql_query("SELECT facilitator.FacID, facilitator.email, assigned.email FROM facilitator INNER JOIN assigned ON facilitator.FacID = assigned.FacID WHERE assigned.email = '$stuemail' AND facilitator.active = '1'") or die($query."<br/><br/>".mysql_error());
            while($line1=mysql_fetch_array($query1,MYSQL_ASSOC)){
                $faci= $line1['FacID'];
                $facemail = $line1['email'];
                $stumail2 = $line1['email'];
.............code goes on..............

I want to fetch both emails field values but wondering how to do. I am sure what I have wrote for $stumail2 is wrong. Please help me.

Upvotes: 2

Views: 8894

Answers (3)

John Fink
John Fink

Reputation: 313

You just need the 'as' keyword in your query:

$query1 = mysql_query("SELECT facilitator.FacID, facilitator.email as fac_email, assigned.email as assign_email FROM facilitator INNER JOIN assigned ON facilitator.FacID = assigned.FacID WHERE assigned.email = '$stuemail' AND facilitator.active = '1'") or die($query."<br/><br/>".mysql_error());
        while($line1=mysql_fetch_array($query1,MYSQL_ASSOC)){
            $faci= $line1['FacID'];
            $facemail = $line1['fac_email'];
            $stumail2 = $line1['assign_email'];

Upvotes: 0

newfurniturey
newfurniturey

Reputation: 38436

You have a few options.

The first would be to assign a field-alias in MySQL:

SELECT
    facilitator.FacID,
    facilitator.email AS facilitatorEmail,
    assigned.email AS assignedEmail
    ....

The second, since you're using mysql_fetch_array(), is to access the fields by their index. The facilitator.email field has index 1 and assigned.email has index 2, so you could use:

$facemail = $line1[1];
$stumail2 = $line1[2];

Alternatively, by default, MySQL should be adding an incremental number (starting at 1) for each duplicate column name. So, you should be able to use email1 to access the assigned.email column:

$facemail = $line1['email'];
$stumail2 = $line1['email1'];

Upvotes: 3

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171411

You can use aliases on the columns to disambiguate them, e.g.:

SELECT facilitator.FacID, 
    facilitator.email as FacilitatorEmail, 
    assigned.email as AssignedEmail
FROM ...

Upvotes: 0

Related Questions