Using a parameter in a php function appears to have broken my mysql query

Question

Edit: This was closed as a duplicate for being about scope, but the issue was actually about my use (or lack) of apostrophes in the SQL query. The scope was incorrect, but wasn't what was causing my issue. I'm happy for this to be deleted, or re-opened for the posting of a solution.

The following code works fine and displays a nice HTML table:

$link = mysqli_connect($host, $username, $password, $db_name);
$round = 1;
$result = mysqli_query($link,"SELECT car.carID, car.team, result.cost FROM result INNER JOIN car ON car.carID=result.carID WHERE result.roundID=$round AND car.class='LMP1'");

echo "<h1>" . 'LMP1' . "</h1>
<table border='1'>
<tr>
<th>Car</th>
<th>Team</th>
<th>Cost</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['carID'] . "</td>";
echo "<td>" . $row['team'] . "</td>";
echo "<td>" . $row['cost'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($link);

But when I wrap it in a function I get the table headers and no data:

function displayClassCosts($class) {
    $link = mysqli_connect($host, $username, $password, $db_name);
    $round = 1;
    $result = mysqli_query($link,"SELECT car.carID, car.team, result.cost FROM result INNER JOIN car ON car.carID=result.carID WHERE result.roundID=$round AND car.class=$class");

    echo "<h1>" . $class . "</h1>
    <table border='1'>
    <tr>
    <th>Car</th>
    <th>Team</th>
    <th>Cost</th>
    </tr>";

    while($row = mysqli_fetch_array($result))
    {
    echo "<tr>";
    echo "<td>" . $row['carID'] . "</td>";
    echo "<td>" . $row['team'] . "</td>";
    echo "<td>" . $row['cost'] . "</td>";
    echo "</tr>";
    }
    echo "</table>";
    mysqli_close($link);
}

displayClassCosts('LMP1');
displayClassCosts('LMP2');
displayClassCosts('GTE_Pro');
displayClassCosts('GTE_Am');

The only differences are the hard-coded string in the query condition and the H1 tag in the first version vs using the passed variable.

Ideas welcome!

you have to define all addition variables in function you didn't do that in code — , Dec 28 '16 at 11:55
Function calls are immediately below the function declaration - as pasted above. Only missing bits are the and 'include "secretDBlogindetails.php"' — Mark Clarkson, Dec 28 '16 at 12:01
you are missing single quotes in your query..."AND car.class=$class" should be "AND car.class='$class' " — Kurohige, Dec 28 '16 at 12:02
`class` is a text field, so its values should be wrapped by single quotes (`'`) in the sql statement: `car.class='$class'`. However, you should use prepared statements or other means to make sure that your code is not vulnerable to sql injections. — Shadow, Dec 28 '16 at 12:05
GENIUS! Of course. I was thinking I only needed them when hard coded because it is a literal in PHP. Once in MySQL... well, let's all agree I'm a dummy. Many thanks! — Mark Clarkson, Dec 28 '16 at 12:05
this is your coding mistake so i think you have to delete this question. — , Dec 28 '16 at 12:09
Still getting the hang of SO etiquette. My problem was actually the single quotes issue. The scoping issues I would have figured out through trial and error (this is currently day 2 of learning anything about PHP, though I have a reasonable bit of experience with a variety of other languages). Can no longer see the option to answer my own question with correct code, which is a shame. Thanks all who chipped in. — Mark Clarkson, Dec 28 '16 at 12:11

score 0 · Answer 1 · answered Dec 28 '16 at 11:55

0

When wrapped in a function, from where will you get the variables for DB connection $host, $username, $password, $db_name

You can use these variables by using global keyword in your function.

function displayClassCosts($class) {
    global $host, $username, $password, $db_name;
    .
    .
}

Another approach is to pass them as parameters.

function displayClassCosts($class, $host, $username, $password, $db_name) {
    .
    .
}

answered Dec 28 '16 at 11:55

Samir Selia

7,007
2
11
30

Alas, no joy. I had tried your second solution, and passing $link. Hadn't tried the global declaration but have now and still no further on. – Mark Clarkson Dec 28 '16 at 12:00
Just checked, you need `$round` variable too. In short, you need to pass all variables used in your function. – Samir Selia Dec 28 '16 at 12:03

Using a parameter in a php function appears to have broken my mysql query

1 Answers1