CODEWITHSUNDEEP
phpmysql
John Smith
John Smith

Reputation: 23

mysql row into variable using while loop

I'm trying to output data into an xml file, everything is working fine except it's only saving the last record fetched.

The following query is used:

$query = mysql_query("SELECT mileage FROM cars WHERE make ='bmw' ORDER BY date DESC, time DESC LIMIT 3");
while ($row = mysql_fetch_array($query)){
$result = $row['mileage'];
}

My question is how do I make it so each record is saved in 3 seperate variables so I can output those 3 variables into an xml file. I am also trying to fetch only the last 3 rows sorted by the last date and last time so not sure if the query is correct for doing that.

Thank you.

Upvotes: 2

Views: 400

Answers (5)

anselm
anselm

Reputation: 13661

try saving the output as an array instead.

$array[]=$value;

will push the $value inside the $array array.

Upvotes: 0

Moyed Ansari
Moyed Ansari

Reputation: 8461

$result=array();
$query = mysql_query("SELECT mileage FROM cars WHERE make ='bmw' ORDER BY date DESC, time DESC LIMIT 3");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$result[]  = $row['mileage'];

}

Upvotes: 1

Hammerite
Hammerite

Reputation: 22340

Firstly: Don't use the mysql_* functions! They are obsolete and you should use PDO instead.

Secondly: The easiest thing to do is to construct an array containing the results.

$result = array();
$query = mysql_query("SELECT mileage FROM cars WHERE make ='bmw' ORDER BY date DESC, time DESC LIMIT 3");
while ($row = mysql_fetch_array($query)){
    $result[] = $row['mileage'];
}

The results of your query are now contained in $result[0], $result[1] and $result[2]. (If you use PDO the code does not look very different.)

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 157850

get yourself a function

function dbGetCol($sql){
  $ret = array();
  $res = mysql_query($sql) or trigger_error(mysql_error()." ".$sql);
  if ($res) {
    while($row = mysql_fetch_row($res)){
      $ret[] = $row[0];
    }
  }
  return $ret;
}

and then just call it 

$data = dbGetCol("SELECT mileage FROM cars WHERE make ='bmw' ORDER BY date DESC, time DESC LIMIT 3");

Upvotes: -1

Hüseyin BABAL
Hüseyin BABAL

Reputation: 15550

You can use folowing;

$query = mysql_query("SELECT mileage FROM cars WHERE make ='bmw' ORDER BY date DESC, time DESC LIMIT 3");
while ($row = mysql_fetch_array($query)){
$result[] = $row['mileage'];
}

You can foreach $result and construct your xml.

Upvotes: 0

Related Questions