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)

    Blocking Database Columns that have No Data

    I'm using the following script to display a dynamic table with sortable columns. But when I sort a column I get a big blank space caused by about two dozen rows that have no data. (For example, Antarctica has no permanent population.)

    How can I modify this script so that a jurisdiction (nation, island, etc.) that has no data doesn't display at all? In other words, I don't even want to see the Antarctica row, because each of its table cells is empty.

    Thanks.

    PHP Code:
    <?php
    $colors 
    = array( '#cfc''''#ff0''''#ccf''''#fc3''' );
    $n=0;
    $size=count($colors);

    $result mysql_query('select count(*) from cia_people');
    if ((
    $result) && (mysql_result ($result 0) > 0)) {
    // continue here with the code that starts
    //$res = mysql_query ("SELECT * FROM type.....
    } else {
    die(
    'Invalid query: ' mysql_error());
    }
    {

    $res mysql_query ("SELECT * FROM cia_people, famarea
     WHERE cia_people.IDArea = famarea.IDArea"
    )
     or die (
    mysql_error());
     
    echo 
    '<table class="sortable" id="tab_cia_people_age">
             <thead>
                <tr><th>Name</th>
                <th>0-14</th><th>M</th><th>F</th>
                <th>15-64</th><th>M</th><th>F</th>
                <th>65+</th><th>M</th><th>F</th>
                <th>Med</th><th>M</th><th>F</th>
                </tr>
             </thead>
             <tbody>'
    ;
    //<!-- BeginDynamicTable -->
    $rowcounter=0;
    while (
    $row mysql_fetch_array ($res)) {
         
    $c=$colors[$rowcounter++%$size];
         echo 
    "<tr style=\"background-color:$c\"><"$_SERVER['PHP_SELF'] .'?id='$row['IDArea'] ."><td class='tdarea'>"$row['Name'] ."</td>
         <td class='numdata'>"
    $row['Age0'] ."%</td>
         <td class='numdata2'>"
    $row['Age0M'] ."</td>
         <td class='numdata3'>"
    $row['Age0F'] ."</td>
         <td class='numdata'>"
    $row['Age15'] ."%</td>
         <td class='numdata2'>"
    $row['Age15M'] ."</td>
         <td class='numdata3'>"
    $row['Age15F'] ."</td>
         <td class='numdata'>"
    $row['Age65'] ."%</td>
         <td class='numdata2'>"
    $row['Age65M'] ."</td>
         <td class='numdata3'>"
    $row['Age65F'] ."</td>
         <td class='numdata'>"
    $row['AgeMedian'] ."%</td>
         <td class='numdata2'>"
    $row['AgeMedianM'] ."</td>
         <td class='numdata3'>"
    $row['AgeMedianF'] ."</td></tr>\n";
    }
    }
    ?>
      </tr>
      </tbody>
      </table>
            </td>
            <td class="tdright" id="tdright<?php echo "$mycode?>">&nbsp;</td>
          </tr>
        </table>
        <!--END TABLEMAIN-->
      </div>
      <!--END BODY3-->
    </div>
    <!--END BODY2-->

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    a lot depends on what is actually in those columns that have "no data"

    something like this --
    Code:
    select * 
      from cia_people
         , famarea 
     where cia_people.IDArea 
         = famarea.IDArea
       and (
           Age0 > 0
        or Age15 > 0
        or Age65 > 0
        or AgeMedian > 0
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, that makes sense for columns filled with numerical data. Unfortunately, it isn't working. I suspect it may because my first column - 'Name" - is filled with text data (the names of nations).

    I suppose one alternative would be to simply nix each individual column I don't want. Suppose I don't want the columns for Antarctica and Svalbard, and their codes are 'ant' and 'sva." How can I write "WHERE .IDArea IS NOT EQUAL TO 'ant' or 'sva'"?

    Thanks.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    where IDArea not in ('ant','sva')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Code:
    where IDArea not in ('ant','sva')
    Hmmm... For some reason, I can't make that work, but I did get your first script to work for numerical values. I just made a stupid mistake the first time. 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
  •