mysql row into variable using while loop

Question

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.

Hammerite · Accepted Answer · 2012-04-29 18:00:46Z

1

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.)

answered Apr 29, 2012 at 18:00

Hammerite

22.5k6 gold badges76 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Smith Over a year ago

Thanks, yeah it is working now using $result[0], $result[1] and $result[2].

Moyed Ansari · Accepted Answer · 2012-04-29 18:06:40Z

1

$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'];

}

answered Apr 29, 2012 at 18:06

Moyed Ansari

8,4612 gold badges38 silver badges57 bronze badges

Comments

Hüseyin BABAL · Accepted Answer · 2012-04-29 17:56:59Z

0

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.

answered Apr 29, 2012 at 17:56

Hüseyin BABAL

15.6k5 gold badges53 silver badges74 bronze badges

Comments

Taha Paksu · Accepted Answer · 2012-04-29 23:08:26Z

0

try saving the output as an array instead.

$array[]=$value;

will push the $value inside the $array array.

edited Apr 29, 2012 at 23:08

Taha Paksu

15.6k2 gold badges50 silver badges83 bronze badges

answered Apr 29, 2012 at 17:55

anselm

13.9k2 gold badges20 silver badges18 bronze badges

Comments

Your Common Sense · Accepted Answer · 2012-04-29 17:59:47Z

-1

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");

answered Apr 29, 2012 at 17:59

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

2 Comments

John Smith Over a year ago

Thanks I guess I'll use this next time.

Your Common Sense Over a year ago

LOL. What next time you are talking about? If you have no idea what is a function and how to use it - just say that, no need to hesitate :)

Collectives™ on Stack Overflow

mysql row into variable using while loop

5 Answers 5

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related