getting a column into an array

[freelance84]

Hi,

I'm pretty new to php & mysql, so this may seem very elementary:

 

$query_mq1 = "SELECT com FROM mq1 WHERE number='$mq1_a'";

 

The above query selects everything from the column named "com" from table "mq1" where it matches variable "$mq1_a" from column "number".

 

The following two lines get the query into a php variable or throw up an error if a problem arrises:

 

$result_mq1 = mysql_query($query_mq1);

if (!$result_mq1) die ("Database access failed: " . mysql_error());

 

 

My question is, is it possible to get these results into one array in one move? I can only seem to find "$row = mysql_fetch_row($result_mq1);" which only gets one result at a time.

 

Any help here would be brilliant as I am pretty stuck.

 

Thanks,

 

John.

[freelance84]

Or if not one move which is the simplest/ easiest?

[SaMike]

The function you're looking would be mysql_fetch_array.

I suggest using following code first to get the idea how it works:

$array = mysql_fetch_array($query_mq1);
echo '<pre>';
print_r($array);
echo '</pre>';

 

Or if you want to process the data rigth away you could do this:

while($row = mysql_fetch_row($resut_mq1)){
//Process data here
//Use this like:
//$row['field name'] = field value
//for example:
echo $row['username'];
//would print value of every 'username' field.
echo $row['username'] . ' - ' . $row['email'];
//you get the idea?
}

[freelance84]

$mq1 = get_post('mq1');

$query_mq1 = "SELECT com FROM mq1 WHERE number='$mq1'";

$result_mq1 = mysql_query($query_mq1);

if (!$result_mq1) die ("Database access failed: " . mysql_error());

 

$row = mysql_fetch_array($query_mq1);

print_r($row);

 

This returns "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL"

 

If I apply the fetch_array to the $result_mq1:

$mq1 = get_post('mq1');

$query_mq1 = "SELECT comment FROM mq1 WHERE number='$mq1'";

$result_mq1 = mysql_query($query_mq1);

if (!$result_mq1) die ("Database access failed: " . mysql_error());

 

$row = mysql_fetch_array($result_mq1);

print_r($row);

...it just returns one result and not all.

 

Do I need to use the array_push?

[freelance84]

Ok I've got the following code to get all the results of the query into one array, but it doe seem a little OTT for what it does. Is there no simpler way?

 

$row = mysql_fetch_row($result_mq1);

$rows = mysql_num_rows($result_mq1);

for ($j = 0 ; $j < $rows ; ++$j)

{

$content = mysql_fetch_row($result_mq1);

array_push($row,$content);

}

print_r($row);

[trq]

The simplest way is....

 

$data = array();
while ($row = mysql_fetch_array($result_mq1)) {
  $data[] = $row;
}

[freelance84]

Using the more efficient extraction into an array:

 

$data = array();

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

  $data[] = $row;

}

print_r($data)

 

The print_r($data) results in the following:

Array ( [0] => Array ( [0] => test AA [comment] => test AA ) [1] => Array ( [0] => test BB [comment] => test BB ) )

 

Is this now an associative array?

echo (data[0]);    this just returns 'array'

 

How do I just return the 1st result from the array?

[SaMike]

Try mysql_fetch_assoc. Also, my original code had wrong variable ($query_mq1), thats why it errored.

 

$array = mysql_fetch_assoc($result_mq1);
echo '<pre>';
print_r($array);
echo '</pre>';

[freelance84]

Hi SaMike,

 

cheers, but this still just returns one result but now in an associative array

[trq]

Hi SaMike,

 

cheers, but this still just returns one result but now in an associative array

 

Sorry, I should have used....

 

$data = array();
while ($row = mysql_fetch_assoc($result_mq1)) {
  $data[] = $row;
}

 

$data will now contain all your records. With the first being within $data[0], the second $data[1] etc etc.

[freelance84]

Ah brilliant cheers

[salathe]

It looks like you might be wanting something more like the following, which brings back the values from the com column.

 

$data = array();
while ($row = mysql_fetch_assoc($result_mq1)) {
    $data[] = $row['com'];
}

 

A more generic version of the above would be to use mysql_fetch_row and $row[0].

[freelance84]

Yea cheers salathe. I managed to figure out  a solution to above using fetch_row in a for loop.