How to store each line of a multi-line text into mysql individually

Question

I am a beginner to php and mysql ,so bear with me if my question sounds dumb.

I have a multi-line text and I need to store each line of that into a row of my table in mysql. I should mention that each line's string is separated by comma (",") as delimiter.

Suppose that I have the following text:

title1,name1,url1,number1

title2,name2,url2,number2

title3,name3,url3,number3

I am not sure if I need to use

$lines=explode("\n",$mytext)

to explode the multi-line text because it would give me an array of lines and I do not know how to split each line to separate string variables and insert them into a table row.

Any help would be much appreciated.

Explode into lines first. And then explode into values. And then insert into each field of a table row. — Ravinder Reddy, May 05 '14 at 06:15
can I make a multi-dimensional array of string using explode? Let's say the first element would indicate the line and second would indicate the word? — Omid, May 05 '14 at 06:18
It is not a csv data but I could save it as a csv if that would help. — Omid, May 05 '14 at 06:19
@Jenz I am not getting any error. I do not know how to go further. exploding the lines is the farthest I have gone. I do not know where to go from there. — Omid, May 05 '14 at 06:21

Bryan P · Accepted Answer · 2014-05-05T08:06:01.090

This should do it, not sure about the mysql part, I just looked it up via w3schools.com

$con = mysqli_connect('host','username','password','dbname');

// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
} else {

    // Slice the data by lines first.
    $linesOfData = explode('\n',$myText); // becomes an array of data

    // $linesOfData = array_filter($linesOfData); // uncomment this if you feel like the data will come with an empty line, removes any empty values in the array.

    // loop through the sliced data
    foreach($linesOfData as $lineOfData) {
        $arrayOfValues = explode(',',$lineOfData);

        // loop through the array of values
        foreach($arrayOfValues as $value) {
            /*  condition_statement ? value_if_yes : value_if_false
                This is equivalent to 
                if(!empty($value[0]){$title=$value[0]}else{$title=null}
                to check if the array has that index, to avoid error
            */
            $title  = !isset($value[0]) ? trim($value[0]) : null;
            $name   = !isset($value[1]) ? trim($value[1]) : null;
            $url    = !isset($value[2]) ? trim($value[2]) : null;
            $number = !isset($value[3]) ? trim($value[3]) : null;

            // insert to database, not recommended for commercial projects, this is vulnerable to sql injections, you should learn about prepared statements.
            mysqli_query($con,"INSERT INTO tablename (title, name, url, number) VALUES ('$title','$name','$url','$number')");
        }
    }

    // close the connection immediately after using it
    mysqli_close($con);
}

I also added mysql commands just in case you also don't know yet — Bryan P, May 05 '14 at 06:41
Add a `trim()` for each value to make sure you cover cases like: **"title1, name1 ,url1 , number1"**. Also a mapping of indexes in the text and the used variables would be useful, to easier maintain an extend the script. Like `$mapping = array(0 => 'title', 1 => 'name', ...)`. Then you loop through the this array, check for set index and set `$data['title'] = $value[0]`. If not found, simply set it to `NULL`. — func0der, May 05 '14 at 08:02
About the mapping, yes that is a good practice, paired with implode() in the VALUES part, but it wasn't asked by the op, so it shouldn't be added anymore to not make things difficult for the beginner OP to understand. I added the trim you suggested though. — Bryan P, May 05 '14 at 08:14
Yeah, but as you said: He/she is a beginner, so he/she should get in touch with best practice as soon as possible, before bad practice is burning inside his/her head ^^ At least one should know about these things. — func0der, May 05 '14 at 12:27

How to store each line of a multi-line text into mysql individually

1 Answers1