Database Explorer – where s parametrem
- Allconius
- Člen | 317
Ahoj, potřeboval bych poradit s where, toto je mi jasné:
<?php
$books = $context->table('book');
$books->where('author.name LIKE ?', '%Jon%');
$books->where('translator.name', 'David Grudl');
?>ale nechápu to spojování více podmínek s tím parametrem:
<?php
$prehled = $database->table('p_t')
->where($where[, $param[, ...]])
?>potřeboval bych přepsat tento dotaz:
<?php
$result = $database->query('SELECT p_t.*, p_t_n.*, p_t_j.*, p_t_p.* FROM p_t LEFT JOIN p_t_n ON p_t.id = p_t_n.id LEFT JOIN p_t_j ON p_t.id = p_t_j.id LEFT JOIN p_t_p ON p_t.id = p_t_p.id WHERE p_t.smazano=0 AND p_t.rok> ? AND p_t.rok LIKE ? AND p_t.cislo LIKE ? AND p_t_n.narozeni>= ? AND p_t_n.narozeni<=? AND p_t_p.prijmeni LIKE ? ORDER BY p_t.id DESC LIMIT ?,?',$skartace,$rok,$cislo,$narozeniod,$narozenido,$prijmeni,$list,$szobraz);
?>
- CZechBoY
- Člen | 3608
WHERE
p_t.smazano = 0 AND
p_t.rok> ? AND
p_t.rok LIKE ? AND
p_t.cislo LIKE ? AND
p_t_n.narozeni >= ? AND
p_t_n.narozeni <=? AND
p_t_p.prijmeni=>
$selection->where('p_t.smazano = ?', 0);
$selection->where('p_t.rok > ?', $skartace);
$selection->where('p_t.cislo LIKE ?', $rok);
$selection->where('p_t_n.narozeni >= ?', $narozeniod);
$selection->where('p_t_n.narozeni <= ?', $narozenido);
$selection->where('p_t_p.prijmeni LIKE ?', $prijmeni)- CZechBoY
- Člen | 3608
Když to dáváš znovu tak se to spojí přes AND.
Samozřejmě můžeš to napsat do jednoho where příkazu takto
$selection->where('p_t.smazano=0 AND p_t.rok> ? AND p_t.rok LIKE ? AND p_t.cislo LIKE ? AND p_t_n.narozeni>= ? AND p_t_n.narozeni<=? AND p_t_p.prijmeni LIKE ?',$skartace,$rok,$cislo,$narozeniod,$narozenido,$prijmeni);Editoval CZechBoY (13. 9. 2018 10:41)
- Allconius
- Člen | 317
Ahoj, tak jsem zkusil:
<?php
$prestupky = $database->table('p_t')
->where('p_t.smazano = ?', 0)
->where('p_t.rok > ?', $skartace)
->where('p_t.rok LIKE ?', $rok)
->where('p_t.cislo LIKE ?', $cislo)
->where('p_t_n.narozeni >= ?', $narozeniod)
->where('p_t_n.narozeni <= ?', $narozenido)
->where('p_t_p.prijmeni LIKE ?', $prijmeni)
->order('p_t.id DESC');
/*
ale skončí to chybou, jak kdyby nemohl přijoinovat tu tabulku p_t_n :
Nette\InvalidArgumentException
No reference found for $p_t->p_t_n.
.../nette/database/src/Database/Table/SqlBuilder.php:652 source Nette\Database\Table\SqlBuilder->parseJoinsCb(arguments)
inner-code Nette\Database\Table\SqlBuilder->Nette\Database\Table\{closure}(arguments)
.../nette/database/src/Database/Table/SqlBuilder.php:653 source preg_replace_callback(arguments)
.../nette/database/src/Database/Table/SqlBuilder.php:210 source Nette\Database\Table\SqlBuilder->parseJoins(arguments)
.../nette/database/src/Database/Table/Selection.php:164 source Nette\Database\Table\SqlBuilder->buildSelectQuery(arguments)
.../nette/database/src/Database/Table/Selection.php:565 source Nette\Database\Table\Selection->getSql()
.../nette/database/src/Database/Table/Selection.php:1011 source Nette\Database\Table\Selection->execute()
/u01/docs/www/html/osc/tp/index.php:335 source Nette\Database\Table\Selection->rewind()
*/
?>Co je špatně ?
- David Matějka
- Moderator | 6445
ukaz sql dump databaze. to se ti opravdu jmenuji tabulky p_t,
p_t_n a spojujes je pres jejich primarni klice (id)?
- Allconius
- Člen | 317
<?php
/*
spojuju to přes ty id klíče viz. ten dotaz nahoře:
"... LEFT JOIN p_t_n ON p_t.id = p_t_n.id LEFT JOIN p_t_j ON p_t.id = p_t_j.id LEFT JOIN p_t_p ON p_t.id = p_t_p.id ..."
CREATE TABLE `p_t` (
`id` int(11) NOT NULL,
`cislo` char(10) COLLATE utf8_czech_ci DEFAULT NULL,
`rok` int(4) NOT NULL DEFAULT '0',
`podstata` char(60) COLLATE utf8_czech_ci DEFAULT NULL,
`poznamka` varchar(250) COLLATE utf8_czech_ci DEFAULT NULL,
`zahajeni` int(8) NOT NULL DEFAULT '0',
`postoupeni_checkbox` int(1) NOT NULL DEFAULT '0',
`postoupeni_vypraveni` int(8) NOT NULL DEFAULT '0',
`postoupeni_doruceni` int(8) NOT NULL DEFAULT '0',
`postoupeni_text` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
`prikaz_checkbox` int(1) NOT NULL DEFAULT '0',
`p_castka` int(9) NOT NULL DEFAULT '0',
`p_vypraveni` int(8) NOT NULL DEFAULT '0',
`p_prevzeti` int(8) NOT NULL DEFAULT '0',
`p_text` char(250) COLLATE utf8_czech_ci DEFAULT NULL,
`p_poznamka` char(250) COLLATE utf8_czech_ci DEFAULT NULL,
`p_pm` int(8) NOT NULL DEFAULT '0',
`poznamky` char(255) COLLATE utf8_czech_ci DEFAULT NULL,
`ukonceni` int(8) NOT NULL DEFAULT '0',
`oznameni` int(8) NOT NULL DEFAULT '0',
`aktualne` int(8) NOT NULL DEFAULT '0',
`pm` int(8) NOT NULL DEFAULT '0',
`npm` int(8) NOT NULL DEFAULT '0',
`smazano` int(11) NOT NULL DEFAULT '0',
`aktualizace` int(20) NOT NULL DEFAULT '0',
`vytvoreno` char(12) COLLATE utf8_czech_ci DEFAULT NULL,
`user` char(250) COLLATE utf8_czech_ci NOT NULL DEFAULT '',
`http` char(50) COLLATE utf8_czech_ci DEFAULT NULL,
`adr` char(50) COLLATE utf8_czech_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
--
-- Klíče pro exportované tabulky
--
--
-- Klíče pro tabulku `p_t`
--
ALTER TABLE `p_t`
ADD PRIMARY KEY (`id`),
ADD KEY `rok` (`rok`,`oznameni`,`smazano`,`ukonceni`);
----------------------------------------------------------------------------------
CREATE TABLE `p_t_n` (
`id` int(11) NOT NULL,
`narozeni` int(8) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
--
-- Klíče pro exportované tabulky
--
--
-- Klíče pro tabulku `p_t_n`
--
ALTER TABLE `p_t_n`
ADD PRIMARY KEY (`id`);
----------------------------------------------------------------------------------
CREATE TABLE `p_t_j` (
`id` int(11) NOT NULL,
`jmeno` char(50) COLLATE utf8_czech_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
--
-- Klíče pro exportované tabulky
--
--
-- Klíče pro tabulku `p_t_j`
--
ALTER TABLE `p_t_j`
ADD PRIMARY KEY (`id`);
----------------------------------------------------------------------------------
CREATE TABLE `p_t_p` (
`id` int(11) NOT NULL,
`prijmeni` char(50) COLLATE utf8_czech_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
--
-- Klíče pro exportované tabulky
--
--
-- Klíče pro tabulku `p_t_p`
--
ALTER TABLE `p_t_p`
ADD PRIMARY KEY (`id`);
*/
?>- David Matějka
- Moderator | 6445
ten navrh databaze je divny a nette\database si s nim neporadi. (navic tam nemas cizi klice a ani kvuli myisam mit nemuzes).
a proc to vubec mas rozdelene do vice tabulek a nemas vse v
p_t?
- Allconius
- Člen | 317
jinak ten původní dotaz funguje:
<?php
/*
$result = $database->query('
SELECT p_t.*, p_t_n.*, p_t_j.*, p_t_p.*
FROM p_t
LEFT JOIN p_t_n ON p_t.id = p_t_n.id
LEFT JOIN p_t_j ON p_t.id = p_t_j.id
LEFT JOIN p_t_p ON p_t.id = p_t_p.id
WHERE p_t.smazano=0
AND p_t.rok> ?
AND p_t.rok LIKE ?
AND p_t.cislo LIKE ?
AND p_t_n.narozeni>= ?
AND p_t_n.narozeni<=?
AND p_t_p.prijmeni LIKE ?
ORDER BY p_t.id DESC
LIMIT ?,?'
,$skartace,$rok,$cislo,$narozeniod,$narozenido,$prijmeni,$list,$szobraz);
ale ty upravené už ne, ten v tom jednom řádku:
$result = $database->table('p_t');
$result->where('p_t.smazano=0 AND p_t.rok> ? AND p_t.rok LIKE ? AND p_t.cislo LIKE ? AND p_t_n.narozeni>= ? AND p_t_n.narozeni<=? AND p_t_p.prijmeni LIKE ?',$skartace,$rok,$cislo,$narozeniod,$narozenido,$prijmeni);
píše stejnou chybu:
Nette\InvalidArgumentException
No reference found for $p_t->p_t_n.
*/
?>
- Allconius
- Člen | 317
Ahoj díky, dalo mi to fušku, ale podařilo se to nakonec takto:
<?php
/*
DATABAZE:
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `prestupky`, `jmeno`, `prijmeni`, `narozeni`;
CREATE TABLE `jmeno` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`jmeno` char(50) COLLATE utf8_czech_ci DEFAULT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB CHARSET=utf8 AUTO_INCREMENT=5;
INSERT INTO `jmeno` (`id`, `jmeno`) VALUES
(1, 'Jan'),
(2, 'Petr'),
(3, 'Jana'),
(4, 'Josef');
CREATE TABLE `prijmeni` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`prijmeni` char(50) COLLATE utf8_czech_ci DEFAULT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB CHARSET=utf8 AUTO_INCREMENT=5;
INSERT INTO `prijmeni` (`id`, `prijmeni`) VALUES
(1, 'Koblížek'),
(2, 'Pražák'),
(3, 'Novotná'),
(4, 'Kratochvíl');
CREATE TABLE `narozeni` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`narozeni` int(8) NOT NULL DEFAULT '0',
PRIMARY KEY(id)
) ENGINE=InnoDB CHARSET=utf8 AUTO_INCREMENT=5;
INSERT INTO `narozeni` (`id`, `narozeni`) VALUES
(1, 19350883),
(2, 19670703),
(3, 19510883),
(4, 19711483);
CREATE TABLE `prestupky` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cislo` char(10) CHARACTER SET utf8 COLLATE utf8_czech_ci DEFAULT NULL,
`rok` int(4) NOT NULL DEFAULT '0',
`jmeno_id` int(10) NOT NULL DEFAULT '0',
`prijmeni_id` int(10) NOT NULL DEFAULT '0',
`narozeni_id` int(10) NOT NULL DEFAULT '0',
`podstata` char(60) CHARACTER SET utf8 COLLATE utf8_czech_ci DEFAULT NULL,
`poznamka` varchar(250) CHARACTER SET utf8 COLLATE utf8_czech_ci DEFAULT NULL,
`oznameni` int(8) NOT NULL DEFAULT '0',
`vytvoreno` char(12) CHARACTER SET utf8 COLLATE utf8_czech_ci DEFAULT NULL,
`user` char(250) CHARACTER SET utf8 COLLATE utf8_czech_ci NOT NULL DEFAULT '',
`http` char(50) CHARACTER SET utf8 COLLATE utf8_czech_ci DEFAULT NULL,
`adr` char(50) CHARACTER SET utf8 COLLATE utf8_czech_ci DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT prestupky_jmeno FOREIGN KEY (jmeno_id) REFERENCES jmeno (id),
CONSTRAINT prestupky_prijmeni FOREIGN KEY (prijmeni_id) REFERENCES prijmeni (id),
CONSTRAINT prestupky_narozeni FOREIGN KEY (narozeni_id) REFERENCES narozeni (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5;
INSERT INTO `prestupky` (`id`, `cislo`, `rok`, `jmeno_id`, `prijmeni_id`, `narozeni_id`, `podstata`, `poznamka`, `oznameni`, `vytvoreno`, `user`, `http`, `adr`) VALUES
(1, '1/2018', 2018, 1, 1, 1, '142/99', '', 20180911, '1536650485', 'michal', '', '10.102.53.28'),
(2, '2/2018', 2018, 2, 2, 2, '1111', '', 20180911, '1536650739', 'michal', '', '10.102.53.28'),
(3, '4/2018', 2018, 3, 3, 3, '16a 1b', 'ěšřěšř', 20180911, '1536652030', 'michal', '', '10.102.53.28'),
(4, '1/2017', 2017, 4, 4, 4, '142/99', '', 20170912, '1536733593', 'michal', '', '10.102.53.28');
SET FOREIGN_KEY_CHECKS = 1;
PHP:
*/
#výchozí hodnoty
$rok = '%';
$cislo = '%';
$nardo = date("Y").'1231';
$narozeniod = nar(19000101);
$narozenido = nar($nardo);
$prijmeni = '%';
$result = $database->table('prestupky')
->where('prestupky.rok > ?', $skartace)
->where('prestupky.rok LIKE ?', $rok)
->where('prestupky.cislo LIKE ?', $cislo)
->where('narozeni.narozeni >= ?', $narozeniod)
->where('narozeni.narozeni <= ?', $narozenido)
->where('prijmeni.prijmeni LIKE ?', $prijmeni)
->order('prestupky.id DESC');
foreach ($result as $row) {
echo $row->cislo . ': ';
echo $row->jmeno->jmeno;
echo $row->prijmeni->prijmeni;
echo $row->narozeni->narozeni;
}
?>jen mi přijde takový zbytečný mít tam 4 sloupce se stejnou hodnotou v té tabulce prestupky, tohle by byl asi nesmysl, že? :
<?php
/*
CONSTRAINT prestupky_jmeno FOREIGN KEY (spolecna_id) REFERENCES jmeno (id),
CONSTRAINT prestupky_prijmeni FOREIGN KEY (spolecna_id) REFERENCES prijmeni (id),
CONSTRAINT prestupky_narozeni FOREIGN KEY (spolecna_id) REFERENCES narozeni (id)
*/
?>Ale je pravda, že záměr asi byl, že budou tabulky jmeno, prijmeni a narozeni sdilene i jinými tabulkami takže takto to má asi větší logiku ;-)
Editoval Allconius (18. 9. 2018 13:14)
- David Matějka
- Moderator | 6445
jen mi přijde takový zbytečný mít tam 4 sloupce se stejnou hodnotou v té tabulce prestupky,
pokud jsou stejny, proc tam vubec mas ty tabulky jmeno, prijmeni a narozeni? nemel bys tam mit pouze tabulku „osoba“, kde budou vsechny ty udaje?