www.webdeveloper.com
Results 1 to 12 of 12

Thread: Problem with SELECT JOIN when trying to print out a name once

  1. #1
    Join Date
    May 2014
    Posts
    9

    Problem with SELECT JOIN when trying to print out a name once

    I have a problem when I'm attempting to print out a specific director name for specific movie. I can print out info such as "title" and "production year" for a specific movie, which is stored in a db-table called "filmer", with this code.
    PHP Code:
      $sql "SELECT * FROM filmer where id = $id"
    But I also want to be able to print out the specific director in text for the specific movie, and to print out the director name in text (while connecting it to the actual movie) I have to join the db-table "regissorer", which stores the director names in strings. But when I use the following code I cannot make the director name print out only ONCE, it just prints out all of the director names stored in the regissorer-table. The code:
    PHP Code:
    $sql "SELECT filmer.*, regissorer.fornamn, regissorer.efternamn
    FROM filmer JOIN regissorer
    ON regissorer.id = filmer.regissor_id WHERE id = 
    $id"
    Does anyone know how to fix this?

  2. #2
    Join Date
    Dec 2002
    Location
    Seattle, WA
    Posts
    1,843
    Hmm... I'm getting rusty in the SQL dept. ><
    I would say try adding a GROUP BY statement. I think that should do the trick.
    Code:
    SELECT filmer.*, regissorer.fornamn, regissorer.efternamn
      FROM filmer JOIN regissorer
        ON regissorer.id = filmer.regissor_id
          WHERE id = $id
            GROUP BY id
    If that don't work, post back. I'll rattle my head some more to see anything useful falls out.
    EDIT: Guess my memory is better than I thought (SQL from an old project)
    PHP Code:
    $query 'SELECT t.*, u.username, SUM(p.left = 0) seeds, SUM(p.left != 0) peers FROM ' .TBL_TORRENTS' t, ' .TBL_PEERS' p, ' .TBL_USERS' u WHERE t.info_hash = p.info_hash AND t.added_by = u.user_id AND t.category = \'' .$catid'\' GROUP BY t.info_hash ORDER BY ' .$sortby' ' .$order' LIMIT ' .(($pg -1) * $ppg). ',' .$ppg''
    Last edited by ShrineDesigns; 05-08-2014 at 04:40 AM.

  3. #3
    Join Date
    May 2014
    Posts
    9
    I've tried your code but I still can't make it work. Here is the code I'm using right now on the page where I want the director name to be printed out under the movie title:
    PHP Code:
    <?php
      $id 
    $_GET['id'];
      
    $safeid mysql_real_escape_string($id); //Escape special characters in a string
      
    $saferid htmlspecialchars($safeid); //Protection against injections

      //Select data from filmer-table for specific director:
      
    $sql "SELECT * FROM filmer WHERE id = $id";
      
    $shoot mysql_query($sql);

      while(
    $rad mysql_fetch_array($shoot)) { //While there are data in db-table, show them:
        
    echo '<h2>Filmdatabas ▸ Filmlista ▸ '.$rad["titel"].' ('.$rad["produktionsar"].')</h2>';
      }

      
    $id $_GET['id']; //To get id from db-tables
      
    $safeid mysql_real_escape_string($id); //Escape special characters in a string
      
    $saferid htmlspecialchars($safeid); //Protection against injections

      //Select data from regissorer-table to print it out once:
      
    $sql "SELECT * FROM regissorer WHERE regissor_id = $id";
      
    $shoot mysql_query($sql);

    while(
    $rad mysql_fetch_array($shoot)) { //While data is in table, loop it out:

        
    echo '<h2>Regissör: '.$rad['fornamn'].'&nbsp;'.$rad['efternamn'].'</h2>';

    ?>

    <b>Handling</b><br>

    <?php

      $id 
    $_GET['id'];
      
    $safeid mysql_real_escape_string($id); //Escape special characters in a string
      
    $saferid htmlspecialchars($safeid); //Protection against injections

      //Select data from filmer-table for specific director:
      
    $sql "SELECT * FROM filmer WHERE id = $id";
      
    $shoot mysql_query($sql);

      while(
    $rad mysql_fetch_array($shoot)) { //While there are data in db-table, show them:

      
    echo $rad['handling'];
      }

    ?>
    You can see my test page her: http://annice.se/test/aboutmovie.php?id=16

    I can't understand why it's so difficult to print out the director name?

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,245
    So...I don't see anywhere in your full code sample where you do the join: you just have separate queries be executed and output. ???
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    May 2014
    Posts
    9
    In this part of code where I'm trying to print out the director name I've tried (except from the code above) this:
    PHP Code:
      //Select data from regissorer-table to print it out once:
      
    $sql "SELECT filmer.*, regissorer.fornamn, regissorer.efternamn
      FROM filmer JOIN regissorer
      ON regissorer.id = filmer.regissor_id"
    ;
      
    $shoot mysql_query($sql);

    while(
    $rad mysql_fetch_array($shoot)) { //While data is in table, loop it out:

        //Here is where I want to print out the director name:
        
    echo '<h2>Regissör: '.$rad['fornamn'].'&nbsp;'.$rad['efternamn'].'</h2>';

    ...this:
    PHP Code:
      //Select data from regissorer-table to print it out once:
      
    $sql "SELECT filmer.*, regissorer.fornamn, regissorer.efternamn
      FROM filmer JOIN regissorer
      ON regissorer.id = filmer.regissor_id WHERE id = 
    $id";
      
    $shoot mysql_query($sql);

    while(
    $rad mysql_fetch_array($shoot)) { //While data is in table, loop it out:

        //Here is where I want to print out the director name:
        
    echo '<h2>Regissör: '.$rad['fornamn'].'&nbsp;'.$rad['efternamn'].'</h2>';

    ...and this:
    PHP Code:
      //Select data from regissorer-table to print it out once:
      
    $sql "SELECT filmer.*, regissorer.fornamn, regissorer.efternamn
      FROM filmer JOIN regissorer
        ON regissorer.id = filmer.regissor_id
          WHERE id = 
    $id
            GROUP BY id"
    ;
      
    $shoot mysql_query($sql);

    while(
    $rad mysql_fetch_array($shoot)) { //While data is in table, loop it out:

        //Here is where I want to print out the director name:
        
    echo '<h2>Regissör: '.$rad['fornamn'].'&nbsp;'.$rad['efternamn'].'</h2>';

    But the closest I get is to print out all director names from the table "regissorer", but I want to print out the director name for the specific movie, only once, and it just doesn't work. :-/

  6. #6
    Join Date
    May 2014
    Posts
    9
    I've also tried this by the way, where I use "regissor_id = $id" instead of "id = $" in the end of the string. But then it prints out one director in some pages, and none in others, and where the names print out it's for the wrong movie anyway, so I just don't know what to do here. :-(

    PHP Code:
    //Select data from regissorer-table to print it out once:
      
    $sql "SELECT * FROM filmer JOIN regissorer
      ON filmer.regissor_id = regissorer.id WHERE regissor_id = 
    $id";
      
    $shoot mysql_query($sql); //Hold selected data from regissorer in a variable

    while($rad mysql_fetch_array($shoot)) { //While data is in table, loop it out:

        
    echo '<h2>Regissör: '.$rad['fornamn'].'&nbsp;'.$rad['efternamn'].'</h2>';


  7. #7
    Join Date
    Dec 2002
    Location
    Seattle, WA
    Posts
    1,843
    hmm... try this, it is a multi-table SELECT. I noticed in the SQL I was referencing, I did not use JOIN.
    Code:
    SELECT f.*, r.fornamn, r.efternamn
      FROM filmer 'f', regissorer 'r'
        WHERE id = $id
          GROUP BY f.id

  8. #8
    Join Date
    May 2014
    Posts
    9
    I tried this now:
    PHP Code:
    $sql "SELECT filmer.*, regissorer.fornamn, regissorer.efternamn FROM filmer 'filmer', regissorer 'regissorer'
      WHERE id = 
    $id GROUP BY filmer.id"
    Still nothing though. :-/

  9. #9
    Join Date
    Dec 2002
    Location
    Seattle, WA
    Posts
    1,843
    I think I figured out where I messed up. You'll need to add to the WHERE clause, the means of filtering the list of "regissorer".
    Do you store an ID of the regissorer in filmer table?
    Code:
    SELECT f.*, r.fornamn, r.efternamn
      FROM filmer 'f', regissorer 'r'
        WHERE id = $id AND r.regissorer_id = f.regissorer
          GROUP BY f.id

  10. #10
    Join Date
    May 2014
    Posts
    9
    I've also tried this now:
    PHP Code:
    $sql "SELECT filmer.*, regissorer.fornamn, regissorer.efternamn
      FROM filmer 'filmer', regissorer 'regissorer'
        WHERE id = 
    $id AND regissorer.regissorer_id = filmer.regissorer
          GROUP BY filmer.id"
    ;
      
    $shoot mysql_query($sql); //Hold selected data from regissorer in a variable

    while($rad mysql_fetch_array($shoot)) { //While data is in table, loop it out:

        
    echo '<h2>Regissör: '.$rad['fornamn'].'&nbsp;'.$rad['efternamn'].'</h2>';

    But unfortunately it still doesn't print out anything. I appreciate your patience and helpfulness, though.

  11. #11
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,245
    How about making things as simple as possible, then finding out what the actual output/response is?
    PHP Code:
    <?php
    // do your database connection/selection stuff, then...

    $id '1'// set this to a known filmer ID value.

    $sql "
    SELECT 
      filmer.*, 
      regissorer.fornamn, 
      regissorer.efternamn
    FROM filmer
    INNER JOIN regissorer ON regissorer.id = filmer.regissor_id
    WHERE filmer.id = 
    $id
    "
    ;

    $result mysql_query($sql);
    if(
    $result == false) {
        die(
    "<pre>".mysql_error().PHP_EOL."$sql</pre>");
    }
    if(
    mysql_num_rows($result) == 0) {
        die(
    "<pre>No rows returned by query.".PHP_EOL.$sql");
    }
    $data = mysql_fetch_all($result);
    echo "
    <pre>".print_r($data, 1)."</pre>";
    Oh, and the usual public service announcement in these cases (e.g., see the manual page for mysql_query):
    Warning

    This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  12. #12
    Join Date
    May 2014
    Posts
    9
    This SQL-code actually did the trick:
    PHP Code:
    SELECT 
      filmer
    .*, 
      
    regissorer.fornamn
      
    regissorer.efternamn
    FROM filmer
    INNER JOIN regissorer ON regissorer
    .id filmer.regissor_id
    WHERE filmer
    .id $id 
    Thank you very much! :-D

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles