Creare una subquery in mysql

[Micogian]

Ho già detto che il punto più complicato è questo:

4) Aggiunga alla scelta appena effettuata le 2 row precedenti e le 2 row successive a quelle individuate nello step 3, sempre escludendo quelle che contengono "_18_" (nell'esempio le row 6, 8, 9, 11 e 12).

Ora una query elabora i records in sequenza, se ha le caratteristiche richieste il record viene selezionato. Ma come faccio a dirgli che deve prendere anche le precedenti 2 righe che non hanno le caratteristiche richieste e che sono già state elaborate e quindi escluse?
Potrei usare un numero sequenziale e se il record estratto è il n.10 posso dirgli di prendere anche i numeri 8 e 9.
Ma i numeri 8 e 9 potrebbero essere già stati selezionati perchè contengono una delle altre caratteristiche, quindi dovrei inserire anche un controllo che non ci siano righe doppie, insomma un bel rebus.
Non so se mi sono spiegato

[Sir Xiradorn]

Giusto per sapere: chi o cosa devono elaborare i dati? Un programma? Una app? Un povero schiavo o uno a cui vuoi molto male ?

Scherzi a parte magari se ci dici da chi o cosa devono leggere vediamo di ottimizzarti la cosa e renderti la vita più semplice (che come mico ha detto semplice non è per nulla anche già con un linguaggio con potenzialità più estese di mysql)

[Micogian]

Ricapitolando, la prima query estrae i records che contengono la stringa "_18_"
Si crea un array contenente tutti i codici di tutte le righe selezionate.
A questo punto bisogna trovare i codici più frequenti.
Si puo fare come nell'esempio seguente:

Codice: Seleziona tutto

<?php
$dati1 ="_1_3_4_13_22__1_11_12_13_22__1_11_12_13_22_";
$dati2 = str_replace("__","_", $dati1);
$array	= array_count_values(explode("_",$dati2));
arsort($array);
foreach ($array as $key => $value) {
	echo $key . " = " . $value . "<br />";
	$cod[] = $key ;
}
echo "i 2 valori pi&ugrave; frequenti sono: <br />";
echo "key1= " . $cod[0] . "<br />";
echo "key2= " . $cod[1] . "<br />";
?>

Spiegazione:
1) $dati1 è la stringa che contiene tutti i codici di tutte le righe selezionate.
2) $dati2 è la stringa pulita dalla doppia linea "__"
3) $array è il conteggio delle volte che si presentano i vari codici nell'array.
4) arsort mette in ordine decrescente il risultato.
5) foreach estrae i 2 codici più frequenti.

La fase successiva è una query che estrare i records che contengono i 2 codici selezionati.
Il problema è il solito, le 2 righe precedenti e quelle successive.
Bisogna selezionare e numerare tutte le righe, accantonare solo quelle che rispettano la selezione e aggiungere le 2 precedenti e le 2 successive.
Il problema è che ci potrebbero finire righe che sono già selezionate, oltre al fatto che se i records sono 50.000 l'operazione è alquanto laboriosa.

[Micogian]

Se non ho fatto errori questa sarebbe la procedura con un file php.
Ovviamente va aggiunto il codice di connessione al DB.

Codice: Seleziona tutto

<?php
// FASE 1 - SELEZIONA I RECORDS CONTENENTI IL CODICE "_18_"
$sql = "SELECT *
    FROM tabella
    WHERE col1 like "%_18_%"
    order by id";
}
$result = mysql_query($sql);
if (!$result) {
  exit('<p>Error performing query: ' . mysql_error() . '</p>');
}
$dati = "";
while ($row = mysql_fetch_array($result)) 
    {
        $dati     .= $row['col1'] ;
    }
    
// FASE 2 - SELEZIONA I 2 CODICI PIU' FREQUENTI
$dati_array = str_replace("__","_", $dati);
$array         = array_count_values(explode("_",$dati_array));
arsort($array);
foreach ($array as $key => $value) {
   $cod[] = $key ;
}
$key1= $cod[0];
$key2= $cod[1];

