Syntax error when testing the query in phpmyadmin

Asked

Viewed 178 times

-4

Error solved .

Query

SELECT i.codiniciativa,
    te.nome as nome_tema,
    te.codtemaestrategico as cod_te,
    p.observacoes as per_obs,
    te.sequencia as tem_sequencia,
    p.sigla as per_sigla,
    p.nome as nome_perspectiva,
    p.codperspectiva as cod_perspectiva,
    o.sequencia as sequencia_obj,
    o.codobjetivo as cod_obj,
    o.nome as nome_obj,concat(p.sigla, ' ', te.sequencia,
    '.', o.sequencia, '.',i.sequencia) as codigo,i.nome as nom_iniciativa,
    i.sequencia as iniciativa_sequencia,
    i.metas,i.responsavel,i.indicadores,
    ifnull((SELECT a.percentual FROM $_base.avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) = $ano AND month(a.data) = ".$_parametros['mes1']." ORDER BY a.data DESC LIMIT 1),
    ifnull((SELECT a.percentual FROM $_base.avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) <= $ano AND month(a.data) < ".$_parametros['mes1']." AND curdate() >= '$ano-".$_parametros['mes1']."-01' ORDER BY a.data DESC LIMIT 1), 0)) as '01',
    ifnull((SELECT a.percentual FROM $_base.avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) = $ano AND month(a.data) = ".$_parametros['mes2']." ORDER BY a.data DESC LIMIT 1),
    ifnull((SELECT a.percentual FROM $_base.avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) <= $ano AND month(a.data) < ".$_parametros['mes2']." AND curdate() >= '$ano-".$_parametros['mes2']."-01' ORDER BY a.data DESC LIMIT 1), 0)) as '02',
    ifnull((SELECT a.percentual FROM $_base.avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) = $ano AND month(a.data) = ".$_parametros['mes3']." ORDER BY a.data DESC LIMIT 1),
    ifnull((SELECT a.percentual FROM $_base.avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) <= $ano AND month(a.data) < ".$_parametros['mes3']." AND curdate() >= '$ano-".$_parametros['mes3']."-01' ORDER BY a.data DESC LIMIT 1), 0)) as '03',
    ifnull((SELECT a.percentual FROM $_base.avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) = $ano AND month(a.data) = ".$_parametros['mes4']." ORDER BY a.data DESC LIMIT 1),
    ifnull((SELECT a.percentual FROM $_base.avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) <= $ano AND month(a.data) < ".$_parametros['mes4']." AND curdate() >= '$ano-".$_parametros['mes4']."-01' ORDER BY a.data DESC LIMIT 1), 0)) as '04',
    ifnull((SELECT a.percentual FROM $_base.avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) = $ano AND month(a.data) = ".$_parametros['mes5']." ORDER BY a.data DESC LIMIT 1),
    ifnull((SELECT a.percentual FROM $_base.avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) <= $ano AND month(a.data) < ".$_parametros['mes5']." AND curdate() >= '$ano-".$_parametros['mes5']."-01' ORDER BY a.data DESC LIMIT 1), 0)) as '05',
    ifnull((SELECT a.percentual FROM $_base.avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) = $ano AND month(a.data) = ".$_parametros['mes6']." ORDER BY a.data DESC LIMIT 1),
    ifnull((SELECT a.percentual FROM $_base.avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) <= $ano AND month(a.data) <".$_parametros['mes6']." AND curdate() >= '$ano-".$_parametros['mes6']."-01' ORDER BY a.data DESC LIMIT 1), 0)) as '06' 
    FROM $_base.perspectivas p  
    LEFT JOIN $_base.temasestrategicos te ON p.codperspectiva = te.codperspectiva 
    LEFT JOIN $_base.objetivos o ON te.codtemaestrategico = o.codtemaestrategico 
    LEFT JOIN $_base.iniciativas i ON o.codobjetivo = i.codobjetivo 
    WHERE p.codperspectiva = '5' AND p.status = 'A' AND te.status = 'A' AND o.status = 'A' AND i.status = 'A'ORDER BY p.sequencia ASC,te.sequencia ASC,
    o.sequencia ASC,CAST(i.sequencia AS DECIMAL) ASC"

