4 Replies - 2684 Views - Last Post: 10 April 2012 - 04:08 AM Rate Topic: -----

#1 OpenBSD  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 58
  • Joined: 15-August 10

php retrieve multiple data from mysql

Posted 08 April 2012 - 11:44 AM

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


Is This A Good Question/Topic? 0
  • +

Replies To: php retrieve multiple data from mysql

#2 Martyr2  Icon User is online

  • Programming Theoretician
  • member icon

Reputation: 4337
  • View blog
  • Posts: 12,137
  • Joined: 18-April 07

Re: php retrieve multiple data from mysql

Posted 08 April 2012 - 12:00 PM

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

Was This Post Helpful? 1
  • +
  • -

#3 OpenBSD  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 58
  • Joined: 15-August 10

Re: php retrieve multiple data from mysql

Posted 08 April 2012 - 12:45 PM

View PostMartyr2, 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 ?!
Was This Post Helpful? 0
  • +
  • -

#4 Martyr2  Icon User is online

  • Programming Theoretician
  • member icon

Reputation: 4337
  • View blog
  • Posts: 12,137
  • Joined: 18-April 07

Re: php retrieve multiple data from mysql

Posted 08 April 2012 - 02:14 PM

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.

:)
Was This Post Helpful? 1
  • +
  • -

#5 OpenBSD  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 58
  • Joined: 15-August 10

Re: php retrieve multiple data from mysql

Posted 10 April 2012 - 04:08 AM

Thanks. I Fixed this with mysql_fetch_array query and replace with name. your code so cleaned
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1