SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Oct 2007
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with PHP table.

    I have a database that is searching 3 tables on an inner join. I want to create a table that looks like this.

    NAME/DATE | 10/15/07 | 10/16/07 | 10/17/07 | 10/18/09..... | 10/23/07
    ---------------|------------|------------|------------|--------------|----------------
    AuthorName | Client-Time | Client-Time | Client-Time | Client-Time | Client-Time
    ---------------|-------------|-------------|--------------|--------------|-------------
    AuthorName2 | Client-Time | Client-Time | Client-Time | Client-Time | Client-Time
    |Client-Time | | Client-Time | | |
    -----------------------------------------------------------------------------------

    SQL is this

    SELECT tblAuthors.AuthorName, tblTasks.ActionDate, tblClients.ClientName, tblTasks.TimeLength, tblTasks.TaskName FROM tblAuthors INNER JOIN tblTasks ON (tblAuthors.ID = tblTasks.NowResponsible) INNER JOIN tblClients ON (tblTasks.ClientID = tblClients.ID) WHERE (tblTasks.ActionDate BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 10 DAY)) ORDER BY tblAuthors.AuthorName, tblTasks.ActionDate, tblClients.ClientName

    I have searched all over the net and have not found anything similar to this. I know it is a custom script, but I need a starting point as to where to find something like it. I do NOT code much. Most of my work is done in Dreamweaver, so obviously I use alot of extensions. Any help would be appreciated.

  2. #2
    SitePoint Enthusiast shredder-man's Avatar
    Join Date
    Sep 2007
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So is your query even working properly? Do you just want to know how to display and manipulate the query results into a table? I'm not sure I understand your question.

  3. #3
    SitePoint Member
    Join Date
    Oct 2007
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by shredder-man View Post
    So is your query even working properly? Do you just want to know how to display and manipulate the query results into a table? I'm not sure I understand your question.

    Yes the query works properly. I just want to know how to display & manipulate the query results into a table. Thanks for your quick response.

  4. #4
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    177
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fetch them with a mysql_fetch_array using a while loop and a list, like this:
    PHP Code:
    while (list($item_1$item_2$item_3, ..., $item_n) = mysql_fetch_array($my_query)) {
      echo 
    '<tr><td>' $item_1 '</td><td>' $item_2 '</td></tr>';


  5. #5
    SitePoint Member
    Join Date
    Oct 2007
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay - being a complete idiot - I don't get it.

    Heres the code. <?php require_once('Connections/KBC.php'); ?>
    <?php
    mysql_select_db($database_KBC, $KBC);
    $query_rsTasks = "SELECT tblAuthors.AuthorName, tblTasks.ActionDate, tblClients.ClientName, tblTasks.TimeLength, tblTasks.TaskName FROM tblAuthors INNER JOIN tblTasks ON (tblAuthors.ID = tblTasks.NowResponsible) INNER JOIN tblClients ON (tblTasks.ClientID = tblClients.ID) WHERE (tblTasks.ActionDate BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 10 DAY)) ORDER BY tblAuthors.AuthorName, tblTasks.ActionDate, tblClients.ClientName";
    $rsTasks = mysql_query($query_rsTasks, $KBC) or die(mysql_error());
    $row_rsTasks = mysql_fetch_assoc($rsTasks);
    $totalRows_rsTasks = mysql_num_rows($rsTasks);
    while (list($item_1, $item_2, $item_3, ..., $item_n) = mysql_fetch_array($my_query)) { echo '<tr><td>' . $item_1 . '</td><td>' . $item_2 . '</td></tr>';}
    ?>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Untitled Document</title>

    </body>
    </html>
    <?php
    mysql_free_result($rsTasks);
    ?>

    What goes in the $item area?

  6. #6
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    177
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's a variable containing each field's information. $item_1 is a variable containing information of the first field (tblAuthors.AuthorName), $item_2 is for the second one (tblTasks.ActionDate), etc. You can name them whatever you want, though.

  7. #7
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
    /**
    * Encode each value of an array for HTML display
    * @param array $array
    * @return array
    */
    function encode_array($array) {
        foreach(
    $array as &$el) {
            
    $el htmlspecialchars($elENT_QUOTES);
        }
        return 
    $array;
    }


    mysql_select_db($database_KBC$KBC);

    $query_rsTasks "SELECT tblAuthors.AuthorName, tblTasks.ActionDate, tblClients.ClientName, tblTasks.TimeLength, tblTasks.TaskName FROM tblAuthors INNER JOIN tblTasks ON (tblAuthors.ID = tblTasks.NowResponsible) INNER JOIN tblClients ON (tblTasks.ClientID = tblClients.ID) WHERE (tblTasks.ActionDate BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 10 DAY)) ORDER BY tblAuthors.AuthorName, tblTasks.ActionDate, tblClients.ClientName";

    $rsTasks mysql_query($query_rsTasks$KBC) or die(mysql_error());

    $tasks "<table>
    <thead>
        <th>Author Name</th>
        <th>Action Date</th>
        <th>Client Name</th>
        <th>etc....</th>
    </thead>
    <tbody>"
    ;

    while(
    $r mysql_fetch_assoc($rsTasks)) {
        
    $r encode_array($r);
        
    $tasks.= "<tr>
            <td>
    $r[AuthorName]</td>
            <td>
    $r[ActionDate]</td>
            <td>
    $r[ClientName]</td>
            <td>etc....</td>
        </tr>\n"
    ;
    }
    $tasks.= "</tbody></table>";

    //echo $tasks where you want the table to appear in your document
    ?>
    I've demonstrated with only the first few fields. Add all of yours to the table definition, and the while loop.

  8. #8
    SitePoint Member
    Join Date
    Oct 2007
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First thanks to both of you for your help. It has been a great learning experience. Here is what I came up with for my script, BUT it is the wrong direction.

    <?php require_once('Connections/KBC.php'); ?>
    <?php
    mysql_select_db($database_KBC, $KBC);
    $query_rsTasks = "SELECT tblAuthors.AuthorName as Name, tblTasks.ActionDate as Date, tblClients.ClientName as Client, tblTasks.TimeLength as Time, tblTasks.TaskName FROM tblAuthors
    INNER JOIN tblTasks ON (tblAuthors.ID = tblTasks.NowResponsible)
    INNER JOIN tblClients ON (tblTasks.ClientID = tblClients.ID)
    WHERE (tblTasks.ActionDate BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 10 DAY))
    ORDER BY tblAuthors.AuthorName, tblTasks.ActionDate, tblClients.ClientName";
    $rsTasks = mysql_query($query_rsTasks, $KBC) or die(mysql_error());
    $row_rsTasks = mysql_fetch_assoc($rsTasks);
    $totalRows_rsTasks = mysql_num_rows($rsTasks);

    ?>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Untitled Document</title>
    <style type="text/css">
    <!--
    .style8 {font-family: Arial, Helvetica, sans-serif; font-weight: bold; font-size: 10pt; }
    .style10 {font-family: Arial, Helvetica, sans-serif; font-size: 10pt; }
    -->
    </style>
    <body><table width="100&#37;" border="0" cellspacing="0" cellpadding="3">
    <tr>
    <td><span class="style8">Name</span></td>
    <td><span class="style8">Date</span></td>
    </tr>
    <?php
    while($rows=mysql_fetch_array($rsTasks)){ // Start looping table row
    ?>
    <tr>
    <td><span class="style10"><? echo $rows['Name']; ?></span></td>
    <td><span class="style10"><? echo $rows['Client']; ?> - <? echo $rows['Time']; ?></span></td>
    </tr>
    <?php
    // Exit looping and close connection
    }
    mysql_close();
    ?>

    I want the table to have a column header with dates accross the top.


    | Date | Date | Date | Date | Date |
    AuthorName | Task | Task | Task | Task | Task |
    AuthorName | Task | Task | Task | Task | Task |

    I can't figure out how to get the dates across the top and do the same with the task below. Right now is is set up as a typical table. It needs to be more like a gridview or pivot table.


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
  •