I am trying to select multiple values in a single column. Basically I want the query to select all those under column family
with values Software_1Y
,XI_1Y
and P1_1Y
I am running this query :
SELECT `salesorder`
,`masterproduct`
,`family`
,`birthstamp`
,`duedate`
,COUNT(*) AS `total`
FROM `report`
WHERE `birthstamp` BETWEEN '$startDT'
AND '$endDT'
AND `family` = 'Software_1Y'
AND `family = 'XI_1Y'
AND `family` = 'PI_1Y'
GROUP BY `salesorder`
,`masterproduct`
,`family`
,`duedate`;
My query returns no rows but is I search each family one by one, I have values.
What is wrong with my query?
Also, my purpose is to get all the rows whose family
values are Software_1Y
, XI_1Y
and PI_1Y
.
Note – Use of IN for matching multiple values i.e. TOYOTA and HONDA in the same column i.e. COMPANY. Syntax: SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IN (MATCHING_VALUE1,MATCHING_VALUE2);
Learn MySQL from scratch for Data Science and Analytics To select multiple values, you can use where clause with OR and IN operator.
You can either loop through the rows with a cursor and append to a field in a temp table, or you could use the COALESCE function to concatenate the fields.
How about using IN instead
SELECT `salesorder`,
`masterproduct`,
`family`,
`birthstamp`,
`duedate`,
COUNT( * ) AS `total`
FROM `report`
WHERE `birthstamp` BETWEEN '$startDT' AND '$endDT'
AND `family` IN ('Software_1Y','XI_1Y','PI_1Y')
GROUP BY `salesorder`,
`masterproduct`,
`family`,
`duedate`;
The reason why no values are returned, is because of this section
AND `family` = 'Software_1Y'
AND `family = 'XI_1Y'
AND `family` = 'PI_1Y'
family
cannot be all 3 values at once, but it might be 1 of the 3 values.
That is why you would use IN.
Another way to look at it would be to use OR, but that gets really long winded.
$query=" SELECT `salesorder`,`masterproduct`,`family`,`birthstamp`,`duedate`, COUNT( * ) AS `total` FROM `report`
WHERE `birthstamp` BETWEEN '$startDT' AND '$endDT'
AND (`family` = 'Software_1Y'
OR `family` = 'XI_1Y'
OR `family` = 'PI_1Y')
GROUP BY `salesorder`,`masterproduct`,`family`,`duedate` ";
It must be due to AND instead of OR while querying FAMILY column.
$result = mysql_query($query);
while ($row = mysql_fetch_array($result))
{
//Operation you want to perform
}
@jude: It seems you are directly passing the query which is of type string directly as a parameter to mysql_fetch_array, which is wrong. Instead follow the above approach...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With