Categories
Mastering Development

Mysql query loop, resulting row of one query in where statement of next query

I have a read only table to calculate relationship between people

DROP TABLE IF EXISTS `reference`;
CREATE TABLE IF NOT EXISTS `reference` (
  `self` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `brother` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `mom` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `wife` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  PRIMARY KEY (`self`),
  UNIQUE KEY `self` (`self`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `reference` (`self`, `brother`, `mom`, `wife`) VALUES
('aunt', 'uncle', 'grandmom', ''),('brother', 'brother', 'mom', 'sisterinlaw'),
('grandmom', 'granduncle', 'greatgrandmom', ''),('greatgrandmom', 'greatgranduncle', 'greatgreatgranduncle', ''),('mom', 'uncle', 'grandmom', ''),
('sisterinlaw', 'brotherinlaw', 'motherinlaw', ''),('uncle', 'uncle', 'grandmom', 'aunt'),('wife', 'brotherinlaw', 'motherinlaw', '');

What i am Trying to do

Example Input : ‘mom brother wife dad’

step1 – mom’s(self) brother(column_name) is uncle(result)

step2 – uncle’s wife in aunt

step3 – aunt’s dad is not a valid column, so stop looping and call them aunt’s relative

My current code

<?php

$db = new mysqli('localhost', 'root', '', 'db_name', 3306, '')
         or die('Could not connect to the database server' . mysqli_connect_error());

$input = 'mom brother wife dad';
$array = explode(' ', $input);
$len = count($array);
$row = $array[0];
for ($x = 1; $x < $len; $x++) {
$column = $array[$x];
if($column=='brother'||$column=='mom'||$column=='wife'){
    $users = $db->query("SELECT ".$column." as part FROM db_name.reference WHERE self ='".$row."'");
     $users_temp = array();
                while ($result= $users->fetch_assoc()) {
                                            $users_temp[] = $result['part'];    }
                                            if(count($users_temp)>0){$row = $users_temp[0];}else{
                                                $row = $row. ' s relative';
                                            }
}else{
        $row = $row. '\' s relative';
}

}
echo $row;
?>

Problems I face:

  • the real dataset is 11Col x 600Rows. Is there an alternate efficient way to store and access this dataset
  • the real input would be upto 25 separate strings

Eg:’Mom Mom Mom Husband Dad Son Daughter Son Son’ 9 steps so there would be 8 queries. Is it possible to reduce those roundtrips.

Thanks for your time

Leave a Reply

Your email address will not be published. Required fields are marked *