creating sql statement from an exploded array

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
GuitarCoder
Forum Newbie
Posts: 4
Joined: Tue Aug 12, 2008 5:27 pm

creating sql statement from an exploded array

Post by GuitarCoder »

tblFruits

Fruits (column)
-------
John 1, 3, 4
Mary 2,3


tblFruitDetail

id Description
---------------
1 Apple
2 Banana
3 Guava
4 Grapes


ok... i can explode the value from tblFruits

Code: Select all

 
$someWords = $return[Fruits];  (generated SQL statement from tblFruits) 
 
$wordChunks = explode(", ", $someWords);
for($i = 0; $i < count($wordChunks); $i++)
 {
    echo "$wordChunks[$i] <br />";   
 }  
 
But how can I generate an SQL statement to pass it on a string variable that would display the Description:

Ex:

John's Basket -
Apple
Banana
Grapes


Mary's Basket -
Banana
Guava


I would appreciate any response. Thanks!
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: creating sql statement from an exploded array

Post by jayshields »

You'd be better of rethinking your database design. You want a many-to-many relationship. Something like this:
users table - id, name
fruits table - id, name
users/fruits - id, id

There probably is a way to explode values in a column in SQL and then to JOIN them to the names in the fruits table, but it's just making things more complicated.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: creating sql statement from an exploded array

Post by califdon »

jayshields wrote:You'd be better of rethinking your database design. You want a many-to-many relationship. Something like this:
users table - id, name
fruits table - id, name
users/fruits - id, id

There probably is a way to explode values in a column in SQL and then to JOIN them to the names in the fruits table, but it's just making things more complicated.
It's more than that, it's just plain wrong. If you need a many-to-many relationship, the only valid way to do it is with a third table, as jayshields showed.
GuitarCoder
Forum Newbie
Posts: 4
Joined: Tue Aug 12, 2008 5:27 pm

Re: creating sql statement from an exploded array

Post by GuitarCoder »

got it

Code: Select all

$conn = odbc_connect("DSNNAME","USER","PASSWORD"); 
 $someWords = $return[Fruits];  (generated SQL statement from tblFruits)  
  
$wordChunks = explode(", ", $someWords); 
for($i = 0; $i < count($wordChunks); $i++) 
 { 
    $q = odbc_exec($conn,"select description from tblFruitDetail where id=" . $wordChunks[$i]); 
    $result = odbc_fetch_array($q); 
    echo "$wordChunks[$i] (".$result[Description].")<br />";    
 }  
Post Reply