Loop overload with use of foreach

Asked

Viewed 230 times

7

Ola I am having a serious problem at least and what the aqual hosting company this my site is claiming that my code below is overloading the queries or performing various loops, and do not know more what I can do.

Is there any way to unify or improve this code to work more effectively ?

Functioning of the code: The code literally does this first makes the news appear and within each news you have how to make appear as many items as you want in the second query since the result of the first query of the table news column medias the values separated by vigulas that in the case function as ID’s exist in the table medias spine id it will apply these results within the where in making appear within the second loop the items in the second query.

Follows below the SQL’s and the codes along with some explanations in it.

SQL NEWS

CREATE TABLE IF NOT EXISTS `news` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `titulo_url` char(255) COLLATE utf8_unicode_ci NOT NULL,
  `titulo` char(255) COLLATE utf8_unicode_ci NOT NULL,
  `noticia` text COLLATE utf8_unicode_ci NOT NULL,
  `data` date NOT NULL,
  `hora` time NOT NULL,
  `medias` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `episodios` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `avatar` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `autor` char(20) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tituloUnico` (`titulo`),
  UNIQUE KEY `urlUnica` (`titulo_url`),
  KEY `news_indexada` (`id`,`titulo_url`,`autor`,`data`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

SQL Membros

CREATE TABLE IF NOT EXISTS `membros` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login` char(20) NOT NULL DEFAULT 'admin',
  `autor` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'admin',
  `senha` char(255) NOT NULL DEFAULT 'admin',
  `idade` int(2) NOT NULL DEFAULT '0',
  `email` varchar(255) NOT NULL DEFAULT '[email protected]',
  `cargo` enum('Adminstrador','Editor','Upload') NOT NULL DEFAULT 'Adminstrador',
  `adm1` enum('ativado','desativado') NOT NULL DEFAULT 'ativado',
  `adm2` enum('ativado','desativado') NOT NULL DEFAULT 'ativado',
  `adm3` enum('ativado','desativado') NOT NULL DEFAULT 'ativado',
  `adicionais` text CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `data_cadastro` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_ultimo_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `arquivo_nome` varchar(355) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `arquivo_tipo` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `arquivo_data_cad` date NOT NULL DEFAULT '2014-05-03',
  `arquivo_hora_cad` time NOT NULL DEFAULT '11:11:11',
  `contaPremium` enum('ON','OFF') NOT NULL DEFAULT 'ON',
  `ativado` enum('0','1') NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `membrosDados` (`id`,`login`,`senha`),
  UNIQUE KEY `loginUnico` (`login`),
  KEY `membro_indexado` (`id`,`autor`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

SQL measures

CREATE TABLE IF NOT EXISTS `medias` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `medias_categoria_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `medias_subcategoria_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cat` int(255) DEFAULT NULL,
  `subcat` int(255) DEFAULT NULL,
  `url` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `url2` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `url3` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `url4` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `url5` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `url6` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `autor` char(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `medias_indexadas` (`id`,`cat`,`subcat`,`medias_subcategoria_url`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

SQL medias_subcategory

CREATE TABLE IF NOT EXISTS `medias_subcategoria` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `modulo` enum('media','filme','ova') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'media',
  `medias_categoria_url` char(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `medias_subcategoria_url` char(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `temporada` int(3) DEFAULT NULL,
  `nome` char(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cat` int(255) DEFAULT NULL,
  `semana` enum('Selecionar Semana','Domingo','Segunda-Feira','Terca-Feira','Quarta-Feira','Quinta-Feira','Sexta-Feira','Sabado') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Selecionar Semana',
  `sinopse` text COLLATE utf8_unicode_ci,
  `status` enum('Completo','Incompleto','Andamento','Lancamento','Pausado') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Andamento',
  `produtora` char(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ano` int(5) DEFAULT NULL,
  `genero` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `genero_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `numeroMedias` int(255) NOT NULL DEFAULT '0',
  `autor` char(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `acessos` int(255) NOT NULL,
  `arquivo_nome` varchar(355) COLLATE utf8_unicode_ci DEFAULT NULL,
  `arquivo_tipo` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `arquivo_data_cad` date DEFAULT NULL,
  `arquivo_hora_cad` time DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nomeUnico` (`nome`),
  UNIQUE KEY `subcategoriaUnica` (`medias_subcategoria_url`),
  KEY `subcategoria_index` (`id`,`cat`,`medias_categoria_url`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

SQL medias_categoria

CREATE TABLE IF NOT EXISTS `medias_categoria` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `medias_categoria_url` char(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `nome` char(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `modo` enum('UNICO','MULTIPLO') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'UNICO',
  `modulo` enum('media','filme','ova') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'media',
  `data` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `categoriaUnica` (`medias_categoria_url`),
  UNIQUE KEY `nomeUnico` (`nome`),
  KEY `categoria_indexada` (`id`,`medias_categoria_url`,`modo`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

Explanation: Values in the table news spine medias are inserted in this way 1,2,3,4,5,6 etc these values are actually the id’s of the table medias spine id to perform a comparison within the 2 loop these values are applied within the WHERE INso that the table items mediasspine id with the same values appear within the second foreach loop.

<?php 
    $newsSQL = $MYSQLI->query("
        SELECT `am1`.id,`am1`.titulo_url,`am1`.titulo,`am1`.noticia,`am1`.autor,`am1`.data,`am1`.medias,`am1`.episodios, `am2`.`arquivo_nome` as avatarImagem 
        FROM `news` as am1 FORCE INDEX (news_indexada)
        INNER JOIN `membros` as am2 FORCE INDEX (membro_indexado) ON `am1`.`autor` = `am2`.`login`
        GROUP BY `am1`.`id`,`am2`.`login`,`am1`.`autor` 
        ORDER BY id DESC LIMIT $inicio, $qnt

    ");
    foreach($newsSQL as $news)
    {
        // Obs simplifiquei aqui os dados do loop no stackoverflow.com
        echo $news['titulo'];
        // Realiza a aplicação dos valores adiquiridos na tabela news coluna media com seus valores separados por virgula   
        $newsmedias1 = trim($news["medias"], ', ' );

        $media_skin = $MYSQLI->query("
            SELECT `am1`.nome,`am1`.id,`am1`.cat, `am1`.medias_categoria_url, `am1`.medias_subcategoria_url, `am1`.acessos, `am1`.ano, `am1`.numeroMedias, `am1`.status, `am1`.produtora, `am1`.genero, `am1`.arquivo_nome, `am1`.genero, `am2`.`modo` as categoriaModo , `am2`.`modulo`, COUNT(am3.id) as totalMedias
            FROM `medias_subcategoria` as am1 FORCE INDEX (subcategoria_index)
            INNER JOIN `medias_categoria` as am2 FORCE INDEX (categoria_index) ON `am1`.`cat` = `am2`.`id` 
            INNER JOIN `medias` as am3 FORCE INDEX (medias_indexadas) ON `am1`.`cat` = `am3`.`cat` AND `am1`.`id` = `am3`.`subcat` 
            WHERE am1.id IN ($newsmedias1)
            GROUP BY `am1`.`id`
            ORDER BY FIND_IN_SET(`am1`.`id`, '$newsmedias1') 
        ");

        foreach($media_skin as $media_dados)
        {
            // Obs simplifiquei aqui os dados do loop no stackoverflow.com
            echo $media_dados['nome'].'</br>';
        } 

    }
    // Fechamento com de conexão com o banco de dados
    $MYSQLI->close();

    // Destroi todos os valores das variaveis da News e suas Skins
    unset($newsSQL, $media_skin);   
?>
php mysqli loop foreach
  • 1

    This is done with that frequency?

  • 2

    @Striffer needs to see if you’re not processing the loop unnecessarily at all times. Depending on what you want to get, just perform the loop only when there is change in the data.

  • 1

    At what times is this done? How many times a day, in what situation, etc.

  • 2

    On a first attempt, I would remove the force index and the ORDER BY FIND_IN_SET, it was good to normalize the table and hire a good quality accommodation.

  • may be excessive or unnecessary use of indices.

  • And even INER JOIN may not be the most appropriate. There is a case that LEFT solves well (although I think it will change little the problem in question). From what you just said, it seems to me even more that you are reprocessing the data unnecessarily when people access the site.

  • 1

    There is another thing, the second query is processed for line of the first, ie have a chained foreach o.o. Are 5 x the return of lines of the second query.

  • 1

    @rray de facto. And it has similar parts in both, it probably pays to rethink the whole code, and simplify the idea. Striffer, try to explain in the question what is the result you want for the query. Suddenly compensates people help solve the main problem (the desired result) than patching the current code.

  • 3

    This is well done, can improve this in a single consultation.

  • @Striffer, could explain better what is the purpose of the code/query. So someone can create an effective approach.

Show 5 more comments

1 answer

1

Is missing the fetch_assoc in your code, you are trying to loop a "Resource" of the executed query:

$newsSQL = $MYSQLI->query(...);
foreach($newsSQL as $news)

This would be right:

$newsSQL = $MYSQLI->query(...);
if($result){
    while ($news = $newsSQL->fetch_assoc()){
        //...
    }
    $newsSQL->close();
}

something else, you can only run another query after closing the first one, you can do an array to group:

$data = array();
$newsSQL = $MYSQLI->query(...);
if($result){
    while ($news = $newsSQL->fetch_assoc()){
        $data[] = array(
                    'titulo' => $news['titulo'],
                    'medias' => trim($news["medias"], ', ' )
                  );
    }
    $newsSQL->close();
}

if (empty($newmedidas) === false) {
    foreach($data as $value) {
         $media_skin = $MYSQLI->query("... ORDER BY FIND_IN_SET(`am1`.`id`, '" . $value['medias'] . "') ");
        echo $value['titulo'].'</br>';
        if ($media_skin) {
            while ($media_dados = $media_skin->fetch_assoc()) {
                echo $media_dados['nome'].'</br>';
            }
            $media_skin->close();
        }
        echo '<hr>';
    }
}

This is all just an example, you can use the mysqli_result::fetch_all instead of creating an array, another situation is that for greater security you study the mysqli that is the php API, because it is very recommended to use stmt.

Recommendations for you to study:

Browser other questions tagged php mysqli loop foreach

You are not signed in. Login or sign up in order to post.