Whats wrong with this code, Always prints result 0. While on running the query direct in phpmyadmin i get the correct result

Question

I am working on an ajax script, and this php code is giving real tough time by always printing 0 results.

I doubted query so I directly ran it in phpmyadmin and got a row of results. so definitely nothing wrong with query.

Any help?

function processDrpdown($selectedVal) {
    $sql = "SELECT id, name FROM mdl_question_categories where parent = $selectedVal";    
    $result = mysqli_query($conn, $sql);
    if (mysqli_num_rows($result) > 0) {
        while($row = mysqli_fetch_assoc($result)) {
            echo "Name: " . $row["name"]. "<br>";
            echo "ID: " . $row["id"]. "<br>";
        }
    } else {
        echo "0 results";
    }
    mysqli_close($conn);
    // echo $selectedVal;
}

If you had error reporting you should get an error as `$conn` is not defined. Probably want to pass it in - and you probably don't want to close this connection as it may be used elsewhere. — Nigel Ren, May 11 '19 at 06:23
$selectedVal is an integer, However @iRaS solved the problem. I wasn't passing the connection to the function. That sorted the problem. Thanks all who contributed! — Waqar Ahmed, May 11 '19 at 09:30

score 0 · Accepted Answer · answered May 11 '19 at 06:06

When I have to guess what $selectedVal contains I would suggest to make it sql injection save. Maybe this also solves your problem:

$query = "SELECT id, name FROM mdl_question_categories where parent = ?";

if ($stmt = mysqli_prepare($link, $query)) {
    /* bind parameters for markers */
    mysqli_stmt_bind_param($stmt, "s", $selectedVal);

    /* execute statement */
    mysqli_stmt_execute($stmt);

    /* bind result variables */
    mysqli_stmt_bind_result($stmt, $id, $name);

    if (mysqli_stmt_num_rows($stmt) > 0) {
        /* fetch values */
        while (mysqli_stmt_fetch($stmt)) {
            echo "Name: " . $name. "<br>";
            echo "ID: " . $id . "<br>";
        }
    } else {
        echo "0 results";
    }

    /* close statement */
    mysqli_stmt_close($stmt);
}

Beside that: I suggest you use the object oriented style of mysqli. It looks like you are closing the connection after this function but I don't see where you open the connection. You should pass the connection to the function or create it inside the function.

$conn = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

processDrpdown($conn, $selectedVal);

/* close connection */
$conn->close();

Please also have a look at the manual regarding prepared statements: https://www.php.net/manual/en/mysqli.prepare.php

Prepared statements are by the way much easier with PDO but it's an extra module and we don't know if it is installed in your php instance.

Great, thanks... I was not passing the connection to the function. — Waqar Ahmed, May 11 '19 at 09:31
This code will never print anything due to the limitations of mysqli_stmt_num_rows. — Your Common Sense, May 11 '19 at 10:34
I see you are trying to adhere your answer to best practices which is good. Take one step further and check [the proper connection code for mysqli](https://phpdelusions.net/mysqli/mysqli_connect) — Your Common Sense, May 11 '19 at 10:37
@YourCommonSense You can edit the answer accordingly if you want. I've never used mysqli. In the past I used a very well working mysql wrapper class and after that I used various ORM libraries (including my own ORM lib). All of them are using PDO - it's not special but it could be disabled or not installed (for what ever reason) — iRaS, May 12 '19 at 05:51

Whats wrong with this code, Always prints result 0. While on running the query direct in phpmyadmin i get the correct result

1 Answers1