Filling an array with values from a single column over specific rows in MySQL DB

gj384 · 11-11-2010, 09:20 AM

Hi there,

I'm trying to populate a single-column array with values from one column in MySQL, but only from certain rows in which a specific other column matches a certain string. This is my function:

PHP Code:


			
function getProductList($field,$term){

      $q = "SELECT PartNo FROM ".TBL_PRODUCTS." WHERE ".$field." = '".$term."'";

      $result = mysql_query($q, $this->connection);

      $dbarray = mysql_fetch_array($result);

      return $dbarray;

   }

"$field" is the name of the column I'm searching, and "$term" is the actual string I'm searching for in that column. "PartNo" is the name of the column from which I want to draw the results, which I then want placed into the array.

This is how I'm calling to get the results of that function:

PHP Code:


			
$compatibleProductsArray=$database->getProductList("Cat",$productInfo[2]);

In the code above, the name of the column I'm searching is "Cat", and the string I'm searching for is stored in "$productInfo[2]".

At the moment, I'm doing a print_r of the array to show the contents, like so:

PHP Code:


			
print_r($compatibleProductsArray);

Which gives me the following output:

Array ( [0] => T7S25T [PartNo] => T7S25T )

But I'm expecting many more rows than that... What am I doing wrong?

Incidentally, after I work out how to fill the array, I'm then planning to convert the array into a CSV string. Is there an easy way to do that, or does knowing that final goal make it any easier to work out a solution to all of the above?

Thanks a million, in advance...

tony · 11-11-2010, 02:27 PM

mysql_fetch_array only returns the first row of the result and then moves the internal data pointer ahead. The array has 2 entries because 1 is for the numeric indexed array and the other one is for the associative array. what you can do is replace this:

php Code:

$dbarray = mysql_fetch_array($result);

to this

php Code:

$dbarray = array();
while($row = mysql_fetch_array($result)) {
    $dbarray[]=$row['PartNo'];
}

If the results are kinda big, it is better if you use mysql_fetch_row or mysql_fetch_assoc since mysql_fetch_array double the entries for numeric and associative.
From the manual:
mysql_fetch_row() - Get a result row as an enumerated array
mysql_fetch_array() - Fetch a result row as an associative array, a numeric array, or both
mysql_fetch_assoc() - Fetch a result row as an associative array
mysql_fetch_object() - Fetch a result row as an object

gj384 · 11-11-2010, 09:45 PM

Wow! So straightforward. Thanks so much! Works like a dream.