// FASE 3 - SELEZIONA I RECORDS CONTENENTI I CODICI $key1 e $key2
$sql1 = "SELECT *
    FROM tabella
    WHERE col1 like "%_18_%"
    order by id";
}
$result1 = mysql_query($sql1);
if (!$result1) {
  exit('<p>Error performing query: ' . mysql_error() . '</p>');
}
$i = 0;
$ok = "";
while ($row1 = mysql_fetch_array($result1)) 
    {
        $id[$i]        = $row1['id'];
        $col1[$i]    = $row1['col1'] ;
            if (strpos($key1,"_" . $col1[$i] . "_") <> 0) || (strpos($key2,"_" . $col1[$i] . "_") <> 0) 
            {
                $ok .= $i . "," ;
            }
        ++$i ;
    }
    
// FASE 4 - predispone la lista degli ID richiesti
$array_ok    = explode(",", $ok);
$lista = "";
foreach ($array_ok as $sel)    
{
    if ($id[$sel - 2) <> '')  // aggiunge i 2 records precedenti
    { 
        $lista .= $id[$sel - 2] . ",";
    }
    if ($id[$sel - 1) <> '') 
    { 
        $lista .= $id[$sel - 1] . ",";
    }
        $lista .= $id[$sel] . ",";  // aggiunge il record selezionato
        
    if ($id[$sel + 1) <> '')  // aggiunge i 2 records successivi
    { 
        $lista .= $id[$sel + 1] . ",";
    }
    if ($id[$sel + 2) <> '') 
    { 
        $lista .= $id[$sel + 2] . ",";
    }
}
echo $lista . "<br />";
?>

Alla fine otteniamo una lista contenente gli ID richiesti, al limite va fatta una pulizia del risultato finale per togliere eventuali doppioni.

[Sir Xiradorn]

I miei complimenti a Mico che ha davvero fatto un ottimo lavoro e lo ha pure semplificato. Davvero complimenti.

Se hai php7 mysql_* non è più usabile. Puoi usare le PDO (e credo anche il set mysqli)

[Micogian]

Grazie Sir, un giorno mi metto a studiare le PDO,
C'è qualche errorino nel codice precedente e poi devo capire meglio cosa si vuole ottenere, comunque il file di prova è questo: http://www.giannidose.altervista.org/prova_mysql.php
Come prevedevo, il risultato ripete alcuni records, ma si può agire sull'array del risultato per eliminare i duplicati.

Il file di prova è il seguente:

Codice: Seleziona tutto

<?php
// CONNESSIONE AL DATABASE

if (!@mysql_connect("localhost", "user","password")) exit('<p>Impossibile connettersi al server del database a4 in questo momento.</p>');

if (!@mysql_select_db("nome_database")) exit('<p>Impossibile connettersi al database in questo momento.</p>');
$result = mysql_query("SET NAMES 'utf8'");
if (!$result) exit('<p>error performing query: ' . mysql_error() . '</p>');

    // FASE 0 - VISUALIZZA IL CONTENUTO DELLA TABELLA
    echo "Contenuto della tabella<br />";
    echo "*******************************<br />";
    $xxx = "SELECT * FROM tabella_prova";
    $result0 = mysql_query($xxx);
    while ($row0 = mysql_fetch_array($result0)) 
        {
            echo "id= " . $row0['id'] . " - " . $row0['col1'] . "<br />";
        }
echo "*******************************<br />";
echo "Record contenenti _18_<br />";    
    // FASE 1 - SELEZIONA I RECORDS CONTENENTI IL CODICE "_18_"
    $sql = "SELECT * FROM tabella_prova WHERE col1 like '%_18_%'";
    $dati = "";
    $result = mysql_query($sql);
    if (!$result) {
      exit('<p>Error performing query1: ' . mysql_error() . '</p>');
    }
    while ($row = mysql_fetch_array($result)) 
        {
            echo "id= " . $row['id'] . " - " . $row['col1'] . "<br />";
            $dati   = $dati . $row['col1'] ;
        }

