Copy all records with 2 equals data from table1 to one record in table2

[paolo123]

Good morning,
i have this problem in mysql.
I have a table like this one:

Table1

AccessDate__UserCode__AnalisisName____AnalisisResult

2012-01-01____1____________a1______________10
2012-01-01____1____________a2______________15
2012-01-01____1____________a3______________12

2014-03-01____1____________a1______________12
2014-03-01____1____________a3______________13

2012-01-01____2____________a1______________14
2012-01-01____2____________a2______________15
2012-01-01____2____________a3______________20

2013-03-01____2____________a1______________3

2012-01-01____3____________a1______________2
2012-01-01____3____________a2______________1

2014-01-01____3____________a1______________10

I would like copy the data in an other table setting all the records with same AccessDate and UserCode on the same record, and using AnalisisName value as column pointer.
I mean:

Table2

AccessDate___UserCode___a1__a2__a3

2012-01-01_____1________10__15_12
2014-03-01_____1________12___-_13
2012-01-01_____2________14__15_20
2013-03-01_____2_________3___
2012-01-01_____3_________2___1_-
2014-01-01_____3________10___

 

i would like to know if it is possible prepare a single query which make this data conversion because table1 has 2500000 records. I wrote also a php script which make this, but it is too slow..

 

Thank you very much for your help!!
Best regards
Paolo

 

[Ch0cu3r]

Why does the data need to be copied into another table? Databases should not be treated like spreadsheets.

[Barand]

I agree with ch0cu3r. The data in tabe1 is correctly normalized and you should keep it like that.

 

If you want to display the data in the format of table2, do it on output after querying table1.

[paolo123]

Hello,

i would like display data in table2 format in order to perform query to select 

Users which have Analisis result over (or under) thresholds, and Analisis performed in the same date.

For example:

Select the users which have a1.value>10, a2.value>13, a3.value>19 in the same date

Do you know how perform this query?

I don't know how performing this query on table1, instead i would know how performing it on table2.

 

Or do you know how performing a query on table1 which return table2 format?

 

thanks a lot

paolo

[Barand]

I generated a test table with 2,550,000 records and used two different methods to produce the output

1. Using PHP to match dates/user and format output
2. Using MySQL to match dates/user and format output

Both the above methods took around 34 seconds to process the output.

 

I then ran a query to create a summary table as in your table2.

INSERT INTO result_summary (usercode,accessdate, a1,a2,a3)
SELECT 
    usercode
    , accessdate
    , MIN(IF(analysisname='a1',analysisresult,NULL)) as a1
    , MIN(IF(analysisname='a2',analysisresult,NULL)) as a2
    , MIN(IF(analysisname='a3',analysisresult,NULL)) as a3
    FROM
        result
    GROUP BY usercode , accessdate

This took around 100 seconds but then querying the summary table (method 3 below) only took about 0.25 seconds.

All three gave the same results.

<?php
$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);


/*************************************************************
* METHOD 1
* 
* USE PHP QUERY TO MATCH 
* AND SELECT REQUIRED VALUES
* AND FORMAT THE OUTPUT
**************************************************************/

$sql = "SELECT 
      usercode
    , accessdate
    , analysisname
    , analysisresult
    FROM result
        WHERE ((analysisname = 'a1' AND analysisresult > 10)
        OR (analysisname = 'a2' AND analysisresult > 13)
        OR (analysisname = 'a3' AND analysisresult > 19))
    ORDER BY usercode, accessdate";
$res = $db->query($sql);

$empty_results = ['a1'=>'0', 'a2'=>0, 'a3'=>0];
$output1 = '';
$currdate = $curruser = '';
$results = $empty_results;
while (list($user, $date, $anal, $val) = $res->fetch_row()) {
    if (($currdate != $date)||($curruser != $user)) {
        if ($currdate && $results['a1'] && $results['a2'] && $results['a3']) {
            $output1 .= "<tr><td>$curruser</td><td>$currdate</td><td>";
            $output1 .= join('</td><td>', $results) . "</td></tr>\n"; 
        }
        $currdate = $date;
        $curruser = $user;
        $results = $empty_results;
    }
    $results[$anal] = $val;             // store value in row 
}
// don't forget the last row
if ($currdate && $results['a1'] && $results['a2'] && $results['a3']) {
            $output1 .= "<tr><td>$curruser</td><td>$currdate</td><td>";
            $output1 .= join('</td><td>', $results) . "</td></tr>\n";
}


/*************************************************************
* METHOD 2
* 
* USE MYSQL QUERY TO MATCH 
* AND SELECT REQUIRED VALUES
* AND FORMAT THE OUTPUT
**************************************************************/

