PHP How to put part of a DB query into a function

Question

I have been banging my head on this for a few days I am finally giving in and am now seeking help.

I have these 2 sets of code that works

This one calls the one below and passes "IS NULL" to the other function and the other function displays the data.

<html>
<head>
   <link rel="stylesheet" href="style.css">
   <title>My PHP</title>
</head>
<body>

<div class="body">

 <?php 
    require_once 'ShowInventory.php';
    DBShowInventory("IS NULL");
 ?> 
 </div>
 </body>
</html>




<?php
function DBShowInventory($WhichInventory){

require_once 'connection.php';
$conn = Connect();

$result = $conn->query("select Description, PartNumber, Serial, Store, Cost, MSRP, DateReceived from inventory where DateSold $WhichInventory;");
echo "</br></br></br>\r\n";
echo "<table class=\"center\"> \r\n";
echo ' <tr>';
echo '    <th>Description</th>';
echo '    <th>Part Number</th>';
echo '    <th>Serial</th>';
echo '    <th>Store</th>';
echo '    <th>Cost</th>';
echo '    <th>MSRP</th>';
echo '    <th>DateReceived</th>';
echo " </tr> \r\n";

while ($row = $result->fetch_assoc()) {
              unset($Description, $PartNumber, $Serial, $Store, $Cost, $MSRP, $DateReceived);
              $Description = $row['Description'];
              $PartNumber = $row['PartNumber'];               
              $Serial = $row['Serial'];
              $Store = $row['Store'];
              $Cost = $row['Cost'];
              $MSRP = $row['MSRP'];
              $DateReceived = $row['DateReceived'];
              echo "<tr> \r\n";
              echo "<td>" .$Description. "</td> \r\n";
              echo "<td>" .$PartNumber. "</td> \r\n";
              echo "<td>" .$Serial. "</td> \r\n";
              echo "<td>" .$Store. "</td> \r\n";  
              echo "<td>";
              echo "₱";
              echo number_format($Cost,2,'.',',');
              echo "</td> \r\n";
              echo "<td>";
              echo "₱";
              echo number_format($MSRP,2,'.',',');
              echo "</td> \r\n";
              echo "<td>" .$DateReceived. "</td> \r\n";           
              echo "</tr> \r\n";

}
echo "</table> \r\n";
}
 ?>

What I am hoping to do is to flip the logic around and have the script on top to do the displaying and use the script in the bottom to do the database query only. This way I can use this function over and over for different things.

I tried doing this but all I get is one record repeated 7 times.

<?php
function DBLookup($DateSold){
require_once 'connection.php';
$conn = Connect();

$result = $conn->query("select Description, PartNumber, Serial, Store, Cost, MSRP, DateReceived from inventory where DateSold $DateSold;");

$row = (array) $result->fetch_assoc();
return $row;
}
?>

This is the script I wanted to use to process $row

<html>
<head></head>
<body>

<?php
require_once 'GetMultiRecordFromDB.php';
$row=DBLookup("IS NULL");

    echo "</br></br></br>\r\n";
    echo "<table class=\"center\"> \r\n";
    echo ' <tr>';
    echo '    <th>Description</th>';
    echo '    <th>Part Number</th>';
    echo '    <th>Serial</th>';
    echo '    <th>Store</th>';
    echo '    <th>Cost</th>';
    echo '    <th>MSRP</th>';
    echo '    <th>DateReceived</th>';
    echo " </tr> \r\n";

 foreach($row as $rowdata) {
              unset($Description, $PartNumber, $Serial, $Store, $Cost, $MSRP, $DateReceived);
              $Description = $rowdata['Description'];
              $PartNumber = $rowdata['PartNumber'];               
              $Serial = $rowdata['Serial'];
              $Store = $rowdata['Store'];
              $Cost = $rowdata['Cost'];
              $MSRP = $rowdata['MSRP'];
              $DateReceived = $rowdata['DateReceived'];
              echo "<tr> \r\n";
              echo "<td>" .$Description. "</td> \r\n";
              echo "<td>" .$PartNumber. "</td> \r\n";
              echo "<td>" .$Serial. "</td> \r\n";
              echo "<td>" .$Store. "</td> \r\n";  
              echo "<td>";
              echo "₱";
              echo number_format($Cost,2,'.',',');
              echo "</td> \r\n";
              echo "<td>";
              echo "₱";
              echo number_format($MSRP,2,'.',',');
              echo "</td> \r\n";
              echo "<td>" .$DateReceived. "</td> \r\n";           
              echo "</tr> \r\n";

}

    echo "</table> \r\n";

?>
</body>
</html>

Thanks guys looking forward to your reply.

See php.net/manual/mysqli-result.fetch-all.php

Phil
– Phil

2018-09-18 06:35:36 +00:00
Commented Sep 18, 2018 at 6:35 — Phil
– Phil, Commented Sep 18, 2018 at 6:35

Nigel Ren · Accepted Answer · 2018-09-18 06:36:09Z

2

You would more commonly retrieve the data in a loop and pass an array of records back...

function DBLookup($DateSold){
    require_once 'connection.php';
    $conn = Connect();

    $result = $conn->query("select Description, PartNumber, Serial, Store, Cost, MSRP, DateReceived from inventory where DateSold $DateSold;");

    $data = array();
    while ($row = $result->fetch_assoc())  {
       $data[] = $row;
    }
    return $data;
}

You should look into prepared statements to protect against various forms of injection, but when using this for substitution, it doesn't fit. Make sure that you are happy with the fact that this is open to abuse.

It would also be 'better' if you passed the connection into the method, so

function DBLookup($DateSold, $conn){

As this allows you to control the connection being used rather than functions always creating their own.

answered Sep 18, 2018 at 6:36

Nigel Ren

57.3k11 gold badges49 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Les Over a year ago

Thank you so much Nigel, it worked like a charm. I feel terrible for asking such a newbie question. Then again I am a newbie.

Collectives™ on Stack Overflow

PHP How to put part of a DB query into a function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related