Passing data to function

[thenorman138]

I'm refactoring an old php file for a site that loads simple data to an html table.

 

I have an existing set of functions that load data and create the html for it.

I'm now creating almost identical functions to load and display html for a different set of data but the structure is very similar. However, the new functions are not loading to the table and the html table currently loads with empty cells.

 

Now, if I put hard-coded values into the html, they of course show fine. Also, if I run the below union queries in MySQL workbench with a specific dealer_id I get this array:

 

    (
    [period] => CY_YTD
    [count] => 41
    )
    Array
    (
    [period] => PY_YTD
    [count] => 28
    )
    Array
    (
    [period] => PY_FULL
    [count] => 52
    )

 

So the query seems to pull the data correctly. I feel like something is going wrong between loading the data and passing to the HTML function.

 

Here's the code:

 

    private function loadPlacement($custno){


$custno += 0;
$this->mysqlConnect();


$sql1 = " 
SELECT 'CY_YTD' as period, count(*) AS count
        FROM placements_new n
            inner join dealers d
            on n.dealer_id = d.dealer_num
     WHERE dealer_id = {$custno}
     and n.start_date between '{$this->cy_from}' and '{$this->cy_thru}'
     union all
     SELECT 'PY_YTD' as period, count(*) AS count
        FROM placements_new n
            inner join dealers d
            on n.dealer_id = d.dealer_num
     WHERE dealer_id = {$custno} 
     and n.start_date between '{$this->py_from}' and '{$this->py_thru}'
     union all
     SELECT 'PY_FULL' as period, count(*) AS count
        FROM placements_new n
            inner join dealers d
            on n.dealer_id = d.dealer_num
     WHERE dealer_id = {$custno} 
     and n.start_date between '{$this->py_from}' and '{$this->py_yearend}'";


$result1 = mysql_connect($this->mysqli, $sql1);
// print_r(mysqli_fetch_assoc($result1))


$this->placeSnap = array();


while ($row1 = mysqli_fetch_assoc($result1)) {
$this->placeSnap[$row1['PERIOD']] = $row1['count'];
$this->isloaded = true;
}


if ($this->placeSnap['PY_YTD'] == 0) {
$this->placeSnap['PCT'] = 0;
} else {
$this->placeSnap['PCT'] = ($this->placeSnap['CY_YTD']-$this->placeSnap['PY_YTD']) / $this->placeSnap['PY_YTD'];
$this->placeSnap['PCT'] = round( $this->placeSnap['PCT'] * 100, 0);
$this->placeSnap['PCT'] = min(array($this->placeSnap['PCT'], 999));
}
    }




    /**
* HTML for placement snapshot table
* 
* @param int $custno
* @param string $header
* @return string
*/
function getPlacementSnapshotHTML($custno, $header='Placement Snapshot') {


$this->loadPlacement($custno);


$h1 = "<table class='customer-volume-snapshot-table'>";
$h1 .= "<thead><tr><th colspan='2'>" . htmlspecialchars($header) . "</th></tr></thead>";
$h1 .= "<tbody>";
$h1 .= "<tr><td>{$this->pyyy} YTD</td><td style='text-align: right;'>{$this->placeSnap['PY_YTD']}</td></tr>";
$h1 .= "<tr><td>{$this->yyyy} YTD</td><td style='text-align: right;'>{$this->placeSnap['CY_YTD']}</td></tr>";
$h1 .= "<tr><td>Percent Change</td><td style='text-align: right;'>{$this->placeSnap['PCT']}%</td></tr>";
$h1 .= "<tr><td>{$this->pyyy} Full Year</td><td style='text-align: right;'>{$this->placeSnap['PY_FULL']}</td></tr>";
$h1 .= "</tbody></table>";


return $h1;
}

 

[requinix]

1. You're mixing mysql and mysqli functions. Don't do that.

2. mysql(i)_connect is for connecting to the database. Not executing queries.

 

Take a closer look at the code you were copying and look for all the places you changed and shouldn't have.

[thenorman138]

The other function is pulling the same data just from a different table, so the main difference is table and column names but I double checked everything there that I actually changed

[requinix]

Okay... so that means you fixed the two things I pointed out? If you're still having problems, post your new code.

[thenorman138]

I actually refactored the first function entirely and some of the 2nd one that does the html rendering. Also, we use mysqlConnect in a function at the top of the page to create a mysqli connection. But, I tested everything with the connections and queries to make sure there are no issues there and it went fine. Also, the new queries here return the values expected when I run the file and dump them. At this point it just seems like something isn't passing from one function to the other

