Cannot query MySQL database via PHP

Question

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!

You don't actually bind the results from your query, if ($result = $conn->query($sql)). But that's not the source of your issue, take a look at mysqli_error and error_reporting(-1);. — Qirel
– Qirel, Commented Dec 11, 2015 at 16:26
You should consider using a condition so that either the select is run, or the insert is run, or the insert is run after the select, or the select is run after the insert. Right now, your code is re-using the same variable without any controls on when that variable is assigned a new value. — devlin carnate
– devlin carnate, Commented Dec 11, 2015 at 16:53

SOFe · Accepted Answer · 2015-12-11 16:33:08Z

6

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.

answered Dec 11, 2015 at 16:33

SOFe

8,3065 gold badges37 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Funk Forty Niner Over a year ago

Looks like the OP took off without saying goodbye and not leaving a nice fat tip ;-)

mitch Over a year ago

Good comments from everyone. I would say that all the information I received helped to solve this. Actually I was thinking perhaps I should be doing this to test the query results $result = $conn->query($sql); if ($result->num_rows > 0) Anyone agree with that?

Funk Forty Niner Over a year ago

@mitch Yes, that would make more sense Mitch, however it could fail (at some point) and you would need to use a WHERE clause (it could work better) >>> depending on what you're really wanting to achieve here. You could use mysqli_error($conn) on query error though, or a mix of.

SOFe Over a year ago

@mitch that could result in a Trying to get property of non-object, since $result === false if query failed.

mitch Over a year ago

Thanks. I'm now checking if($result !== FALSE) followed by my check on $result->num_rows. Does that seem good?

|

sandeepsure · Accepted Answer · 2015-12-11 16:32:40Z

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;
    }

Telmo Dias · Accepted Answer · 2015-12-11 16:33:46Z

1

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>";
    }
}

answered Dec 11, 2015 at 16:33

Telmo Dias

4,2762 gold badges41 silver badges50 bronze badges

Comments

Monty Khanna · Accepted Answer · 2015-12-11 16:43:37Z

1

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>";
    }
}

answered Dec 11, 2015 at 16:43

Monty Khanna

1,1207 silver badges15 bronze badges

Collectives™ on Stack Overflow

Cannot query MySQL database via PHP

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related