How to use query data for another sql query in yii2

Question

I have a query here:

  public function SearchExactJob($id) {
      $connect = \Yii::$app->db;

      $query = $connect->createCommand('
           SELECT job_name FROM job_questions WHERE job_id=:id')
           ->bindValue(':id':$id)
           ->queryAll();

}

I want to use the job_name for another query: PS: I just wrote this down on a paper in case i'll be able to find out how to get this data

  $anotherquery = $connect->createCommand(
                  'SELECT * FROM company_questions
                   WHERE company_question = [the job_name I want to get from the `$query`])

Also if I will successfully get the $anotherquery work, I want to get the data and pass it on the view assigned to this.

But then, im asking for help because I just recently used Yii2. Please help me.

Answer 1

get Jobs Name

public function SearchExactJob($id) {
  $connect = \Yii::$app->db;

  $query = $connect->createCommand('
       SELECT job_name FROM job_questions WHERE job_id=:id')
       ->bindValue(':id':$id)
       ->asArray()
       ->queryAll();
}

Above code will return two-dimesional array. Convert two dimensional array to one-dimensional array

 $jobs = array_map('current',$query);

Finally you can use where in clause to search for jobs.

$anotherquery = $connect->createCommand(
              'SELECT * FROM company_questions
               WHERE IN $jobs)

I highly recommend you to use active record when querying data, less syntax error and more easily readable.

Answer 2

I've solved this one.

     $query=$connect->createCommand('
       SELECT job_name FROM job_questions WHERE job_id=:id')
       ->bindValue(':id':$id)
       ->queryOne(\PDO::FETCH_OBJ);

add the data on a variable: ex. $job_name

$job_name = $query->job_name;