Jump to content


Photo

Insert with Select inside


  • Please log in to reply
7 replies to this topic

#1 Darkness Soul

Darkness Soul
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts
  • LocationBrazil; São Paulo.

Posted 10 April 2006 - 03:39 PM

Hi Guys,

I have a newbie question... I think..

I have a import file that need to read some information, retrieve they ID from other tables, and record the data into the view table.

My sql line command is exactly like that:
INSERT INTO
    tbl_Company
    ( idcategoria , idsubcategoria , idcategoria2 , idsubcategoria2 , idcategoria3 , idsubcategoria3 , nomefantasia , endereco , numero , complemento , idestado , idcidade , idbairro , idregiao , cep , telefone , telefone2 , fax , tel0800 , palavrachave )
VALUES
    ( SELECT id FROM tbCategoria WHERE lcase(descricao)="esportes" LIMIT 1 ,
    SELECT id FROM tbSubCategoria WHERE lcase(descricao)="Academias de Ginástica" LIMIT 1 ,
    , , , , "academia corpo e lazer" , "r. joão cancian" , "76" , "Sem Registro" ,
    SELECT id FROM tbEstados WHERE lcase(sigla)="sp" LIMIT 1 ,
    SELECT id FROM tbCidades WHERE lcase(cidade)="hortolândia" LIMIT 1 ,
    SELECT id FROM tbBairros WHERE lcase(bairro)="parque hortolândia" LIMIT 1 ,
    SELECT id FROM tbRegioes WHERE lcase(regiao)="sem região" LIMIT 1 ,
    "" , "(19) 3865-1328" , "" , "" , "" , "academia ginástica;" )
My script don't return a mysql_error(), but in the phpmyadmin, it said have an error..

--
A little edit: I'm trying to use it in the UPDATE too.
--

Thanks for any help.

