What's wrong with my select query?

Question

I've written this code in order to fetch all the records in a table; however for some reason it's not working:

function GetAllData(){
    $result = mysql_query("SELECT * FROM a2h_member_type");

    while($row = mysql_fetch_array($result))
    {
        echo $row['member_type_id'] . " " . $row['member_type_name'] . " " . $row['description'];       
    }
}

It's not getting into the while loop, yet there are 2 records in the table. Any ideas?

Answer 1

There'd be two reasons that the while loop would not be running:

1. The query is failing. Given the simplicity of the query, this could only happen if:
   • the table doesn't exist
   • there's no connection to the database
2. There are no rows in the table.

Can you check these three things? You can check if there was an error by printing out mysql_error(), and you can check the number of rows returned by using mysql_get_num_rows($result)

Answer 2

Check what mysql_errno() and mysql_error() return.

As per the PHP documentation:

Errors coming back from the MySQL database backend no longer issue warnings.

This means you have to check for errors manually (which is good practice anyway).

Answer 3

The only suggestion I have is to enclose the table name in single-back-quotes (I'm sure there's a more official name for the character!):

$result = mysql_query("SELECT * FROM `a2h_member_type`");

- but since there aren't any weird characters in your table name I don't think this will make much difference. Possibly worth a try though.

Answer 4

You have this encapsulated in a function. Depending on when and where the function is called in your script, you may not yet have opened your database connection or have selected your database. You need to do both before you call this function. Go look for where the function is called in your code and make sure you call these two things before you call it:

$con = mysql_connect([the databases URL], [your database username], [your database password]);
if(!$con) {
        die("Could not connect: " .mysql_error());
}
mysql_select_db([the name of your database], $con);

Probably the best thing to do would be to save your connection variable somehow (the $con in this example) and check it in the beginning of your function using the if(!$con) code.

Answer 5

I don't see a mysql_connect() or a mysql_select_db() command so I assume you've connected before calling this method. I also assume you've used the exact spelling for the field names, since they are case sensitive! So, logic would dictate that mysql_fetch_array($result) returns no rows.

Since the method doesn't explicitly open a database and the mysql_query() doesn't specify a specific database resource, the query would be looking at the last database that you've opened with mysql_connect(). If you never connected or switched to a different database then it can't find the database. mysql_query() could be returning FALSE, thus mysql_fetch_array() returns FALSE too.

Answer 6

First try to add some debug info:

error_reporting(E_ALL);
ini_set("display_errors", true);
..........
function GetAllData(){
    $result = mysql_query("SELECT * FROM a2h_member_type") or die(mysql_error());

    while($row = mysql_fetch_array($result))
    {

        echo $row['member_type_id'] . " " . $row['member_type_name'] . " " . $row['description'];

    }
}

Execute this please and tell us what you see.