Page 1 of 1

creating sql statement from an exploded array

Posted: Wed Aug 27, 2008 4:48 pm
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!

Re: creating sql statement from an exploded array

Posted: Wed Aug 27, 2008 5:27 pm
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.

Re: creating sql statement from an exploded array

Posted: Wed Aug 27, 2008 7:38 pm
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.

Re: creating sql statement from an exploded array

Posted: Fri Aug 29, 2008 5:35 pm
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 />";    
 }