MySQL: PHP while with query and another while with query, how to improve?

Question

Guys I have this query, that gets a keyword, and searches in DB for an array of articles to show according that keyword query results, then I need to fetch information about those articles according the ids I get and show it. The query is not well done, could someone help me clean the next code and improve it?

Thank you

$cleanKeyword = str_replace("+", " ", $keyword);
$i = mysql_query("SELECT IdNorma FROM $cpalabras WHERE Descripcion = '$cleanKeyword'");
  while($materia = mysql_fetch_array($i)){
       // storing idNorma into a variable
       $IdNorma = $materia['IdNorma'];
  $SQL = "SELECT n.*, j.Descripcion as jurisdiccion, 
          t.Descripcion as tipo, 
          date_format(n.FechaDesde,'%d') as dia, 
          date_format(n.FechaDesde,'%m') as mes, 
          date_format(n.FechaDesde,'%Y') as anio 
          FROM c_normas as n, c_jurisdiccion as j, c_tiponorma as t, c_materia as m
          WHERE 1=1 ";
   $SQL .= "AND n.IdNorma = '$IdNorma' ";
   $SQL .= "ORDER BY Fechadesde DESC LIMIT 300";

   var_dump($SQL);
   $a = mysql_query($SQL);
       if(mysql_num_rows($a) > 0) {
         while($row = mysql_fetch_array($a)){ ?>
           <tr style="display:inline-block;width:100%;border-bottom:1px solid #E3E6EB;padding:4px 0;font-size: 10px;" class="listaEventoUnicoTr" id="quickSearchTr">
             <td width="120" align="left" id="searchable"><strong><?php echo fromDB($row['tipo']); ?></strong></td>
             <td width="90" align="left"><a href="http://www.ips.com.ar/normmunipC/IPS_Municipal_files/files/<?php echo fromDB($row['Archivo']); ?>" target="_blank"><?php echo fromDB($row['Numero']); ?></a></td>
             <td width="90" align="left" id="searchable"><?php echo fromDB($row['dia'])."-".fromDB($row['mes'])."-".fromDB($row['anio']); ?></td>
             <td width="255" align="left" style="padding-top:4px;padding-bottom:4px;" id="searchable"><?php echo fromDB($row['Descripcion']); ?></td>
           </tr>
         <?php } //while
       } else { //if ?>
           <tr style="display:inline-block;width:100%;border-bottom:1px solid #E3E6EB;padding:4px 0;font-size: 10px;" class="listaEventoUnicoTr" id="quickSearchTr">
             <td width="500" align="center" colspan="4"> No hay resultados.</td>
           </tr>


       <?php } // else 

     }// /while ids

Thank you guys!

Start by learning how to use JOINs in SQL queries

Mark Baker
– Mark Baker

2011-03-03 13:21:48 +00:00
Commented Mar 3, 2011 at 13:21 — Mark Baker
– Mark Baker, Commented Mar 3, 2011 at 13:21

David · Accepted Answer · 2011-03-03 13:37:07Z

2

This should help you move in the right direction:

SELECT n.*, j.descripcion as jurisdiccion, t.Descripcion as tipo, date_format(n.FechaDesde,'%d') as dia, date_format(n.FechaDesde,'%m') as mes, date_format(n.FechaDesde,'%Y') as anio

FROM c_normas as n

LEFT JOIN $cpalabras on $cpalabras.IdNorma = n.IdNorma

ORDER BY Fechadesde DESC LIMIT 300

answered Mar 3, 2011 at 13:37

David

2,6207 gold badges28 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

binaryLV · Accepted Answer · 2011-03-03 13:28:42Z

1

2 options:

use JOIN to join data sources. In this case you would need only 1 query, though I'm not quite sure what it would look like, as selecting from c_normas as n, c_jurisdiccion as j, c_tiponorma as t, c_materia as m seems weird/wrong to me.
after getting data from $cpalabras in first query, create list of "valid" $IdNorma values and then do a single query to select all necessary data from c_normas as n, c_jurisdiccion as j, c_tiponorma as t, c_materia as m, someting like (..) where n.IdNorma in (1, 2, 3), rather than (..) where n.IdNorma = 1.

answered Mar 3, 2011 at 13:28

binaryLV

9,1422 gold badges43 silver badges43 bronze badges

Collectives™ on Stack Overflow

MySQL: PHP while with query and another while with query, how to improve?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related