CODEWITHSUNDEEP
phpmysqlarrayspdo
Sora
Sora

Reputation: 95

Build an associative array from a SQL SELECT JOIN (PHP)

I have a table with list of users. Every user has a column with a Select . I am trying to fill a Select with a list of items coming from an associative table related to every user.

I would like to save those items into an array and then loop this array in the select tag, but I am lost to have one array per user and make the relation in a query result. I am using PDO for the queries. Hope you to understand my question. My code below of how I am trying to do it. I am newbie on this.

After trying many times I've got this.

 $stmt = $pdo->prepare("SELECT id,account,gender,age,education,expected_salary,phone,email 
    FROM user 
    ORDER BY id");
    $stmt->execute();
     // set the resulting array to associative
    $seeker_list = $stmt->fetchAll(PDO::FETCH_ASSOC);

    //To get the list for the select <tag>
    foreach($seeker_list as $key => $value){

    $seeker = $value['account'];
    $query = $pdo->prepare('SELECT user_specialty.specialty_id as    id_user_specialty,
                                specialty.specialty
                                FROM user_specialty
                                JOIN specialty ON user_specialty.specialty_id = specialty.id 
                                WHERE user_specialty.user = $seeker');
                                $query->execute();


    $specialty_list = $query->fetchAll(PDO::FETCH_ASSOC) ;    
      foreach($specialty_list as $key => $value){

            echo $value['id_user_specialty'];
    }  
    }

Upvotes: 2

Views: 907

Answers (1)

Sora
Sora

Reputation: 95

It worked like this:

$stmt = $pdo->prepare("SELECT id,account,gender,age,education,expected_salary,phone,email 
FROM user 
ORDER BY id");
$stmt->execute();
 // set the resulting array to associative
$seeker_list = $stmt->fetchAll(PDO::FETCH_ASSOC);

//To get the specialty list
foreach($seeker_list as $key => $value){

$seeker = $value['account'];
$query = $pdo->prepare("SELECT user_specialty.specialty_id, specialty.specialty
                        FROM user_specialty JOIN specialty 
                        ON user_specialty.specialty_id=specialty.id
                        WHERE user_specialty.user=?");
$query->execute(array($seeker));

//To get the specialty list
$specialty_list[$seeker] = $query->fetchAll(PDO::FETCH_ASSOC);
}

and do like this on table where I am printing the info:

<?php if(!empty($seeker_list)){ 
 foreach ($seeker_list as $key => $value){ ?>
<select>
<?php foreach ($specialty_list[ $value['account']]  as $subkey => $subvalue){ ?>
<option value="<?php  ?>"><?php echo $subvalue['specialty'];  ?></option>

Upvotes: 1

Related Questions