MySQL - Improve Query Performance

Question

I have a POST METHOD $tab_tuto_sauvegarde = $_POST['tableau_valeurs_modifiees']; who with a Ajax request returns to me this kind of informations :

(
    [Categories] => Array
        (
            [Cat_1] => AA
            [Cat_2] => DD
        )
)

But it can be that or other...

 (
        [Categories] => Array
            (
                [Cat_1] => CC
                [Cat_2] => HH
                [Cat_3] => AA
            )
    )

I have a TABLE named 'Groupe_Categories'

|  id_groupe_categorie  ||  titre_categorie_nv1 |  titre_categorie_nv2 |  titre_categorie_nv3 |
______________________________________________________________________________________________
|    1                  ||         AA           |         DD          |                     |
|    2                  ||         AA           |         EE          |         RR          |
|    3                  ||         BB           |         FF          |         FF          |
|    4                  ||         CC           |         HH          |         AA          |
...

And an other TABLE named Wheel

|  id_wheel  ||  id_groupe_categorie 
_____________________________________
|    1       ||         1          
|    2       ||         5           
|    3       ||         9           
|    4       ||         11         
...

What I do is in function of [Cat_1], [Cat_2]... match the 'id_groupe_categorie' in TABLE 'Groupe_Categories' and UPDATE this 'id_groupe_categorie' in TABLE Wheel

In the 1st example the result is 1 In the 2nd example the result is 4

For that I made this code :

if (isset ($tab_tuto_sauvegarde['Categories'])){

    $cat1 = $tab_tuto_sauvegarde['Categories']['Cat_1'];
    $cat2 = $tab_tuto_sauvegarde['Categories']['Cat_2'];

    if (isset ($tab_tuto_sauvegarde['Categories']['Cat_3'])){
        $titre_cat3 = $tab_tuto_sauvegarde['Categories']['Cat_3'];
        $column_cat3 = "titre_categorie_nv3" ;
        $cat3 = "AND $column_cat3 = '$titre_cat3'" ;
    }else $cat3 = "";

    if (isset ($tab_tuto_sauvegarde['Categories']['Cat_4'])){
        $titre_cat4 = $tab_tuto_sauvegarde['Categories']['Cat_4'];
        $column_cat4 = "titre_categorie_nv4" ;
        $cat4 = "AND $column_cat4 = '$titre_cat4'" ;
    }else $cat4 = "";

    if (isset ($tab_tuto_sauvegarde['Categories']['Cat_5'])){
        $titre_cat5 = $tab_tuto_sauvegarde['Categories']['Cat_5'];
        $column_cat5 = "titre_categorie_nv5" ;
        $cat5 = "AND $column_cat5 = '$titre_cat5'" ;
    }else $cat5 = "";

    $stmt_cat = mysqli_query($BDD_connect, "SELECT id_groupe_categorie FROM Groupe_de_categories WHERE titre_categorie_nv1 = '$cat1' AND titre_categorie_nv2 = '$cat2' '$cat3' '$cat4' '$cat5'");
    //$stmt_cat = replace($stmt_cat, '\'\'', '');

    $row_stmt_cat = mysqli_fetch_assoc($stmt_cat);
    $id_gpe_cat = $row_stmt_cat['id_groupe_categorie'];

    mysqli_query($BDD_connect, "UPDATE Tutoriels SET id_groupe_categorie = '$id_gpe_cat', niveau_choix_categorie_autre ='', categorie_autre='' WHERE id_tutoriel = '$id_tutoriel'"); 
}

First, this code works but I think he is very heavy... Is there a way to simplify the code ?

Secondly, with my code, if [Cat_3] doesn't exist like in my 1st example, in "SELECT id_groupe_categorie FROM Groupe_de_categories WHERE titre_categorie_nv1 = '$cat1' AND titre_categorie_nv2 = '$cat2' '$cat3' "); '$cat3' is replace by floating ' ' ... it works but how can I delete this floating '' ?

Thanks for your help !

I would think the best bet would be to write a procdure that embodies both the SELECT and the UPDATE. You could pass in just the "Categories" as parameters that come up and the rest null and handle it in your procedure. — Jacob Goulden
– Jacob Goulden, Commented May 6, 2014 at 11:10
@JacobGoulden Yes is that I want but i'm a noob and I'm learning php... i'll try :) — Zagloo
– Zagloo, Commented May 6, 2014 at 11:45

