SQL SELECT WHERE statement works but prepared statement doesn't?

user_13099489 0
Viewed 56

(First question so sorry if it makes no sense) Trying to get data for all users who are from the same company as the current user using session variables to get the company name which works fine, $company has the correct value in it.

$company = $_SESSION['company'];

If I run a normal SELECT WHERE statement it works perfectly fine:

$sql = "SELECT FirstName, SecondName, Progress FROM progress WHERE Company = ''";
$result = $conn->query($sql);

This outputs the correct results from the database. However, when I try and make this a prepared statement, it doesn't produce any results:

$sql = $conn->prepare("SELECT FirstName, SecondName, Progress FROM progress WHERE Company = ?");
$sql->bind_param("s", $company);

Here's the whole code snipet with the working query commented out:

                $conn = mysqli_connect($servername, $username, $password, $dbname);

                if (!$conn) {
                    die("Connection failed: " . mysqli_connect_error());
                }

                $company = $_SESSION['company'];

                // $sql = "SELECT FirstName, SecondName, Progress FROM progress WHERE Company = ''";
                // $result = $conn->query($sql);


                $sql = $conn->prepare("SELECT FirstName, SecondName, Progress FROM progress WHERE Company = ?");
                $sql->bind_param("s", $company);

                $sql->bind_result($result);
                $sql->execute();
                $sql->fetch();


                if (mysqli_num_rows($result) > 0) {
                    // output data of each row
                    while($row = mysqli_fetch_assoc($result)) {
                    echo "Name: " . $row["FirstName"]. " " . $row["SecondName"]. " - Progress: " . $row["Progress"]. "<br>";
                    }
                } else {
                    echo "0 results";
                }
mysql sql php database
2 Answers

As per my comment, call execute before binding result 

//After bind_param();
$sql->execute();
$result = $sql->get_result();
print_r($result->fetch_assoc);
user_71045270

How about this? I made it more concise.

        $conn = mysqli_connect($servername, $username, $password, $dbname);

        if (!$conn) {
            die("Connection failed: " . mysqli_connect_error());
        }

        $company = $_SESSION['company'];

        $sql = $conn->prepare("SELECT FirstName, SecondName, Progress FROM progress WHERE Company = ?");
        $sql->bind_param("s", $company);

        $sql->execute();

        $sql->bind_result($firstName, $secondName, $progress);

        if($sql->fetch()==NULL) {
            echo "0 results";
        } else {
           do {
               echo "Name: " . $firstName. " " . $secondName. " - Progress: " . $progress. "<br>";
           } while($sql->fetch());
        }
user_30126220
Related