CODEWITHSUNDEEP
mysqlsqlwhere-clause
tluebke
tluebke

Reputation: 31

Where clause is not working for all columns in MySQL Select-Query

I hava a query for extracting entries of a MySQL table, depending on a search input done by the user which are then presented.

$abfrage = "SELECT id, motivname, preis, masse, produktart, neu, angebot FROM $db.artikel
 INNER JOIN $db.formate ON $db.artikel.format=$db.formate.format
 WHERE (UPPER(motivname) LIKE UPPER('%{$keywordAct}%')
 OR id LIKE '%{$keywordAct}%'
 OR UPPER(produktart) LIKE UPPER('%{$keywordAct}%'))
 AND $db.artikel.aktiv=1
 ORDER BY id";

The query works perfectly, except that the expression aktiv=1 is omitted. Results will also be displayed where aktiv is set to 0. "aktiv" is an int-column within the table "artikel" (See below).

However the query works fine if I switch the last clause

AND $db.artikel.aktiv=1

to e.g.

AND $db.artikel.neu=1

Articles where neu=0 are not displayed, which is also an int-column.

So I think there is a problem within the table setup in MySQL but cannot figure it out. There is one other column "aktiv" in another table of the same database but this table is not addressed by this query and I also specify the aktiv column of the artikel-table.

The table setup looks like this:

CREATE TABLE `artikel` (
 `id` varchar(11) COLLATE utf8_bin NOT NULL,
 `motivnummer` int(4) NOT NULL,
 `motivname` varchar(255) COLLATE utf8_bin NOT NULL,
 `format` int(2) NOT NULL,
 `preis` decimal(10,2) NOT NULL,
 `anzahl_lager` int(3) NOT NULL,
 `anzahl_verkauft` int(4) NOT NULL,
 `anzahl_kostenlos` int(4) NOT NULL,
 `aktiv` int(1) NOT NULL,
 `neu` int(1) NOT NULL,
 `angebot` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

I am happy for some input how to make the query works as it is suppposed to be. Thank you in advance.

PS: In terms of security reasons, the keywords are already escaped in advance.

Upvotes: 1

Views: 470

Answers (1)

Pupil
Pupil

Reputation: 23958

You have added OR clause.

So, even if artikel.aktiv is 0 and other condition is true, it will return rows with aktiv 0.

Solution:

And proper curly braces.

$abfrage = "SELECT id, motivname, preis, masse, produktart, neu, angebot FROM $db.artikel
 INNER JOIN $db.formate ON $db.artikel.format=$db.formate.format
 WHERE ((UPPER(motivname) LIKE UPPER('%{$keywordAct}%')
 OR id LIKE '%{$keywordAct}%'
 OR UPPER(produktart) LIKE UPPER('%{$keywordAct}%')))
 AND ($db.artikel.aktiv=1)
 ORDER BY id";

Your all conditions were going in OR.

AND condition will work only if you separate it from OR.

Upvotes: 1

Related Questions