create php function for different queries

Question

I have a query which I need to run multiple times within a page with different varriables I would need help with creating a function which would do the same as this code:

    $type = "A";
    $getRecordsQuery = "SELECT * from records where domain_id=$domainID and type='". $type ."'" or die("Error:" . \mysqli_error($link));
    $result = mysqli_query($link, $getRecordsQuery);
    if ($result === FALSE) {
die("Error: " . mysqli_error($link));
    } else {
        echo "<table border='1'>
            <tr>
                <td>Name</td>
                <td>IP</td>
                <td>DynDNS</td>
                <td>TTL</td>
            </tr>";
            while ($row = mysqli_fetch_array($result)) {
                $name = $row["name"];
                $ip = $row["content"];
                $ttl = $row["ttl"];
                echo "<tr>
                    <td>". $name ."</td>
                    <td>". $ip ."</td>
                    <td>Off</td>
                    <td>". $ttl ."<td>
                </tr>";
            }
    echo "</table>";

Basically this queries a table from the database and it puts the required information in a table. I would need the function to do the same.

Thanks in advance.

You are using MYSQLI_* so lookup prepare() and execute(). Then prepare it once and pass paramters to it 10000 times and execute unique quesries 1000 times if you like. {RTFM](http://php.net/manual/en/mysqli.prepare.php) — RiggsFolly, Dec 17 '14 at 19:11

score 1 · Accepted Answer · edited May 23 '17 at 12:06

You basically put the code in a function block that you can call -

function getData($domainID, $domainType, $link) {
    $getRecordsQuery = "SELECT * from records where domain_id=$domainID and type='". $domainType ."'" or die("Error:" . \mysqli_error($link));
    $result = mysqli_query($link, $getRecordsQuery);
    if ($result === FALSE) {
        die("Error: " . mysqli_error($link));
    } else {
        $table = "<table border='1'>
            <tr>
                <td>Name</td>
                <td>IP</td>
                <td>DynDNS</td>
                <td>TTL</td>
            </tr>";
        while ($row = mysqli_fetch_array($result)) {
            $name = $row["name"];
            $ip = $row["content"];
            $ttl = $row["ttl"];
            $table .= "<tr>
                          <td>". $name ."</td>
                          <td>". $ip ."</td>
                          <td>Off</td>
                          <td>". $ttl ."<td>
                      </tr>";
        }
    $table .= "</table>";
    return $table;
}

Then you can call it while passing in the domain id and type.

echo getData($fooID, $barType, $databaseConnection);

You also have to pass in the database connection as you have likely made that connection somewhere outside of this function.

This can be greatly improved by learning about prepared statements and using them to help prevent problems like SQL Injection.

`prepare()` and `execute()` You can lead a horse to water but you cannot make it drink — RiggsFolly, Dec 17 '14 at 19:09
That's why I included the info @RiggsFolly. I wasn't planning to rewrite all of the code for the OP. Thanks for the DV :) — Jay Blanchard, Dec 17 '14 at 19:11
My absolute pleasure. Nothing more or less that you deserve. — RiggsFolly, Dec 17 '14 at 19:11
`$link` wasn't defined previously either @FunkDoc. Edited to explain. — Jay Blanchard, Dec 17 '14 at 19:16

score 0 · Answer 2 · answered Dec 17 '14 at 20:24

This function is what you need :

function getRecords($domainId, $type, $link) {
$getRecordsQuery = "SELECT * FROM records WHERE domain_id='".mysql_real_escape_string($domainId)."' AND type='".mysql_real_escape_string($type)."'";
$result = mysqli_query($link, $getRecordsQuery);
if (!$result){
    die("Error: " . mysqli_error($link));
} else{
    echo "<table border='1'>
            <tr>
                <td>Name</td>
                <td>IP</td>
                <td>DynDNS</td>
                <td>TTL</td>
            </tr>";
            while ($row = mysqli_fetch_array($result)) {
                $name = $row["name"];
                $ip = $row["content"];
                $ttl = $row["ttl"];
                echo "<tr>
                    <td>". $name ."</td>
                    <td>". $ip ."</td>
                    <td>Off</td>
                    <td>". $ttl ."<td>
                </tr>";
            }
    echo "</table>";
}
}

Use it so :

getRecords(7, 'A', $link);

You ought to separate HTML codes from function :

function getRecords($domainId, $type, $link) {
$getRecordsQuery = "SELECT * FROM records WHERE domain_id='".mysql_real_escape_string($domainId)."' AND type='".mysql_real_escape_string($type)."'";
$result = mysqli_query($link, $getRecordsQuery);
if (!$result){
    die("Error: " . mysqli_error($link));
} else{
    return $result;
}
}

Use it in HTML :

<table border='1'>
        <tr>
            <td>Name</td>
            <td>IP</td>
            <td>DynDNS</td>
            <td>TTL</td>
        </tr>
    <?php
    $res = getRecords(7, 'A', $link);
    while ($row = mysqli_fetch_assoc($res)){
            $name = $row["name"];
            $ip = $row["content"];
            $ttl = $row["ttl"];
    ?>
            <tr>
                <td><?=$name ?></td>
                <td><?=$ip ?></td>
                <td>Off</td>
                <td><?=$ttl ?><td>
            </tr>
    <?php
    }
    ?>
    </table>

create php function for different queries

2 Answers2