Loop overload with foreach use [closed]

Question

Loop overload with foreach use [closed]

Navigation

#1 by (1 votes)

7

Hello I'm having a serious problem at least and what the hosting company aqual this my site is claiming that my code below is overloading queries or doing several loops, and I do not know what else I can do. / p>

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

Function 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 work as ID's exist in the table medias column id it will apply these results inside where in making appear within the second loop the items in the second query. p>

Below are 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 Members

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_category

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 put in table news column medias are inserted this way 1,2,3,4,5,6 etc. These values are actually the id's of the medias table % column id to perform a comparison within loop 2 these values are applied within WHERE IN so that the items in the medias column 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 loop foreach mysqli

asked by anonymous 13.11.2015 / 17:08

1 answer

Search name file uploaded by json IF issues in javascript

score 1 · Answer 1

This is missing fetch_assoc in your code, you are trying to loop a query resource executed:

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

The correct one would be this:

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

Another thing, you can only execute another query after closing the first one, you can make a vector 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 mysqli_result::fetch_all instead of creating a vector, another situation is that for greater security you study more the mysqli that is the API of php, therefore it is highly recommended that it be used stmt .

Recommendations for you to study: