Home > Back-end >  How can I bundle output from a database query so I can write it to a file
How can I bundle output from a database query so I can write it to a file

Time:08-04

I have this code that gets records from an API and displays it on a page. The code works and the records are displayed correctly.

    $allrecords = array();
    for ($i = 0; $i <= 3; $i  ) {
        $record = getData("https://api.site.com/v2/offers?page_size=100&page_number=" . $i);
        $allrecords[] = $record['offers'];
    }
    $date = date('d/m/Y');
    $items = $allrecords[0];
//TABLE WITH ALL THE PRODUCTS
    echo "<html><head><style>td{padding:5px}th {padding: 5px;background: #035;color: #fff;}</style></head>
    <body>
    <br/>
    <h3 class='pageDate'>" . $date . "</h3>
    <table id='reportsTable' style='width:80%;margin:20px auto'><thead>
    <tr id='reportRow'>
    <th class='image'></th>
    <th class='title'>Title</th>
    <th class='small'>Price</th>
    <th class='small'>CPT</th>
    <th class='small'>JHB</th>
    </tr>
    </thead>";
    foreach ($items as $key => $row) {
        foreach ($row['stock'] as $val) {
            foreach ($val['warehouse'] as $value) {
                if ($value == 1) {
                    $cpt = $val['quantity_available'];
                }
            }
        };
        foreach ($row['stock'] as $key => $val) {
            foreach ($val['warehouse'] as $key => $value) {
                if ($value == 3) {
                    $jhb = $val['quantity_available'];
                }
            }
        };
        echo
        "<tr>
    <td class='num centerCol'><img src='placeholder.png'/></td>
    <td class='productTitle'>" . $row['title'] . "</td>
    <td class='small centerCol' style='text-align:center'>" . 'R' . $row['selling_price'] . "</td>
    <td class='small centerCol' style='text-align:center'>" . $cpt . "</td>
    <td class='small centerCol' style='text-align:center'>" . $jhb . "</td>
    </tr>";
    }
    echo "</table></body></html>";

There are different users on the system and each has his/her own API key. I have a cron job running every night that needs to run a file that will generate a report for each user. How can I bundle this output table data and write it to html files pls? In other words I don't want to display it I want to put all this table data with the variables into a single variable that I can write to a file.

I have started writing the generate report function but am stuck at generating the html file:

function generateReports($con)
{
    $query = "select username, apikey from users";
    $result = mysqli_query($con, $query);
    while ($row = mysqli_fetch_assoc($result)) {
        $user = $row['username'];
        $key = $row['apikey'];
        $date = date('dm');
        $filename = $user . '/' . $date . '.html';
        $allrecords = array();
        for ($i = 0; $i <= 3; $i  ) {
            $record = getData("https://seller-api.takealot.com/v2/offers?page_size=100&page_number=" . $i, $key);
            $allrecords[] = $record['offers'];
        }
        $items = $allrecords[0];
    }
}

Somehow my question is not understood This gives me an error:

        $content = "
        <html><head><style>td{padding:5px}th {padding: 5px;background: #035;color: #fff;}</style></head>
        <body>
        <br/>
        <h3 class='pageDate'>" . $date . "</h3>
        <table id='reportsTable' style='width:80%;margin:20px auto'><thead>
        <tr id='reportRow'>
        <th class='image'></th>
        <th class='title'>Title</th>
        <th class='small'>Price</th>
        <th class='small'>CPT</th>
        <th class='small'>JHB</th>
        </tr>
        </thead>"
        foreach ($items as $key => $row) {
            foreach ($row['stock_at_takealot'] as $val) {
                foreach ($val['warehouse'] as $value) {
                    if ($value == 1) {
                        $cpt = $val['quantity_available'];
                    }
                }
            };
            foreach ($row['stock_at_takealot'] as $key => $val) {
                foreach ($val['warehouse'] as $key => $value) {
                    if ($value == 3) {
                        $jhb = $val['quantity_available'];
                    }
                }
            };
        "<tr>
        <td class='num centerCol'><img src='placeholder.png'/></td>
        <td class='productTitle'>" . $row['title'] . "</td>
        <td class='small centerCol' style='text-align:center'>" . 'R' . $row['selling_price'] . "</td>
        <td class='small centerCol' style='text-align:center'>" . $cpt . "</td>
        <td class='small centerCol' style='text-align:center'>" . $jhb . "</td>
        </tr>;
        }
        </table></body></html>
        ";

Not an expert and the foreach statements is what I don't know how to include in the single string I want to write to a file.

CodePudding user response:

What you need is something called file handling in programming. If you haven't worked with it, here is a tutorial to show you how its done. You can take your variable containing the html structure and put it in the file_put_contents(). Here is the complete documentation for file_put_contents().

By the way this is a simple example of file handling using PHP:

<?php
    $file = 'path/where/you/need/file/index.html';
    $content = "<h1>Hello World!</h1>";

    // Write the contents in to the file
    file_put_contents($file, $content);
?>

Edit:

By understanding your requirement further, I came to realise you're having trouble echo-ing the php code (i.e: foreach block). For this, you can simply do what others have mentioned in the comments, echo all the rows into that variable with complete html markup instead of printing a foreach() loop and then put that whole markup into a file. Happy coding!

CodePudding user response:

To get the data into a file you first need to have all the necessary data in a string. Once you have that, you can write the string to a file.

So instead of echoing your data, just assign it to a string variable instead:

//create a string with an initial value.
$content = "
    <html><head><style>td{padding:5px}th {padding: 5px;background: #035;color: #fff;}</style></head>
    <body>
    <br/>
    <h3 class='pageDate'>" . $date . "</h3>
    <table id='reportsTable' style='width:80%;margin:20px auto'><thead>
    <tr id='reportRow'>
    <th class='image'></th>
    <th class='title'>Title</th>
    <th class='small'>Price</th>
    <th class='small'>CPT</th>
    <th class='small'>JHB</th>
    </tr>
    </thead>";

foreach ($items as $key => $row) {
    foreach ($row['stock_at_takealot'] as $val) {
        foreach ($val['warehouse'] as $value) {
            if ($value == 1) {
                $cpt = $val['quantity_available'];
            }
        }
    };

    foreach ($row['stock_at_takealot'] as $key => $val) {
        foreach ($val['warehouse'] as $key => $value) {
            if ($value == 3) {
                $jhb = $val['quantity_available'];
            }
        }
    };

    //carry on adding to the string as needed
    $content .= "<tr>
    <td class='num centerCol'><img src='placeholder.png'/></td>
    <td class='productTitle'>" . $row['title'] . "</td>
    <td class='small centerCol' style='text-align:center'>" . 'R' . $row['selling_price'] . "</td>
    <td class='small centerCol' style='text-align:center'>" . $cpt . "</td>
    <td class='small centerCol' style='text-align:center'>" . $jhb . "</td>
    </tr>";
}

$content .= "</table></body></html>";

//now write the finished string to a file
file_put_contents("text.txt", $content);
  •  Tags:  
  • php
  • Related