SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Greenville, SC
    Posts
    388
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    querying more than one ID from different tables.

    Hello,

    I'm writing an application thats coming along well, but I'm having problems with a query. I have two tables both have a ID field set to primary key auto increment. How do I query both Id's to display both if needed. Heres my page:

    topic_list.php
    PHP Code:

    <?php
    //connect to the database
    $dbcnx mysql_connect('localhost','jive','zeroone');
    $bookconnect mysql_select_db('bookwriter');
    //query chapters and topics db to display the chapter/topic text
    $chapID $_GET['chapID'];
    $topiclist mysql_query("SELECT chap_title, topics.ID, topic_title, topic_text
    FROM chapters, topics
    WHERE chap_ID = chapters.ID AND
    chap_ID = 
    $chapID) or die(mysql_error());


    if(
    $topiclist) {
    echo (
    'connected');
    }
    else {
    echo (
    'not connected somethings wrong with the query');
    }


    //create the topic array in the while loop
    while($topic mysql_fetch_array($topiclist)) {
    $chapter_title $topic['chap_title'];
    $topic_title $topic['topic_title'];
    $topic_text $topic['topic_text'];
    $topic_ID $topic['topics.ID'];
    //display the topics of the chosen chapter
    echo("<table>
    <tr>
    <td><a href="
    topic_form_edit.php">$topic_ID $topic_title</a></td>
    </tr>
    </table>" 
    );
    echo(
    $topic_ID);
    }
    ?>
    I want to be able to query both chapters.ID and topics.ID to I can list them both and pass the topics.ID in a query string to update the topic.

    any ideas.

  2. #2
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select firstTable.ID firstID, secondTable.ID as secondID from ....

    and then use firstID, secondID in your php code

  3. #3
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Greenville, SC
    Posts
    388
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I did that, (look at my code). It doesn't seem to work. Do you have any examples?

  4. #4
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You may find that your problem is this:

    PHP Code:
    $topic_ID $topic['topics.ID'];
    ... 
    Doesn't work - I know from bitter experience here. The way around it is in your query assign it a different field name using AS and then refer to it that way when reading from the resultset;

    PHP Code:
    SELECT chap_titletopics.ID AS TOPIC_IDtopic_titletopic_text
    FROM chapters
    topics
    WHERE chap_ID 
    chapters.ID AND
    chap_ID $chapID 
    And then this:

    PHP Code:
    $topic_ID $topic['TOPIC_ID']; 
    See how that works... Usually the problem for me and this fix works everytime...


  5. #5
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Greenville, SC
    Posts
    388
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well I tried it. Heres my code:
    PHP Code:
    <?php
    //connect to the database
    $dbcnx mysql_connect('localhost','jive','zeroone');
    $bookconnect mysql_select_db('bookwriter');
    //query chapters and topics db to display the chapter/topic text
    $chapID $_GET['chapID'];
    $topiclist mysql_query("SELECT chap_title, topics.ID AS TOPIC_ID, topic_title, topic_text
    FROM chapters, topics
    WHERE chap_ID = chapters.ID AND
    chap_ID = 
    $chapID) or die(mysql_error());


    if(
    $topiclist) {
    echo (
    'connected');
    }
    else {
    echo (
    'not connected somethings wrong with the query');
    }


    //create the topic array in the while loop
    while($topic mysql_fetch_array($topiclist)) {
    $chapter_title $topic['chap_title'];
    $topic_title $topic['topic_title'];
    $topic_text $topic['topic_text'];
    $topic_ID $topic['TOPIC.ID'];
    //display the topics of the chosen chapter
    echo("<table>
    <tr>
    <td><a href="
    topic_form_edit.php">$topic_ID $topic_title</a></td>
    </tr>
    </table>" 
    );
    echo(
    $topic_ID);
    }
    ?>
    Its still not displayings the $topic_ID variable...

  6. #6
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Umm... Then you do have a problem then ? Can you post the database tables you have...

    --EDIT--

    Change this:

    PHP Code:
    $topic_id $topic['TOPIC.ID']; 
    To this:

    PHP Code:
    $topic_id $topic['TOPIC_ID']; 
    You used a period (.) instead of the underscore (_) when you refered to the field name you defined in your SQL mate ? Try that change and see what happens...

  7. #7
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Greenville, SC
    Posts
    388
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that worked. Thanks Dr.!!

  8. #8
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Your Welcome...


Bookmarks

Posting Permissions

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