Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006
    Posts
    2

    Unanswered: [Beginner] PHP (ADODB) - Displaying content of 2 tables

    Hi community, I am not very skilled in using databases, I hope someone can help me. Currently I want to write a "Voting System" with PHP using an Access-DB to store the Questions and answers. Here comes what I´ve done up to now.
    There are to 2 tables like this:
    http://gnatbite.de/files/db.gif

    The connections to the Access-DB works fine, I can read and write data to it. But I have problems to use the right "SQL-command". I want to print the Questions and electable answers as follows.

    Question-1
    ========
    Answer-1
    Answer-2
    ...
    Answer-n

    Question-2
    ========
    Answer-1
    Answer-2
    ...
    Answer-n

    Unfortunately, I don´t know how I can program it. Here is my code:
    PHP Code:
    <?php
      ini_set
    ('error_reporting',E_ALL);
      
    $Cursor=null//pointer to the results (rows) of a query
      
    $Row=null//one result (one row), accessed from $Cursor
      
    $NumRows0//number of rows in a cursor


    $Query'SELECT questionText, answerText FROM questions, answers WHERE questionID = QID';

    $DBgetcwd().'\voting.mdb';
    require_once(
    'C:\wamp\adodb\adodb.inc.php'); 
    $DSN="Driver={Microsoft Access Driver (*.mdb)};DBQ=$DB";//as in Perl

    $Conn= &ADONewConnection('access');  $Conn->Connect($DSN);
    if (!(
    $Cursor$Conn->Execute($Query)))
      die(
    "<p>Could not execute the query '$Query'");
    ?>


    <h3>Search Results</h3>

    <table border="1" cellpadding="0" cellspacing="0">
    <?php
    for (;(!$Cursor->EOF); $Cursor->MoveNext()) 
    {
      print(
    "<tr>");
      for (
    $i=0$l=$Cursor->FieldCount(); $i $l$i++)
        print(
    '<td>'.$Cursor->fields[$i]."</td>\n");
      print(
    "</tr>\n");
    }

    $NumRows$Cursor->RecordCount();  $Cursor->close(); $Conn->close();
    ?>
    The output looks like this:
    Code:
    Search ResultsWhat time is it?	10:00
    What time is it?	11:00
    What time is it?	12:00
    I hope someone can help me.
    Attached Thumbnails Attached Thumbnails db.gif  

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you need to JOIN the two tables. have a go with:

    SELECT questions.questionText, answers.answerText
    FROM questions LEFT JOIN answers ON questions.questionID = answers.qID
    ORDER BY questions.questionText;

    izy

    LATER: forget it - i misread your question.
    join is still going to produce
    country uk
    country germany
    country spain
    name sue
    name tony
    name fred
    Last edited by izyrider; 08-12-06 at 05:09.
    currently using SS 2008R2

  3. #3
    Join Date
    Aug 2006
    Posts
    2
    Thx a lot for your answer. I tried it but the answer is still printed out three times? :-/

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here's a really ugly way that approximates what you asked for.
    there must be better way...


    output:
    name====
    tony
    sue
    fred
    country====
    uk
    spain
    germany


    query:
    SELECT t1
    FROM (SELECT q1, t1
    FROM (SELECT questions.QuestionID AS q1, questions.questionText & "====" AS t1
    FROM tblQuestions)
    UNION
    (SELECT answers.QID & "ZZZ" AS q1, answers.answerText AS t1
    FROM answers))
    ORDER BY q1;

    izy


    LATER & UGLIER
    ...but in the format you wanted:

    output:

    name
    ====
    tony
    sue
    fred

    country
    =======
    uk
    spain
    germany



    query:

    SELECT t1
    FROM (SELECT q1, t1
    FROM
    (SELECT QuestionID & "A" AS q1, questionText AS t1
    FROM questions)
    UNION (SELECT QuestionID & "B" AS q1, string(len(questionText), "=") AS t1
    FROM questions)
    UNION (SELECT QuestionID AS q1, " " AS t1
    FROM questions)
    UNION (SELECT QID & "C" AS q1, answerText AS t1
    FROM answers
    ))
    ORDER BY q1;

    which access aliases on save to something like:
    SELECT t1
    FROM [SELECT q1, t1
    FROM
    (SELECT QuestionID & "A" AS q1, questionText AS t1
    FROM questions)
    UNION (SELECT QuestionID & "B" AS q1, string(len(questionText), "=") AS t1
    FROM questions)
    UNION (SELECT QuestionID AS q1, " " AS t1
    FROM questions)
    UNION (SELECT QID & "C" AS q1, answerText AS t1
    FROM answers
    )]. AS Z
    ORDER BY q1;
    Last edited by izyrider; 08-12-06 at 13:51.
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •