Location: PHPKode > scripts > phpMyAccess > phpmyaccess-1.5.3/phpmyaccess/pma-queryinc.php
<?php

// this is where you should add input verification
function make_valarr_from_postvar_query() {
  echo "    <!--// make_valarr_from_postvar_query //--!>\n";
  for ($k=0 ; $k<$GLOBALS['opt']['num_fds']; $k++) {
    $fieldname = $GLOBALS['fds'][$k];
    $GLOBALS['val']["sh_"."$fieldname"] = $_POST["sh_"."$fieldname"];
    $GLOBALS['val']["ord_"."$fieldname"] = $_POST["ord_"."$fieldname"];
    $GLOBALS['val']["whq_"."$fieldname"] = $_POST["whq_"."$fieldname"];
    $GLOBALS['val']["tb_"."$fieldname"] = $_POST["tb_"."$fieldname"];
    $GLOBALS['val']["qr_"."$fieldname"] = $_POST["qr_"."$fieldname"];
    $GLOBALS['val']["whqc_"."$fieldname"] = $_POST["whqc_"."$fieldname"];
  }
}

function  do_save_hidden_vars() {
  echo '  <input type="hidden" name="mode" value="' . $GLOBALS['opt']['mode'] . "\" />\n";
  // echo '  <input type="hidden" name="mode" value="' . $GLOBALS['opt']['mode'] . " />\n";
}

function  do_query_table_display() {
  $qry = "Select * from " . $GLOBALS['opt']['querytable'] ." WHERE qry_name = \"" . $GLOBALS['opt']['self'] . "\"";
  $res = mysql_db_query($GLOBALS['opt']['db'],$qry);
  echo "<table border=\"2\">";
  echo "    <tr><th>Query Id</th><th>Description</th><th>Level</th><th>Owner</th></tr>\n";
  if (!$res) {
    echo "No Records found<br/>\n";
    pma_debug ("Mysql: " . mysql_errno().": ".mysql_error() );
  } else {
    if (!$GLOBALS['opt']['sel_query_ID']) {
      $first = 1;
    }
    while(list($id, $name, $desc, $level, $owner, $state, $headers, $query, $mod) = mysql_fetch_array($res)) {
      echo "    <tr>\n";
      if ($id==$GLOBALS['opt']['sel_query_ID']) {
        echo "    <td align=\"center\"><input type=\"radio\" checked name=\"sel_query_ID\" value=\"$id\"></td>\n";
        $GLOBALS['opt']["query_ID"]=$id;
        $GLOBALS['opt']["query_name"]=$name;
        $GLOBALS['opt']["query_desc"]=$desc;
        $GLOBALS['opt']["query_level"]=$level;
        $GLOBALS['opt']["query_owner"]=$owner;
        $GLOBALS['opt']["query_state"]=$state;
        $GLOBALS['opt']["query_headers"]=$headers;
        $GLOBALS['opt']["query_query"]=$query;
        $GLOBALS['opt']["query_mod"]=$mod;
      } elseif ($first) {
        $first = 0;
        echo "    <td align=\"center\"><input type=\"radio\" checked name=\"sel_query_ID\" value=\"$id\"></td>\n";
      } else {
        echo "    <td align=\"center\"><input type=\"radio\" name=\"sel_query_ID\" value=\"$id\"></td>\n";
      }
      echo "    <td>$desc</td>\n";
      echo "    <td>$level</td>\n";
      echo "    <td>$owner</td>\n";
      echo "    </tr>\n";
    }
  }
  echo "</table>\n";
}

function  do_display_field_headers() {
  echo "<tr>\n";
  for ($k=0 ; $k<$GLOBALS['opt']['num_fds']; $k++) {
     echo "<th align=\"center\">" . $GLOBALS['fds'][$k] . "</th>\n";
  }
}

function  do_display_fieldselect_order() {
  echo "<tr>\n";
  for ($k=0 ; $k<$GLOBALS['opt']['num_fds']; $k++) {
     // needs the selected bit implemented
     echo "<td align=\"center\"><input type=\"checkbox\" checked name=\"sh_" . $GLOBALS['fds'][$k] ."\" value=\"on\">&nbsp\n";
     echo "   <select name=\"ord_" . $GLOBALS['fds'][$k] . "\">\n";
     echo "         <option> </option>\n";
     echo "         <option>1</option>\n";
     echo "         <option>2</option>\n";
     echo "         <option>3</option>\n";
     echo "         <option>4</option>\n";
     echo "      </select>\n";
     echo "</td>\n";
  }
  echo "<td>Select to show field in report/sort order</td>\n";
  echo "</tr>\n";
}

function do_display_wheretype_selectors() {
  echo "<tr>\n";
  for ($k=0 ; $k<$GLOBALS['opt']['num_fds']; $k++) {
     // needs the selected bit implemented
     echo "<td align=\"center\">\n";
     echo "   <select name=\"whq_" . $GLOBALS['fds'][$k] . "\">\n";
     echo "         <option> </option>\n";
     echo "         <option>LIKE</option>\n";
     echo "         <option>EQ</option>\n";
     echo "         <option>NE</option>\n";
     echo "         <option>LT</option>\n";
     echo "         <option>GT</option>\n";
     echo "         <option>LE</option>\n";
     echo "         <option>GE</option>\n";
     echo "      </select>\n";
     echo "</td>\n";
  }
  echo "<td>Select type of where clause</td>";
  echo "</tr>\n";
}

function do_display_wheretype_combine_selectors() {
  echo "<tr>\n";
  echo "<td></td>\n";
  for ($k=1 ; $k<$GLOBALS['opt']['num_fds']; $k++) {
     // needs the selected bit implemented
     echo "<td align=\"center\">\n";
     echo "   <select name=\"whq_" . $GLOBALS['fds'][$k] . "\">\n";
     echo "         <option> </option>\n";
     echo "         <option>AND</option>\n";
     echo "         <option>OR</option>\n";
     echo "      </select>\n";
     echo "</td>\n";
  }
  echo "<td>And / OR selection. AND = Default</td>";
  echo "</tr>\n";
}

function  do_display_by_table_selectors() {
  echo "<tr>\n";
  for ($k=0 ; $k<$GLOBALS['opt']['num_fds']; $k++) {
    $fieldname=$GLOBALS['fds'][$k];
    SetLinkOpt($fieldname, 0);
    if ($GLOBALS['opt']['ttable']) {
      if ($GLOBALS['opt']['tcolumn'] and $GLOBALS['opt']['tview']) {
          $x = set_values_from_table($GLOBALS['opt']['db'],$GLOBALS['opt']['tb'].','.$GLOBALS['opt']['ttable'],$GLOBALS['opt']['ttable'].'.'.$GLOBALS['opt']['tcolumn'],
                 $GLOBALS['opt']['ttable'].'.'.$GLOBALS['opt']['tview'],' WHERE ('.$GLOBALS['opt']['ttable'].'.'.$GLOBALS['opt']['tcolumn'].' = '.
                 $GLOBALS['opt']['tb'].'.'.$fieldname.')');
      } else {
          $x = set_values_from_table($GLOBALS['opt']['db'],$GLOBALS['opt']['tb'],$fieldname);
      }
      echo "      <td><select name=\"tb_$fieldname\" size=\"1\">\n";
      // display drop-down box
      for ($j=0; $j<count($x['option']); $j++)  {
        if ( $x['value'][$j] == $GLOBALS['val']["tb_$fieldname"] ) {
            echo '     <option selected>'.$x['value'][$j].'</option>'."\n";
        } else {
            echo '     <option>'.$x['value'][$j].'</option>'."\n";
        }
      }
      echo "</select></td>\n";
    } else {
      echo "<td align=\"center\">-</td>\n";
    }
  }
  echo "<td>Select from table</td>\n";
  echo "</tr>\n";
}

function  do_display_query_text() {
  echo "<tr>\n";
  for ($k=0 ; $k<$GLOBALS['opt']['num_fds']; $k++) {
     echo "<td align=\"left\" nowrap><input type=\"text\" name=\"qr_". $GLOBALS['fds'][$k] ."\" ";
     echo " value=\"".  $GLOBALS['val']["qr_".$GLOBALS['fds'][$k]] . "\"</td>\n";
  }
  echo "<td>Use * as wildcard </td>\n";
  echo "</tr>\n";
}

function  do_create_query() {
    for ($k=0 ; $k<$GLOBALS['opt']['num_fds']; $k++) {
       $fieldname = $GLOBALS['fds'][$k];
       // column Sort in query
       $ordby  = abs($GLOBALS['val']["ord_"."$fieldname"]);
       if ($ordby > $GLOBALS['opt']['MaxSortBy']) {
          $ordby = $GLOBALS['opt']['MaxSortBy'];
       }
       if ($ordby) {
         $ord[$GLOBALS['val']["ord_"."$fieldname"]] = $ordby;
       }
       // And/or values
       $t1="whq_".$fieldname;
       if (strpos("ANDOR",$t1)) {
          $andor[$k]  = $GLOBALS['val'][$t1];
       }
       $tabflds[$k] = $GLOBALS['val']["tb_"."$fieldname"];
       $qrytxt[$k]  = $GLOBALS['val']["qr_"."$fieldname"];
       $like[$k]    = $GLOBALS['val']["whqc_"."$fieldname"];
    }


    $qry= "SELECT DISTINCT ";
    $cnt= 0;
    for ($k=0 ; $k<$GLOBALS['opt']['num_fds']; $k++) {
       $fieldname = $GLOBALS['fds'][$k];
       $idx = "sh_".$fieldname;
       // echo "sh_ $idx" .  $GLOBALS['val'][$idx] . "<br>\n";
       if($GLOBALS['val'][$idx]=='on') {
          if ($cnt!=0) {
             $qry .= ", ";
          } else {
             $cnt++;
          }
          $qry .= $fieldname;
       }
       $t1 = $GLOBALS['val']["ord_"."$fieldname"];
       // echo "ord_ " . $t1 . "<br>\n";
       if ($t1) {
         $ord[$t1] = $fieldname;
       }
    }
    $srt = " ORDER BY ";
    $cnt= 0;
    for ($k=0 ; $k<=$GLOBALS['opt']['num_fds']; $k++) {
        if($ord[$k]) {
          // echo "ord_ " . $ord[$k] . "<br>\n";
          if ($cnt!=0) {
             $srt .= ", ";
          } else {
             $cnt++;
          }
          $srt .= $ord[$k];
        }
      $fieldname = $GLOBALS['fds'][$k];
   }
   return $qry . $srt . $t5 . $t2 . $t3 . $t4;
}

function do_display_query() {
  make_valarr_from_postvar_query();
  // DisplayArrayVar($GLOBALS['val']);
  echo "<form action=\"" . $GLOBALS['opt']['self']. "\" method=\"post\">\n";

  echo "<hr width=\"30%\" align=\"left\"></hr><br><br>\n";
  do_save_hidden_vars();

  do_query_table_display();

  echo "<p></p>\n";
  // should display the current query number and comment
  echo "Editing: " . $GLOBALS['opt']["query_desc"] . "<br>\n";

  echo "<table border=\"2\">\n";

  // now display field names as headers.
  do_display_field_headers();
  // field display check boxes and sort order dropdowns
  do_display_fieldselect_order();
  // Display where type dropdown :  eq, gt, st etc.
  do_display_wheretype_selectors();
  // Display where type dropdown :  and / or
  do_display_wheretype_combine_selectors();
  // show the table drop down boxes, where applicable
  do_display_by_table_selectors();
  // Display the text boxes for the queries
  do_display_query_text();
  // create the query string
  $qry = do_create_query();
  if (!$GLOBALS['val']['sql-mod']) {
     $qrymod=$qry;
  } else {
     $qrymod=$GLOBALS['val']['sql-mod'];
  }
  echo "</table>\n";

  echo "<hr width=\"30%\" align=\"left\"></hr><br><br>\n";

  echo "Current query :<br><br>\n";
  echo "<textarea name=\"sql-gen\" cols=\"70\" rows=\"6\" readonly>$qry</textarea><br><br>\n";
  echo "You can edit the query here, use [save] to save it : <br><br>\n";
  echo "<textarea name=\"sql-mod\" cols=\"70\" rows=\"6\">$qrymod</textarea><br><br>\n";
  echo "Query Description : <br><br>\n";
  echo "<input type=\"text\" name=\"querydes\" size=\"50\" maxlength=\"50\" value=\"Describe the query\"><br><br>\n";
  echo "Query Level : <br><br>\n";
  echo "<input type=\"text\" name=\"querylevel\" value=200 size=3 maxlength=3><p></p>\n";

  echo "<hr width=\"30%\" align=\"left\"></hr><br>\n";

  echo "<table>\n";
  echo "<tr>\n";
  echo "  <td><input type=\"submit\" name=\"qadd\" value=\"add\"></td>\n";
  echo "    <td><input type=\"submit\" name=\"qchange\" value=\"change\"></td>\n";
  echo "    <td><input type=\"submit\" name=\"qdel\" value=\"del\"></td>\n";
  echo "    <td><input type=\"submit\" name=\"qshow\" value=\"show\"></td>\n";
  echo "    <td><input type=\"submit\" name=\"qsave\" value=\"save\"></td>\n";
  echo "    <td><input type=\"submit\" name=\"qtable\" value=\"table\"></td>\n";
  echo "  </tr>\n";
  echo "  </table>\n";
  echo "  </form>\n";
  echo "  <br>\n";
}
?>
Return current item: phpMyAccess