PHP code not returning correct MySQL result

Question

I'm querying a set of data from MySQL on a hosted server. When I did it on my local machine, it worked fine, but on the hosted server, it's not returning anything. The weird thing is, when I use this GUI for the hosted environment to run the same query, it does return the results.

So here are some codes, etc.

This is index.php:

<?php

$servername = "username.db.theservername.ether.com";
$mysqllogin = "username";
$mysqlpassword = "thepassword";
$dbName = "StepsMath";
$conn = new mysqli($servername, $mysqllogin, $mysqlpassword, $dbName);

if($conn->connect_error){
    die ("connection failed: " . $conn->connect_error);
    echo "connection failed.";
}

$set_name = 'test_set';
$query = "select word, definition from word_list where set_name = '$set_name'";
$result = $conn->query($query);
$conn->close();
             // *********************************************
echo $query; // <-------- *******this prints the query*******
             // *********************************************
$result = mysqli_fetch_array($result);

?>

<!DOCTYPE html>
<html lang="en"><head>
<title>this is a title</title>

<script type="text/javascript">
    var json = '<?= json_encode($result) ?>';
    var word_list = JSON.parse(json);
    console.log(word_list);     //line 24
    console.log(json);          //line 25
    function getUserId(){
        return '<?= $user_id ?>';
    }
    if(!getUserId()) window.location = 'login.html';
</script>
</head>
<body>body stuff</body>
</html>

Table word_list only has three columns: word, definition, and set_name

The above code is supposed to return rows of words and definitions. Instead, as I check in the browser console, it returns the following:

{"current_field":null,"field_count":null,"lengths":null,"num_rows":null,"type":null}

This is the query that runs:

select word, definition from word_list where set_name = 'test_set'

If I copy that exact query and run it in the MySQL GUI in the hosted server,

GUI MySQL environment provided by GoDaddy

the following is returned:

Result of running the same query in GUI

So to summarize the question, why is there a discrepancy in the result between the GUI and the code??

Do you use the same passwords for both local and remote databases? — MiltoxBeyond
– MiltoxBeyond, Commented Jul 2, 2015 at 21:28
Also the databases on your local and remote box are different. In your GUI it shows StepsMath, whereas in your code it shows vocab_builder — MiltoxBeyond
– MiltoxBeyond, Commented Jul 2, 2015 at 21:31
@CodeGodie: there are no results to loop through. The object that's returned doesn't have anything in it. Everything is Null. — J.Ko
– J.Ko, Commented Jul 2, 2015 at 21:32

CodeGodie · Accepted Answer · 2015-07-02 21:33:51Z

2

change this line:

$result = mysqli_fetch_array($result);

to this:

while ($row = mysqli_fetch_assoc($result)) {
    $res[] = $row;
}
var_dump($res);

The information you previously got: {"current_field":null,"field_count":null,"lengths":null,"num_rows":null,"type":null} are all mysqli_result properties.

Read this, it will help you uderstand what you did wrong:http://php.net/manual/en/class.mysqli-result.php

answered Jul 2, 2015 at 21:33

CodeGodie

12.2k6 gold badges40 silver badges66 bronze badges

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

Collectives™ on Stack Overflow

PHP code not returning correct MySQL result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related