mysql_fetch_array does not work

Question

I am trying to get some rows from a table that has columns row_ID,c_ID, e_ID, each e_ID can have different c_ID's. I use the mysql_fetch_array to get all c_ID's that are associated to a certain ID.

$result=mysql_query("SELECT c_ID from User_Competence WHERE e_ID=".$id);
            $test=mysql_fetch_array($result);
            print_r ($test['c_ID']);

But instead of getting several c_IDs for each e_ID, I get only one value in the array. Am I missing something in the query?

You need to call it in a loop. There is an example on the PHP tag wiki http://stackoverflow.com/tags/php/info — Michael Berkowski, Mar 11 '13 at 13:14
You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). We can't see where `$id` comes from but you may also be **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Mar 11 '13 at 13:14
FYI, PHP functions that start with mysql_ have been deprecated as of PHP 5.5.0. If you are in a position to do so, please consider updating your code to use the MySQLi or PDO extensions instead. — dnagirl, Mar 11 '13 at 13:15
why do you think you only have one value, because of the single print statement? — Mike C., Mar 11 '13 at 13:17

score 3 · Accepted Answer · answered Mar 11 '13 at 13:15

3

No, you do not have any errors. if e_ID is unique you will only get one result. You may want to try this if e_ID is not unique:

$result=mysql_query("SELECT c_ID from User_Competence WHERE e_ID=".$id);
while($test=mysql_fetch_array($result))
{
    print_r ($test['c_ID']);
}

answered Mar 11 '13 at 13:15

Zim84

3,404
2
35
40

Why would I need a while if I want to print the whole array at once? – auicsc Mar 11 '13 at 13:23
2

@auicsc - because each record is loaded as a separate array. If you switch to using `PDO` as your DB layer instead of the obsolete `mysql_xxx()` functions, then that does have the ability to load all records into a big array, using `PDO::fetchall()`, but as things stand, you need to use a loop; that's just the way it works. – SDC Mar 11 '13 at 13:26
1

@auicsc `mysql_fetch_array` is an array of the column values for a single row. You still have to call it separately for each row. – Boann Mar 11 '13 at 13:55

score 1 · Answer 2 · answered Mar 11 '13 at 13:21

try this to get all ids

   $id = mysql_real_escape_string($id); // escape your variable here before u use it in the query

   $result=mysql_query("SELECT c_ID from User_Competence WHERE e_ID='".$id."' ");
        while ($test=mysql_fetch_array($result) ){
        echo $test['c_ID'].'<br />';
            }

mysql_fetch_array does not work

2 Answers2