#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    721
    Rep Power
    7

    Help w/ output from a while() loop


    Well, I use this same function through my entire site to build the main tables in each module. The basic idea of it is simple and has worked just fine. I am now on a module whose table is visually layed out as a basic table, but the build of columns 6-16 are giving me a loss in thought.

    The first 5 columns display in same order selected from the database. Only 1 column in 6-9 and 12-16 will hold a value from $col6. $col5 holds the category ID of that column, not the column number. I need to figure out how to make the value of $col6 print to the correct column.

    My categories IDs are like 1-10, but I can alter them to hold perhaps the column number it would be to match, such as 6-15, if that would work for anything.

    The focus is the first 'echo' in the while() loop.
    PHP Code:
    function buildListing() {
      global 
    $link;
      
    $statement $link->prepare("SELECT expensereports.`ID`, expensereports.`DateBegin`, expensereports.`CustomerID`, expensereports.`JobNumber`, expensereports.`Technician`, expensereports.`Category`, expensereports.`Cost` FROM `expensereports`");
      
    $statement->execute();
      
    $statement->bind_result($col0,$col1,$col2,$col3,$col4,$col5,$col6);
      
    $statement->store_result();
      
    $count $statement->num_rows;
      
    $i 1;
      while (
    $statement->fetch()) {
        if(
    $i != 1) {
          echo 
    '<TR class="highlightable" href="?loc=exre&action=view&id=' $col0 '">
                  <TD>' 
    $col0 '</TD>
                  <TD>' 
    $col1 '</TD>
                  <TD>' 
    $col2 '</TD>
                  <TD>' 
    $col3 '</TD>
                  <TD>' 
    $col4 '</TD>
                  <TD>' 
    $col5 '</TD>
                  <TD>' 
    $col6 '</TD>
                  <TD>' 
    $col7 '</TD>
                  <TD>' 
    $col8 '</TD>
                  <TD>' 
    $col9 '</TD>
                  <TD>' 
    $col10 '</TD>
                  <TD>' 
    $col11 '</TD>
                  <TD>' 
    $col12 '</TD>
                  <TD>' 
    $col13 '</TD>
                  <TD>' 
    $col14 '</TD>
                  <TD>' 
    $col15 '</TD>
                </TR>' 
    PHP_EOL;
          
    $i++;
        } else {
          echo 
    '<TR class="highlightable" href="?loc=exre&action=view&id=' $col0 '"><TD>' $col0 '</TD><TD>' $col1 '</TD><TD>' $col2 '</TD><TD>' $col3 '</TD><TD>' $col4 '</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR>' PHP_EOL;
          
    $i++;
        }
      }
      
    $statement->close();

    Last edited by Triple_Nothing; September 30th, 2013 at 01:31 PM.
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  2. #2
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    I read your post three times and actually said "huh!?" loud enough for my coworkers to ask me what I was reading.

    Show some example data of 3-4 rows, with their proper output, and why it should output that way.

    Edit: The questions I need answered:
    1) What about columns 9 and 10?
    2) What's the category ID and why is it relevant?
    3) Are you saying that database column 5 is used to determine the actual HTML column position of the data in database column 6? If so, is column 5 supposed to be displayed at all?
    4) Why are you using unnamed variables here and no loops?
    5) Where do you expect the values of $col7-$col15 to come from if they don't exist anywhere?
    6) This code itself is actually invalid entirely, your $col values don't get set anywhere. Did you trim that bit?
    Last edited by ManiacDan; September 30th, 2013 at 01:45 PM.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    721
    Rep Power
    7
    Ok. The overall project is me writing a site from an old Access file the company abandoned due to issues, but wishes to re-use if it can be fixed and upgraded. I have attached 4 images, 2 from Access, 2 from the MySQL database created from it.

    AccessMain:
    This image is actually the one that will use the buildListing() function. I believe it will be most difficult because it will need to do additions to build each row.

    AccessDetailed:
    This image is what an individual will see after clicking an item off the main image. This will be an internal page breaking down a single instance of an employee's job. Each column here is to be added to build the total under the job's row in the AccessMain.

    PMAMain:
    This image is from the table that holds the report's main info.

    PMAItems:
    This image is from the table that holds each transaction made, and ERID is the ID of its report from the Main table.
    Attached Images
    Last edited by Triple_Nothing; September 30th, 2013 at 02:44 PM.
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    721
    Rep Power
    7
    1) What about columns 9 and 10?
    Some culumns will be generated on-the-fly. Like Per Diem is .55, so if col9 is 7, col 10 would generate to $3.85
    2) What's the category ID and why is it relevant?
    Category ID is my first thought to attach/reference. $CatID 3 may reference Taxi since it's the 3rd cost column.
    3) Are you saying that database column 5 is used to determine the actual HTML column position of the data in database column 6? If so, is column 5 supposed to be displayed at all?
    Correct. It is meerly holding the column position. I placed variables in each <TD> just to visually offer column reference.
    4) Why are you using unnamed variables here and no loops?
    As per the $col0, $col1...? I just name all my database ones like that in reference to the order selected.
    No loops? This is in a while loop like I build most. Wasn't sure how to build perhaps an internal loop.
    5) Where do you expect the values of $col7-$col15 to come from if they don't exist anywhere?
    There are as many columns to fill as categories.
    EDIT: I misunderstood the question. FINAL variables won't truely be named $col7-$col15 Was just a visual for forums to offer reference
    6) This code itself is actually invalid entirely, your $col values don't get set anywhere. Did you trim that bit?
    Set via: $statement->bind_result($col0,$col1,$col2,$col3,$col4,$col5,$col6);
    Last edited by Triple_Nothing; September 30th, 2013 at 02:50 PM.
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    721
    Rep Power
    7
    I guess I should say alot is missing here. I am not troubleshooting errors, just at a loss on how to continue. I will have to use a JOIN to connect the 2 database tables. That I do know. The initial question is just over 2 variables, really.

    $columnNumber and $cost

    So if a table had 10 columns, what would be the best way to use the value of $columnNumber to place $cost ?

    And as per the Main one, I do know kinda how to select items in groups, but the SELECT cat, cost FROM items WHERE ERID = main.ID, then group those 1 category at a time, add each sategory, assign them to their correct column, then continue to the next row is where I'm at loss.


    EDIT: And these are 2 different pages. The one listing the deailed 1 item at a time is an internal page after clicking the main report.

    EDIT2: I figured out this will group and add kinda as needed. Now I just gotta figure out how to have it place the values in the correct column.
    PHP Code:
    $statement $link->prepare("SELECT CatID, SUM(Cost) FROM expenseitemsrebuilt WHERE ERID = ' . $col5 . ' GROUP BY CatID"); 
    Last edited by Triple_Nothing; September 30th, 2013 at 03:14 PM.
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    721
    Rep Power
    7
    I do know the 1st query is plenty for me to successfully build the first 5 columns on my table. Now, how to build the totals columns. The 2 queries below fetch EVERYTHING needed from the database. Now, I am down to just needing help with the final thing. Telling it which column to place SUM(Cost) in based on the integer value of CatID. 1 = Food, 2 = Gas, 3 = Electric, etc...
    Code:
    SELECT e.`ID` , e.`Begin` , c.`Customer Name` , e.`JobNumber` , u.`EmpName` 
    FROM expensereportsrebuilt AS e
    INNERJOIN customers AS c ON e.CID = c.ID
    INNERJOIN userpass AS u ON e.TechID = u.ID
    
    $col0 below equals e.`ID` from above
    
    SELECT CatID, SUM(Cost)
    FROM expenseitemsrebuilt
    WHERE ERID = ' . $col0 . '
    GROUP BY CatID
    Last edited by Triple_Nothing; September 30th, 2013 at 04:56 PM.
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    721
    Rep Power
    7
    Sweet. I think I got the idea to my answer, if someone can help me build this array...

    Once this loop is done, the array should hold 10 items...

    PHP Code:
    $totals = array();
    for(
    $i 0$i 10$i++) {
      
    $totals[$i] = $colB//SUM(Cost) from the query

    Now, the thing I can't figure out is the MySQL query may only return the amount of columns in which hold any expenses. I figure this will build an array I can easily assign to the correct columns for output, but I need the values to land correctly in the array. Any ideas how if CatID equals 7, the value of SUM(Cost) will land in $totals[6] ?

    Keep in mind, and can always adjust CatID numbers if needed, such as drop em all 1 in the database so they match the array's $arr[0] spot.



    SOLUTION:
    PHP Code:
    function buildListing() {
      global 
    $link;
      
    $statement1 $link->prepare("SELECT e.`ID`, e.`Begin`, c.`Customer Name`, e.`JobNumber`, u.`EmpName`, e.`End`, e.`MileageRate`, e.`PerDiemRate` FROM expensereportsrebuilt AS e INNER JOIN customers AS c ON e.`CID` = c.`ID` INNER JOIN userpass AS u ON e.`TechID` = u.`ID`");
      
    $statement1->execute();
      
    $statement1->bind_result($col0,$col1,$col2,$col3,$col4,$col5,$col6,$col7);
      
    $statement1->store_result();
      while (
    $statement1->fetch()) {
        
    $start DateTime::createFromFormat('Y-m-d'$col1);
        
    $end DateTime::createFromFormat('Y-m-d'$col5);
        
    $pDays $start->diff($end);
        
    $pCost $pDays->format('%a') * $col7;
        
    $totals array_fill(010'');
        
    $statement2 $link->prepare("SELECT `CatID`, SUM(`Cost`) FROM expenseitemsrebuilt WHERE `ERID` = ? GROUP BY `CatID`");
        
    $statement2->bind_param('i'$col0);
        
    $statement2->execute();
        
    $statement2->bind_result($colA,$colB);
        while (
    $statement2->fetch()) {
          
    $totals[$colA] = $colB;
        }
        
    $statement2->close();
        
    $mCost $totals[4] * $col6;
        echo 
    '<TR class="highlightable" href="?loc=exre&action=view&id=' $col0 '"><TD>' $col0 '</TD><TD>' $col1 '</TD><TD>' $col2 '</TD><TD>' $col3 '</TD><TD>' $col4 '</TD><TD>' $totals[0] . '</TD><TD>' $totals[1] . '</TD><TD>' $totals[2] . '</TD><TD>' $totals[3] . '</TD><TD>' $totals[4] . '</TD><TD>' $mCost '</TD><TD>' $pDays->format('%a') . '</TD><TD>' $pCost '</TD><TD>' $totals[5] . '</TD><TD>' $totals[6] . '</TD><TD>' $totals[7] . '</TD><TD>' $totals[8] . '</TD><TD>' $totals[9] . "</TD></TR>\n";
      }
      
    $statement1->close();

    Last edited by Triple_Nothing; September 30th, 2013 at 11:09 PM.
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him

IMN logo majestic logo threadwatch logo seochat tools logo