php retrieve multiple data from mysql

Question

I insert multiple id from my checkbox to MySQL database using php post form. In example I insert id (checkbox value table test) to mysql. Now I need a function to retrieve data from MySQL and print to my page with my example output (print horizontal list name of table test where data = userid)

My checkbox value (table name is test):

 id | name
----+-------
  1 | test1
  2 | test2
  3 | test3
  4 | test4
  5 | test5
  6 | test6
  7 | test7
  9 | test9

MySQL data insert (name of table usertest):

 id | data    | userid
----+---------+--------
  1 | 1:4:6:9 | 2
  2 | 1:2:3:4 | 5
  3 | 1:2     | 7

Example outout :( print horizontal list name of table test where data = userid )

user id 2 choise : test1 - test4 - test6 - test9

Thanks

You need to normalize your database design. Storing multiple values in a single field is 99.999% of the time a BAD design. And this isn't one of the 0.001% times. — Marc B
– Marc B, Commented Apr 8, 2012 at 18:09
Why did you have no choice? I suggest you start by reading this article on the first normal form — user1191247
– user1191247, Commented Apr 8, 2012 at 18:28
my checkbox value is dynamic ( add new, Edit, Delete form admin ) ! i dont create manual row 1,2,3,4 ..... for dynamic value Is the only and best way — Gcoder
– Gcoder, Commented Apr 8, 2012 at 18:34
It is neither the only nor the best way as explained by @MarcB. Read the article linked to in my previous comment. — user1191247
– user1191247, Commented Apr 8, 2012 at 18:39
@gcoder: you do have a choice. Normalizing your database makes a LOT of standard DB operations trivial. Your method leads to nothing but trouble/hair tairing. There's enough bald people in the world as is. Don't join their ranks. — Marc B
– Marc B, Commented Apr 8, 2012 at 19:07

user1191247 · Accepted Answer · 2012-04-08 21:27:09Z

2

Assuming your usertest table has only the three columns listed in your example you should replace it with the following -

CREATE TABLE usertest (
    data INTEGER NOT NULL,
    userid INTEGER NOT NULL,
    PRIMARY KEY (data, userid)
);

Then your data will look like -

+------+--------+
| data | userid |
+------+--------+
|  1   |   2    |
|  4   |   2    |
|  6   |   2    |
|  9   |   2    |
|  1   |   5    |
|  2   |   5    |
|  3   |   5    |
|  4   |   5    |
|  1   |   7    |
|  2   |   7    |
+------+--------+

Querying this data then becomes trivial -

SELECT usertest.userid, GROUP_CONCAT(test.name SEPARATOR ' - ')
FROM usertest
INNER JOIN test
    ON usertest.data = test.id
GROUP BY usertest.userid

You can read more about GROUP_CONCAT here

You could use a PHP solution and store the possible checkbox values in an array indexed by their ids. Something like -

<?php

$db = new PDO('mysql:dbname=test;host=127.0.0.1', 'user', 'pass');
$sql = 'SELECT id, name FROM test';

$stmt = $db->prepare($sql);
$stmt->execute();

$array = array();

while ($row = $stmt->fetchObject()) {
    $array[$row->id] = $row->name;
}

$sql = 'SELECT userid, data FROM usertest';

$stmt = $db->prepare($sql);
$stmt->execute();

while ($row = $stmt->fetchObject()) {
    $data = explode(':', $row->data);
    foreach($data as $key => $val) {
        $data[$key] = $array[$val];
    }
    print "user id {$row->userid} choise : " . implode(' - ', $data) . "<br/>\n";
}

edited Apr 8, 2012 at 21:27

answered Apr 8, 2012 at 18:49

user1191247

13k2 gold badges25 silver badges33 bronze badges

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

2 Comments

Gcoder Over a year ago

I totally agree, Junction table is perfect. but I have editing any old script and I have no chance to change. so I am forced to work with the same method. can you help me ?!

user1191247 Over a year ago

There is no way to join to the individual values in a multi value field AFAIK. You could do it using a stored procedure and looping over the values in the mv field. You might as well do it in PHP and use the poor performance as part of the justification for normalising your data structures.

Collectives™ on Stack Overflow

php retrieve multiple data from mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related