Replace all matched strings from matched position up to the first ' symbol

Question

I have a query string that contains $lang keyword, now I want to replace everything before $lang until first occurrence of ' symbol with content of another array using loop.

Below is my query string:

select 
COUNT(t1.id) as total, 
COUNT(t1.id) as 'total_$lang', 
CASE t2.`precence`
        WHEN '0' THEN 'Absent'
        WHEN '1' THEN 'Present'
        ELSE 'Unknown'
    END AS series,
CONCAT(DAYNAME(t1.date),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS ticks,
CONCAT(DATE_FORMAT(t1.date, '%a'),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS abv,
    courses.no AS label,
CASE t2.`precence`
        WHEN '0' THEN 'absent_$lang'
        WHEN '1' THEN 'present_$lang'
        ELSE 'unknown_$lang'
    END AS 'attendance_state_$lang',
CONCAT(DAYNAME(t1.date),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS 'attendance_date_$lang',
CONCAT(DATE_FORMAT(t1.date, '%a'),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS 'attendance_date_abv_$lang',
courses.no AS 'course_num_$lang'

from course_attendances as t1 left join `student_attendances` as t2 on t2.`course_attendance_id` = t1.`id`
right join courses ON courses.id = t1.course_id
where t1.`course_id` = '1' AND t1.date BETWEEN '2015-01-01' AND '2015-11-02'
group by t1.`date`,t2.`precence`

Now I want to replace with content of below array

Array
(
    [total_en] => Total
    [absent_en] => Absent
    [present_en] => Present
    [unknown_en] => Unknown
    [attendance_state_en] => Attendance State
    [attendance_date_en] => Attendance Date
    [attendance_date_abv_en] => Attendance Date (abv)
    [course_num_en] => Course Number
)

Just I need the find match case section of question, for any help thanks.

Answer 1

You can do like this

 // Your query string 
 $main_query = "SELECT 
                  COUNT(t1.id) as total, 
                  COUNT(t1.id) as 'total_$lang', 
                  CASE t2.`precence`
                    WHEN '0' THEN 'Absent'
                    WHEN '1' THEN 'Present'
                    ELSE 'Unknown'
                 END AS series,
                 CONCAT(DAYNAME(t1.date),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS ticks,
                 CONCAT(DATE_FORMAT(t1.date, '%a'),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS abv,
                 courses.no AS label,
                 CASE t2.`precence`
                   WHEN '0' THEN 'absent_$lang'
                   WHEN '1' THEN 'present_$lang'
                   ELSE 'unknown_$lang'
                 END AS 'attendance_state_$lang',
                 CONCAT(DAYNAME(t1.date),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS 'attendance_date_$lang',
                 CONCAT(DATE_FORMAT(t1.date, '%a'),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS 'attendance_date_abv_$lang',
                 courses.no AS 'course_num_$lang'

            FROM course_attendances as t1 left join `student_attendances` as t2 on t2.`course_attendance_id` = t1.`id`
            RIGHT JOIN courses ON courses.id = t1.course_id
            WHERE t1.`course_id` = '1' AND t1.date BETWEEN '2015-01-01' AND '2015-11-02'
            GROUP BY t1.`date`,t2.`precence`";

And your data sample array

$reportConfig = Array
     (
       'total_en' => 'Total',
       'absent_en' => 'Absent',
       'present_en' => 'Present',
       'unknown_en' => 'Unknown',
       'attendance_state_en' => 'Attendance State',
       'attendance_date_en' => 'Attendance Date',
       'attendance_date_abv_en' => 'Attendance Date (abv)',
       'course_num_en' => 'Course Number'
   );


 if(preg_match('/_\$lang/',$main_query)){
    foreach($reportConfig as $rep_conf_key=>$rep_conf_val){
       $rep_conf_key_temp = preg_replace('/_'.$lang.'/','_\\\$lang',$rep_conf_key);
       if(preg_match('/'.$rep_conf_key_temp.'/',$main_query)){
          $main_query = preg_replace('/'.$rep_conf_key_temp.'/',$rep_conf_val,$main_query);
       }
    }

 }
 echo '<pre/>'; print_r($main_query);

And the result

SELECT 
COUNT(t1.id) as total, 
COUNT(t1.id) as 'Total', 
CASE t2.`precence`
        WHEN '0' THEN 'Absent'
        WHEN '1' THEN 'Present'
        ELSE 'Unknown'
    END AS series,
CONCAT(DAYNAME(t1.date),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS ticks,
CONCAT(DATE_FORMAT(t1.date, '%a'),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS abv,
    courses.no AS label,
CASE t2.`precence`
        WHEN '0' THEN 'Absent'
        WHEN '1' THEN 'Present'
        ELSE 'Unknown'
    END AS 'Attendance State',
CONCAT(DAYNAME(t1.date),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS 'Attendance Date',
CONCAT(DATE_FORMAT(t1.date, '%a'),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS 'Attendance Date (abv)',
courses.no AS 'Course Number'

 FROM course_attendances as t1 left join `student_attendances` as t2 on t2.`course_attendance_id` = t1.`id`
 RIGHT JOIN courses ON courses.id = t1.course_id
 WHERE t1.`course_id` = '1' AND t1.date BETWEEN '2015-01-01' AND '2016-11-17'
 GROUP BY t1.`date`,t2.`precence`