PHP - Multi Selects & Search

Whats the most efficient way of searching within a multi dimensional array?

My Array =

Array ( => Array ( => Item 1 [title] => Item 1 [1] => 2012-03-21 [eventDate] => 2012-03-21 [2] => 21 [eventDay] => 21 ) [1] => Array ( => Item 2 [title] => Item 2 [1] => 2012-03-21 [eventDate] => 2012-03-21 [2] => 21 [eventDay] => 21 ) )

String to find = '21'

If (MY ARRAY contains STRING TO FIND) {}

Clearly in this case there are several '21' (s) so if I only wanted to search the [eventDay] keys...would there be a fast effective and efficient manner?


Thoughts and help gratefully received.

Will

 

So let's assume i'm making a script for searching cars by different criteria. Name of the car, color, and mileage. I used to do this by using LIKE and just adding AND for as many times as needed. But i've read that that is obsolete and is giving servers a hard time, so i went ahead and started making a new script that will use fulltext search. I've hit a dead end.

Code: [Select]
   function search($table,$what,$string,$limit,$start,$country) {
   global $totrows;
   global $pages;
   $string = explode("*",$string);
   $what = explode("*",$what);
   $cname = trim($string[0]);
   $t = 0;
   $parameters = "";
   while (isset($what[$t])){
   if ($t>0) { $paramm .= ", "; }
   $paramm .= trim($what[$t]);
   if ($t>0) { $parama .= " "; }
   $parama .= trim($string[$t]);
   $t++;
   }
   $parameters .= " AND locationc = '".$country."'";
   $sql = "SELECT *, MATCH(".$paramm.") AGAINST('".$parama."') AS score FROM main WHERE MATCH(".$paramm.") AGAINST('".$parama."') ORDER BY score DESC";
   $result = mysql_query($sql) or die(mysql_error());
   while ($row = mysql_fetch_array($result)) {
var_dump($row);   
   
    }
   }
How does this work:

The user selects and enters his parameters>
The script then joins them all into a single variable which is then sent to the function(the reason i do this i because the user might input only the name, so that way i can add more criteria and not need to change the script)>
The script explodes those into arrays and then it processes them into a form for use with sql>
I run the sql query and then return the results.

What's the problem? There's two of them actually>

The first one is that mysql returns an error "Can't find FULLTEXT index matching the column list" even though i did setup fulltext indexes.

(phpmyadmin c/p)
Code: [Select]
name FULLTEXT No No name 0 YES
color FULLTEXT No No color 0 YES
mileage FULLTEXT No No mileage 0 YES

The second problem is that the script is not selective and will not work as intended. For example, a car's name is 300, the users input's 300, and the script will return those rows that had mileage 300 or 300000 or whatever.

How do i fix this, and is FULLTEXT the right way to go with multi criteria search?

Hi Guys,

I am a typical newbie with a twist, i program in VB, recently moved to PHP.

I need help, preferable an example, but first let me explain what i have.

I have a .tpl page that has 3 drop down boxes, one of these is populated from within the .tpl page itself. The other 2 i waant to pupulate from a SELECT on a database from a .php file.

The .TPL file i have so far is:-

Code: [Select]
<style type="text/css">
<!--
.style7 {
   color: #006600;
   font-weight: bold;
   font-size: 18px;
}
.style14 {color: #003300; font-weight: bold; }
.style16 {
   color: #000066;
   font-style: italic;
}
.style25 {
   color: #FF0000;
   font-weight: bold;
   font-size: 12px;
}
.style28 {
   font-size: 12px;
   color: #330000;
}
.style29 {
   font-size: 12px
}
.style31 {color: #003300; font-weight: bold; font-size: 16px; }
.style33 {
   color: #000099;
   font-weight: bold;
   font-style: italic;
   font-size: 18px;
}
.style34 {
   color: #FF0000;
   font-weight: bold;
   font-size: 24px;
}
.style35 {font-size: 12px; color: #003300; }
.style36 {
   color: #FF0000;
   font-weight: bold;
   font-size: 14px;
   font-style: italic;
}
.style38 {color: #003300; font-weight: bold; font-size: 16px; font-style: italic; }
-->
</style>

  <script src="Scripts/AC_RunActiveContent.js" type="text/javascript"></script>
<h1 align="center" class="style34">Scotbirds Alertz - Rare and Scarce</h1>
<h3 align="center" class="style36">Soon Only VIP Members will be able to Access this Page</h3>
<h3 align="center" class="style14">If you have seen something unusual / rare then please call it in on -- <span class="style33">Hotline: 0333 5772473</span></h3>
<p align="center" class="style14">

<table width="90%" border="0" align="center" cellpadding="5">
  <tr>

  <?php print $_SERVER['PHP_SELF'];
     $the_date_filter = $_GET["DATE_FILTER"];?>

    <td width="1%"><td width="5%"><td width="2%"><td width="2%"><td width="2%"><form action="alertz_VIP.php" method="post">
    <td width="2%"><td width="2%"><td width="5%"><span class="style38">Date</span><td width="3%"></td>
    <td width="12%"><span class="style14">
      <select name=DATE_FILTER size="1" id=DATE_FILTER onchange="this.form.submit()">
        <option value="All Dates">All Dates</option>
        <option value="Today">Today</option>
        <option value="Last 48hrs">Last 48hrs</option>
        <option value="Last week">Last Week</option>
        <option value="last month">Last Month</option>
      </select>
    </span></td>
    <td width="1%">&nbsp;</td>
    <td width="7%"><span class="style16"><span class="style14"><span class="style31">Region</span></span></span></td>
   
        <td width="22%"><span class="style16"><span class="style14">
          <select name=REGION_FILTER size="1" id=REGION_FILTER onchange="this.form.submit()">
            <option value="AllRg">All Regions</option>
            <option value={REGION_FILTER}></option>
          </select>
        </span></span></td>
    <td width="1%">&nbsp;</td>

    <td width="8%"><span class="style16"><span class="style14"><span class="style31">Species</span></span></span></td>
    <td width="20%"><span class="style16"><span class="style14">
      <select name=SPECIES_FILTER size="1" id=SPECIES_FILTER onchange="this.form.submit()">
        <option value="AllSpec">All Species</option>
        <option value={SPECIES_FILTER}></option>
      </select>
    </span></span></td>
    <td width="9%"><input type="Submit" name="submit2" value="Search"  method="get" action="alertz_VIP.php" /></td>
</table>
<p>&nbsp;</p>
<table width="870" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#99CC99">

  <tr>
    <th width="178" class="style31" scope="col">Region</th>
    <th width="184" class="style31" scope="col">Species</th>
    <th width="96" class="style31" scope="col">Date</th>
    <th width="96" class="style31" scope="col">Time</th>
    <th width = "304" class="style31" scope="col">Comments</th>
  </tr>
  <!-- BEGIN alerts -->
  <tr>
    <th class="style7 style29" scope="col">{alerts.REGION}</th>
    <th class="style25" scope="col">{alerts.SPECIES}</th>
    <th class="style35" scope="col">{alerts.DATE} </th>
    <th scope="col"><span class="style35">{alerts.TIME} </span></th>
    <th scope="col"><span class="style28">{alerts.COMMENTS} </span></th>
  </tr>
  <!-- END alerts -->
</table>
<p align="center" class="style14">&nbsp;</p>

Now from here i expect the user to select a date from the date box, ( Ideally i would like the other 2 options to only become visible once a selection on the date has been made, i would also like to retain the selected in the date box once the form has been submited.

Now the code i have for the PHP so far is quite long winded as my PHP skills are not so good, although i am learning fast.

Code: [Select]
<?php
/***************************************************************************
*                                Alertz.php
*                            -------------------
*   begin                : 30/10/04/10
*   copyright         : (C) 2010 Andy Guppy
*   email                : webmaster@scotbird.co.uk
*
*
***************************************************************************/

define('IN_ICYPHOENIX', true);

if (!defined('IP_ROOT_PATH')) define('IP_ROOT_PATH', './');
if (!defined('PHP_EXT')) define('PHP_EXT', substr(strrchr(__FILE__, '.'), 1));

// Include files
include(IP_ROOT_PATH . 'common.' . PHP_EXT);
include_once(IP_ROOT_PATH . 'includes/functions_groups.' . PHP_EXT);
include(IP_ROOT_PATH . '/alerts/alertsconfig.'.PHP_EXT);

// Page Authorise
$cms_page_id = 'scotalertz';
$cms_page_nav = (!empty($cms_config_layouts[$cms_page_id]['page_nav']) ? true : false);
$cms_global_blocks = (!empty($cms_config_layouts[$cms_page_id]['global_blocks']) ? true : false);
$cms_auth_level = (isset($cms_config_layouts[$cms_page_id]['view']) ? $cms_config_layouts[$cms_page_id]['view'] : AUTH_ALL);
check_page_auth($cms_page_id, $cms_auth_level);

// Obtain Select Criteria
$temp_region_filter = $_REQUEST["REGION_FILTER"];
$temp_species_filter = $_REQUEST["SPECIES_FILTER"];
$temp_date_filter = $_REQUEST["DATE_FILTER"];

// Filter characters if required
$region_filter =str_replace(" & ", "&", $temp_region_filter);
$species_filter = $temp_species_filter;
$date_filter = $temp_date_filter;

// standard session management
$userdata = session_pagestart($user_ip);
// Check to see if user is logged in 
if ((!$userdata['session_logged_in']) )
   // No he isnt
   {
        redirect(append_sid(LOGIN_MG . '?redirect=alerts.' . PHP_EXT));
   
   }

else
   // Yes they are
   {
         
      init_userprefs($userdata);
      // set page title
      $page_title = "ScotBird alerts - VIP's ONLY !! ";

      // standard page header
      include(IP_ROOT_PATH . 'includes/page_header.'.PHP_EXT);
      
      // Connect to the database
      
      $db = new sql_db($alerts_mysql_host,$alerts_mysql_username,$alerts_mysql_password,$alerts_mysql_db,false);

       if(!$db)
      {
              die("Database Connection Failed:- Please Contact Site Admin" . mysql_error());
       }
      
      $template->set_filenames(array('body' => 'alertz_VIP.tpl'));

      if (!isset($_REQUEST['DATE_FILTER']))
      
      {
      
         // if date is NOT set then perform this
            echo 'Date has not been set';
            $sql2 = "SELECT * FROM alerts  ORDER BY Date DESC, time DESC  ";
            $sql3 = "SELECT DISTINCT species FROM alerts GROUP BY species";
            $sql = "SELECT DISTINCT region FROM alerts GROUP BY region";
            
            echo $sql2;
      }
      else
      {
         // if date is set then perform this
            $year =date("Y");
            $month = date("m");
            $day = date("d");
            $theenddate  = $year . '-' .$month . '-' . $day ;
            echo 'the choice selected was :- '.$_REQUEST['DATE_FILTER'];
         
         switch ($date_filter)
         {
            case "All Dates":
               $sql2 = "SELECT * FROM alerts  ORDER BY Date DESC, time DESC  ";
            break;
            case "Today":
               $sql2 = "SELECT * FROM alerts WHERE Date = '".$theenddate  ."'   ORDER BY Date DESC, time DESC  ";
               $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date = '".$theenddate ."'  GROUP BY species";
               $sql = "SELECT DISTINCT region FROM alerts WHERE Date ='".$theenddate ."' GROUP BY region";
            break;
            case "Last 48hrs":
               $year =date("Y");
               $month = date("m");
               $day = date("d")-1;
               $thestartdate  = $year . '-' .$month . '-' . $day ;
               $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  ORDER BY Date DESC, time DESC  ";
               $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  GROUP BY species";
               $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region";
            break;   
            case "Last week":
               $year =date("Y");
               $month = date("m");
               $day = date("d")-7;
               $thestartdate  = $year . '-' .$month . '-' . $day ;
               $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  ORDER BY Date DESC, time DESC  ";
               $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  GROUP BY species";            
               $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region";
            break;
            case "last month":
               $year =date("Y");
               $month = date("m")-1;
               $day = date("d");
               $thestartdate  = $year . '-' .$month . '-' . $day ;
               $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  ORDER BY Date DESC, time DESC  ";
               $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  GROUP BY species";            
               $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region";
            break;
            case "last 3 months":
               $year =date("Y");
               $month = date("m")-3;
               $day = date("d");
               $thestartdate  = $year . '-' .$month . '-' . $day ;
               $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  ORDER BY Date DESC, time DESC  ";
               $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  GROUP BY species";            
               $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region";
            break;
            default:
               // Default is the last 48hrs
               $year =date("Y");
               $month = date("m");
               $day = date("d")-1;
               $thestartdate  = $year . '-' .$month . '-' . $day ;
               $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  ORDER BY Date DESC, time DESC  ";
               $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY species";
               $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region";
            
         }
      }

      $result2 = $db->sql_query($sql2);
      if (!($result2 = $db->sql_query($sql2)))
      {
         die("Database Query Failed" . mysql_error());
      }
      $i  = 1;

      while ( $row2 = $db->sql_fetchrow($result2) )
         {
            $template->assign_block_vars('alerts', array( 'POS' => $i ,
                  'REGION' => str_replace("&", " & ", $row2['region']),
                  'SPECIES' => str_replace("%", "'",$row2['species']),
                  'DATE' => $row2['Date'],
                  'TIME' =>  $row2['time'],
                  'COMMENTS' => str_replace("'", "%",$row2['comments']),
                  )
               );
            $i++;
         }
      
      $template->assign_vars(array(
            'USERNAME' => htmlspecialchars($userdata[username]),
            'REGION_FILTER' => str_replace("&", " & ", $region_filter_options),
            'SPECIES_FILTER' => $species_filter_options,
            )
         );

      
      
   
      $result = $db->sql_query($sql);
    
      if (!($result = $db->sql_query($sql)))
         {
               die("Database Query Failed" . mysql_error());
         }

      // This is where you would add a new VARS Array if you intend to use your own custom VARS.
      
      
      while ($row = $db->sql_fetchrow($result))
         {
               $region_filter_options .= '<option value="' . $row['region'] . '">' . $row['region'] . '</option>';

         }
         
            
      
   
      $result = $db->sql_query($sql3);
    
      if (!($result = $db->sql_query($sql3)))
         {
               die("Database Query Failed" . mysql_error());
         }

      // This is where you would add a new VARS Array if you intend to use your own custom VARS.
      
      
      while ($row = $db->sql_fetchrow($result))
         {
               
            $species_filter_options .= '<option value="' . $row['species'] . '">' . $row['species'] . '</option>';
         }
         
         
      $template->assign_vars(array(
            'USERNAME' => htmlspecialchars($userdata[username]),
            'REGION_FILTER' => str_replace("&", " & ", $region_filter_options),
            'SPECIES_FILTER' => $species_filter_options,
            
         )
      );   
         
         
      // Build the page
      $template->pparse('body');

      // standard page footer
      include(IP_ROOT_PATH . 'includes/page_tail.'.PHP_EXT);

   }
?>
Now my questions are these:-

1) In the .tpl file hows can i submit from any of the dropdown boxes and retain the selection after submission
2) How can i have it so that the second and third drop down boxes are only visible after the previous one, ie the first box ( date ) has to have a selection before the Region ( 2nd one ) is visible and so
3) How can i reduce the amount of code to cover all options in building a select statement or is Switch - case the best way.

I would greatly appreciate help with this and even more so for some example of what i am asking for so i can learn from them.

Hello,   Any ideas why server resources not exhausted on the code below, when my.cnf and php.ini are both set to use half the resources each? PHP & MySQL will both utilize all allotted resources on other scripts, so it's not a tuning problem. There is no script-side tuning.   The bottleneck: pdo select as shown below

foreach($bigList as $listObject) {
                $sql = $dbl->prepare("SELECT * FROM fewMillionRows WHERE indexedCol1=:indexedCol1 AND indexedCol2=:indexedCol2 AND indexedCol3=:indexedCol3 AND indexedCol4=:indexedCol4 LIMIT 1");
                $sql->execute($preparedValues);
                $return = $sql->fetchAll(PDO::FETCH_ASSOC);
}

On a dedicated server with 8GB RAM, the server uses >5% cpu/ram but takes a long time to finish the script.     Second Question What are some alternative designs?   Because the column values all happen to be alphanumeric, I could select the entire table and store it in an array. Accessing the keys like so: indexedCol1indexedCol2indexedCol3indexedCol4.   Results: MyISAM - Select Whole Table: 30 seconds MyISAM – Select Individual Rows (10k times) – 68 seconds InnoDB – Select Whole Table: 30 seconds InnoDB – Select Individual Rows (10k times) – 131 seconds     I am surprised it takes so long to select a whole table. The server resources use 1% for about 20 seconds, then cpu/ram jump to 30%+ for about 10 seconds. This is still drastically faster than individual selects.   In this instance, $bigList is over 500k items. At 68 seconds per 10k rows it’s absurdly long. Building an array with key/values is the only realistic way I currently know of, but I suspect there is a much better way of doing this.     As far as I know, I cannot do a select like so: SELECT * FROM t1 WHERE (column1,column2,column3) IN  ((val1, val2,val3), (val4, val5,val6)) There is no way to determine whether a row was found for each entry as far as I know.