maaceeklis Posted April 3, 2015 Report Share Posted April 3, 2015 Sveiki, tātad man ir šāds Query "SELECT DISTINCT questions.question, questions.username, questions.author, questions.date FROM questions LEFT JOIN users ON '$getUrl' = questions.username AND '$getUrl' = users.username" Kā jau iespējams varat noprast pēc vaicājuma - ideja ir tāda, man ir divas tabulas -> users un questions, lieta tada, ka katrs lietotajs ir unikaals un katram ir savs links piemeram http://localhost/tavsniks ieejot šajā adresē ir nepieciešams nolasīt visus jautājumus no table - questions, katram attiecīgajam uzerim. Questions tabula ir rows username, kur tiek salidzinats userneme no users tabulas un no questions tabulas, taa lai atlasitu jautajumus... Tikai šajā gadījumā tas nestrādā, pieminēšu to, ka $getUrl = $segments[1], kas nolasa padoto informāciju aiz slasha :) LIELS, LIELS paldies jau iepriekš. Ceru, uz palīdzību.. Quote Link to comment Share on other sites More sharing options...
jurchiks Posted April 3, 2015 Report Share Posted April 3, 2015 (edited) 1) Drausmīgi noformēts query. LEFT JOIN users vispār ir lieks, jo neko nedara. 2) NEKĀDĀ GADĪJUMĀ neievieto kaut kādu random stringu pa taisno no URL datubāzes pieprasījumā! Vai nu prepared statement ar parametru, vai vismaz pārbaudi, vai username atbilst kaut kādam patternam, kāds norādīts reģistrācijas formā. Ja tu neizmanto nekādus frameworkus, tad paņem vismaz šito: https://github.com/jurchiks/dbhandler Tad vari rakstīt šādi: $data = \database\Handler::getInstance() ->prepare('SELECT DISTINCT question, username, author, date FROM questions WHERE username = ?') // jautājums - kāpēc username un author nav viens un tas pats? // ja author = authorID, tad username jāatrodas tikai un vienīgi users tabulā, nevis šeit. ->execute(array($segments[1])) ->fetchAllRows(); Ja es pareizi saprotu, tu ar to query biji domājis panākt kaut ko šādu: SELECT DISTINCT q.question, q.date, q.author, u.username FROM questions q INNER JOIN users u ON q.author = u.id WHERE u.username = ? Edited April 3, 2015 by jurchiks Quote Link to comment Share on other sites More sharing options...
maaceeklis Posted April 3, 2015 Author Report Share Posted April 3, 2015 jaa, tiesi ta, tagad viss strada, tikai kaa ir ar atbildem? lai dabutu ari atbildes vel jataisa atsevisks querijs kur pieprasa datus no answers table? Quote Link to comment Share on other sites More sharing options...
jurchiks Posted April 3, 2015 Report Share Posted April 3, 2015 (edited) Kaut kā tā: $questions = \database\Handler::getInstance() ->prepare('SELECT q.id, q.question, q.date, q.author, u.username FROM questions q INNER JOIN users u ON q.author = u.id WHERE u.username = ? ORDER BY q.date DESC') ->execute(array($segments[1])) ->fetchAllRows(); $answerStmt = \database\Handler::getInstance() ->prepare('SELECT answer, author, date FROM answers WHERE questionID = ? ORDER BY date ASC'); // or desc, w/e foreach ($questions as $question) { $answers = $answerStmt->execute(array($question['id']))->fetchAllRows(); ... echo question and answers } Ja tās atbildes izvadi tikai atsevišķā jautājuma lapā, tad to $answerStmt nevajag glabāt atsevišķi, var uzreiz $answers = ... Edited April 3, 2015 by jurchiks Quote Link to comment Share on other sites More sharing options...
maaceeklis Posted April 3, 2015 Author Report Share Posted April 3, 2015 Kaut kā tā: $questions = \database\Handler::getInstance() ->prepare('SELECT q.id, q.question, q.date, q.author, u.username FROM questions q INNER JOIN users u ON q.author = u.id WHERE u.username = ? ORDER BY q.date DESC') ->execute(array($segments[1])) ->fetchAllRows(); $answerStmt = \database\Handler::getInstance() ->prepare('SELECT answer, author, date FROM answers WHERE questionID = ? ORDER BY date ASC'); // or desc, w/e foreach ($questions as $question) { $answers = $answerStmt->execute(array($question['id']))->fetchAllRows(); ... echo question and answers } Ja tās atbildes izvadi tikai atsevišķā jautājuma lapā, tad to $answerStmt nevajag glabāt atsevišķi, var uzreiz $answers = ... Milzīgs Tev paldies, mēģināšu darboties tālāk! :) Quote Link to comment Share on other sites More sharing options...
briedis Posted April 3, 2015 Report Share Posted April 3, 2015 jurchik, rly, kvēriju saukšanas ciklā? :) un ja ir 100 jautājumi? sauksi 101 kvēriju? Quote Link to comment Share on other sites More sharing options...
codez Posted April 4, 2015 Report Share Posted April 4, 2015 Nu ja, kā tad savādāk :), ar prepared statement jau nevar uztaisīt WHERE IN kveriju ar mainīgu parametru skaitu, ja neņem vērā, ka der arī join-s. Quote Link to comment Share on other sites More sharing options...
Kasspars Posted April 4, 2015 Report Share Posted April 4, 2015 Kur problēma uzģenerēt query ar vajadzīgo skaitu "?" function repeat( $s, $sep, $times ) { return str_repeat( $s.$sep, $times-1 ).$s; } $ids = [1, 2, 3, 4, 234, 234234]; $q = "SELECT * FROM table WHERE field IN (".repeat( "?", ",", count($ids) ).")"; $stmt = DB::prepare($q); $stmt->execute($ids); Toties Jurim smuks kods :D Quote Link to comment Share on other sites More sharing options...
jurchiks Posted April 4, 2015 Report Share Posted April 4, 2015 (edited) @briedis - jā, saukšu. 100 pieprasījumi galīgi nav nekas daudz, it īpaši ņemot vērā, ka tas ir iekešots pieprasījums ar parametru. Ja izvada atbildes sarakstā, tad tur anyway neizvadīs visas atbildes, vai nu izvadīs apstiprināto atbildi vai top voted atbildi, tā kā tas nekas nav. @codez - ej dirst. Simplicity > all. Edited April 4, 2015 by jurchiks Quote Link to comment Share on other sites More sharing options...
codez Posted April 4, 2015 Report Share Posted April 4, 2015 100 pieprasījumi galīgi nav nekas daudz, it īpaši ņemot vērā, ka tas ir iekešots pieprasījums ar parametru. Tā mums rodas tās lapas, kuras nevelk vairāk par 10 paralēliem lietotājiem. Jurčikam sācies buthurts. Quote Link to comment Share on other sites More sharing options...
jurchiks Posted April 4, 2015 Report Share Posted April 4, 2015 Pats sāc strīdu ar uzbraucienu un tad brīnies. Gtfo, idiot. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.