Mysql get column value in a single fetch

Question

I would like to have e single query to get column value without "while"... is possible? I have two table like this... Owners:

+----+-------+---------+
| id | names | surname |
+------------+---------+
| 1  | John  | Red     |
| 2  | Mark  | Green   |
| 3  | Frank | Yellow  |
| ...| ...   |...      |
+------------+---------+

Animals

+----+--------+--------+
| id |idOwner | animal |
+-------------+--------+
| 1  | 1      | Cat    |
| 2  | 2      | Bird   |
| 3  | 1      | Dog    |
| ...| ...    |...     |
+-------------+--------+

Now I have this code:

$query='SELECT * FROM Owners WHERE id=1';
$con=new mysqli($dbhostname, $dbusername, $dbpassword, $dbname);
$result=$con->query($query);
$arr=array();
while ($own = $result->fetch_array(MYSQLI_ASSOC)) {
    $qry='SELECT animal FROM Animals WHERE idOwner=own["id"]';
    $rslt=$con->query($qry);
    $i=1;
    while ($aml = $rslt->fetch_array(MYSQLI_ASSOC)) {
        $txt='animal'.$i;
        $own['animal'.$i]=$aml['animal'];
        $i++;
    }
    $arr[]=$own;
}
$con->close();

I would like to have the same result but with a single query because now the process is too late... is possible?

SELECT * FROM owners o INNER JOIN animals a ON a.idOwner = o.id WHERE o.id = 1 — devpro
– devpro, Commented Jan 26, 2016 at 15:45

Jay Blanchard · Accepted Answer · 2016-01-26 16:28:37Z

1

Yes, all you have to do is use a JOIN:

SELECT owners.*, animals.animal
FROM owners
LEFT JOIN animals
ON owners.id = animals.idOwner

Now you can display all of the owners and their animals in one loop or limit the query to one owner by using a WHERE clause. For instance, adding this clause:

WHERE owners.id = 1

Will return 2 rows from what you're showing in your tables.

OP mentions in comments that he desires one row per owner. To get a single row for each owner you will have to employ a sub-query and a GROUP_CONCAT (totally untested):

SELECT owners.*, sub-animals.animal
FROM owners
LEFT JOIN (SELECT GROUP_CONCAT(animal) AS animal
          FROM animals 
          GROUP BY animals.idOwner) AS sub-animals 
ON owners.id = animals.idOwner

edited Jan 26, 2016 at 16:28

answered Jan 26, 2016 at 15:44

Jay Blanchard

34.5k17 gold badges82 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Francesco G. Over a year ago

thanks... but I would like to have only 1 rows... suggest?

Jay Blanchard Over a year ago

One row with all of the animals in the row @FrancescoG.? If so, you will need a sub-query for the animals part. Your original question did not state the desired outcome, so maybe this is a different question?

Francesco G. Over a year ago

Yes one row for owner... I would like to have for example... Jhon, Red,..., cat, dog

Jay Blanchard Over a year ago

Can you setup a SQLFiddle with your data? @FrancescoG.

Francesco G. Over a year ago

yes... but I have done... thanks so much... There is an error in the query, the correct query is: SELECT owners.*, sub-animals.animal FROM owners LEFT JOIN (SELECT idOwner, GROUP_CONCAT(animal) AS animal FROM animals GROUP BY animals.idOwner) AS sub-animals ON owners.id = sub-animals.idOwner

|

devpro · Accepted Answer · 2016-01-26 15:47:57Z

1

Yes, you can use JOIN for one single query as:

SELECT o.*,a.animal FROM owners o 
INNER JOIN animals a ON a.idOwner = o.id

If you want to add WHERE id = 1 clause for getting specific user data than you can use this query as:

SELECT o.*,a.animal FROM owners o 
INNER JOIN animals a ON a.idOwner = o.id
WHERE o.id = 1

answered Jan 26, 2016 at 15:47

devpro

16.1k3 gold badges29 silver badges39 bronze badges

Collectives™ on Stack Overflow

Mysql get column value in a single fetch

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related