How to create sql file and write query dynamically in sql file using PHP?

Question

I am creating .sql file dynamically and write create table query, but the query print in one line, but i need it something like

        $sql = "CREATE TABLE `tbl_demo` (
        `system_id` varchar(200) NOT NULL,
        `last_name` varchar(200) NOT NULL,
        `first_name` varchar(200) NOT NULL,
        `full_name` varchar(200) NOT NULL,
        `phone` varchar(200) NOT NULL,
        `ext` varchar(200) NOT NULL,
        `email` varchar(200) NOT NULL,
        `dept` varchar(200) NOT NULL,
        `site` varchar(200) NOT NULL,
        `room` varchar(200) NOT NULL,
        `job_title` varchar(200) NOT NULL,
        `image` varchar(200) NOT NULL,
        `url` varchar(200) NOT NULL,
        `active` varchar(200) NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

Here is my code.

        <?php

        $content = file_get_contents('demo.csv');
        $table = 'demo';

        $exp = explode("\n", $content);

        echo '<pre>';

        $headers = explode(',', strtolower(str_replace(' ', '_', $exp[0])));

        $table_columns = array();
        foreach ($headers as $header) {
            $table_columns[] = '`' . $header . '`' . ' VARCHAR(255)';
        }
        $sql = 'CREATE TABLE ' . '`' . $table . '`' .  '(' . implode(',', $table_columns) . ') ENGINE=InnoDB DEFAULT CHARSET=utf8';
        print_r($sql);


        $myfile = fopen("temp/demo.sql", "a+") or die("Unable to open file!");
        fwrite($myfile, $sql);
        fclose($myfile);

Any solution Appreciated!

Have you tried anything? Seems that it's enough to add newline characters where needed — barbsan
– barbsan, Commented Jun 18, 2019 at 10:33
Yes, i tried \n but it doesn't work, now i know where was the error — user11636668
– user11636668, Commented Jun 18, 2019 at 10:33
If you get some error it's good to include it in your question — barbsan
– barbsan, Commented Jun 18, 2019 at 10:34

Jacques Barker · Accepted Answer · 2019-06-18 10:10:32Z

1

Change the single quotes (') to double quotes (") in your php and at the end of the line put in backslash n (\n). i.e.

$VarToFile = "This is my line\n";
$VarToFile .= "This is a new line\n";

Or try change

implode(',', $table_columns)

to

implode(",\n", $table_columns)

answered Jun 18, 2019 at 10:10

Jacques Barker

961 gold badge1 silver badge6 bronze badges

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

Collectives™ on Stack Overflow

How to create sql file and write query dynamically in sql file using PHP?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related