private function loadPlacementData($custno) 
{
$this->mysqlConnect();//This is a function created in the file that createds a mysqli connection


$query = "  
SELECT count(*) as current
     FROM placements_new n
            inner join dealers d
            on n.dealer_id = d.dealer_num
    WHERE dealer_id = $custno
    and n.start_date between '2016-01-01' and '2016-08-01'";


$result = $this->mysqli->query($query);


if ($result) {
while ($row = mysqli_fetch_assoc($result)) {
$this->currYTD = $row['current'];


}
$result->free();
}


$query = "  
SELECT count(*) as prior
     FROM placements_new n
            inner join dealers d
            on n.dealer_id = d.dealer_num
    WHERE dealer_id = $custno
    and n.start_date between '2016-01-01' and '2016-10-01'";


$result = $this->mysqli->query($query);


if ($result) {
while ($row = mysqli_fetch_assoc($result)) {
$this->priorYTD = $row['prior'];


}
$result->free();
}


$query = "  
SELECT count(*) as full
     FROM placements_new n
            inner join dealers d
            on n.dealer_id = d.dealer_num
    WHERE dealer_id = $custno
    and n.start_date between '2016-01-01' and '2016-12-31'";


$result = $this->mysqli->query($query);


if ($result) {
while ($row = mysqli_fetch_assoc($result)) {
$this->fullPriorYTD = $row['full'];


}
$result->free();
}
}




public function getPlacementSnapshotHTML($custno){


$this->loadPlacementData($custno);


$CurrentYTDPlacement = $this->currYTD;
$PriorYTDPlacement = $this->priorYTD;
$PriorFullPlacement = $this->fullPriorYTD; 


$h1 = "<table class='customer-volume-snapshot-table'>";
$h1 .= "<thead><tr><th colspan='2'>Placement Snapshot for " . $custno . "</th></tr></thead>";
$h1 .= "<tbody>";
$h1 .= "<tr><td>{$this->pyyy} YTD</td><td style='text-align: right;'>" . $PriorYTDPlacement . "</td></tr>";
$h1 .= "<tr><td>{$this->yyyy} YTD</td><td style='text-align: right;'>" . $CurrentYTDPlacement . "</td></tr>";
$h1 .= "<tr><td>Percent Change</td><td style='text-align: right;'>%</td></tr>";
$h1 .= "<tr><td>{$this->pyyy} Full Year</td><td style='text-align: right;'>" . $PriorFullPlacement . "</td></tr>";
$h1 .= "</tbody></table>";


return $h1;
}

[Barand]

1 ) You are selecting a count() so you will only get a single row returned. Why, then, are using a while loop to to read a single result row?

 

2 ) You need only one query, not three.

 

 

The test data
+----+---------------------+
| id | dtime               |
+----+---------------------+
|  1 | 2018-02-05 00:00:00 |
|  2 | 2018-02-06 00:00:00 |
|  3 | 2018-02-07 00:00:00 |
|  4 | 2018-02-08 00:00:00 |
|  5 | 2018-02-09 00:00:00 |
|  6 | 2018-02-10 00:00:00 |
|  7 | 2018-02-12 00:00:00 |
|  8 | 2018-02-13 00:00:00 |
|  9 | 2018-02-14 00:00:00 |
| 10 | 2018-02-15 00:00:00 |
| 11 | 2018-02-16 00:00:00 |
| 12 | 2018-02-17 00:00:00 |
| 13 | 2018-02-19 00:00:00 |
| 14 | 2018-02-20 00:00:00 |
| 15 | 2018-02-21 00:00:00 |
| 16 | 2018-02-22 00:00:00 |
| 17 | 2018-02-23 00:00:00 |
| 18 | 2018-02-24 00:00:00 |
| 19 | 2018-02-26 00:00:00 |
| 20 | 2018-02-27 00:00:00 |
| 21 | 2018-02-28 00:00:00 |
| 22 | 2018-03-01 00:00:00 |
| 23 | 2018-03-02 00:00:00 |
| 24 | 2018-03-03 00:00:00 |
+----+---------------------+
 
mysql> SELECT
    ->     SUM(CASE WHEN dtime BETWEEN '2018-02-05' AND '2018-02-15' THEN 1 ELSE 0 END) as current
    ->   , SUM(CASE WHEN dtime BETWEEN '2018-02-05' AND '2018-02-22' THEN 1 ELSE 0 END) as prior
    ->   , SUM(CASE WHEN dtime BETWEEN '2018-02-05' AND '2018-02-28' THEN 1 ELSE 0 END) as full
    -> FROM mytable;
+---------+-------+------+
| current | prior | full |
+---------+-------+------+
|      10 |    16 |   21 |
+---------+-------+------+
1 row in set (0.01 sec)

[thenorman138]

So by removing the while loop and restructuring the query like that I would just be pulling from one row as opposed to 3 unions or 3 queries and I can just create one object for each value in the row