$dati1 = str_replace("_",",",$dati);
$dati2 = str_replace(",18","",$dati1);
$dati3 = str_replace(",,",",",$dati2);
echo "*******************************<br />";
echo "Lista dei codici selezionati<br />";
echo "dati= " . $dati3 . "<br />";
echo "*******************************<br />";
echo "calcola quante volte sono presenti le key selezionate<br />";
    // FASE 2 - SELEZIONA I 2 CODICI PIU' FREQUENTI
    $array1  = explode(",",$dati3);

    $array2  = array_count_values($array1);
    arsort($array2);
    foreach ($array2 as $key => $value) {
        if ($key <> '')
        {
        $cod[] = $key ;
        echo "key " . $key . " value " . $value . "<br />";
        }
    }
    $key1= "_".$cod[0]."_";
    $key2= "_".$cod[1]."_";
echo "*******************************<br />";
echo "Seleziona le prime 2 key <br />";
echo "key1= " . $key1 . "<br />";
echo "key2= " . $key2 . "<br />";
echo "*******************************<br />";
echo "Elenco dei records selezionati<br />";  
    // FASE 3 - SELEZIONA I RECORDS CONTENENTI I CODICI $key1 e $key2
    $sql1 = "SELECT * FROM tabella_prova 
        WHERE col1 like '%".$key1."%'
        OR col1 like  '%".$key2."%'";
    $result1 = mysql_query($sql1);
    if (!$result1) {
      exit('<p>Error performing query2: ' . mysql_error() . '</p>');
    }
    $i = 0;
    $ok = "";
    while ($row1 = mysql_fetch_array($result1)) 
        {
        
            $id[$i]      = $row1['id'];
            $col1[$i]    = $row1['col1'] ;
                if (strpos("-".$row1['col1']."-",$key1 ) <> 0 || strpos("-".$row1['col1']."-",$key2 ) <> 0)
                {
                echo "record: " . $id[$i] . " - " . $col1[$i] ."<br />";
                    $ok .= $i . "," ;
                }
            ++$i ;
        }
echo "*******************************<br />";
    // FASE 4 - predispone la lista degli ID richiesti
    $array_ok    = explode(",", $ok);
    $lista = "";
    foreach ($array_ok as $sel)    
    {
        if ($id[$sel - 2] <> '')  // aggiunge i 2 records precedenti
        { 
            $lista .= $id[$sel - 2] . ",";
        }
        if ($id[$sel - 1] <> '') 
        { 
            $lista .= $id[$sel - 1] . ",";
        }
            $lista .= $id[$sel] . ",";  // aggiunge il record selezionato
            
        if ($id[$sel + 1] <> '')  // aggiunge i 2 records successivi
        { 
            $lista .= $id[$sel + 1] . ",";
        }
        if ($id[$sel + 2] <> '') 
        { 
            $lista .= $id[$sel + 2] . ",";
        }
    }
// elimina duplicati
$lista1     = explode(",",$lista);    
$unici = array_unique($lista1);
sort($unici);
echo "Lista finale degli id selezionati: ";
foreach($unici as $rec)
{
echo $rec . ",";
}
?>

[Micogian]

Versione PDO: http://giannidose.altervista.org/prova_pdo.php

Codice: Seleziona tutto

<?php
/**
 * Query tramite PDO
 */
 
require_once('./include/connect.php');  // i dati di connessione sono nel file ./include/connect.php
global $glb_db_name;
global $glb_db_host;
global $glb_db_user;
global $glb_db_password;


$dbtype     = "mysql";


/// Inizializzazione, controllo e connessione al DB
try {
    if (!in_array($dbtype, PDO::getAvailableDrivers())) {
        throw new PDOException ("I settaggi sono sbagliati o i driver non avviabili. Connessione è solo per db mysql");
    }    
} catch (PDOException $e) {
    echo "Errore: " . $e->getMessage();
}

