MySQLi Installed, Connecting to Database, Valid Query that works in phpMyAdmin, but not working in PHP

Question

I've googled this issue, and seems there are a variety of reasons that this occurs. I have the following code which ruled out most of the top reasons I saw:

$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);
// check connection
if ($conn->connect_error) {
    trigger_error('Database connection failed: '  . $conn->connect_error, E_USER_ERROR);
}
if (function_exists('mysqli_query')) {
    print('installed');
}
else{
    print('not installed');
}

$sqlQuery="SELECT fname FROM `lists_users` WHERE `id`='2'";

echo $sqlQuery;

// Execute Query -----------------------------
$result = mysqli_query($conn, $sqlQuery);
if(!$result) {
    echo "Cannot do query" . "<br/>";
    exit;
}

$row = mysqli_fetch_row($result);
$count = $row[0];

if ($count > 0) {
    echo "Query works";
} else {
    echo "Query doesn't work" ."<br/>";
}

The result from above code that I get is, Mysqli "installed", and "Query doesn't work". I can copy and paste the echoed above sql query into phpMyAdmin, and it will return a row.

Really really stumped as to what the issue could possibly be.

So at the suggestion of YourCommonSense, I put in error reporting. and the error I get is 'mysqli class not found'.

So did some more googling, and tried this code:

if (!function_exists('mysqli_init') && !extension_loaded('mysqli')) {
echo 'We don\'t have mysqli!!!';
} else {
echo 'Phew we have it!';
}

....and it returns 'Phew we have it!' :\

AND I can run "php -r "new mysqli();" from the commmand line and it works.

You have to learn to make use of [**error reporting**](http://stackoverflow.com/questions/22662488/bind-param-between-environment/22662582#22662582) - or you may stumble forever. — Your Common Sense, May 06 '14 at 19:22
@Your Common Sense--that helped. Getting error that "class Mysqli not found" (which I don't get because it says function exists...but whatever, now I have an idea of what I need to do). — brizz, May 06 '14 at 19:27
and I just checked php.ini and php_mysqli.dll is installed :\ — brizz, May 06 '14 at 19:31
What happens if you standardize the way you write your `mysqli`? Right now you are mixing Object Oriented style `$conn->connect_error` and procedural `mysqli_query`. While I don't think this will fix the problem, it's a VERY good idea to standardize your code. — the_pete, May 06 '14 at 19:40
Tales you are telling us are quite improbable. Can't you post the actual code you run along with exact output you get? and make this code proper, without strings compared to numbers. — Your Common Sense, May 06 '14 at 19:53
code posted is what I'm running. I think I'm getting somewhere tho. I have no idea why the above code says "Phew we have it!", then it says its not found. But I ran php -m from command line, and mysqli isn't listed(should it be?) — brizz, May 06 '14 at 19:56
ok, voting down. "mysqli not found" is a fatal error that should halt the script and prevent all further output. — Your Common Sense, May 06 '14 at 19:57

meda · Answer 1 · 2014-05-06T19:55:10.240

Change

SELECT fname FROM `lists_users` WHERE `id`='2'

to

SELECT fname FROM `lists_users` WHERE `id`=2

Usually Ids dont need quotes.

Try this approach

$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);

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

/* Prepare an insert statement */
$sqlQuery = "SELECT fname FROM `lists_users` WHERE `id`=?";
$stmt = mysqli_prepare($conn, $sqlQuery);

mysqli_stmt_bind_param($stmt, "i", 2);

/* Execute the statement */

if (mysqli_stmt_execute($stmt)) {
    echo "Query works";
} else {
    echo "Query doesn't work" ."<br/>";
}


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


/* close connection */
mysqli_close($conn);

score 2 · Answer 2 · answered May 06 '14 at 19:38

2

In Your below Query

SELECT fname FROM `lists_users` WHERE `id`='2'

You are trying to fetch fname, which I assume is String/Varchar type. In your code following line

$row = mysqli_fetch_row($result);

Would return Array and $row[0] would fetch the fname (String Type) value and store it in $count variable Now comparing String with int in if statement would convert String to int value, in our case "0" and the if statement would give false result.

If you want to fetch Count use mysqli_num_rows function (http://www.php.net/manual/en/mysqli-result.num-rows.php).

This should give correct result.

answered May 06 '14 at 19:38

Priyadarshan Salkar

534
5
7

I've done the mysqli_num_rows function also, and I've done * in place of fname also, so this is not it. – brizz May 06 '14 at 19:39
Ok ... But the edit the question to use mysqli_num_rows, coz the code seems wrong using mysqli_fetch_row and comparing its result to be greater than 0. – Priyadarshan Salkar May 06 '14 at 19:44

score 0 · Answer 3 · edited May 06 '14 at 19:54

0

If you use mysqli procedural style then use mysqli_connect(), not new mysqli().

edited May 06 '14 at 19:54

zessx

68,042
28
135
158

answered May 06 '14 at 19:32

Deadooshka

478
6
8

MySQLi Installed, Connecting to Database, Valid Query that works in phpMyAdmin, but not working in PHP

3 Answers3