I want all values, but MySQL is only returning one

[satre]

Hi Folks,

 

I'm thinking this is simple, but just can't seem to figure it out. I use similar code for different tables that works fine, but for this table, it doesn't work so I'm thinking this is a MySQL issue.

 

Here's the code:

$sqlmeds = mysql_query("SELECT ALL value FROM test WHERE type like 'media:'");
$meds = mysql_fetch_array($sqlmeds);

foreach ($meds as $med){
echo "$med";
}

 

and here is the relevant area of the table with row names "type" and "value"

 

type          value

.

.

.

media: painting

media: works on paper

media: collage/assemblage

.

.

.

 

When I run the query to find all 3 values from within phpMyAdmin, no problem, all three are returned, but when I run the exact same query from php, I only get "painting" returned, and oddly, I get an array with two values in it, both of which are "painting".

 

Any ideas??? Thanks!!

 

p.s. I also tried this just in case instead of the foreach, but same result:

for ($i=0; isset($meds[$i]); ++$i) {
echo "$meds[$i]";
}

[trq]

You need to loop through all the results within $sqlmeds.

 

The general syntax is:

 

if ($result = mysql_query($sql)) {
  while ($row = mysql_fetch_assoc($result)) {
    // $row holds a row from the database
  }
}

[Psycho]

When I run the query to find all 3 values from within phpMyAdmin, no problem, all three are returned, but when I run the exact same query from php, I only get "painting" returned, and oddly, I get an array with two values in it, both of which are "painting".

 

And the reason you get an array with the same value twice is explained in the manual for mysql_fetch_array()

Returns an array of strings that corresponds to the fetched row, or FALSE if there are no more rows. The type of returned array depends on how result_type is defined. By using MYSQL_BOTH (default), you'll get an array with both associative and number indices. Using MYSQL_ASSOC, you only get associative indices (as mysql_fetch_assoc() works), using MYSQL_NUM, you only get number indices (as mysql_fetch_row() works).

 

Personally, I always use mysql_fetch_assoc() which only returns an array of associative (i.e. named) indices - which is the same as using mysql_fetch_array() with the MYSQL_ASSOC flag. I've never seen the need to return two sets of the same values or to refer to values by their indices. Either one can easily cause code to break of fields are added/removed. There are some situations when you would need the other data - but those should be the exception, not the norm.

[satre]

thank you both. got it!