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
