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

Question

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'];
    }  
    }

$data[$value['id_user_specialty']][] = $value['specialty'];? Then you need to loop through each user_id associative array to print out your select elements? — Darren
– Darren, Commented Apr 30, 2015 at 3:16
do take note that your second query is wrapped in single quotes which means variables in there aren't interpolated, but thats not the case there. its prepared statements, and thus variables need to be bound, you're defeating its purpose. — Kevin
– Kevin, Commented Apr 30, 2015 at 3:21
you might want to consider rewriting the whole query and putting them into one instead, this will be painfully slow since you have another query inside the fetching loop — Kevin
– Kevin, Commented Apr 30, 2015 at 3:23
so, both loops are ok? I tried your suggestion before, however when echo the value I have no results even my query is working in the SQL console. Sorry I have not read your comments before I wrote the message above. I am going to read carefully — Sora
– Sora, Commented Apr 30, 2015 at 3:24
@Ghost yes it is really slow!...so you mean to do only one loop and make only one query? Sorry I do not understand. I have two queries because one is to get the key of the user. I do not know how to get everything at the same time. — Sora
– Sora, Commented Apr 30, 2015 at 3:28

Sora · Accepted Answer · 2015-05-07 04:42:09Z

1

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>

answered May 7, 2015 at 4:42

Sora

951 silver badge10 bronze badges

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

1 Comment

Sora Over a year ago

the select tag close is missing, tried to fix but do not know how, sorry.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related