How to save data from various Mysql queries?

Asked

Viewed 149 times

2

I have a table with data of various metals and I need to take this data to use on my page, but I’m not getting to pick all, only the first works.

Code:

$link = mysqli_connect("localhost", "root", "", "metais");
$result = mysqli_query($link, "SELECT * FROM historical_data ORDER BY ID DESC LIMIT 2");

    $cobre = "[";
    while($array_cobre = mysqli_fetch_array($result)){
        $d1 = explode("-", $array_cobre['data']);
        $cobre .= "{x: new Date(".$d1['0'].", ".($d1['1'] - 1).", ".$d1['2']."), y: ".$array_cobre['copper']."},";
    }
    echo $cobre .= "]";

    $aluminio = "[";
    while($array_aluminio = mysqli_fetch_array($result)){
        $d1 = explode("-", $array_aluminio['data']);
        $aluminio .= "{x: new Date(".$d1['0'].", ".($d1['1'] - 1).", ".$d1['2']."), y: ".$array_aluminio['aluminium']."},";
    }
    echo $aluminio .= "]";

    $chumbo = "[";
    while($array_chumbo = mysqli_fetch_array($result)){
        $d1 = explode("-", $array_chumbo['data']);
        $chumbo .= "{x: new Date(".$d1['0'].", ".($d1['1'] - 1).", ".$d1['2']."), y: ".$array_chumbo['lead']."},";
    }
    echo $chumbo .= "]";

Exit:

[{x: new Date(2016, 2, 25), y: 493100},{x: new Date(2016, 2, 24), y: 493100},]
[]
[]

New code (Array):

$itens = []; 
while($array = mysqli_fetch_array($result)){
    $d1 = explode("-", $array['data']); 
    $itens['cobre'][] = sprintf("{x: new Date(%s, %s, %s), y: %s}", $d1[0], $d1[1]-1, $d1[2], $array['copper']);
    $itens['aluminio'][] = sprintf("{x: new Date(%s, %s, %s), y: %s}", $d1[0], $d1[1], $d1[2], $array['aluminium']);
    $itens['chumbo'][] = sprintf("{x: new Date(%s, %s, %s), y: %s}", $d1[0], $d1[1]-1, $d1[2], $array['lead']);
} 
echo json_encode($itens);

New output (Array):

{
"cobre":["{x: new Date(2016, 2, 25), y: 493100}","{x: new Date(2016, 2, 24), y: 493100}"],
"aluminio":["{x: new Date(2016, 03, 25), y: 146950}","{x: new Date(2016, 03, 24), y: 146950}"],
"chumbo":["{x: new Date(2016, 2, 25), y: 175500}","{x: new Date(2016, 2, 24), y: 175500}"]
}

How do I take each metal separately and play in the variable I will use?

echo json_encode($itens['cobre']);
echo json_encode($itens['aluminio']);
echo json_encode($itens['chumbo']);
php mysql

  • Are you mounting a json with this? pq does not use json_encode?

  • because I don’t know how to use msm @rray, would you help me with this? I was doing it the way I knew how.

  • These 3 queries are chained or separated?

  • they are separate, I need to fzr 3 different json and independent of each other using the $result query. Each json takes the info from a different metal in the table

  • I did not understand you run 3 times the same negotiations to get the same information? in all is using $result in the mysqli_fetch_array

  • 1

    see if it works : $itens = [];
 while($array_cobre = mysqli_fetch_array($result)){
 $d1 = explode("-", $array_cobre['data']);
 $itens['coper']['x'][] = sprintf("new Date(%s, %s, %s)", $d1[0], $d1[1]-1, $d1[2]);
 $itens['coper']['y'][] = $array_aluminio['copper'];
 
 $itens['aluminium']['x'][] = sprintf("new Date(%s, %s, %s)", $d1[0], $d1[1]-1, $d1[2]);
 $itens['aluminium']['y'][] = $array_aluminio['aluminium'];
 
 $itens['lead']['x'][] = sprintf("new Date(%s, %s, %s)", $d1[0], $d1[1]-1, $d1[2]);
 $itens['lead']['y'][] = $array_aluminio['lead'];
 }
 
 echo json_encode($itens);

  • at first not @rray, but with some adjustments it worked yes, I will edit the question with the new output, help me to separate please

  • Use, echo $itens['cobre']['x'] or echo $itens['chumbo']['x'] does not solve? what is missing? or the formatting of the data got wrong?

  • so I’m using this in a jquery function (canvasjs), and each material has to look exactly like this: {x: new date(2016, 03, 24), y: 493100}, {x: new date(2016, 03, 25), y: 493100}, ...

  • I changed the code, I believe that now the result is right, basically changed the assignment of the array and the creation of an Indice($i) to join x and y, see at that link

  • I didn’t quite understand @rray, but I made a change to its first code that worked: $items = []; while($array = mysqli_fetch_array($result)){ $D1 = explode("-", $array['data']); $items['copper'][] = sprintf("{x: new Date(%s, %s, %s), y: %s}", $D1[0], $D1[1]-1, $D1[2], $array['Copper']); $items['aluminio'][] = sprintf("{x: new Date(%s, %s, %s), y: %s}", $D1[0], $D1[1], $D1[2], $array['Aluminium']); $items['lead'][] = sprintf("{x: new Date(%s, %s, %s), y: %s}", $D1[0], $D1[1]-1, $D1[2], $array['lead']); } echo json_encode($items);

  • This code has become simpler, basically you already send the formatted string.

  • was at the base ta intuição kk vlw msm! grabs the new code I put in the question and leaves as answer Aki for me to tag @rray

  • Raylan answers you yourself :) who had the idea of how to simplify was you. Look my code of ideone hahaha was not legal. After a while you can accept your answer.

  • ah blz then, but vlw msm!

Show 10 more comments

1 answer

1


Solved!

Code:

$itens = []; 
while($array = mysqli_fetch_array($result)){
    $d1 = explode("-", $array['data']); 
    $itens['cobre'][] = sprintf("{x: new Date(%s, %s, %s), y: %s}", $d1[0], $d1[1]-1, $d1[2], $array['copper']);
    $itens['aluminio'][] = sprintf("{x: new Date(%s, %s, %s), y: %s}", $d1[0], $d1[1], $d1[2], $array['aluminium']);
    $itens['chumbo'][] = sprintf("{x: new Date(%s, %s, %s), y: %s}", $d1[0], $d1[1]-1, $d1[2], $array['lead']);
} 
echo json_encode($itens);

Exit:

{
"cobre":["{x: new Date(2016, 2, 25), y: 493100}","{x: new Date(2016, 2, 24), y: 493100}"],
"aluminio":["{x: new Date(2016, 03, 25), y: 146950}","{x: new Date(2016, 03, 24), y: 146950}"],
"chumbo":["{x: new Date(2016, 2, 25), y: 175500}","{x: new Date(2016, 2, 24), y: 175500}"]
}

Separate metals:

echo json_encode($itens['cobre']);
echo json_encode($itens['aluminio']);
echo json_encode($itens['chumbo']);

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.