Re: Selecting Rows Based on Row Values Being in Array

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Ok Jim,

This is what I have so far and I'm still working it out.

$in_list = "".join('',$someArrayList);  // do I really need to concatenate 
or separate anything here since my array values will be '7orange50lbs'? // 
this is the format I want.

$query_One = "SELECT * FROM shoe WHERE CONCAT(size,color,weight) 
IN({$in_list})";    // size, color, weight are my column names
$result = mysql_query($query_One ,$connection) or die("Query failed: ". 
mysql_error($connection));
$row = mysql_fetch_array($result);

This is the error I get back from the query:
Query failed: Unknown column '6blue40lbs' in 'where clause'    // where am I 
going wrong?
======================================================================
"Jim Lucas" <lists@xxxxxxxxx> wrote in message 
news:468895E8.6090403@xxxxxxxxxxxx
> kvigor wrote:
>> Jim,
>>
>> Please excuse the ignorance, I'm a newbie, but I'm only use to simple 
>> SELECT, INSERT statements.
>>
>>
>> Your original code: $SQL = "SELECT * FROM my_Table WHERE CONCAT(value1, 
>> value2, value3) IN ('".join("','", $list)."')
>
> This can be broken down into smaller parts so to explain by example.
>
> # This is to clean the input values for the SQL statement
> function mysql_clean($value) {
> return mysql_real_escape_string($value);
> }
>
> # Define your list of values to compare to
> $list = array(
> '6blue40lbs',
> '7orange50lbs',
> '8orange60lbs',
> '9purple70lbs',
> );
>
> # You will want to do something like this with the values of the $list
> # array just to make sure they are clean: reference the function above
> array_walk($list, 'mysql_clean');
>
> # This will return a string formated like this.
> # '6blue40lbs','7orange50lbs','8orange60lbs','9purple70lbs'
> $IN_VALUE = "'".join("','", $list)."'";
>
> $SQL = "SELECT *
> FROM my_Table
> WHERE CONCAT(value1, value2, value3)
> IN ({$IN_VALUE})";
>
> # The final query string will look like this
> SELECT *
> FROM my_Table
> WHERE CONCAT(value1, value2, value3)
> IN ('6blue40lbs','7orange50lbs','8orange60lbs','9purple70lbs')
>
> # Now run this through your query function and get the results
> $results = mysql_query($SQL) OR die('SQL Failure: '.$SQL);
>
> So basically what we have is a comparison that is based off the output of 
> the CONCAT() function that creates one string out of value1, value2, 
> value3 and then compares that with each of the values listed within the 
> parenthesis.  the IN (...) part of the SQL statement tells SQL that it is 
> getting a list of values that it should compare the concat() value 
> against.
>
> Doing it this way, will allow you to only run one query instead of running 
> one per value that you want to compare against.  As you can tell, as your 
> data set grows your multiple queries would drag your DB to a halt
>
> Hope this explains it.
>
> Let me know if you need further explanation.
>
>>
>> OK, I get everything up to  the ('''.join(''','''$list).''')
>> I'm guessing that the .join( ). putting together some values, but I don't 
>> know what
>> also the .join( ). is to be preceded by something... I don't know what. 
>> //Forgive my ignorance, I'll can get it.
>>
>> Also the .join( ). what is this doing I looked at the PHP and MySQL 
>> function of each, and haven't seen comparable code.
>>
>> I'm asking because I don't know where we're telling the code to compare 
>> the values.
>>
>> You stated...
>>>>> and create one string from them
>> Where do I give the name to the string?
>>
>> So this is where I am so far:
>>
>> $sql = "SELECT* FROM table WHERE CONCAT(size,color,weight) IN( )";
>>
>>
>> "Jim Lucas" <lists@xxxxxxxxx> wrote in message 
>> news:4685FDE7.8000703@xxxxxxxxxxxx
>>> K. Hayes wrote:
>>>> Will do.  Thanks.
>>>>
>>>>
>>>> ----- Original Message ----- From: "Jim Lucas" <lists@xxxxxxxxx>
>>>> To: "kvigor" <k3cheese@xxxxxxxxxxxxx>
>>>> Cc: <php-general@xxxxxxxxxxxxx>
>>>> Sent: Saturday, June 30, 2007 1:46 AM
>>>> Subject: Re:  Selecting Rows Based on Row Values Being in Array
>>>>
>>>>
>>>>> kvigor wrote:
>>>>>> Hello All,
>>>>>>
>>>>>> I'm attempting to return rows from a mysql DB based on this criteria:
>>>>>>
>>>>>> I have a list, in the form of an array that I need to compare against 
>>>>>> each row
>>>>>> in the table.  Where theres a match I need that entire row returned.
>>>>>>
>>>>>> e.g.    $varListof 3outOf_10Fields = array(6blue40lbs, 7orange50lbs, 
>>>>>> 8orange60lbs, 9purple70lbs);
>>>>>>
>>>>>> The array contains 3 of the db row fields in 1 value. However there 
>>>>>> are 10 fields/columns in the table.
>>>>>>
>>>>>> ===============
>>>>>> what table looks like  |
>>>>>> ===============
>>>>>>                   size       color    weight
>>>>>> ROW 1    | value1 | value1 | value1 | value1 | value1 | value1 |
>>>>>>
>>>>>> So how could I set up a query that would SELECT the entire row, if 
>>>>>> the row contained $varListof 3outOf_10Fields[1].
>>>>>>
>>>>>> Open to any suggestions or work arounds.  I'm playing with extract() 
>>>>>> but code is too crude to even post.
>>>>>>
>>>>> I would suggest approaching the problem with a slightly different 
>>>>> thought.
>>>>>
>>>>> just have the sql concat() the columns together and then compare.
>>>>>
>>>>> something like this should do the trick
>>>>>
>>>>> $list = array(
>>>>> '6blue40lbs',
>>>>> '7orange50lbs',
>>>>> '8orange60lbs',
>>>>> '9purple70lbs',
>>>>> );
>>>>>
>>>>> $SQL = "
>>>>> SELECT *
>>>>> FROM my_Table
>>>>> WHERE CONCAT(value1, value2, value3) IN ('".join("','", $list)."')
>>>>> ";
>>>>>
>>>>> mysql_query($SQL);
>>>>>
>>>>> this should take, for each row in the DB, value1 + value2 + value3 and 
>>>>> create one string from them, then it will compare each string in the
>>>>> IN (...)  portion to each entry in the $list array().
>>>>>
>>>>> Let me know if you need any further help
>>> one other thing, make sure that you run each of the values in the $list 
>>> array() through mysql_real_escape_string().  That way it is all nicely 
>>> encoded for the SQL statement.
>> 

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux