• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

Table configuration in SQL output

I have the following code (sorry if it is gnarly):

- - - - - - - - - - - - - - -
$sql='SELECT targ, count(*) as cnt FROM tracker WHERE user ="'.$userid.'" GROUP BY targ'; // WHERE user ="'.$userid.'"';
$result=mysql_query($sql);      
$count=mysql_num_rows($result);      

echo '<table border="1">';
echo '<tr><td colspan="3">'.$userid.' Data to go here...</td></tr>';
   
    while($rows=mysql_fetch_array($result)){            
          echo '<tr>';
          echo '<th>';
                          if($rows['targ']==1){ echo 'Partner';};
                        if($rows['targ']==2){ echo 'Family';};
                        if($rows['targ']==3){ echo 'Friend';};
                        if($rows['targ']==4){ echo 'Acquaintence';};
                        if($rows['targ']==5){ echo 'Institution';};
                        if($rows['targ']==6){ echo 'Stranger';};
                        $target=$rows['targ'];
          echo '</th>';
      
                    
                   $sqlb='SELECT * FROM tracker WHERE targ="'.$target.'" AND user ="'.$userid.'" ORDER BY postd';
                   // $sqlb='SELECT * FROM tracker WHERE targ="'.$target.'" AND user ="'.$userid.'" AND postd ="'.$countday.'" ORDER BY postd';
                  $resultb=mysql_query($sqlb);      
                  $countb=mysql_num_rows($resultb);      
                    // $countday=1;
                  while($rows=mysql_fetch_array($resultb)){
                        // $countday=$countday+1;
                        echo '<td>';
                      echo $countb;
                      echo '</td>';
                  }  
          echo '</tr>';
      }
                    echo '<tr>';
                          echo '<td>&nbsp;</td>';
                          $sqlc='SELECT postd, count(*) as cnt FROM tracker WHERE user ="'.$userid.'" GROUP BY postd';
                        $resultc=mysql_query($sqlc);      
                        $countc=mysql_num_rows($resultc);
                              while($rows=mysql_fetch_array($resultc)){
                              echo '<td>';
                              echo $rows['postd'];
                              echo '</td>';
                              }
                        echo '</tr>';

echo '</table>';
- - - - - - - - - - - - -  -

What I need is a table output like this:

TABLE --------------------------
day of month----1 - 2 - 3 - 4 - 5 (1-30 days of the month)
partner ---------- 4 - 5 - 4 - 3 - 1 (total number (count,not sum) of "1" entries on each particular day)
family ----------- 4 - 5 - 0 - 3 - 1 (total number of "2" entries on each particular day)
friend ----------- 1 - 5 - 2 - 7 - 0 (total number of "3" entries on each particular day)
Acquaintance - same      "      "      "      "
Institution -  same      "      "      "      "
Stranger -  same      "      "      "      "
-----------------------------------
As you can see - 1 is converted to Partner, 2 to Family, etc.

The image I have attached shows the currents code output  and also shows my current sql table...

I am a bit green but feel my code is getting too complicated. Is there an easier way...
table-output.jpg
0
edavo
Asked:
edavo
  • 10
  • 7
1 Solution
 
lwadwellCommented:
This is a bit of an overhaul ... have a read through and feel free to ask questions.  It is largely untested - but I added comments to help.

// Specify the year and month to be displayed
$month = "08";
$year  = "2012";
$days_in_month = date("t", strtotime(sprintf("%d-%02d-01", $year, $month)));

// Initialise a two dimensional array for the data
// The array will be like:
// [$i]:
//   1  [$j]: 1 2 3 4 5 6 7 ... 30/31 (or 28 in Feb)
//   2  [$j]: 1 2 3 4 5 6 7 ... 30/31
//   ...
//   6  [$j]: 1 2 3 4 5 6 7 ... 30/31
$data_array = array();
for ($i=1; $i<=6; $i++) {
    $data_array[$i] = array();
    for ($j=1; $j<=$days_in_month; $j++) {
        $data_array[$i][$j] = 0;
    }
}

// Extract the data
$sql = <<<ENDOFSQL
SELECT targ, postd, count(*) as cnt 
FROM tracker 
WHERE user ='$userid'
AND postm = '$month'
AND pasty = '$year'
GROUP BY targ, postd';
ENDOFSQL;
$result=mysql_query($sql);      
$count=mysql_num_rows($result);      
while($rows=mysql_fetch_array($result)){            
    $i = $rows['targ'];
    $j = $rows['postd'];
    $data_array[$i][$j] = $rows['cnt'];
}

// Print the table
echo '<table border="1"><thead><tr><td>Category</td>',PHP_EOL;
for ($j=1; $j<=$days_in_month; $j++) {
    echo '<td>',$i,'</td>';
}
echo '</tr></thead><tbody>',PHP_EOL;
for ($i=1; $i<=6; $i++) {
    echo '<tr><td>';
    if($i==1){ echo 'Partner';};
    if($i==2){ echo 'Family';};
    if($i==3){ echo 'Friend';};
    if($i==4){ echo 'Acquaintence';};
    if($i==5){ echo 'Institution';};
    if($i==6){ echo 'Stranger';};
    echo '</td>';
    for ($j=1; $j<=$days_in_month; $j++) {
        echo '<td>',$data_array[$i][$j],'</td>';
    }
    echo '</tr>';
}
echo '</tbody></table>';

Open in new window

0
 
edavoAuthor Commented:
Whew - thats throwing me in the deep end :-) But Ill try to swim. I have tried it and it is close but see the attached image to see the result... I appreciate the time you put into this... but I am a bit lost... Perhaps I am asking too much?
table-code.jpg
0
 
lwadwellCommented:
What you are asking for is actually quite simple.

It seems to be populated a bit strange ... yes.  Did you change the code in any way?  If changed I cannot guess the effects.

Also - I was not able to test the SQL statement.  It would be great if you could run it by itself.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
lwadwellCommented:
I found one bug, this loop used $i instead of $j - change to $j (this caused the row of 7's)
for ($j=1; $j<=$days_in_month; $j++) {
    echo '<td>',$j,'</td>';
}
0
 
lwadwellCommented:
Oh ... and a typo in SQL statement ... I mistyped 'posty' as 'pasty' (hmmm Freudian slip...?)
0
 
edavoAuthor Commented:
Well I think we are very close. See the attached image. It has the "current" output and then the "desired" output.

Pay particular attend to the days between the 11 and 18 as that is where the only data is in the test database. The number represents the Total number of entries on that day, per row category of course

I will run some tests in SQL as you have suggested. If you see anything else - let me know. I appreciate your efforts...
table-desired.jpg
0
 
edavoAuthor Commented:
It is also important to note that if a "day" has no entries, it should still have a column, with a "0" in the column, we dont want it to omit a day if there are no entries on that day...
0
 
lwadwellCommented:
The SQL is failing or something is not right with how the array is being updated.
I have two suggestions:
  1. Add error checking on the query to ensure it worked in PHP
  2. FOR DEBUGGING ONLY (remove once working) add some extra outputs to see what is going on ... I suggest a 'print_r' after the array is updated + the num_rows.
Snippet from larger script below:
// Extract the data
$sql = <<<ENDOFSQL
SELECT targ, postd, count(*) as cnt 
FROM tracker 
WHERE user ='$userid'
AND postm = '$month'
AND pasty = '$year'
GROUP BY targ, postd';
ENDOFSQL;
$result=mysql_query($sql);
// Check if query failed and report error if it did
if ( !$result ) {
    $err = "Query Failed: " . $sql . ' Error: (' . mysql_errno() . ') ' . mysql_error();
    die($err);
}
$count=mysql_num_rows($result);      
print_r($count); // debugging only
while($rows=mysql_fetch_array($result)){            
    $i = $rows['targ'];
    $j = $rows['postd'];
    $data_array[$i][$j] = $rows['cnt'];
}
print_r($data_array); // debugging only

Open in new window

If you post the output from the debugging print_r's I would be happy to look and see if I see a problem.
0
 
edavoAuthor Commented:
Image screen grab attached...
table-print.jpg
0
 
lwadwellCommented:
Did the query return any data?  By that screen shot I am guessing that it didn't.
0
 
edavoAuthor Commented:
I ran :

SELECT targ, postd, count(*) as cnt FROM tracker WHERE user ='davo' AND postm = '08' AND posty = '2012' GROUP BY targ, postd

in sql (admin) and got this as output:

see image attached
output.jpg
0
 
lwadwellCommented:
Did you put in the error checking for the SQL like I suggested?  I built a dummy table to run the code against and it identified a syntax error ... a trailing single quote mark on the end of the SQL statement.
$sql = <<<ENDOFSQL
SELECT targ, postd, count(*) as cnt 
FROM tracker 
WHERE user ='$userid'
AND postm = '$month'
AND pasty = '$year'
GROUP BY targ, postd;
ENDOFSQL;

Open in new window

0
 
edavoAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for edavo's comment #a38319404

for the following reason:

genius with patience for a newbie and fortitude!
0
 
lwadwellCommented:
Did you mean to close this by accepting your own answer?
0
 
lwadwellCommented:
I believe that I made significant contribution to the solution.
0
 
edavoAuthor Commented:
It was closed inadvertently - lwadwell completely provided the solution... he was patient and very helpful - single handedly solving the problem.
0
 
lwadwellCommented:
You did help.  Thanks for sorting it out.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now