Hello,
I need to get a single result from a SELECT from a table with the following sturcture:
component - varchar(25) primary key
catNum - varchar(25) primary key
price - decimal(10,2)
my component list may contain duplicate catNum ie:
primary drive -WD1GJB
- WD800JB
-none
secondary drive - WD800JB
-none
monitor -SONLCD19
-none
any one catNum will have the same price so when i go looking for parts, knowing the
catNum, i do this:
SELECT price FROM syscomp WHERE catNum = $catNum
no problem - i only look at the first return and am happy!
However when putting together a system - and i have the following:
$system = qq('WD800JB', 'SONLCD19', 'none', 'WD1GJB');
and insert that into my SQL as:
SELECT price FROM syscomp WHERE catNum IN ($system);
if 'WD800JB' is in TWO components, i get two results - thus, and incorrect number of
return results.
(price is doubled)
However when i use:
SELECT DISTINCTROW price FROM syscomp WHERE catNum IN ($system);
and my list looks like this:
$system = qq('WD800JB', 'WD800JB', 'SONLCD19', 'none');
i only get 4 results, the second 'WD800JB' is ignored.
The only solution i can think of is:
$system = ('partA', 'partB', 'partC', 'partD', 'partD');
foreach ($system){
SELECT price FROM syscompt WHERE catNum = '$_'
# incrememnt $total with the first resutl.
}
BUT this seems painfully slow - as this would result in up to 20 calls to the sql
server.
I could ensure that each $system had the matching component, to match on the primary
key, but i don't know how to set up the IN statement for a double part:
WHERE concat (component, '-', catNum) IN ($system)
can anyone help me with a solution.
thanks.
ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
