PHP Fetch data with two different function

Question

I have a question for this, maybe I am doing the wrong way as I have created a two different search function which they have own different function search as one is SKU and one is Date

I have created a code for MySQL and Workbench and it working fine as I'm using

SELECT 
    settlement_id, sku
FROM
    settlements
WHERE
    sku LIKE 'ISCE%' 
        AND settlement_id LIKE '7072432852'
GROUP BY sku
HAVING sku IS NOT NULL AND LENGTH(sku) > 0
ORDER BY sku

and it displayed fine

I use this same function for the PHP pages

$output = '';
if (isset($_POST["query"])) {
    $search       = mysqli_real_escape_string($conn, $_POST["query"]);
    $queryskulist = "
    SELECT
        settlement_id,
        sku,
    FROM settlements
    WHERE sku LIKE '%" . $search . "%' and settlement_id LIKE '%" . $search . "%'    
    GROUP BY sku 
    HAVING sku IS NOT NULL AND LENGTH(sku) > 0
    ORDER BY sku
    ";
} else {
    $queryskulist = "
    SELECT 
        settlement_id,
        sku,
    FROM settlements 
    GROUP BY sku 
    HAVING sku IS NOT NULL AND LENGTH(sku) > 0
    ORDER BY sku  ";
}

as you can see

WHERE sku LIKE '%" . $search . "%' and settlement_id LIKE '%" . $search . "%'

that's where I am getting an error as I am able to get data from only one function like this

WHERE sku LIKE '%" . $search . "%'

but I am unable to get two different search result, should I use two different query $_POST["query"]?

Any advice would be great, or Sample/Example demo PHP site would be great, it would help me a big picture on it.

"I have created a code for MySQL and Workbench and it working fine as I'm using " SELECT settlement_id, sku .... GROUP BY sku is a invalid use of GROUP BY read psce.com/en/blog/2012/05/15/… — Raymond Nijland
– Raymond Nijland, Commented Apr 9, 2018 at 14:50
a great document to read it, thanks as I am still learning all MySQL stuff. thanks — Steven Smith
– Steven Smith, Commented Apr 9, 2018 at 14:59

HelloSpeakman · Accepted Answer · 2018-04-09 14:52:34Z

2

You can fix this by using the following

SELECT
    settlement_id,
    sku
FROM
    settlements
WHERE
    (CONCAT(sku, settlement_id) LIKE '%" . $search . "%')
AND
    sku IS NOT NULL AND LENGTH(sku) > 0
ORDER BY
    sku

Alternatively you should be able to wrap in parenthesis

WHERE (sku LIKE '%" . $search . "%') OR (settlement_id LIKE '%" . $search . "%')

edited Apr 9, 2018 at 14:52

answered Apr 9, 2018 at 14:50

HelloSpeakman

8309 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2018-04-09 14:55:49Z

2

do the fact you have not aggregation function you should use where

    SELECT 
    settlement_id
    , sku
FROM  settlements
WHERE sku LIKE 'ISCE%' 
        AND settlement_id LIKE '7072432852'

WHERE sku IS NOT NULL AND LENGTH(sku) > 0
ORDER BY sku

the use of group by without aggreagtion function is deprecated in SQL .. and in the most recent version of mysql is not allowed .. (by default)

using group by without aggregation function you could obtain unpredictable result

edited Apr 9, 2018 at 14:55

answered Apr 9, 2018 at 14:48

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

3 Comments

Raymond Nijland Over a year ago

"the use of group by without aggreagtion function is deprecated in SQL .. and uin the most recent version of mysql is not allowed .. " really? thats nice do you have a valid source for this?

ScaisEdge Over a year ago

mysql 5.7 doc .. @RaymondNijland

Raymond Nijland Over a year ago

Well read throuh all the links on this page dev.mysql.com/doc/refman/5.7/en/… could not find annything related to the deprecation claim where i would expect it to be... Also looked under dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html where they summarize what has been added to, deprecated in, and removed from MySQL 5.7.. Couldn't find annything.

Steven Smith · Accepted Answer · 2018-04-11 09:02:05Z

0

Thank for the update, I have fixed this issue as I have decided to remove 'Sku' and keep the rest of it, so here is what happened.

'Settlement_id' is used an options dropdown value on the PHP pages which they are pulling data from MySQL and the 'SKU' search box are using on the input box. and the input box is using Javascript Ajax Filters to get data which it displayed result from 'Settlement_Id' data

here is a demo http://amazonsettlement.ishka.ie/MultiSelect/

answered Apr 11, 2018 at 9:02

Steven Smith

4065 silver badges21 bronze badges

Collectives™ on Stack Overflow

PHP Fetch data with two different function

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related