0

I'm not sure how to proceed in order to display the results of my MySQL on my website. I know that my procedures are working locally. I can only display one one of the select and when I print the result with one select I would like to display on two lines and I can't so I would like to have some suggestions. I did not find any clear answers on other posts

use kino;
DROP procedure IF EXISTS `nbrCas`;
DROP procedure IF EXISTS `Ville`;
DROP procedure IF EXISTS `Police`;
delimiter $$

create  procedure nbrCas()
begin
    DECLARE cnt INT DEFAULT 0 ;
    DECLARE max INT DEFAULT 0 ;
    DECLARE year int DEFAULT 0 ;
    DECLARE minYear int DEFAULT 50 ;
    DECLARE maxYear int DEFAULT 0 ;
    DECLARE counterYear int DEFAULT 0;
    DECLARE big int default 0 ;
    DECLARE min int default 50 ;
    create temporary table year select DISTINCT YEAR(Ertaussstrahlung) from folge ;
    
    select count(*) into max from year;
    WHILE cnt < max
    DO
        set year=(select * from year limit 1 offset cnt);
        set counterYear = (select count(*) from folge where Ertaussstrahlung >=CONCAT(year,'-','01','-','01') and Ertaussstrahlung < CONCAT((year + 1),'-','01','-','01')) ;
        IF counterYear > big
        THEN
            set big = counterYear;
            set maxYear = year;
        END IF;
        IF counterYear < min
        then
            set min = counterYear;
            set minYear = year;
        END IF;
    
        SET cnt=cnt+1;
    END WHILE;
    select "L'année ayant eu le plus de cas:", maxYear,"\n";
    select "L'annee ayant eu le plus de cas:",minYear;
    
    drop table year;
END;
create procedure Ville()
begin
    DECLARE max int DEFAULT 0 ;
    DECLARE cnt int DEFAULT 0 ;
    DECLARE ville varchar(100);
    DECLARE counterVille int DEFAULT 0 ;
    DECLARE biggest int DEFAULT 0 ;
    DECLARE smallest int DEFAULT 1000 ;
    DECLARE villeWin varchar(100);
    DECLARE villeMin varchar(100);
    create temporary table ville select DISTINCT(stadt) from folge ;
    select count(*) into max from ville;
    WHILE cnt < max
    DO
        set ville = (select * from ville limit 1 offset cnt);
        
        SET counterVille = (select count(*) from folge where stadt=ville);
        IF counterVille > biggest
        then
            set biggest = counterVille;
            set villeWin = ville;
        END IF;
        IF counterVille < smallest
        then
            set smallest = counterVille;
            set villeMin = ville;
        END IF;
        set cnt = cnt +1;
    END WHILE;
    select biggest;
    select villeWin;
    select smallest;
    select villeMin;
    drop table ville;
END;

create procedure Police()
begin
    DECLARE max int DEFAULT 0 ;
    DECLARE cnt int DEFAULT 0 ;
    DECLARE Policier varchar(100);
    DECLARE counterPolicier int DEFAULT 0 ;
    DECLARE biggest int DEFAULT 0 ;
    DECLARE smallest int DEFAULT 1000 ;
    DECLARE PolicierMin varchar(100) ;
    DECLARE PolicierMax varchar(100) ;


    create temporary table police select DISTINCT(Ermittler) from folge ;
    select count(*) into max from police;

    WHILE cnt < max
    DO
        set Policier = (select * from police limit 1 offset cnt);
        set counterPolicier = (select count(*) from folge where Ermittler = Policier);
        IF counterPolicier > biggest
        then
            set biggest = counterPolicier;
            set PolicierMax = Policier;
        END IF;
        IF counterPolicier < smallest
        then
            set smallest = counterPolicier;
            set PolicierMin = Policier;
        END IF;
        set cnt = cnt + 1;
    END WHILE;
    select biggest;
    select PolicierMax;
    select smallest;
    select PolicierMin;
    drop table police;
END;
$$

<?php
    $gateway = new EpisodeGateway();    
   $db = $gateway->acces_db();
   $result = array();
   $result = mysqli_query($db,'call nbrCas');
   $result = mysqli_fetch_all($result);
   foreach($result as $val)
   {
    foreach($val as $value)
    {
        echo $value;
    }
   }
?>
kmoser
  • 8,780
  • 3
  • 24
  • 40
Math142
  • 19
  • 6
  • A bit of code would be nice. –  May 22 '21 at 19:55
  • acces_db(); $result = array(); $result = mysqli_query($db,'call nbrCas'); $result = mysqli_fetch_all($result); foreach($result as $val) { foreach($val as $value) { echo $value; } } ?> – Math142 May 22 '21 at 19:57
  • select count(*) into max from year; WHILE cnt < max DO set year=(select * from year limit 1 offset cnt); set counterYear = (select count(*) from folge where Ertaussstrahlung >=CONCAT(year,'-','01','-','01') and Ertaussstrahlung < CONCAT((year + 1),'-','01','-','01')) ; IF counterYear > big THEN set big = counterYear; set maxYear = year; END IF; IF counterYear < min then set min = counterYear; set minYear = year; END IF; SET cnt=cnt+1; END WHILE; – Math142 May 22 '21 at 20:00
  • select "L'année ayant eu le plus de cas:", maxYear,"\n"; select "L'annee ayant eu le plus de cas:",minYear; – Math142 May 22 '21 at 20:00
  • I had to cut the beginning sorry – Math142 May 22 '21 at 20:01
  • Please add the code to the question (use the [Edit](https://stackoverflow.com/posts/67653634/edit) button) – Nigel Ren May 22 '21 at 20:01
  • Have you read the manual? https://www.php.net/manual/en/mysqli.quickstart.stored-procedures.php – Dharman May 22 '21 at 21:38
  • @Dharman Thanks a lot but I don't know why I didn't fully understand. Thank you for the support/ – Math142 May 23 '21 at 00:28

0 Answers0