SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Why is my place name data repeating?

    I'm trying to display data from two tables - symbols2 and symbolstree - so that visitors get a list of symbols in one column and a list of places each symbol represents in the other column. For example:

    United States | bald eagle
    Ecuador, Peru, Bolivia | Andean condor

    It works fine on one of my pages, but it fell apart when I tried to adapt it to a second set of data focusing on mineral symbols. At first, it correctly displayed all the places represented by a particular symbol, but it didn't include rows for other symbols that should have appeared with it. Then I tweaked a few settings and wound up with the opposite problem - it lists all the symbols it's supposed to, but the place names for each symbol are the same, like this:

    Alaska, Yukon | gold
    Alaska, Yukon | silver
    Alaska, Yukon | jade

    When it should look something like this:

    Alaska, Yukon | gold
    Nevada | silver
    Alaska, Wyoming | jade

    Anyway, I wonder if someone can spot any obvious problems in my script.

    PHP Code:
    <?php
    $result 
    mysql_query('select count(*) from symbols2');
    if ((
    $result) && (mysql_result ($result 0) > 0)) {
    } else {
    die(
    'Invalid query: ' mysql_error());
    }
    {
    if ( 
    mysql_errno() > ) {
      echo 
    mysql_error();
      exit;
    }
    else {

    echo 
    '<table">
    <thead>
    <tr><th>Places</th>
    <th>Minerals</th></tr>
    </thead>
    <tbody>'
    ;
    //<!-- BeginDynamicTable -->
    {

    while ( 
    $row mysql_fetch_array$res ) ) {

     
    $areas = array();
      
    $area_res mysql_query("SELECT DISTINCT Area FROM symbols2 S2
      WHERE `Order` = '
    $MyName'
      AND DesigGeneral != ''"
    );
      while (
    $area_row mysql_fetch_array$area_res ) )
      {
    $areas[] = $area_row['Area'];}
      
    $areas htmlspecialchars(implode(', '$areas));

    $Name2 $row['Name2'];
    echo <<<EOD
       <tr>
         <td">
    {$areas}</td>
         <td>
    {$row["Name2"]}</td>
       </tr>
    EOD;
    }
    }
    }
    }
    ?>
    </tbody>
    </table>
    The problem could also relate to a couple scripts that are included higher up the food chain:

    PHP Code:
    $SymSpecies mysql_fetch_assoc(mysql_query("SELECT DISTINCT * FROM symbolstree ST
      INNER JOIN symbols2 S2 ON S2.DesigGroup = ST.Name2 OR S2.Class = ST.Name2 OR S2.Order = ST.Name2 OR S2.Family = ST.Name2
      OR S2.Genus = ST.Name2 OR S2.Latin = ST.Name2
      WHERE ST.Name2 LIKE '
    $MyName'"));


    $res mysql_query("SELECT DISTINCT * FROM symbolstree ST
     WHERE Parent = '
    $MyName'"); 
    Thanks.

  2. #2
    Maniacally depressed robot poncho's Avatar
    Join Date
    Dec 2004
    Location
    Belfast, N.Ireland
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure if there is anything else wrong, but I just spotted your mysql_fetch_array function was being passed a non-existing variable. $res

    Code:
     $result = mysql_query('select count(*) from symbols2'); 
     if (($result) && (mysql_result ($result , 0) > 0)) { 
     } else { 
     die('Invalid query: ' . mysql_error()); 
     } 
     { 
     if ( mysql_errno() > 0 ) { 
       echo mysql_error(); 
       exit; 
     } 
     else { 
      
     echo '<table"> 
     <thead> 
     <tr><th>Places</th> 
     <th>Minerals</th></tr> 
     </thead> 
     <tbody>'; 
     //<!-- BeginDynamicTable --> 
     { 
      
     while ( $row = mysql_fetch_array( $result ) ) { 
      
      $areas = array(); 
       $area_res = mysql_query("SELECT DISTINCT Area FROM symbols2 S2 
       WHERE `Order` = '$MyName' 
       AND DesigGeneral != ''"); 
       while ($area_row = mysql_fetch_array( $area_res ) ) 
       {$areas[] = $area_row['Area'];} 
       $areas = htmlspecialchars(implode(', ', $areas)); 
      
     $Name2 = $row['Name2']; 
     echo <<<EOD 
        <tr> 
     	 <td">{$areas}</td> 
     	 <td>{$row["Name2"]}</td> 
        </tr> 
     EOD;
     } 
     } 
     } 
     }
    Cheers;
    Poncho
    Perfecting the art of breaking stuff.
    Check 'em: CakePHP | TextMate

  3. #3
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, $res is included in a script on the home page:

    PHP Code:
    $res mysql_query("SELECT DISTINCT * FROM symbolstree ST
     WHERE Parent = '
    $MyName'"); 
    If I delete that script, I get an error that says it is not a "valid MySQL resource."

    Thanks.

  4. #4
    Maniacally depressed robot poncho's Avatar
    Join Date
    Dec 2004
    Location
    Belfast, N.Ireland
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not have a look at simpliying your SQL into a JOIN query? Have a search on the forums for "SQL+JOIN".

    Cheers;
    Poncho
    Perfecting the art of breaking stuff.
    Check 'em: CakePHP | TextMate

  5. #5
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I joined it like this:

    PHP Code:
    while ( $row mysql_fetch_array$res ) ) {
     
    $areas = array(); 
       
    $area_res mysql_query("SELECT DISTINCT Area FROM symbols2 S2 
       LEFT JOIN symbolstree ST on ST.Parent = S2.Latin
       WHERE `Order` = '
    $MyName' AND DesigGeneral != ''"); 
       while (
    $area_row mysql_fetch_array$area_res ) ) 
       {
    $areas[] = $area_row['Area'];} 
       
    $areas htmlspecialchars(implode(', '$areas)); 
    Then I tried replacing $row["Name2"] with $area_res or $area_row["Name2"], but neither one works.

    PHP Code:
    echo <<<EOD
       <tr>
         <td>
    {$areas}</td>
         <td>
    {$area_res["Name2"]}{$area_row["Name2"]}{$row["Name2"]}</td>
       </tr>
    EOD;
    }
    }
    }

    Does anyone know how I can rewrite $areas = array(); so it handles both columns, allowing me to ditch all the extra baggage?

    Thanks.


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
  •