Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
3  
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 Apr 8 '12 at 18:09
I had no choice! – Gcoder Apr 8 '12 at 18:12
1  
Why did you have no choice? I suggest you start by reading this article on the first normal form – nnichols Apr 8 '12 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 Apr 8 '12 at 18:34
2  
@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 Apr 8 '12 at 19:07
show 4 more comments

1 Answer

up vote 1 down vote accepted

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";
}
share|improve this answer
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 ?! – Gcoder Apr 8 '12 at 19:55
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. – nnichols Apr 8 '12 at 23:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.