CODEWITHSUNDEEP
phpmysqlsql
alexma
alexma

Reputation: 27

Insert multiple enteries into one column

Is it possible to add multiple entries into one column and single entries in other columns?

For example 

$sql="INSERT INTO The_table (firstname, lastname, address) VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[address]'";

Then I have multiple checkbox entries I need to insert - age, shirt size, height in one column called about.

How would I write the $sql statement?

Upvotes: 0

Views: 1609

Answers (5)

Piyush
Piyush

Reputation: 23

Concatenate your fields using PHP concat function.

$about="$age|$shirt_size|$height";
$sql = "INSERT INTO table (firstname, lastname, address, about) VALUES ('$fname','$lname','$address', '$about')";

And to retrieve the same from the database, use explode function in PHP.

$about=explode('|',$result['about']);
$age=$about[0];
$shirt_size=$about[1];
$height=$about[2];

Upvotes: 0

nomistic
nomistic

Reputation: 2962

One possible option (other than the CONCAT mentioned), is to create composite variables in PHP.

For instance,

$about = $_POST['about1'] . ', ' . $_POST['about2'];

and then in your query you could do something like this:

$sql="INSERT INTO The_table (firstname, lastname, address) VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[address]', '$about'";

If you don't know how many different entries there will be in in $about, then you could create something in your form grab the data with a foreach statement such as

foreach ($_POST['about'] as $about) { etc.  }

and you've named your variable in the form like this.

<input type="text" name="about[]" />

if your "about variable is a textarea then this:

<textarea name='about[]'></textarea>

Upvotes: 1

Peter Bowers
Peter Bowers

Reputation: 3093

You can very easily concatenate a string in PHP which will then be put into the SQL insert query.

$about = "AGE: $_POST[age]\nSHIRT SIZE: $_POST[shirt_size]\nHEIGHT: $_POST[height]";
$sql = "INSERT INTO The_table (firstname, lastname, address, about) VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[address]', '$about')";

Of course you should be preparing your statement and using appropriate bind values instead:

$sql = "INSERT INTO The_table (firstname, lastname, address, about) VALUES (?, ?, ?, ?)";
// now prepare, bind and execute

If you don't do the prepare/bind stuff then you are asking for problems security wise. At the very least you need to apply appropriate escaping mechanisms to the user-supplied data.

Copying the example from http://php.net/manual/en/mysqli-stmt.execute.php and modifying it for you:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* Prepare an insert statement */
$query = "INSERT INTO The_table (firstname, lastname, address, about) VALUES (?,?,?,?)";
$stmt = $mysqli->prepare($query);

$stmt->bind_param("ssss", $_POST['first'], $_POST['last'], $_POST['address'], $about);

/* here's where you do your special assignments */
$about = "AGE: $_POST[age]\nSHIRT SIZE: $_POST[shirt_size]\nHEIGHT: $_POST[height]";    

/* Execute the statement */
$stmt->execute();

/* close statement */
$stmt->close();

Upvotes: 3

Shahriar Nat
Shahriar Nat

Reputation: 1

You can separating your entries by pipe or another thing.

Such as:

$sql="INSERT INTO `The_table` (`firstname`,`lastname`,`address`,`info`) VALUES ('{$_POST[firstname]}','{$_POST[lastname]}','{$_POST[address]}','Red|XXL|50')";

Upvotes: -1

CrApHeR
CrApHeR

Reputation: 2655

You can use the function CONCAT

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat

I don't have a mysql server to test it but I think this is a good approach to do that.

$sql="INSERT INTO The_table (firstname, lastname, address, about) VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[address]', CONCAT('$_POST[age]', ' ', '$_POST[shirtSize]', ' ', '$_POST[height]')";

Hope this helps

Upvotes: 1

Related Questions