I’m trying to find the mistake but I’m not succeeding: Please, if anyone can help me I’ll be very grateful! Hugs.

Updated query for phpmyadmin

SELECT i.codiniciativa,
    te.nome as nome_tema,
    te.codtemaestrategico as cod_te,
    p.observacoes as per_obs,
    te.sequencia as tem_sequencia,
    p.sigla as per_sigla,
    p.nome as nome_perspectiva,
    p.codperspectiva as cod_perspectiva,
    o.sequencia as sequencia_obj,
    o.codobjetivo as cod_obj,
    o.nome as nome_obj,concat(p.sigla, ' ', te.sequencia,
    '.', o.sequencia, '.',i.sequencia) as codigo,i.nome as nom_iniciativa,
    i.sequencia as iniciativa_sequencia,
    i.metas,i.responsavel,i.indicadores,
    ifnull((SELECT a.percentual FROM avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) = 2015 AND month(a.data) = 1 ORDER BY a.data DESC LIMIT 1),
    ifnull((SELECT a.percentual FROM avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) <= 2015 AND month(a.data) <1 AND curdate() >= 2015-1-01' ORDER BY a.data DESC LIMIT 1), 0)) as '01',
    ifnull((SELECT a.percentual FROM avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) = 2015 AND month(a.data) =2 ORDER BY a.data DESC LIMIT 1),
    ifnull((SELECT a.percentual FROM avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) <= 2015 AND month(a.data) < 2 AND curdate() >= 2015-2-01' ORDER BY a.data DESC LIMIT 1), 0)) as '02',
    ifnull((SELECT a.percentual FROM avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) = 2015 AND month(a.data) = 3 ORDER BY a.data DESC LIMIT 1),
    ifnull((SELECT a.percentual FROM avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) <= 2015 AND month(a.data) < 3 AND curdate() >=2015-3-01' ORDER BY a.data DESC LIMIT 1), 0)) as '03',
    ifnull((SELECT a.percentual FROM avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) = 2015 AND month(a.data) = ".$_parametros['mes4']." ORDER BY a.data DESC LIMIT 1),
    ifnull((SELECT a.percentual FROM avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) <= 2015 AND month(a.data) < 4 AND curdate() >=2015-4-01' ORDER BY a.data DESC LIMIT 1), 0)) as '04',
    ifnull((SELECT a.percentual FROM avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) = 2015 AND month(a.data) = 5  ORDER BY a.data DESC LIMIT 1),
    ifnull((SELECT a.percentual FROM avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) <= 2015 AND month(a.data) < 5 AND curdate() >=2015-5-01' ORDER BY a.data DESC LIMIT 1), 0)) as '05',
    ifnull((SELECT a.percentual FROM avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) = 2015 AND month(a.data) = 6  ORDER BY a.data DESC LIMIT 1),
    ifnull((SELECT a.percentual FROM avaliacoes a WHERE a.status = 'A' AND a.codiniciativa = i.codiniciativa AND year(a.data) <= 2015 AND month(a.data) < 6 AND curdate() >= 2015-6-01' ORDER BY a.data DESC LIMIT 1), 0)) as '06' 
    FROM perspectivas p  
    LEFT JOIN temasestrategicos te ON p.codperspectiva = te.codperspectiva 
    LEFT JOIN objetivos o ON te.codtemaestrategico = o.codtemaestrategico 
    LEFT JOIN .iniciativas i ON o.codobjetivo = i.codobjetivo 
    WHERE p.codperspectiva = '5' AND p.status = 'A' AND te.status = 'A' AND o.status = 'A' AND i.status = 'A'ORDER BY p.sequencia ASC,te.sequencia ASC,
    o.sequencia ASC,CAST(i.sequencia AS DECIMAL) ASC
php mysql sql phpmyadmin
  • 1

    The simplest is in php vc print the value of this/query variable and test in phpmyAdmin.

  • how could you correctly test the value of this/query variable in phpmyadmin

  • Oops, I accidentally deleted... change that part '$ano-".$_parametros['mes1']."-01' therefore "'" . $ano . "-" . $_parametros['mes1'] . "-01" . "'"

  • I made some changes and is giving this error now #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 ' ifnull((SELECT a.percentage FROM 2015 evaluations a WHERE a.status = 'A' AND' at line 1

  • In that AND curdate() >= 2015-1-01', don’t you think you forgot the quote at the beginning of the date? I think it should be AND curdate() >= '2015-1-01'.

  • @Victorstafusa already correct this error , only that I wanted to print with php the Financial , because I echo the ['perspective'] she just print the 1 but I want to print the name Financial . How can I do ? <select name="perspective"> <option value="1">Financial</option> </select>

  • @allanaraujo If you have corrected this error, edit the question and put it the way it is. Otherwise, someone would hardly reopen/reopen your question, since there is a blatant typo in your SQL that spoils everything and will jump in front of anyone who wants to help you.

  • @Victorstafusa already put as solved , but how do I print with php the Financial , because I echo the ['perspective'] she just print the 1 but I want to print the name Financial . How can I do ? <select name="perspective"> <option value="1">Financial</option> </select>

  • @allanaraujo I don’t know. I don’t really understand what it is you’re asking. Your question is about SQL and now you ask something about PHP finance that I have no idea what is containing HTML code. Whatever this is, it would be to ask another totally different question (if the system allows you). And just for the record, I didn’t, and I didn’t vote to close your question, but I wouldn’t put it up for reopening either.

  • @Victorstafusa already fix query error, missing only with php.

Show 5 more comments

1 answer

6


phpMyAdmin does not accept PHP variables, in the example you used: $_base.avaliacoes.

Try to accomplish: echo $sua_query; and test the result on phpMyAdmin

Also, the dates are missing a single quote before:

change of:

curdate() >= 2015-1-01'

for:

curdate() >= '2015-1-01'

  • I made some changes and is giving this error now #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 ' ifnull((SELECT a.percentage FROM 2015 evaluations a WHERE a.status = 'A' AND' at line 1

  • I put in the question post .

  • Missing a simple quotation mark at the beginning of the Dates.

  • can check the error. Thank you very much .

  • How can I do what’s in the question ?

  • Here you are saying I can only post in a week .

  • But tell me how I can make this query to be searched in phpmyadmin to be able to return all columns type ,when I do the search it only returns perspectives, I wanted it to return all these other evaluations,initiatives,objectives,perspective,types of valuables

  • 1

    @allanaraujo the system ended up blocking you from asking new questions due to the quality, to have this privilege again you need to edit, whichever questions, here has some tips on how to improve. If you have any questions let us know or you can post a question on meta which is the place for discussions about stack overflow, site issues or users etc.

  • @rray how can I do for WHERE p.codperspectiva = '5' where it is '5' to be automatic for when selecting some column.

  • the only thing I want is to let WHERE p.codperspectiva = '5' stay in dynamic mode, like when selecting one of the 8 perspective it selects automatically in WHERE p.codperspectiva = ''

  • is giving this error Parse error: syntax error, Unexpected T_PUBLIC in /var/www/Planningstrategy/model/indomitable/Indoors.php on line 74

  • @Allanandrade $_unlicensed = $_model->Returnprospects($_perspective); that would be the error in this line Public Function Returnprospects($_perspective){ Return $_perspective; }

  • Why make that mistake

Show 8 more comments

Browser other questions tagged php mysql sql phpmyadmin

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