Vfrdtyky

I have a php query working with no problems. However, prior to exporting to a csv file, I need to use a regular expressions to customize the look of a few variables. I have this working as well.

$form_field_number_UPD = preg_replace('(W{3}.*)', 'n', $form_field_number);

My problem is that I need to export these customized variables, instead of the raw query results.

Is this possible?

Also,

I have been trying to use a regular expression in mysql 5.7 but have had no luck.
Is there a preg_replace equivalent in MySQL 5.7?
I haven't found one yet. There seems to be one in MySQL 7 but I cannot upgrade yet.

my php that I would like to find an mysql equivalent for:

$form_field_number_UPD = preg_replace('(W{3}.*)', 'n', $form_field_number);

I tried below but it does not work.

REPLACE(fd.form_field_number, '(W{3}.*)', 'n', 1, 5) AS New_Field 

Thanks for any assistance, even if it is "look over here..."

Revised::

I have a checkbox group column where the results are:

result 1|~|result 1

result 2|~|result 2

result 3|~|result 3

The regex removes the |~| and everything to the right of it, so the results are:

result 1

result 2

result 3

I can do this in php but need to get these results into a downloadable csv file.

I would suggest getting an array of the data you'd like to export into php using a MySQL SELECT query and using your php preg_replace() statement since you know that works. Exporting the replaced data to a csv file from the php array is pretty straightforward from there.

This link has answers with code samples of how this can be done: Export to CSV via PHP

If your column is always demarcated with the symbol |~|, you don't need a regular expression; look for occurrences of this exact sequence, and use SUBSTR to chop off the rest of your string:

SELECT SUBSTR(fd.form_field_number, 1, LOCATE('|~|', fd.form_field_number)-1) AS trimmed

FROM ...

Well, I am half way there. I did need to use php and I have the php working on the display end. What I need to do now is turn the results into an array and pass that to the csv export.

I need to pass the php results from the foreach and not from the query result because I need to use the regular expression to adjust on column of results.

I though I could use the $res_UPD or $list array but no success there.

Below is what I have used so far. I am confused about where to put the function for the output as well as generate the csv file. Above or below the foreach?

Any help is appreciated and if to let me know that I am way off base and look...

    header("Content-Type: text/csv");

    header("Content-Disposition: attachment; filename=$q1b.csv");



    switch( $q1b )

    {

        case "research-request":





                $query = "SELECT

                            DATE_FORMAT(FROM_UNIXTIME(fd.entry_date), '%Y-%m-%d') AS date_formatted,

                            fd.entry_id,

                            fd.form_field_1 AS FIRST_NAME,

                            fd.form_field_2 AS LAST_NAME,

                            fd.form_field_3 AS EMAIL,

                            fd.form_field_957 AS checkbox_field, /* checkbox group */

                        FROM db_table AS fd

                        WHERE 1  ORDER BY date_formatted DESC";

            break;



    }



    $res = $this->EE->db->query($query);



        if ($res->num_rows() > 0)

        {



            foreach($res->result_array() as $row)

            {

                $date_formatted = $row['date_formatted'];

                $FIRST_NAME = $row['FIRST_NAME'];

                $LAST_NAME = $row['LAST_NAME'];

                $EMAIL = $row['EMAIL'];

                $checkbox_field = $row['checkbox_field'];

                $checkbox_field_UPD = preg_replace('(W{3}.*)', 'n', $checkbox_field);



                $res_UPD = 

                $date_formatted.",".$FIRST_NAME.",".$LAST_NAME.",".$EMAIL.",".$checkbox_field_UPD;



                $list = array($date_formatted,$FIRST_NAME,$LAST_NAME,$EMAIL,$checkbox_field_UPD);



            }



        }