mySQL- SELECT needs a value comming from an array

[phpscripter]

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:

|---------------|------------------|---------------------|
|     ID        |    strFilename   |     strFields       |
|---------------|------------------|---------------------|
|     1         |    shop_pref     |   €|16|german|6     | 

The query:

SELECT strFields from ".VIEW_PREFS_TABLE." where strFilename = 'shop_pref'
.... 

I get the value I need with this chunk of code:

$fieldnames = explode("|",$DB_WE->f("strFields"));
$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:

 $sqlOo = "SELECT ".OBJECT_X_TABLE."$classid.input_shoptitle as obTitle,";
$sqlOo .= ".OBJECT_X_TABLE."$classid.OF_ID as obID,".SHOP_TABLE.".IntArticleID as aID,";
$sqlOo .= ".SHOP_TABLE.".IntOrderID as oID, DATE_FORMAT(".SHOP_TABLE.".DateOrder, '%d.%m.%Y - %H:%m:%s') as procd,";
$sqlOo .= ".SHOP_TABLE.".Price * ".SHOP_TABLE.".IntQuantity as sumt, DATE_FORMAT(".SHOP_TABLE.".DatePayment, '%d.%m.%Y') as dPay";
$sqlOo .= " FROM ".OBJECT_X_TABLE."$classid,".SHOP_TABLE." "; 
   $sqlOo .="WHERE ".OBJECT_X_TABLE."$classid.OF_ID = ".SHOP_TABLE.".IntArticleID";
   $sqlOo .= " AND YEAR(".SHOP_TABLE.".DateOrder) = $optYear ORDER BY ".SHOP_TABLE.".DateOrder"; 

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:

|---------------|------------------|---------------------|
|     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:

$fe = explode(",",$fieldname[3]);
      foreach($fe as $key => $val)
    {
        echo $key." is ".$val."<br />\n";
       
    }
        echo $fe[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?

[wahsaga]

Re: mySQL- SELECT needs a value comming from an array

please insert some line breaks in your code, to avoid scrollbars going from here to north pole.
thank you.

[phpscripter]

sorry for that, yeah I've allreay saw that....

so reload and thanks.

[wahsaga]

Re: mySQL- SELECT needs a value comming from an array

uhm, and what kind of DBMS are we talking about ...?

and what do you use your $classid for - only in a WHERE clause, WHERE field = $classid ...?

then (in MySQL) the IN-operator could do, http://dev.mysql.com/doc/mysql/en/co...operators.html

but

$sqlOo .= " FROM ".OBJECT_X_TABLE."$classid,".SHOP_TABLE." "; 

looks like you also want to use your $classid as part of some table name (?)
so if there's more then one generated table name, and you'd put them all into the FROM part, that would result in a Cartesian product, not very good regarding performance ...

[phpscripter]

hi,
yeah I thought about that also.
The thing is, that the script doesn't know the $classid jet.

A user can create a class for a defined purpose. As a result an object-ID will be created to identfy if the class would be used for what ever..

In that connection a new table for that purpose will be created with a name pattern like this:

tblObject_ (a following a number).

This number is the object-ID wich identifies the mySQL-table.
Thus the pattern should do something like
tblObject_(+ the number comming from my problem script).

If for exampel the $flednames[3] value is 3, the
new table would be tblObject_3.

The convention 'OBJECT_X_TABLE."$classid' is only a
php constant + number

I've also scrutinized the IN-operator. But I think I rather need some kind of function.

With in_array I could check, if a number is in the result set. But the result of in_array is only boolean.

Hmm, some further thoughts?

[Wurzel]

you have only to check whether it is an array or not and, if it is, that the array isn't empty. if it is an array use implode() to put the values to your sql statement. otherwise leave this part empty.

eg

$sql = '.... WHERE '.(is_array($arr) && !empty($arr) ? 'col IN ('.implode(', ', $arr).')' : '1').' AND/OR ....'; 

check it out with some sample vars.

[asp2php]

I don't know what you're looking for? see:

with the 1st use of explode function you'll get either $fieldnames[3] as a single number or $fieldnames[3] as a string, which contains coma separated numbers. In both cases you can use IN in the WHERE-Clause, e.g.

... WHERE ... IN (6)

or

... WHERE ... IN (6,7,8,9)

right?

It's very terrible with the classid in the tablename, is it possible to change your database structure?

[phpscripter]

thank you for your thoughts. +

Think it might be even more difficult, if you put in mind, that I like to have the result-set of all ID's in $fielnames[]; wich could be more than 1,2 or 3...

Think I cannot do this. The only way might be using more than one SELECTS with the values comming from the $fieldnames[]-array..

like...

SELECT ".OBJECT_X_TABLE."$fieldnames [0].input_shoptitle as obTitle......
SELECT ".OBJECT_X_TABLE."$fieldnames [1].input_shoptitle as obTitle.....
SELECT ".OBJECT_X_TABLE."$fieldnames [2].input_shoptitle as obTitle.....

etc... 

and maybe this is the wrong way anyway ;(...

puh..

Well...