inserting serialized array in mysql

Question

I am trying to insert a serialized array into mysql without column names, is it possible?

I plan to use this in a function with a dynamic number of textboxes so I will just call the function and pass the tablename and insert into the database in order.

Here is the code I currently have:

<form action = "" method = "POST">

    Username: <input required type = "text" name = "register[]" / ><br />
    Password: <input required type = "text" name = "register[]" / ><br />
    Usertype: <input required type = "text" name = "register[]"  / ><br />
    Status:   <input required type = "text" name = "register[]" / ><br />
    <input  type = "submit" name = "add" value = "add" / >

</form>

if(isset($_POST['add']))
{

    $reg = serialize($_POST['register']); //takes the data from a post operation...
    $register = mysql_real_escape_string($reg);
    $query = mysql_query("INSERT INTO user VALUES('$register')");    
}

working queries:

$query = mysql_query("INSERT INTO users (username,password,usertype,status) VALUES('$register','2','3','4')");

output:

a:4:{i:0;s:1:"1";i:1;s:1:"1";i:2;s:1:"1";i:3;s:1:"1";} 2 3 4

it is not inserting to my database ive checked the database connection and tried to insert values manually and it works fine.but when i use serialize it just doesnt work — Joe Sie
– Joe Sie, Commented Nov 21, 2014 at 7:29
@LoganWayne im trying to insert it without column name because im going to use this as a function and dynamic numbers of input so i will just call the function and pass the tablename — Joe Sie
– Joe Sie, Commented Nov 21, 2014 at 7:33

David162795 · Accepted Answer · 2014-11-21 09:00:41Z

1

You can't use PHP serialize() to conveniently insert multiple data into multiple MySQL fields. serialze() transforms complex php's type into single string. One single string. You may use it to store an array with all these values into one text field, but that is not what you wanted.

mysql_query("INSERT INTO users (username,password,usertype,status)
 VALUES('".mysql_real_escape_query($_POST['register'][0])."',
 '".mysql_real_escape_query($_POST['register'][1])."',
 '".mysql_real_escape_query($_POST['register'][2])."',
 '".mysql_real_escape_query($_POST['register'][3])."' )");

Alternatively using PDO:

$dbh = new PDO('mysql:dbname=test;host=localhost','root','pass');
$sth = $dbh->prepare('INSERT INTO users (username,password,usertype,status)
  VALUES(?,?,?,?)');
$sth->execute($_POST['register']);

edited Nov 21, 2014 at 9:00

answered Nov 21, 2014 at 8:43

David162795

1,8763 gold badges15 silver badges20 bronze badges

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

Comments

Edrich · Accepted Answer · 2014-11-21 07:32:07Z

0

You forgot to include the fieldnames of table

$query = mysql_query("INSERT INTO user(username,password,usertype,status) VALUES('$register')");

answered Nov 21, 2014 at 7:32

Edrich

2422 silver badges8 bronze badges

12 Comments

Joe Sie Over a year ago

is it possible to not have fieldnames? because im going to use this as a function and dynamic number of input so i will just call the function and pass the tablename and insert it to database in order

Edrich Over a year ago

you mean, you will include the fieldnames in the function as parameter?

Joe Sie Over a year ago

no just insert it without fieldnames just in order of the array/serial is it possible?

Edrich Over a year ago

I think it is possible but it cannot be random and make sure the array count is the same as total count of fieldnames in your table

Joe Sie Over a year ago

i tried to echo the array using for each and its 4 the same as my fieldname (not including the id/primary key) what do you mean random? randomly arrange in array?

|

Joe Sie · Accepted Answer · 2014-11-21 08:18:53Z

0

i madeit work but using a different method i used implode but its the same

$register = "'" . implode("','", $_POST['register']) . "'";
$query = mysql_query("INSERT INTO users VALUES(null,$register)");

answered Nov 21, 2014 at 8:18

Joe Sie

11 silver badge4 bronze badges

2 Comments

David162795 Over a year ago

Please, not like this, very vulnerable to mysql injection attack.

Joe Sie Over a year ago

im going to change it to mysqli and myrealescape

Collectives™ on Stack Overflow

inserting serialized array in mysql

3 Answers 3

Comments

12 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

12 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related