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" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
K. Hayes wrote:
Will do. Thanks.
----- Original Message ----- From: "Jim Lucas" <[EMAIL PROTECTED]>
To: "kvigor" <[EMAIL PROTECTED]>
Cc: <php-general@lists.php.net>
Sent: Saturday, June 30, 2007 1:46 AM
Subject: Re: [PHP] 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
