[OpenBSD]

I insert multiple id from my checkbox to mysql database using php post form. in e.x i insert id (checkbox value table test) to mysql. no i need to any function for retrieve data from mysql and print to my page with my e.x output.(print horizontal list name of table test where data = userid)

my checkbox value ( name table is test ) :

---id----- name    ----
---1 ----- test1   ----
---2 ----- test2   ----
---3 ----- test3   ----
---4 ----- test4   ----
---5 ----- test5   ----
---6 ----- test6   ----
---7 ----- test7   ----
---8 ----- test8   ----
---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

This post has been edited by OpenBSD: 08 April 2012 - 11:45 AM

[Martyr2]

Ummm yeah this is why it is never advisable to store multiple values in a list like this inside a database. The problem is that for each record of 1 table is now forcing you to spawn multiple operations on another table. For example retrieving userid 2's choice is now going to cause you to make queries for 1, 4, 6, and 9. Now luckily I think an "IN CLAUSE" can save you here.

In PHP if you read the 1:4:6:9 data, explode it into an array using explode() with the ":" as the separator, you can then build a comma string (using implode() with a comma separator) and use it in an IN clause like so...

select test.name from test where test.id IN (1,4,6,9)

This should then give you all the names for those with the id in the list.

Ideal solution? No. Especially if the list gets longer. However if you stay under a list of like 10 or something it may suffice. If this list describes relationships like "friends" would be on facebook or something like that, you will need to instead restructure your database to use a "junction table".

Hope this helps!

This post has been edited by Martyr2: 08 April 2012 - 12:02 PM

[OpenBSD]

Martyr2, on 08 April 2012 - 12:00 PM, said:

Ummm yeah this is why it is never advisable to store multiple values in a list like this inside a database. The problem is that for each record of 1 table is now forcing you to spawn multiple operations on another table. For example retrieving userid 2's choice is now going to cause you to make queries for 1, 4, 6, and 9. Now luckily I think an "IN CLAUSE" can save you here.

In PHP if you read the 1:4:6:9 data, explode it into an array using explode() with the ":" as the separator, you can then build a comma string (using implode() with a comma separator) and use it in an IN clause like so...

select test.name from test where test.id IN (1,4,6,9)

This should then give you all the names for those with the id in the list.

Ideal solution? No. Especially if the list gets longer. However if you stay under a list of like 10 or something it may suffice. If this list describes relationships like "friends" would be on facebook or something like that, you will need to instead restructure your database to use a "junction table".

Hope this helps!

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 ?!

[Martyr2]

Well I told you how to do this. Select the record you want, pull out your string of values, use explode() to break the ids apart, assemble them together again with commas (using the implode() function), then put it into a new query string.

$sql = "select data from usertest where userid = 2";

$results = mysql_query($sql);

if ($row = mysql_fetch_array($results)) {
   // Explode into ids and implode into comma list
   // or could string replace as well. Up to you.
   $ids = implode(",", explode(":", $row["data"]));

   $new_sql = "select test.name from test where test.id in ($ids)";

   // Run command and process the results, which will be a list of names
}

Code above is just meant to give you an idea of one way how you could do this. Play with it and you should get something along the lines of what you need.

[OpenBSD]

Thanks. I Fixed this with mysql_fetch_array query and replace with name. your code so cleaned