D.Soul
(If something is wrong, please tell me. I'm learning this language. Thank you)

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 10 April 2006 - 04:44 PM

You probably have a quoting problem. Show me more code surrounding that query.

#3 Darkness Soul

Darkness Soul
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts
  • LocationBrazil; São Paulo.

Posted 10 April 2006 - 05:04 PM

Guy.. thats a big-complexe one..

while ( !feof ( $arq_importacao ))
{
    // Le os registros de uma linha
    $arq_registro = fgetcsv ( $arq_importacao , 1000 , "\t" , '"' );
    // Guarda a quantia de campos
    $num_campos = count ( $arq_registro );
    // Le os dados dos registros
    $rs_gravacao['categoria']        = 'SELECT id FROM tbCategoria    WHERE lcase(descricao)="'.        strtolower ( ltrim ( rtrim ( $arq_registro[0] ))) .'" LIMIT 1';
    $rs_gravacao['subcategoria']    = 'SELECT id FROM tbSubCategoria WHERE lcase(descricao)="'. fun_procura_array ( ltrim ( rtrim ( $arq_registro[1] ))) .'" LIMIT 1';
    ( strlen ( $arq_registro[2] ) > 0 ) ? $rs_gravacao['categoria1']    = 'SELECT id FROM tbCategoria    WHERE lcase(descricao)="'.        strtolower ( ltrim ( rtrim ( $arq_registro[2] ))) .'" LIMIT 1' : 0;
    ( strlen ( $arq_registro[3] ) > 0 ) ? $rs_gravacao['subcategoria1']    = 'SELECT id FROM tbSubCategoria WHERE lcase(descricao)="'. fun_procura_array ( ltrim ( rtrim ( $arq_registro[3] ))) .'" LIMIT 1' : 0;
    ( strlen ( $arq_registro[4] ) > 0 ) ? $rs_gravacao['categoria2']    = 'SELECT id FROM tbCategoria    WHERE lcase(descricao)="'.        strtolower ( ltrim ( rtrim ( $arq_registro[4] ))) .'" LIMIT 1' : 0;
    ( strlen ( $arq_registro[5] ) > 0 ) ? $rs_gravacao['subcategoria2']    = 'SELECT id FROM tbSubCategoria WHERE lcase(descricao)="'. fun_procura_array ( ltrim ( rtrim ( $arq_registro[5] ))) .'" LIMIT 1' : 0;
    $rs_gravacao['nomefantasia']    = ltrim ( rtrim ( $arq_registro[6] ));
    $rs_gravacao['endereco']        = ltrim ( rtrim ( $arq_registro[7] ));
    $rs_gravacao['numero']            = ltrim ( rtrim ( $arq_registro[8] ));
    $rs_gravacao['complemento']        = ltrim ( rtrim ( $arq_registro[9] ));
        ( $rs_gravacao['complemento'] == 'sem complemento' ) ? $rs_gravacao['complemento'] = '' : FALSE;
    $rs_gravacao['estado']            = 'SELECT id FROM tbEstados WHERE lcase(sigla)="'.  ltrim ( rtrim ( strtolower ( $arq_registro[10] ))) .'" LIMIT 1';
    $rs_gravacao['cidade']            = 'SELECT id FROM tbCidades WHERE lcase(cidade)="'. ltrim ( rtrim ( strtolower ( $arq_registro[11] ))) .'" LIMIT 1';
    $rs_gravacao['bairro']            = 'SELECT id FROM tbBairros WHERE lcase(bairro)="'. ltrim ( rtrim ( strtolower ( $arq_registro[12] ))) .'" LIMIT 1';
    $rs_gravacao['regiao']            = 'SELECT id FROM tbRegioes WHERE lcase(regiao)="'. ltrim ( rtrim ( strtolower ( $arq_registro[13] ))) .'" LIMIT 1';
    $rs_gravacao['cep']                = ltrim ( rtrim ( $arq_registro[14] ));
    $rs_gravacao['telefone']        = ltrim ( rtrim ( $arq_registro[15] ));
        ( strlen ( $rs_gravacao['telefone'] ) > 0 ) ? $rs_gravacao['telefone'] = '(19) '. substr ( $rs_gravacao['telefone'] , 0 , 4 ) .'-'. substr ( $rs_gravacao['telefone'] , -4 ) : FALSE;
    $rs_gravacao['telefone1']        = ltrim ( rtrim ( $arq_registro[16] ));
        ( strlen ( $rs_gravacao['telefone1'] ) > 0 ) ? $rs_gravacao['telefone1'] = '(19) '. substr ( $rs_gravacao['telefone1'] , 0 , 4 ) .'-'. substr ( $rs_gravacao['telefone1'] , -4 ) : FALSE;
    $rs_gravacao['fax']                = ltrim ( rtrim ( $arq_registro[17] ));
        ( strlen ( $rs_gravacao['fax'] ) > 0 ) ? $rs_gravacao['fax'] = '(19) '. substr ( $rs_gravacao['fax'] , 0 , 4 ) .'-'. substr ( $rs_gravacao['fax'] , -4 ) : FALSE;
    $rs_gravacao['800']                = ltrim ( rtrim ( $arq_registro[18] ));
        ( strlen ( $rs_gravacao['800'] ) > 0 ) ? $rs_gravacao['800'] = '0800 '. substr ( $rs_gravacao['800'] , 0 , 3 ) .'-'. substr ( $rs_gravacao['800'] , -4 ) : FALSE;
    $rs_gravacao['palavrachave']    = ltrim ( rtrim ( $arq_registro[19] ));
    // Executa apenas se ouver os campos obrigatórios
    if ((( $rs_gravacao['categoria']    ) != '' ) &&
        (( $rs_gravacao['subcategoria'] ) != '' ) &&
        (( $rs_gravacao['nomefantasia'] ) != '' ))
    {
        // Verifica se a empresa já existe
        $rs_emp_sql        = 'SELECT id, endereco, numero FROM tbEmpresas WHERE lcase(nomefantasia)="'. $rs_gravacao['nomefantasia'] .'" LIMIT 1';
        $rs_emp_query    = mysql_query ( $rs_emp_sql , $conn );
        // Monta a string de conexão para inserção
        $rs_str_sql  = ' INSERT INTO tbEmpresas ';
        $rs_str_sql .= ' ( idcategoria , idsubcategoria , idcategoria2 , idsubcategoria2 , idcategoria3 , idsubcategoria3 , nomefantasia , endereco , numero , complemento , idestado , idcidade , idbairro , idregiao , cep , telefone , telefone2 , fax , tel0800 , palavrachave ) ';
        $rs_str_sql .= ' VALUES ( ';
        // Testa se a empresa existe
        if ( mysql_num_rows ( $rs_emp_query ) > 0 )
        {
            // Verifica se possui novo endereço ou é alteração
            $rs_emp_dados = mysql_fetch_array ( $rs_emp_query );
            if (( strtolower ( $rs_emp_dados['endereco'] ) == strtolower ( $rs_gravacao['endereco'] )) &&
                ( $rs_emp_dados['numero'] == $rs_gravacao['numero'] ))
            {
                // Monta a string de conexão para alteração
                $rs_str_sql  = ' UPDATE tbEmpresas SET ';
                $bool_str_insupd = 'U';
            }
            else
            {
                $bool_str_insupd = 'A';
            }
        }
        // Verifica se é inserção ou alteração
        if ( $bool_str_insupd == 'U' )
        {
            $cont_max_update ++;
            // Montagem da string de conexão
            if ( strlen ( $rs_dados['idcategoria'] <= 0 ))
              {
                  $rs_str_sql .= ' idcategoria='.  $rs_gravacao['categoria']  .',  idsubcategoria='.  $rs_gravacao['subcategoria']  .', ';
                  $rs_str_sql .= ' idcategoria2='. $rs_gravacao['categoria1'] .',  idsubcategoria2='. $rs_gravacao['subcategoria1'] .', ';
                  $rs_str_sql .= ' idcategoria3='. $rs_gravacao['categoria2'] .',  idsubcategoria3='. $rs_gravacao['subcategoria2'] .', ';
              }
              elseif ( strlen ( $rs_dados['idcategoria2'] <= 0 ))
              {
                  $rs_str_sql .= ' idcategoria2='. $rs_gravacao['categoria']  .',  idsubcategoria2='. $rs_gravacao['subcategoria']  .', ';
                  $rs_str_sql .= ' idcategoria3='. $rs_gravacao['categoria1'] .',  idsubcategoria3='. $rs_gravacao['subcategoria1'] .', ';
                  $rs_str_sql .= ' idcategoria4='. $rs_gravacao['categoria2'] .',  idsubcategoria4='. $rs_gravacao['subcategoria2'] .', ';
              }
              elseif ( strlen ( $rs_dados['idcategoria3'] <= 0 ))
              {
                  $rs_str_sql .= ' idcategoria3='. $rs_gravacao['categoria']  .',  idsubcategoria3='. $rs_gravacao['subcategoria']  .', ';
                  $rs_str_sql .= ' idcategoria4='. $rs_gravacao['categoria1'] .',  idsubcategoria4='. $rs_gravacao['subcategoria1'] .', ';
                  $rs_str_sql .= ' idcategoria5='. $rs_gravacao['categoria2'] .',  idsubcategoria5='. $rs_gravacao['subcategoria2'] .', ';
              }
              elseif ( strlen ( $rs_dados['idcategoria4'] <= 0 ))
              {
                  $rs_str_sql .= ' idcategoria4='. $rs_gravacao['categoria']  .',  idsubcategoria4='. $rs_gravacao['subcategoria']  .', ';
                  $rs_str_sql .= ' idcategoria5='. $rs_gravacao['categoria1'] .',  idsubcategoria5='. $rs_gravacao['subcategoria1'] .', ';
              }
              else
              {
                  $rs_str_sql .= ' idcategoria5='. $rs_gravacao['categoria']  .',  idsubcategoria5='. $rs_gravacao['subcategoria']  .', ';
              }
            // Continuação da expressao
            $rs_str_sql    .= ' nomefantasia="'. $rs_gravacao['nomefantasia']     .'", 
                                endereco="'.     $rs_gravacao['endereco']     .'", 
                                numero="'.       $rs_gravacao['numero']       .'", 
                                complemento="'.  $rs_gravacao['complemento']  .'", 
                                idestado='.     $rs_gravacao['estado']       .', 
                                idcidade='.     $rs_gravacao['cidade']       .', 
                                idbairro='.     $rs_gravacao['bairro']       .', 
                                idregiao='.     $rs_gravacao['regiao']       .', 
                                cep="'.          $rs_gravacao['cep']          .'", 
                                telefone="'.     $rs_gravacao['telefone']     .'", 
                                telefone2="'.    $rs_gravacao['telefone1']    .'", 
                                fax="'.          $rs_gravacao['fax']          .'", 
                                tel0800="'.      $rs_gravacao['800']          .'", 
                                palavrachave="'. $rs_gravacao['palavrachave'] .'", 
                                cliente="N", ativo="S" 
                                WHERE id="'. $rs_emp_dados['id'] .'" LIMIT 1';
            
            $rs_str_query    = mysql_query ( $rs_str_sql , $conn );
            // Verifica erro
            if ( !$rs_str_query )
            {
                print '<hr>' . mysql_error() .'<br><br>'. $rs_str_sql;
            }
            else
            {
                // Aumenta o contador
                $cont_num_update ++;
            }
        }
        else
        {
            ( $bool_str_insupd == 'A' ) ? $cont_max_insupd ++ : $cont_max_insert ++;
            // Montagem da string de conexão
            $rs_str_sql .= $rs_gravacao['categoria'] .' , 
                        '. $rs_gravacao['subcategoria'] .' , 
                        '. $rs_gravacao['categoria1'] .' , 
                        '. $rs_gravacao['subcategoria1'] .' , 
                        '. $rs_gravacao['categoria2'] .' , 
                        '. $rs_gravacao['subcategoria2'] .' , 
                       "'. $rs_gravacao['nomefantasia'] .'" , 
                       "'. $rs_gravacao['endereco'] .'" , 
                       "'. $rs_gravacao['numero'] .'" , 
                       "'. $rs_gravacao['complemento'] .'" , 
                        '. $rs_gravacao['estado'] .' , 
                        '. $rs_gravacao['cidade'] .' , 
                        '. $rs_gravacao['bairro'] .' , 
                        '. $rs_gravacao['regiao'] .' , 
                       "'. $rs_gravacao['cep'] .'" , 
                       "'. $rs_gravacao['telefone'] .'" , 
                       "'. $rs_gravacao['telefone1'] .'" , 
                       "'. $rs_gravacao['fax'] .'" , 
                       "'. $rs_gravacao['800'] .'" , 
                       "'. $rs_gravacao['palavrachave'] .'" )';
            // Verifica erro
            if ( !$rs_str_query )
            {
                print '<hr>' . mysql_error() .'<br><br>'. $rs_str_sql;
            }
            else
            {
                // Aumenta o contador
                ( $bool_str_insupd == 'A' ) ? $cont_num_insupd ++ : $cont_num_insert ++;
            }
        }
    }
    else
    {
        $cont_num_erros ++;
    }
    unset ( $rs_gravacao );
}

