Andrés Andrés - 6 months ago 7
SQL Question

How make a growth baby chart with data in mysql +PDO

I am trying to make a chart with growth baby table I have in DB... I lost the idea and right now I don't know how to do it...The chart I need to show this is the chart I need to show when the doctor insert the height and weight of every child...need to show the inserted the percentiles data and that will depend of the height and weight of the baby to show the graph (gray line)...

here is my code until now (EDITED WITH NEW CODE):

<?php
include 'includes/configs.php';
/*
* $normal is an array of (edad => peso) key/value pairs
* $desnutricion is an array of (edad => peso) key/value pairs
* $desnutricionSevera is an array of (edad => peso) key/value pairs
*
* you can hard-code these or pull them from a database, whatever works for you
*/
$sql = $conn->prepare("SELECT * FROM ESTATURA WHERE edad<>'' AND peso<>'' AND id_paciente = 1");
$sql->execute();
$data = array(array('Meses', $apellido, 'Normal', 'Desnutricion', 'Desnutricion Severa'));
while($row = $sql->fetch(PDO::FETCH_ASSOC)) {
$edad = $row['edad'];
// use (int) to parse the value as an integer
// or (float) to parse the value as a floating point number
// use whichever is appropriate
$edad = (int) preg_replace('/\D/', '', $edad);
$peso = $row['peso'];
$peso = (float) preg_replace('/\D/', '', $peso);

$data[] = array($peso, $edad, $normal[$edad], $desnutricion[$edad], $desnutricionSevera[$edad]);
$data1[] = array($peso, $edad);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title> </title>
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([<?php echo json_encode($data); ?>]);
// sort the data by "Meses" to make sure it is in the right order
data.sort(0);

var options = {
title: 'Grafica de Crecimiento de niñas de 0 a 24 meses',
hAxis: {
title: 'Meses',
titleTextStyle: {color: '#333'}
},
vAxis: {
minValue: 0
},
series: {
0: {
<?php echo implode(",", $peso); ?>
type: 'line'
},
1: {
// series options for normal weight
type: 'area'
},
2: {
// series options for desnutricion
type: 'area'
},
3: {
// series options for desnutricion severa
type: 'area'
}
}
};

var chart = new google.visualization.ComboChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>
</head>
<body>
<div id="chart_div" style="width: 800px; height: 400px;"></div>
</body>
</html>


I don't understand how can insert the default variables (normal, desnutricion and desnutricion severa) with the baby variable.. I need to create a new table with the defaults data and then make a union? or just insert the variables in every series??

--OLD CODE--

<?php
include 'includes/configs.php';

$sql = $conn->prepare("SELECT nombre, apellido, edad, peso FROM ESTATURA WHERE edad<>'' AND peso<>'' ");
$sql->execute();
while($row = $sql->fetch(PDO::FETCH_ASSOC)) {
$nombre = trim(addslashes($row['nombre']));
$lapellido = trim(addslashes($row['apellido']));
$edad = $row['edad'];
$edad = preg_replace('/\D/', '', $edad);
$peso = $row['peso'];
$peso = preg_replace('/\D/', '', $peso);

$myurl[] = "['".$nombre." ".$apellido."', ".$edad.",".$peso."]";


}

print_r($myurl);
echo implode(",", $myurl);
?>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([
['Meses', 'Normal', 'Desnutrición', 'Desnutrición Severa'],
/*['0', 4.23, 2.39, 2.00],
['1', 5.55, 3.10, 2.85],
['2', 6.75, 3.95, 3.41],
['3', 7.60, 4.50, 4.00],
['4', 8.23, 5.00, 4.40],
['5', 8.81, 5.38, 4.80],
['6', 9.30, 5.71, 5.11],
['7', 9.87, 6.00, 5.38],
['8', 10.19, 6.21, 5.58],
['9', 10.56, 6.47, 5.76],
['10', 10.95, 6.66, 5.95],
['11', 11.20, 6.80, 6.10],
['12', 11.55, 7.00, 6.21],
['13', 11.91, 7.20, 6.40],
['14', 12.10, 7.38, 6.58],
['15', 12.37, 7.54, 6.77],
['16', 12.60, 7.75, 6.85],
['17', 12.96, 7.86, 7.00],
['18', 13.16, 8.05, 7.20],
['19', 13.41, 8.20, 7.31],
['20', 13.72, 8.38, 7.42],
['21', 14.02, 8.49, 7.61],
['22', 14.24, 8.70, 7.79],
['23', 14.68, 8.90, 7.95],
['24', 14.90, 9.00, 8.00]*/
<?php echo implode(",", $myurl); ?>
]);

var options = {
title: 'Grafica de Crecimiento de niñas de 0 a 24 meses',
hAxis: {title: 'Meses', titleTextStyle: {color: '#333'}},
vAxis: {minValue: 0}
};

var chart = new google.visualization.AreaChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>

<div id="chart_div" style="width: 800px; height: 400px;"></div>


inside of
/*.....*/
is the percentiles that I need to show with the data in mysql...but I comented because the chart is not shown when that data don't have
/*...*/


here the chart right now..enter image description here

can you help me with my type of chart?

Best Regards

Andrés Valencia

Answer

This is the basic framework you will need to make this work:

/*
 * $normal is an array of (edad => peso) key/value pairs
 * $desnutricion is an array of (edad => peso) key/value pairs
 * $desnutricionSevera is an array of (edad => peso) key/value pairs
 * 
 * you can hard-code these or pull them from a database, whatever works for you
 */
$sql = $conn->prepare("SELECT edad, peso FROM ESTATURA WHERE <criteria to select baby>");
$sql->execute();
$data = array(array('Meses', $apellido, 'Normal', 'Desnutricion', 'Desnutricion Severa'));
while($row = $sql->fetch(PDO::FETCH_ASSOC))  {
    $edad = $row['edad'];
    // use (int) to parse the value as an integer
    // or (float) to parse the value as a floating point number
    // use whichever is appropriate
    $edad = (int) preg_replace('/\D/', '', $edad);
    $peso = $row['peso'];
    $peso = (float) $peso;

    $data[] = array($edad, $peso, $normal[$edad], $desnutricion[$edad], $desnutricionSevera[$edad]);
}

Then, in your javascript:

function drawChart() {
    var data = google.visualization.arrayToDataTable(<?php echo json_encode($data); ?>);
    // sort the data by "Meses" to make sure it is in the right order
    data.sort(0);

    var options = {
        title: 'Grafica de Crecimiento de niñas de 0 a 24 meses',
        hAxis: {
            title: 'Meses',
            titleTextStyle: {color: '#333'}
        },
        vAxis: {
            minValue: 0
        },
        series: {
            0: {
                // series options for this babys weight
                type: 'line'
            },
            1: {
                // series options for normal weight
                type: 'area'
            },
            2: {
                // series options for desnutricion
                type: 'area'
            },
            3: {
                // series options for desnutricion severa
                type: 'area'
            }
        }
    };

    var chart = new google.visualization.ComboChart(document.getElementById('chart_div'));
    chart.draw(data, options);
}

Give that a try and see if it works for you.