$sql = "SELECT 
    usercode
    , accessdate
    , MIN(IF(analysisname='a1',analysisresult,NULL)) as an1
    , MIN(IF(analysisname='a2',analysisresult,NULL)) as an2
    , MIN(IF(analysisname='a3',analysisresult,NULL)) as an3
    FROM
        (
        SELECT 
        usercode
        , accessdate
        , analysisname
        , analysisresult
        FROM result
            WHERE (analysisname = 'a1' AND analysisresult > 10)
            OR (analysisname = 'a2' AND analysisresult > 13)
            OR (analysisname = 'a3' AND analysisresult > 19)
        ) x
    GROUP BY usercode , accessdate 
    HAVING an1 IS NOT NULL and an2 IS NOT NULL AND an3 IS NOT NULL";
$res = $db->query($sql);
$output2 = '';
while (list($user, $date, $a1, $a2, $a3) = $res->fetch_row()) {
    $output2 .= "<tr><td>$user</td><td>$date</td><td>$a1</td><td>$a2</td><td>$a3</td></tr>\n";
}

/*************************************************************
* METHOD 3
* 
* USE MYSQL QUERY TO CREATE 
* SUMMARY TABLE IN REQUIRED
* FORMAT AND QUERY THE SUMMARY
* TABLE FOR REQUIRED VALUES
**************************************************************/
$t1 = microtime(1);
$sql = "SELECT usercode
    , accessdate
    , a1
    , a2
    , a3
    FROM result_summary
    WHERE a1 > 10 AND a2 > 13 AND a3 > 19
    ORDER BY usercode, accessdate";
$res = $db->query($sql);
$output3 = '';
while (list($user, $date, $a1, $a2, $a3) = $res->fetch_row()) {
    $output3 .= "<tr><td>$user</td><td>$date</td><td>$a1</td><td>$a2</td><td>$a3</td></tr>\n";
}
$t2 = microtime(1);
?>
<!DOCTYPE=html>
<html>
<head>
<title>Example</title>
</head>
<body>
    <table border='1' style="border-collapse: collapse;">
        <tr><th>User</th><th>Date</th><th>a1</th><th>a2</th><th>a3</th></tr>
        <?=$output1?>
    </table>
    <br>
    <hr>
    <table border='1' style="border-collapse: collapse;">
        <tr><th>User</th><th>Date</th><th>a1</th><th>a2</th><th>a3</th></tr>
        <?=$output2?>
    </table>
    <table border='1' style="border-collapse: collapse;">
        <tr><th>User</th><th>Date</th><th>a1</th><th>a2</th><th>a3</th></tr>
        <?=$output3?>
    </table>
    <br>
</body>
</html>

[paolo123]

Thank you very much Barand!!   

this is very useful for my graduation thesis in medicine.

Best regards

paolo

[paolo123]

Hi,

it's again me.

I tried the query, but it says:

 

#1054 - Unknown column 'Acido Urico' in 'field list' 

 

(Acido Urico is an analisysname)

 

Infact in table result there are only these fields:

AccessDate__UserCode__AnalisisName____AnalisisResult

 

 

Could you help me?

Thanks a lot

[Barand]

Sounds like missing quote marks. What is the query you are running?

[paolo123]

The query is this:

 

INSERT INTO mastertot2 (CodicePz,DataRichiesta,

`Acido Urico`,

`Albumina`,

`Azotemia`,

`Creatinina`,

`Glicemia`,

`HGB Emoglobina`,

`INR`,

`Litio`,

`Peptide Natriuretico Tipo B`,

`Peptide Natriuretico Tipo B N-Terminale`,

`Potassio`,

`Proteina C Reattiva`,

`Proteine`,

`Proteine Totali`,

`Sodio`)