(If something is wrong, please tell me. I'm learning this language. Thank you)

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 April 2006 - 05:39 PM

I've not looked carefully, but you need to wrap each of those subqueries in parentheses.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 10 April 2006 - 06:36 PM

Yeah fenway's right, all those subqueries need their own parentheses:

INSERT INTO
    tbl_Company
    ( idcategoria , idsubcategoria , idcategoria2 , idsubcategoria2 , idcategoria3 , idsubcategoria3 , nomefantasia , endereco , numero , complemento , idestado , idcidade , idbairro , idregiao , cep , telefone , telefone2 , fax , tel0800 , palavrachave )
VALUES
    ( (SELECT id FROM tbCategoria WHERE lcase(descricao)="esportes" LIMIT 1) ,
    (SELECT id FROM tbSubCategoria WHERE lcase(descricao)="Academias de Ginástica" LIMIT 1) ,
    , , , , "academia corpo e lazer" , "r. joão cancian" , "76" , "Sem Registro" ,
    (SELECT id FROM tbEstados WHERE lcase(sigla)="sp" LIMIT 1) ,
    (SELECT id FROM tbCidades WHERE lcase(cidade)="hortolândia" LIMIT 1) ,
    (SELECT id FROM tbBairros WHERE lcase(bairro)="parque hortolândia" LIMIT 1) ,
    (SELECT id FROM tbRegioes WHERE lcase(regiao)="sem região" LIMIT 1) ,
    "" , "(19) 3865-1328" , "" , "" , "" , "academia ginástica;" )


#6 Darkness Soul

Darkness Soul
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts
  • LocationBrazil; São Paulo.

Posted 10 April 2006 - 07:54 PM

I've tryied it.. but..

Mensagens do MySQL : Documentação
#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM tbCategoria WHERE lcase(descricao)="esportes" LI
=( unhappy..
(If something is wrong, please tell me. I'm learning this language. Thank you)

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 April 2006 - 08:53 PM

That being the case, I'll have to ask again what version you're running -- that sounds oddly like MySQL's version of "I don't know that syntax", rather than "you've made a syntax error".
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 10 April 2006 - 10:48 PM

fenway is right. subqueries are only available after a certain version of MySQL; i myself can't use them on my own hosted server. i think if i remember rightly, it is in a fairly recent server that they're supported.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users