CODEWITHSUNDEEP
phpmysql
mitch
mitch

Reputation: 436

Cannot query MySQL database via PHP

In my PHP code, i'm easily writing records to my database but for some reason i can't read anythign out. My PHP code is:

$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT * FROM companies";

if ($conn->query($sql) === TRUE)
{
    echo "query success";

    while($row = $result->fetch_assoc())
    {
        echo "ID: " . $row["ID"]. " - Name: " . $row["name"]. "<br>";
    }
}
else
{
    echo "query failure";
    echo "Error: " . $sql . "<br>" . $conn->error;
}


$sql = "INSERT INTO companies (name) 
        VALUES ('mycompany')";

if ($conn->query($sql) === TRUE)
{

    echo "insert success";
}
else
{
    echo "insert failure";
    echo "Error: " . $sql . "<br>" . $conn->error;
}

The output I get from the browser when i run it is: query failureError: SELECT * FROM companies insert success

I've tried variations of apostrophes, carets, quotes in that $sql string. I've tried running this query in HeidiSQL and it works fine. Any ideas where I'm going wrong? Any suggestions of more basic stuff I can try to narrow down the source of the problem?

thanks!

Upvotes: 1

Views: 14467

Answers (4)

Monty Khanna
Monty Khanna

Reputation: 1120

just change your if condition :- 

if ($result = $conn->query($sql))
{
    echo "query success";

    while($row = $result->fetch_assoc())
    {
        echo "ID: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
    }
}

Upvotes: 1

Telmo Dias
Telmo Dias

Reputation: 4168

The problem is that a SELECT query will return a mysqli_result object on SUCCESS not a boolean TRUE. Only if the query fails, will it return a boolean FALSE;

Therefore you should use it like this :

$result = $conn->query($sql);

if ($result !== FALSE){
    while($row = $result->fetch_assoc())
    {
        echo "ID: " . $row["ID"]. " - Name: " . $row["name"]. "<br>";
    }
}

Upvotes: 1

SOFe
SOFe

Reputation: 8214

Using mysqli->query() with a SELECT statement returns an instance of mysqli_result. It is not identical to true (=== true), but nor does it represent an error.

Moreover, $result is undefined.

Use this instead:

$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT * FROM companies";

if (($result = $conn->query($sql)) !== FALSE)
{
    echo "query success";

    while($row = $result->fetch_assoc())
    {
        echo "ID: " . $row["ID"]. " - Name: " . $row["name"]. "<br>";
    }
}
else
{
    echo "query failure";
    echo "Error: " . $sql . "<br>" . $conn->error;
}
...

This simply changes your === TRUE check to !== FALSE. MySQLi::query() returns boolean FALSE on failure, boolean TRUE on a successful query without result sets or a mysqli_result upon success with a result set. This also assigns the result of query() into $result.

Upvotes: 6

sandeepsure
sandeepsure

Reputation: 1115

You have not assign the query result to $result variable.

    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 

    $sql = "SELECT * FROM companies";
    $result = $conn->query($sql);
    if ($result === TRUE)
    {
        echo "query success";

        while($row = $result->fetch_assoc())
        {
            echo "ID: " . $row["ID"]. " - Name: " . $row["name"]. "<br>";
        }
    }
    else
    {
        echo "query failure";
        echo "Error: " . $sql . "<br>" . $conn->error;
    }


    $sql = "INSERT INTO companies (name) 
            VALUES ('mycompany')";

    if ($conn->query($sql) === TRUE)
    {

        echo "insert success";
    }
    else
    {
        echo "insert failure";
        echo "Error: " . $sql . "<br>" . $conn->error;
    }

Upvotes: 1

Related Questions