Limit kompleksnog upita

Limit kompleksnog upita 11.07.2011. u 01:01 · pre 186 meseci #1

Dakle, imam problem... Zelim da uradim da mi se ukoliko imam mnogo stvari na jednoj strani, to izdeli na vise njih (pagination). Sve radi super kada imam jednostavan upit, ali kada mi upit izgleda ovako:

$upit = mysql_query("
(SELECT akusticne.marka, akusticne.model, akusticne.cena FROM akusticne WHERE akusticne.MARKA like '%$pretraga%' or akusticne.MODEL like '%$pretraga%')
UNION (SELECT elektricne.marka, elektricne.model, elektricne.cena FROM elektricne WHERE elektricne.MARKA like '%$pretraga%' or elektricne.MODEL like '%$pretraga%')
UNION (SELECT klasicne.marka, klasicne.model, klasicne.cena FROM klasicne WHERE klasicne.MARKA like '%$pretraga%' or klasicne.MODEL like '%$pretraga%')
UNION (SELECT ostalo.marka, ostalo.model, ostalo.cena FROM ostalo WHERE ostalo.MARKA like '%$pretraga%' or ostalo.MODEL like '%$pretraga%') LIMIT ".$limits.",$max") or die(mysql_error());

ne radi... Ne prijavljuje nikakvu gresku, jednostavno je upit isuvise kompleksan da bi mogao da ima i limit.

Ima li neko ideju sta da radim? Hvala puno!

Re: Limit kompleksnog upita 11.07.2011. u 02:21 · pre 186 meseci #2

napisi normalno upit posto debagiranje tog php-a smara, bice da si tu negde nesto pogresno kucno ... na primer u $pretraga ti se nalazi apostrof i to prsne zato sto ti je mailformed sql .. etc etc ..

dakle napisi upit i pusti ga direktno na mysql cli i vidi dal radi ili ne, to sto ti hoces naravno radi bez problema:

Code:

mysql> drop table if exists a;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists b;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists c;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> create table a (i int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table b (i int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table c (i int);
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> insert into a values (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into b values (11), (12), (13), (14), (15);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into c values (21), (22), (23), (24), (25);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from a;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

mysql> select * from b;
+------+
| i    |
+------+
|   11 |
|   12 |
|   13 |
|   14 |
|   15 |
+------+
5 rows in set (0.00 sec)

mysql> select * from c;
+------+
| i    |
+------+
|   21 |
|   22 |
|   23 |
|   24 |
|   25 |
+------+
5 rows in set (0.00 sec)

mysql>
mysql> (select * from a) union (select * from b) union (select * from c);
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|   11 |
|   12 |
|   13 |
|   14 |
|   15 |
|   21 |
|   22 |
|   23 |
|   24 |
|   25 |
+------+
15 rows in set (0.00 sec)

mysql>
mysql> (select * from a) union (select * from b) union (select * from c) limit 10;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|   11 |
|   12 |
|   13 |
|   14 |
|   15 |
+------+
10 rows in set (0.00 sec)

mysql> (select * from a) union (select * from b) union (select * from c) limit 2;
+------+
| i    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> (select * from a) union (select * from b) union (select * from c) limit 2,7;
+------+
| i    |
+------+
|    3 |
|    4 |
|    5 |
|   11 |
|   12 |
|   13 |
|   14 |
+------+
7 rows in set (0.00 sec)

mysql> (select * from a) union (select * from b) union (select * from c) limit 10,2;
+------+
| i    |
+------+
|   21 |
|   22 |
+------+
2 rows in set (0.00 sec)

mysql> -- ili komplikovanije
mysql> (select i from a where i>3) union (select i from b where i <15) union (select i from c where i >22);
+------+
| i    |
+------+
|    4 |
|    5 |
|   11 |
|   12 |
|   13 |
|   14 |
|   23 |
|   24 |
|   25 |
+------+
9 rows in set (0.00 sec)

mysql> (select i from a where i>3) union (select i from b where i <15) union (select i from c where i >22) limit 3,5;
+------+
| i    |
+------+
|   12 |
|   13 |
|   14 |
|   23 |
|   24 |
+------+
5 rows in set (0.00 sec)

mysql>

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Re: Limit kompleksnog upita 11.07.2011. u 02:23 · pre 186 meseci #3

btw, nadam se da si svestan da ce taj tvoj upit SVAKI PUT proci kroz CELE SVE TRI TABELE OD POCETKA DO KRAJA!!! i raditi poredjenje SVAKOG sloga iz tih tabela sa tvojim uslovom? ako te tabele imaju po vise od par hiljada slogova i nalaze se na hard disku ovaj upit ce ti kompletno zakucati masinu svaki put kada se pozove ... o tome koliko ti je pogresan db model necu ni da komentarisem

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Forum kao aplikacija na telefonu

Srodne teme