SQL ORDER String (NumberOrder/CurrentYear) From two tables

Question

I have TABLE ONE and TABLE TWO and both have a field named "n_inscription"

I have this value saved in both tables as VARCHAR to a MySQL database as "n_inscription" LIKE This (NumberOrder/CurrentYear). Example: 1/2013, 2/2013, 13/2014, ....etc in (TABLE ONE) AND TABLE TWO have these values

1/2014, 2/2014,  5/2013...

I tried this QUERY :

$list_students = array();

$sql_students = $mysqli->query("SELECT * FROM `es_student_infos`
                                        WHERE school_year='".$school_year."'                                                                    
                                        ORDER BY
                                        CAST(RIGHT(n_inscription, 4) AS UNSIGNED) ASC,
                                        CAST(LEFT(n_inscription, LOCATE('/', n_inscription) - 1) AS UNSIGNED) ASC");

        while($data1 = $sql_students->fetch_object()){
            $list_students[] = $data1;
        }

        $sql_students2 = $mysqli->query("SELECT * FROM `es_upgrades`
                                        WHERE school_year='".$school_year."'                                                                    
                                        ORDER BY
                                        CAST(RIGHT(n_inscription, 4) AS UNSIGNED) ASC,
                                        CAST(LEFT(n_inscription, LOCATE('/', n_inscription) - 1) AS UNSIGNED) ASC");

        while($data2 = $sql_students2->fetch_object()){
            $list_students[] = $data2;
        }

// RETRIEVE DATA :

foreach($list_students AS $student): 
............
endforeach;

The problem here in this query is that it SORTs data from the first table and then sorts data from the second table and it gives me result like this:

1/2013| 2/2013 | 13/2014 | 5/2013 | 1/2014 | 2/2014 |

It seems the query starts sorting data from table one and when it finishes sorting data it starts again sort data from TABLE TWO.

I want to sort data from both tables at the same time to give me result like this:

1/2013| 2/2013 | 5/2013 | 1/2014 | 2/2014 | 13/2014 |

and NOT

1/2013| 2/2013 | 13/2014 | 5/2013 | 1/2014 | 2/2014 |

the tables structure:

Table one:

 `es_student_infos` (
  `id_student` int(11) NOT NULL,
  `id_grade` int(11) NOT NULL,
  `id_class` int(11) NOT NULL,
  `n_inscription` varchar(255) NOT NULL,
  `date_inscription` date NOT NULL,
  `cne` varchar(255) NOT NULL,
  `nom_fr` varchar(255) NOT NULL,
  `nom_ar` varchar(255) CHARACTER SET utf8 NOT NULL,
  `prenom_fr` varchar(255) NOT NULL,
  `prenom_ar` varchar(255) CHARACTER SET utf8 NOT NULL,
  `date_naissance` date NOT NULL,
  `lieu_naissance_ar` varchar(255) CHARACTER SET utf8 NOT NULL,
  `lieu_naissance_fr` varchar(255) NOT NULL,
  `tel` varchar(255) NOT NULL,
  `sexe` varchar(10) CHARACTER SET utf8 NOT NULL,
  `ancien_niveau_scolaire` varchar(255) CHARACTER SET utf8 NOT NULL,
  `date_ancienNiveauScolaire` varchar(10) NOT NULL,
  `ancienne_ecole` varchar(255) CHARACTER SET utf8 NOT NULL,
  `type_ecole` varchar(255) CHARACTER SET utf8 NOT NULL,
  `type_inscription` varchar(20) CHARACTER SET utf8 NOT NULL,
  `assurance` int(11) NOT NULL,
  `mensualite` int(11) NOT NULL,
  `nom_tuteur` varchar(255) CHARACTER SET utf8 NOT NULL,
  `profession_tuteur` varchar(255) CHARACTER SET utf8 NOT NULL,
  `adresse_tuteur` varchar(255) CHARACTER SET utf8 NOT NULL,
  `tel_tuteur` varchar(255) NOT NULL,
  `remarques` text CHARACTER SET utf8 NOT NULL,
  `photo` varchar(255) NOT NULL,
  `school_year` varchar(10) NOT NULL,
  `cours_type` enum('jour','soir') NOT NULL,
  `id_subject` int(11) NOT NULL,
  `id_prof_soir` varchar(30) NOT NULL,
  `montant_soir` varchar(255) NOT NULL,
  `archived` enum('0','1') NOT NULL

Table TWO:

  `es_upgrades` (
      `id_upgrade` int(11) NOT NULL,
        `id_student` int(11) NOT NULL,
         `n_inscription` varchar(20) NOT NULL,
         `old_id_class` int(11) NOT NULL,
         `new_id_class` int(11) NOT NULL,
         `status` enum('up','down') NOT NULL,
         `school_year` varchar(30) NOT NULL,
         `date_inscription` date DEFAULT NULL,
         `cne` varchar(255) DEFAULT NULL,
         `nom_fr` varchar(255) DEFAULT NULL,
         `nom_ar` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
         `prenom_fr` varchar(255) DEFAULT NULL,
         `prenom_ar` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
         `date_naissance` date DEFAULT NULL,
         `lieu_naissance_ar` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
         `lieu_naissance_fr` varchar(255) DEFAULT NULL,
         `tel` varchar(255) DEFAULT NULL,
         `sexe` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
         `ancien_niveau_scolaire` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
         `date_ancienNiveauScolaire` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
         `ancienne_ecole` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
         `type_ecole` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
         `type_inscription` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
         `assurance` int(11) DEFAULT NULL,
         `mensualite` int(11) DEFAULT NULL,
         `nom_tuteur` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
         `profession_tuteur` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
         `adresse_tuteur` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
         `tel_tuteur` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
         `remarques` text CHARACTER SET utf8,
         `photo` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
         `cours_type` enum('jour','soir') CHARACTER SET utf8 DEFAULT NULL,
         `id_subject` int(11) DEFAULT NULL,
         `archived` enum('0','1') DEFAULT NULL

to use UNION ALL, make sure that both the tables must have same number of columns.. — Pathik Vejani, Dec 08 '14 at 10:29
the tables don't have the same number of columns. I've edited my question and added tables structure — Mariem, Dec 08 '14 at 10:33
Do you need ALL fields from tables or just some of them? Maybe you should try to get the data from both tables at same time like `SELECT A.id AS aid, B.id AS bid FROM A, B ORDER BY A.id`? — Mark Zucchini, Dec 08 '14 at 10:51
I tried this but it gives me an error this time: here what i tried `SELECT t1.n_inscription, t1.nom_ar, t1.prenom_ar, t1.mensualite, t1.assurance FROM es_student_infos AS t1 WHERE t1.school_year='$school_year' UNION ALL SELECT t2.n_inscription, t2.nom_ar, t2.prenom_ar, t2.mensualite, t2.assurance FROM es_upgrades AS t2 WHERE t2.school_year='$school_year' ORDER BY CAST(RIGHT(t1.n_inscription, 4) AS UNSIGNED) ASC, CAST(LEFT(t1.n_inscription, LOCATE('/', t1.n_inscription) - 1) AS UNSIGNED) ASC` — Mariem, Dec 08 '14 at 11:09

score 0 · Answer 1 · edited May 23 '17 at 11:57

0

UNION should works but it will work only with tables with same number of columns or if you specified columns you need (in orther words you cannot use * symbol)

So, if you have the tables like tables:

CREATE TABLE a
(
 id SERIAL,
 dat DATE,

 PRIMARY KEY (id)
);

and

CREATE TABLE b
(
 id SERIAL,
 dat DATE,
 ip VARCHAR(16),

 PRIMARY KEY (id)
);

you should use such request

SELECT dat FROM a
UNION 
SELECT dat FROM b;

then it should works.

And read this topic (especially second answer (with 7 votes)) it may helpful: Order by descending date - month, day and year

edited May 23 '17 at 11:57

Community

1
1

answered Dec 08 '14 at 10:28

Mark Zucchini

925
6
11

UNION request should works. If you need the certain columns from the request you should specified them in request. – Mark Zucchini Dec 09 '14 at 09:10

SQL ORDER String (NumberOrder/CurrentYear) From two tables

1 Answers1