SELECT 

      CodicePz

    , DataRichiesta

    , MIN(IF(DescrPrestaz=`Acido Urico`,risultato,NULL)) as `Acido Urico`

    , MIN(IF(DescrPrestaz=`Albumina`,risultato,NULL)) as `Albumina`

    , MIN(IF(DescrPrestaz=`Azotemia`,risultato,NULL)) as `Azotemia`

    , MIN(IF(DescrPrestaz=`Creatinina`,risultato,NULL)) as `Creatinina`

    , MIN(IF(DescrPrestaz=`Glicemia`,risultato,NULL)) as `Glicemia`

    , MIN(IF(DescrPrestaz=`HGB Emoglobina`,risultato,NULL)) as `HGB Emoglobina`

    , MIN(IF(DescrPrestaz=`INR`,risultato,NULL)) as `INR`

    , MIN(IF(DescrPrestaz=`Litio`,risultato,NULL)) as `Litio`

    , MIN(IF(DescrPrestaz=`Peptide Natriuretico Tipo B`,risultato,NULL)) as `Peptide Natriuretico Tipo B`

    , MIN(IF(DescrPrestaz=`Peptide Natriuretico Tipo B N-Terminale`,risultato,NULL)) as `Peptide Natriuretico Tipo B N-Terminale`

    , MIN(IF(DescrPrestaz=`Potassio`,risultato,NULL)) as `Potassio`

    , MIN(IF(DescrPrestaz=`Proteina C Reattiva`,risultato,NULL)) as `Proteina C Reattiva`

    , MIN(IF(DescrPrestaz=`Proteine`,risultato,NULL)) as `Proteine`

    , MIN(IF(DescrPrestaz=`Proteine Totali`,risultato,NULL)) as `Proteine Totali`

    , MIN(IF(DescrPrestaz=`Sodio`,risultato,NULL)) as `Sodio`

FROM

        mastertot

    GROUP BY CodicePz , DataRIchiesta

 

 

 

While the tables are these:

 

mastertot

Colonna Tipo Null Predefinito Commenti MIME

DataRichiesta varchar(10) Sì  NULL    

CodicePz varchar(15) Sì  NULL    

Cognome varchar(50) Sì  NULL    

Nome varchar(50) Sì  NULL    

Genere varchar(1) Sì  NULL    

DataNascita varchar(15) Sì  NULL    

eta int(11) Sì  NULL    

