I want to add multiple values to the mysql data base

Question

Database

table: "country" 
coloums:"id_country,country_name"

i have only one text feild:

Country: <input type=text name="country_name" >
$conn->query("insert into country");

i want to fill this feild with

"india,australia,canada,etc."

from the above line the (,) has to treat like new value.! so it has to store in db with id_country increment..

Why would you want to do that? It'll make your data hard to use later and is far from good database design. — Jay Blanchard, Sep 06 '16 at 12:09
can you suggest any best way to add multiple values..@JayBlanchard — Mr world wide, Sep 06 '16 at 12:11
why it is bad @JayBlanchard . he said `from the above line the (,) has to treat like new value.!` so it will become like 1,India 2,Australia etc — Alive to die - Anant, Sep 06 '16 at 12:11
This may help you http://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql — Tedo G., Sep 06 '16 at 12:12
Are you exploding this Value afterwards? Then you should Implode it with PHP. — Pascal Boschma, Sep 06 '16 at 12:12
I think the question wasn't explained properly; that's why there's confusion @Anant — Funk Forty Niner, Sep 06 '16 at 12:15

Alive to die - Anant · Accepted Answer · 2016-09-06T19:21:25.427

You can do it like below:-

<?php

$data = "india,australia,canada";
$exploded_data = explode(',',$data);
$data_to_insert = "('".implode("','",$exploded_data)."')";

echo $data_to_insert;

Output:-

https://eval.in/636118

and

https://eval.in/636131

Note:- Your id_country column must be Auto-incremented primary key.

If so then just one query:-

"INSERT INTO country (country_name) VALUES ".$data_to_insert;

Or

"INSERT INTO country (country_name) VALUES $data_to_insert";

will do every-thing

Better to do this with prepared statements.

Iain · Answer 2 · 2017-04-26T14:50:43.150

2

Best would be to use PHP's explode() function on the input text, using ", " as a delimiter, and then adding each country in the resulting array to your database via a foreach loop.

More info: http://php.net/manual/en/function.explode.php

edited Apr 26 '17 at 14:50

answered Sep 06 '16 at 12:12

Iain

387
2
12

score 2 · Answer 3 · edited Sep 06 '16 at 12:35

2

I'm not sure if I understand you, but I can explain you, EXPLODE. It'll return an array of strings.

$values = "value1, value2";
$explode = explode(",", $values);

You can continue with the following after that;

$explode[0];
$explode[1];

edited Sep 06 '16 at 12:35

Slatyoo

130
15

answered Sep 06 '16 at 12:16

Pascal Boschma

187
1
14

upto explode ok how to store data..? – Mr world wide Sep 06 '16 at 12:18
When you want to store your data in 1 Value, how do you let the user seperate these values? With a space or a `,` ? You could Implode it then – Pascal Boschma Sep 06 '16 at 12:20

score 2 · Answer 4 · answered Sep 06 '16 at 12:22

2

$countries = ['india','australia','canada']

foreach ($countries as $country) {
     // Run/build you sql query
}

SQL example query

INSERT INTO `country` (`country_name`)
VALUES ('india');

answered Sep 06 '16 at 12:22

Spholt

3,724
1
18
29

score 2 · Answer 5 · answered Sep 06 '16 at 12:49

To process the posted string firstly ensure the variable is actually set and passes rudimentary tests and use explode to generate an array containing the country names. Using mysqli and prepared statements allows you to construct the sql statement once, bind the necessary parameter(s) and variables once but execute multiple times very quickly.

$dbhost =   'localhost';
$dbuser =   'xxx'; 
$dbpwd  =   'xxx';
$dbname =   'xxx';
$db =   new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );

/* make sure the posted variable isn't empty */
$countries=!empty( $_POST['country_name'] ) && stristr( $_POST['country_name'], ',' ) ? $_POST['country_name'] : false;
if( $countries ){

    /* explode the string to create the array */
    $countries=explode(',',$countries);

    /* construct sql statement for use as a prepared statement */
    $sql='insert into `country` set `country_name`=?';

    /* prepare the statement object */
    $stmt=$db->prepare( $sql );

    /* bind the param to the variable */
    $stmt->bind_param( 's', $country );

    /* iterate through all countries from POSTed string and execute the statement */
    foreach( $countries as $country ){
        $stmt->execute();
    }
}

$stmt->close();
$db->close();

I want to add multiple values to the mysql data base

5 Answers5