Jump to content
php.lv forumi

Query problēma


maaceeklis

Recommended Posts

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..

 

 

Link to comment
Share on other sites

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 by jurchiks
Link to comment
Share on other sites

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 by jurchiks
Link to comment
Share on other sites

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! :)

Link to comment
Share on other sites

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
Link to comment
Share on other sites

@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 by jurchiks
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...