try {
    $db = new PDO("$dbtype:host=$glb_db_host;dbname=$glb_db_name;charset=utf8", $glb_db_user, $glb_db_password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
} catch (PDOException $e) {
    echo "Errore: " . $e->getMessage();
}

//0) VISUALIZZA IL CONTENUTO DELLA TABELLA
echo "Contenuto della tabella<br />";
echo "*******************************<br />";
$query = "SELECT * FROM tabella_prova";

    $dati = $db->prepare($query);
    $dati->bindValue(':id', $id,PDO::PARAM_INT);
    $dati->bindValue(':col1', $col1, PDO::PARAM_STR);
    $dati->execute();

    $results = $dati->fetchAll();

    foreach ($results as $row) {
        echo $row['id'] . ' = ' . $row['col1'] . "<br />";
    }
echo "*******************************<br />";
echo "ELABORAZIONE RICHIESTA<br />";    
echo "1) Trovare tutte le row che contengono \"_18_\"<br />";
echo "2) Trovare le 2 sequenze pi&ugrave; frequenti nelle row contenenti \"_18_\"<br />";
echo "3) Scegliere tutte le row che, allo stesso tempo, non contengano \"_18_\" ma contengano le precedentemente individuate stringhe pi&ugrave; frequenti \"_22_\" e \"_1_\"<br />";
echo "4) Aggiungere alla scelta appena effettuata le 2 row precedenti e le 2 row successive a quelle individuate nello step 3, sempre escludendo quelle che contengono \"_18_\"<br />";
echo "*******************************<br />";
//FASE 1 - SELEZIONA I RECORDS CONTENENTI _18_
echo "Records contenenti il codice _18_<br />";
$query1 = "SELECT * FROM tabella_prova WHERE col1 like '%_18_%'";
$dati1 = $db->prepare($query1);
    $dati1->bindValue(':id', $id,PDO::PARAM_INT);
    $dati1->bindValue(':col1', $col1, PDO::PARAM_STR);
    $dati1->execute();

    $results1 = $dati1->fetchAll();
    $lista_cod = '';
    foreach ($results1 as $row1) {
        $lista_cod   .= $row1['col1'] ;
        echo $row1['id'] . ' = ' . $row1['col1'] . "<br />";
    }
    
// ELABORA LA LISTA DEI CODICI PRESENTI NEI RECORDS SELEZIONATI ESCLUDENDO _18_    
$lista_tmp1 = str_replace("_",",",$lista_cod);
$lista_tmp2 = str_replace(",18","",$lista_tmp1);
$lista_codici = str_replace(",,",",",$lista_tmp2);
echo "*******************************<br />";
echo "Lista dei codici selezionati escluso _18_<br />";
echo $lista_codici . "<br />";
echo "*******************************<br />";

// FASE 2 - SELEZIONA I 2 CODICI PIU' FREQUENTI
$array1  = explode(",",$lista_codici);
$array2  = array_count_values($array1);
arsort($array2);
echo "calcola quante volte sono presenti le key selezionate<br />";
foreach ($array2 as $key => $value) 
{
    if ($key <> '')
    {
        $cod[] = $key ;
        echo "key " . $key . " value " . $value . "<br />";
    }
}
    $key1= "_".$cod[0]."_";
    $key2= "_".$cod[1]."_";
echo "*******************************<br />";
echo "Seleziona le prime 2 key <br />";
echo "key1= " . $key1 . "<br />";
echo "key2= " . $key2 . "<br />";
echo "*******************************<br />";
// FASE 3 - SELEZIONA I RECORDS CONTENENTI I CODICI $key1 e $key2
echo "Elenco dei records contenenti le prime 2 key<br />"; 
$query2 = "SELECT * FROM tabella_prova 
    WHERE col1 like '%".$key1."%'
    OR col1 like  '%".$key2."%'";
    $dati2 = $db->prepare($query2);
    $dati2->bindValue(':id', $id,PDO::PARAM_INT);
    $dati2->bindValue(':col1', $col1, PDO::PARAM_STR);
    $dati2->execute();
    $results2 = $dati2->fetchAll();
    $i = 0;
    $ok = "";
    foreach ($results2 as $row2) 
    {
        $id[$i]        = $row2['id'];
        $col1[$i]    = $row2['col1'] ;
        if (strpos("-".$col1[$i]."-",$key1 ) <> 0 || strpos("-".$col1[$i]."-",$key2 ) <> 0)
        {
            echo "record: " . $id[$i] . " - " . $col1[$i] ."<br />";
            $cod_ok .= $i . "," ;
        }
        ++$i ;
    }
echo "*******************************<br />";    
// FASE 4 - predispone la lista degli ID richiesti
    $array_ok    = explode(",", $cod_ok);
    $lista = "";
    foreach ($array_ok as $sel)    
    {
        if ($id[$sel - 2] <> '')  // aggiunge i 2 records precedenti
        { 
            $lista .= $id[$sel - 2] . ",";
        }
        if ($id[$sel - 1] <> '') 
        { 
            $lista .= $id[$sel - 1] . ",";
        }
            $lista .= $id[$sel] . ",";  // aggiunge il record selezionato
            
        if ($id[$sel + 1] <> '')  // aggiunge i 2 records successivi
        { 
            $lista .= $id[$sel + 1] . ",";
        }
        if ($id[$sel + 2] <> '') 
        { 
            $lista .= $id[$sel + 2] . ",";
        }
    }
$array_lista     = explode(",",$lista);    
$risultato = array_unique($array_lista);
sort($risultato);
echo "Risultato finale degli id selezionati: ";
foreach($risultato as $rec)
{
    if ($rec <> '')
    {
        echo $rec . ",";
    }
}
?>

EDIT: la cosa che non mi è chiara è se bisogna sempre escludere i records che contengono "_18_" perchè può capitare che alcuni records contengano, oltre a "_18_", anche uno dei 2 codici più frequenti oppure il "_18_" sia presente nei 2 records precedenti o in quelli successivi.
Per fare questo bisogna modificare la parte finale, quella che inserisce i 2 precedenti records e i 2 successivi. Basta inserirli nella lista solo se non contengono il codice "_18_".

[Sir Xiradorn]

Occhio solo ad una piccolissima imprecisione. Il binding dei parametri va usato come segue:

Codice: Seleziona tutto

<?php 

$driver = "mysql";
/*
    Mancano i dati di log e il nome del db....fate vobis
*/

define("TABELLA", "tabella_prova");

if ( !in_array($driver, PDO::getAvailableDrivers()) ) {
    throw new PDOException ("Driver non presenti"); 
} else {
    try {
        $cnt_string = "{$driver}:host={$host};dbname={$dbname};charset=utf8";
        $options = array(
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES    => false
        );

        $db = new PDO($cnt_string, $user, $pass, $options);
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

$col1 = "%\_18\_%";
$sql = "SELECT * FROM " . TABELLA . " WHERE col1 LIKE :col1";

$stmt = $db->prepare($sql);
$stmt->bindParam(':col1', $col1, PDO::PARAM_STR);
$stmt->execute();

echo "<pre>";
print_r($stmt->fetchAll(PDO::FETCH_ASSOC));
echo "</pre>";

// equivalente al freeresult
$stmt->closeCursor();

$sql = "SELECT * FROM " . TABELLA;

$stmt = $db->query($sql);
// addirittura puo essere fatto questo
/*
$stmt = $db->query($sql, PDO::FETCH_ASSOC);
*/

echo "<pre>";
print_r($stmt->fetchAll(PDO::FETCH_ASSOC));
echo "</pre>";

$stmt->closeCursor();

// per chiudere la connessione
unset($db);    

Solo uno spunto per imparare insieme. Nemmeno io ci ho scombattuto molto ma sono un set davvero molto potente. In alternativa potete usare mysqli.