Wil · Accepted Answer · 2014-05-06 11:21:00Z

This code should solve both of your "problems":

if (isset ($tab_tuto_sauvegarde['Categories'])){

    $cat1 = $tab_tuto_sauvegarde['Categories']['Cat_1'];
    $cat2 = $tab_tuto_sauvegarde['Categories']['Cat_2'];

    $sSelect = "SELECT id_groupe_categorie FROM Groupe_de_categories WHERE titre_categorie_nv1 = '$cat1' AND titre_categorie_nv2 = '$cat2'";

    $i = 3;
    while ($i < 6) {
        if (isset ($tab_tuto_sauvegarde['Categories']['Cat_'.$i])){
            $titre_cat = $tab_tuto_sauvegarde['Categories']['Cat_'.$i];
            $column_cat = "titre_categorie_nv".$i ;
            $sSelect .= " AND $column_cat = '$titre_cat' " ;
        }
        $i++;
    }

    $stmt_cat = mysqli_query($BDD_connect, $sSelect);
    //$stmt_cat = replace($stmt_cat, '\'\'', '');

    $row_stmt_cat = mysqli_fetch_assoc($stmt_cat);
    $id_gpe_cat = $row_stmt_cat['id_groupe_categorie'];

    mysqli_query($BDD_connect, "UPDATE Tutoriels SET id_groupe_categorie = '$id_gpe_cat', niveau_choix_categorie_autre ='', categorie_autre='' WHERE id_tutoriel = '$id_tutoriel'"); 
}

Zsolt Varga · Accepted Answer · 2014-05-06 11:54:10Z

to short your code you can use for loop in his way:

for($i=1;$i<=5;$i++) {
      ${cat_ . $i} = (isset($tab_tuto_sauvegarde['Categories']["Cat_".$i]) ? $tab_tuto_sauvegarde['Categories']["Cat_".$i] : "");
}

As I see if the array item is not isset you want to create an empty variable for it. The code above will do it for you.

You can change in your code this:

    $cat1 = $tab_tuto_sauvegarde['Categories']['Cat_1'];
    $cat2 = $tab_tuto_sauvegarde['Categories']['Cat_2'];

    if (isset ($tab_tuto_sauvegarde['Categories']['Cat_3'])){
        $titre_cat3 = $tab_tuto_sauvegarde['Categories']['Cat_3'];
        $column_cat3 = "titre_categorie_nv3" ;
        $cat3 = "AND $column_cat3 = '$titre_cat3'" ;
    }else $cat3 = "";

    if (isset ($tab_tuto_sauvegarde['Categories']['Cat_4'])){
        $titre_cat4 = $tab_tuto_sauvegarde['Categories']['Cat_4'];
        $column_cat4 = "titre_categorie_nv4" ;
        $cat4 = "AND $column_cat4 = '$titre_cat4'" ;
    }else $cat4 = "";

    if (isset ($tab_tuto_sauvegarde['Categories']['Cat_5'])){
        $titre_cat5 = $tab_tuto_sauvegarde['Categories']['Cat_5'];
        $column_cat5 = "titre_categorie_nv5" ;
        $cat5 = "AND $column_cat5 = '$titre_cat5'" ;
    }else $cat5 = "";

To this:

for($i=1;$i<=5;$i++) {
${cat_ . $i} = (isset($tab_tuto_sauvegarde['Categories']["Cat_".$i]) ? $tab_tuto_sauvegarde['Categories']["Cat_".$i] : "");
}

To understand it I wrote a standalone sample code for you, because i didn't know your full code, so try to experiment with this small code snippet :-)

<?
$array1 = array();
$array1["Cat_1"] = "cc";
$array1["Cat_2"] = "dd";
$array1["Cat_3"] = "rr";


for($i=1;$i<=4;$i++) {
${cat_ . $i}    = (isset($array1["Cat_".$i]) ? $array1["Cat_".$i] : "-");

}
echo $cat_1;
echo $cat_2;
echo $cat_3;
echo $cat_4;
?>

Collectives™ on Stack Overflow

MySQL - Improve Query Performance

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related