That's not enough information. For example, is
$chart rendered as a <dataset> element, or a parent of <dataset>? As it is, there's too much we must guess about, so any specifics we might go into are largely supposition.
Code samples should be enough to be complete and to reproduce the problem, but just enough. To be complete, we'll need the table schemata, sample data and a better example of the XML formatted data. You should also
indent your code for readability, which can reveal some basic errors.
the xml appears like this
Code:
<dataset>
<set open="24.6" high="25.24" low="24.58" close="25.19" x="1" volume="17856350"/>
<dataset>
<set open="24.6" high="25.24" low="24.58" close="25.19" x="2" volume="17856350"/>
That can't be right. The <dataset> elements aren't closed. What else is missing? You don't necessarily need to show us the whole XML format, but any elements that appear in the PHP code (such as
$chart) should definitely appear in the XML. Ellipses and indentation can be used to show that the sample is a fragment. "set" doesn't seem an appropriate tag name for the elements. If it isn't forced on you by the API, change it to something more semantically correct, such as "point", "price" or "stock".
Code:
...
<dataset>
<point open="24.6" high="25.24" low="24.58" close="25.19" x="1" volume="17856350"/>
<point open="25.19" high="25.46" low="24.95" close="25.28" x="2" volume="17849533"/>
</dataset>
<dataset>
<point open="13.4" high="13.47" low="13.2" close="13.2" x="1" volume="13488405"/>
<point open="13.2" high="13.23" low="13.08" close="13.21" x="2" volume="13501861"/>
</dataset>
PHP:
$count=count($row['open']);
Relational database columns are scalars (also said to be "atomic" or "simple domains"), which means they don't hold compound values (a.k.a aggregate values). Assuming
$row is a row fetched from a result,
$row['open'] will be a scalar and
count($row['open']) will always be 1. This is also why you only get one result row when adding elements to the chart. Each row contains exactly one datapoint, so your code needs to reflect this.
PHP:
/* The data model's fields shouldn't be defined in the DB query, hence the
* $fields, which should come from inspecting the model definition, whatever
* that is. Here, we inspect a class, which is also used to hold fetched row data.
*/
// get data from the database
$fields = get_fields($class);
$datapoints=$db->prepare("SELECT $fields FROM ... ORDER BY ...");
$datapoints->execute(...);
$datapoints->setFetchMode(PDO::FETCH_CLASS, $class);
// convert data to a document
foreach ($datapoints as $i => $point) {
$point->x = $i;
if (!isset($stocks[$row->stock])) {
$stock = $stocks[$row->stock] = /* create an <dataset> element */;
$chart->appendChild($stock);
} else {
$stock = $stocks[$row->stock];
}
$stock->appendChild(/* convert $point to a <point> element */);
}
This is actually two tasks (
ORM and creating an XML document for later
serialization), and should be split into separate functions.
Note the necessity of ordering the results (the
ORDER BY clause), as otherwise the results will be in an arbitrary order (likely the primary key order) and the x-values will be incorrect.
As for selecting table columns, the columns should be defined elsewhere to reduce
coupling between the
model and data storage/retrieval. For example, you could define a class with properties with the same names as the columns, and inspect it to determine which columns to retrieve.
PHP:
// here's one way of getting a list of fields to fetch from the DB
function get_fields($class) {
return array_keys(get_class_vars($class));
}
// and here's an example class that could be used when fetching rows
class Trading extends Model { // Model also needs to be defined
public $stock, $open, $high, $low, $close, $volume;
...
};
You could make objects responsible for conversion to elements:
PHP:
class Trading extends Model {
static $tagName='point';
public $stock, $open, $high, $low, $close, $volume;
// $stock is the parent, so it isn't an attribute.
static protected $_nonattr = array('stock' => 1);
...
function toElement() {
$elt = $doc->createElement(self::$tagName);
foreach ($this as $field => $value) {
if (! isset(self::$_nonattr[$field])) {
$elt->setAttribute($field, $value);
}
}
return $elt;
}
};
Or you could create a separate class that maps
Trading to elements, à la
data mappers (
[2],
[3]). Data mappers are potentially more flexible, but also a more advanced topic. The first PHP code in this post could be a fragment of a data mapper.
. \but how ever I found I am able to take count of rows and increase the x value by this