mySQL- SELECT needs a value comming from an 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
phpForX
Forum Newbie
Posts: 10
Joined: Fri Jun 17, 2005 5:51 am

mySQL- SELECT needs a value comming from an array

Post by phpForX »

Hi there,

lets say I have a working script wich gets a value form a mysql table and uses this value in another SELECT. Besides the value comes from a $_REQUEST (form).

The table strFields:

Code: Select all

|---------------|------------------|---------------------|
|     ID        |    strFilename   |     strFields       |
|---------------|------------------|---------------------|
|     1         |    shop_pref     |   €|16|german|6     |
The query:

Code: Select all

SELECT strFields from &quote;.VIEW_PREFS_TABLE.&quote; where strFilename = 'shop_pref'
....
I get the value I need with this chunk of code:

Code: Select all

$fieldnames = explode(&quote;|&quote;,$DB_WE->f(&quote;strFields&quote;));
$classid= $fieldnamesї3];
As you can see, in $classid now is the value '6'

Only for the record:
I need to use that value in another SELECT wich is here:

Code: Select all

$sqlOo = &quote;SELECT &quote;.OBJECT_X_TABLE.&quote;$classid.input_shoptitle as obTitle,&quote;.OBJECT_X_TABLE.&quote;$classid.OF_ID as obID,&quote;.SHOP_TABLE.&quote;.IntArticleID as aID,&quote;.SHOP_TABLE.&quote;.IntOrderID as oID, DATE_FORMAT(&quote;.SHOP_TABLE.&quote;.DateOrder, '%d.%m.%Y - %H:%m:%s') as procd,&quote;.SHOP_TABLE.&quote;.Price * &quote;.SHOP_TABLE.&quote;.IntQuantity as sumt, DATE_FORMAT(&quote;.SHOP_TABLE.&quote;.DatePayment, '%d.%m.%Y') as dPay FROM &quote;.OBJECT_X_TABLE.&quote;$classid,&quote;.SHOP_TABLE.&quote; &quote;; 
   $sqlOo .=&quote;WHERE &quote;.OBJECT_X_TABLE.&quote;$classid.OF_ID = &quote;.SHOP_TABLE.&quote;.IntArticleID&quote;;
   $sqlOo .= &quote; AND YEAR(&quote;.SHOP_TABLE.&quote;.DateOrder) = $optYear ORDER BY &quote;.SHOP_TABLE.&quote;.DateOrder&quote;;
So far everything is working properly. But here's my problem:

What if the $fieldnames[3] itself is an array. Lets say, somebody insert a comma-separated list in his form and the mysql-table would look like this:

Code: Select all

|---------------|------------------|---------------------|
|     ID        |    strFilename   |     strFields       |
|---------------|------------------|---------------------|
|     1         |    shop_pref     | €|16|german|6,7,8,9 |
With this chunk I could get the values from that list into a new array:

Code: Select all

$fe = explode(&quote;,&quote;,$fieldnameї3]);
      foreach($fe as $key => $val)
    {
        echo $key.&quote; is &quote;.$val.&quote;<br />\n&quote;;
       
    }
        echo $fe&#1111;2];
the echo outputs this, wich is correct.

0 is 6
1 is 7
2 is 8
3 is 9
8

So up to here I have the values in my array. But still I need to use any of that figures in my SELECT. How could this be done?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

The easiest is

Code: Select all

WHERE columnvalue IS IN ($fieldname&#1111;3])
It is the same as (using OR)

Code: Select all

WHERE columnvalue=0 OR columnvalue=3 ...
Btw, search this forum for a tutorial on database normalization... For example at http://www.thedailywtf.com/forums/39000/ShowPost.aspx they don't really appreciate your db design..
phpForX
Forum Newbie
Posts: 10
Joined: Fri Jun 17, 2005 5:51 am

Post by phpForX »

thank you very much..

It's not that easy though.

I'd like to have all( not only any) figures within the result-set in my query..
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

phpForX wrote: I'd like to have all( not only any) figures within the result-set in my query..
Meaby it's me... But i don't a clue what you are trying to say..
Post Reply