codprestaz varchar( Sì  NULL    

DescrPrestaz varchar(50) Sì  NULL    

risultato decimal(8,3) Sì  NULL    

udm varchar(20) Sì  NULL    

RepartoProvenienza varchar(50) Sì  NULL    

Laboratorio varchar(45) Sì  NULL    

CodImpegnativa varchar(32) Sì  NULL    

DataEmissione varchar(9) Sì  NULL    

CodMedico varchar( Sì  NULL    

Medico varchar(55) Sì  NULL    

 

mastertot2

Colonna Tipo Null Predefinito Commenti MIME

CodicePz varchar(15) No       

DataRichiesta date No       

Acido Urico decimal(10,3) No       

Albumina decimal(10,3) No       

Azotemia decimal(10,3) No       

Creatinina decimal(11,3) No       

Glicemia decimal(11,3) No       

HGB Emoglobina decimal(11,3) No       

INR decimal(11,3) No       

Litio decimal(11,3) No       

Peptide Natriuretico Tipo B decimal(8,3) No       

Peptide Natriuretico Tipo B N-Terminale decimal(10,3) No       

Potassio decimal(11,3) No       

Proteina C Reattiva decimal(11,3) No       

Proteine decimal(10,3) No       

Proteine Totali decimal(11,3) No       

Sodio decimal(11,3) No       

[Barand]

 

MIN(IF(DescrPrestaz=`Acido Urico`,risultato,NULL)) as `Acido Urico`

The backticks specify it as a column name but in this instance it is string value and needs to be in single quotes IE

MIN(IF(DescrPrestaz='Acido Urico',risultato,NULL)) as `Acido Urico`

Don't throw your normalized table away, keep updating that one as it is far more versatile. Suggest you recreate the summary table and run this query overnight as as cron job. Use the summary table purely for speeding up the web queries.

[paolo123]

ok, i replaced  char with ' in the SELECT arguments

 MIN(IF(DescrPrestaz='Acido Urico',risultato,NULL)) as 'Acido Urico'

 

the query now is

 

INSERT INTO mastertot2 (CodicePz,DataRichiesta,

`Acido Urico`,

`Albumina`,

`Azotemia`,

`Creatinina`,

`Glicemia`,

`HGB Emoglobina`,

`INR`,

`Litio`,

`Peptide Natriuretico Tipo B`,

`Peptide Natriuretico Tipo B N-Terminale`,

`Potassio`,

`Proteina C Reattiva`,

`Proteine`,

`Proteine Totali`,

`Sodio`)

SELECT 

      CodicePz

    , DataRichiesta

    , MIN(IF(DescrPrestaz='Acido Urico',risultato,NULL)) as 'Acido Urico'

    , MIN(IF(DescrPrestaz='Albumina',risultato,NULL)) as 'Albumina'

    , MIN(IF(DescrPrestaz='Azotemia',risultato,NULL)) as 'Azotemia'

    , MIN(IF(DescrPrestaz='Creatinina',risultato,NULL)) as 'Creatinina'

    , MIN(IF(DescrPrestaz='Glicemia',risultato,NULL)) as 'Glicemia'

    , MIN(IF(DescrPrestaz='HGB Emoglobina',risultato,NULL)) as 'HGB Emoglobina'

    , MIN(IF(DescrPrestaz='INR',risultato,NULL)) as 'INR'

    , MIN(IF(DescrPrestaz='Litio',risultato,NULL)) as 'Litio'

    , MIN(IF(DescrPrestaz='Peptide Natriuretico Tipo B',risultato,NULL)) as 'Peptide Natriuretico Tipo B'

    , MIN(IF(DescrPrestaz='Peptide Natriuretico Tipo B N-Terminale',risultato,NULL)) as 'Peptide Natriuretico Tipo B N-Terminale'

    , MIN(IF(DescrPrestaz='Potassio',risultato,NULL)) as 'Potassio'

    , MIN(IF(DescrPrestaz='Proteina C Reattiva',risultato,NULL)) as 'Proteina C Reattiva'

    , MIN(IF(DescrPrestaz='Proteine',risultato,NULL)) as 'Proteine'

    , MIN(IF(DescrPrestaz='Proteine Totali',risultato,NULL)) as 'Proteine Totali'

    , MIN(IF(DescrPrestaz='Sodio',risultato,NULL)) as 'Sodio'

FROM

        mastertot

    GROUP BY CodicePz , DataRIchiesta

 

 

it is correct?

 

i launched it and it works (elapsed time 111.1160 secondi.)

 

Just to clarify, i do not understand the meaning of MIN.

The IF function is not enough? Does not already  return null or the value if it exists?

 

Then i do not understand how the SELECT in the query works.

 

Thank you !!

[Barand]

I wanted to create a single row for each CodicePz,DataRichiesta combination, hence the group by. But to get the individual values into 1 row I had to use an aggregation function (eg MIN) other wise it would only  take the values from the first row of each group.

 

The IFs were to put each value in its own column.

[paolo123]

Here it is the php script which i wrote, but it is really too slow.... but why is so slow?

Then an other doubt, when i create a connection, i can perform all the query that i want (as in the script)?

Thanks!

 

<html>

<head>

<style>

h1 {color:black;font-family:verdana;}

p {color:black;font-family:verdana;}

table, th, td {

    border: 1px solid black;

    border-collapse: collapse;

}

</style>

</head>

<body bgcolor="#33CC33">

<h1 style="text-align:center">Insert data in table mastertot2</h1>

<p style="text-align:center">Tutte le analisi di uno stesso giorno nella stessa riga</p>

 

 <?php

 

$servername = "localhost";

$username = "root";

$password = "";

$dbname = "IRAdb";

$CodicePz ="";

 

$i = 1;

$j = 0;

 

$startTime = new DateTime();

echo "Start time: " . $startTime->format('Y-m-d H:i:s'); 

echo  "<br> <br> <br>";

 

// Create connection 

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}

 

$sql1 = "SELECT DataRichiesta, CodicePz FROM mastertot2;"; //" LIMIT 100;";

$result1 = $conn->query($sql1);

 

//$conn->close();

 

while($row1 = $result1->fetch_assoc())

{

/* $conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

*/

$sql3 = "SELECT * FROM mastertot_head10000 WHERE CodicePz='" . $row1["CodicePz"] . 

"' AND DataRichiesta ='" . $row1["DataRichiesta"] . "'";

 

$result3 = $conn->query($sql3);

// $conn->close();

 

if ($result3->num_rows > 0)

{

while($row3 = $result3->fetch_assoc())

{

 

echo $i . "   " . $row3["DescrPrestaz"] . " " . $row3["risultato"] . "  " . $row1["CodicePz"] . "  " . $row1["DataRichiesta"] . "<br>";

 

if ($j > 1000) {

echo "Progress index " . $i . "<br>";

$j = 0;

}

 

$i++;

$j++;

 

/* $conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

*/

$sql4 = "UPDATE mastertot2 SET " . $row3["DescrPrestaz"] . "=" . $row3["risultato"] . 

" WHERE CodicePz='" . $row1["CodicePz"] . "' AND DataRichiesta = '" . $row1["DataRichiesta"] . "'";

 

$result4 = $conn->query($sql4);

// $conn->close();

 

 

}

} else {};

 

//$conn->close();

 

}

 

$conn->close();

 

echo  "<br> <br> <br>";

 

$endTime = new DateTime();

echo "End time: " . $endTime->format('Y-m-d H:i:s') . "<br>";

 

$elapsedTime = $endTime->diff($startTime);

echo "Elapsed time: " . $elapsedTime->format('%Y-%m-%d %H:%i:%s');

 

 

?>

 

</body>

</html>

[Barand]

It's slow because you are hammering the server by running queries inside a loop. If the first query returns 10,000 rows then you are calling the queries inside the loop 10000 times.

 

You should run a single query using JOINs and process that.

[Barand]

Or perhaps something along these lines where all values in a record are updated in a single query instead of lots of individual queries for each value

<?php
$db= new mysqli($servername, $username, $password, $dbname);
$sql = "SELECT 
          DataRichiesta 
        , CodicePz
        , DescrPrestaz 
        , risultato 
        FROM mastertot
        ORDER BY DataRichiesta , CodicePz";
$res = $db->query($sql);
$results = [];
$curdat = $curcode = '';
while (list($dat, $code, $descr, $risult) = $res->fetch_row()) {
    if (($curdat != $dat) || ($curcode != $code)) {
        if ($curdat) {
            updateMastertot2($db, $curdat, $curcode, $results);
        }
        $curdat = $dat;
        $curcode = $code;
        $results = [];
    }
    $results[$descr] = $risult;
}
updateMastertot2($db, $curdat, $curcode, $results);

//
// function to update all values in mastertot2 with a single query
//

function updateMastertot2($db, $curdat, $curcode, $results)
{
    $sets = [];
    foreach($results as $desc=>$res) {
        $sets[] = "`$desc` = $res";
    }
    $sql = "UPDATE mastertot2 SET \n"
            . join(",\n", $sets)
            . "\nWHERE DataRichiesta='$curdat' AND CodicePz='$curcode' ";
    $db->query($sql);
}
?>

[paolo123]

I compare two php script launching your php script and my php script on a 10000 records mastertot, and your is really faster!

Your

Start time: 2015-05-07 09:24:45
End time: 2015-05-07 09:27:17
Elapsed time: 00-0-0 00:2:32

 

Mine

Start time: 2015-05-07 09:35:38

End time: 2015-05-07 09:47:04
Elapsed time: 00-0-0 00:11:26

 

 

Anyway i have your query which is the best!

 

Thanks again!!!

 

ps

sorry i forgot telling you that mastertot2 was filled previously with the query

INSERT INTO mastertot2 (`CodicePz`, `DataRichiesta`)
SELECT `CodicePz`, `DataRichiesta` FROM mastertot_head10000
GROUP BY `DataRichiesta`, `CodicePz`

[paolo123]

just a curiosity... cuold you clarify me your php script, 

 

this part is satified each time code or user changing:

 

    if (($curdat != $dat) || ($curcode != $code)) {

        if ($curdat) {

            updateMastertot2($db, $curdat, $curcode, $results);

        }

 

So updateMastertot2 is called for each mastertot record, i understood correctly?

[Barand]

The first "if" test for new mastertot record so we can update with the array of values accumulated for the previous mastertot record.

 

The second "if" is so we don't update on the first record when we have not yet accumulated any data

[paolo123]

Hi,

i have an other problem, i would like obtaining for each user the time trend of an analysis.

I mean, considering table

 

Table1

AccessDate__UserCode__AnalisisName____AnalisisResult
2012-01-01____1____________a1______________10 
2012-01-01____1____________a2______________15 
2012-01-01____1____________a3______________12 
2014-03-01____1____________a1______________12 
2014-03-01____1____________a3______________13 
2012-01-01____2____________a1______________14 
2012-01-01____2____________a2______________15 
2012-01-01____2____________a3______________20 
2013-03-01____2____________a1______________3 
2012-01-01____3____________a1______________2 
2012-01-01____3____________a2______________1 
2014-01-01____3____________a1______________10

 

I would like obtaining a table like this, which contains the analysis a1 time trend for each user:

 

Table 2

User_____date.a1.1_____a1.1_____date.a1.2_____a1.2_____date.a1.3_____a1.3_____date.a1.4_____a1.4_____date.a1.5_____a1.5

1_______2012-01-01_______10______2012-01-01______12______________________________________________________________

2_______2012-01-01______14______2013-03-01______3______________________________________________________________

3_______2012-01-01_______2______2014-01-01_____10______________________________________________________________

 

 

Table 2 should contain 50 column.

I would like obtaining this table format in order to select users with a particular analysis time trend.

For instance users which have a1 time trend like this: five times over 10 in 5 consecutive analysis.

 

Do you think that table2 is useful to obtain this trend.

Do you think it is possible performing it with a sql query or i must use also php ?

 

Thank a lot

Best regards

Paolo

[Barand]

Don't double-post your questions, it wastes our time.

 

See my answer in the other post you made on this.

[paolo123]

ok, sorry