89861

What code approach would let users apply three optional variables in PHP/MySQL? [closed]

Question:

I have a large CSV file containing >11k rows of data, with 13 columns. The 3 user-selected variables are State, City, and Job Title. My question is what methodology/function type would best allow me to easily aggregate the user choices from drop-down menus and then use that to query the database for the correct row?

Overview: This has all 50 US states, and each state has quite a few cities, in addition to over a dozen different job titles for each city.

Answer1:

Your server script simply translates the parameters to appropriate conditions in the WHERE clause of a query. E.g.

$wheres = array(); $params = array(); if (!empty($_POST['state'])) { $wheres[] = "state = :state"; $params['state'] = $_POST['state']; } if (!empty($_POST['city'])) { $wheres[] = "city = :city"; $params['city'] = $_POST['city']; } if (!empty($_POST['title'])) { $wheres[] = "title = :title"; $params['title'] = $_POST['title']; } $query = "SELECT * FROM YourTable"; if (count($wheres)) { $query .= " WHERE " . implode(" AND ", $wheres); } $stmt = $pdo->prepare($query); $result = $stmt->execute($params);

Recommend

  • Reading Attached Property from Non-DependencyObject
  • In TDD, splitting main class into sub classes during refactoring
  • Logging broadcast Intents and manually trigger them (Android)
  • controller only initialized once even if `$stateparams` changed
  • acts_as_taggable_on Tags added twice
  • Getting a line that has the coordinates defined by the mouse location
  • get latest text entry of GROUP BY statement
  • Find symbol dependencies in source code [closed]
  • How to implement arriving behavior with time delta?
  • Extract data between rows r
  • Embedding a Google map
  • How to force Composer to download a local package?
  • Is it possible to send skype chat messages from a linux server without X?
  • Sending Content-Type application/x-www-form-urlencoded WSO2 ESB
  • Refactoring advice: maps to POJOs
  • Want to save selected (i.e., more than 1) enums as string with NHibernate
  • Jquery UI Sortable, move item automatically
  • Client side validation mvc dropdown
  • Pre-populated SQLite Database not reading properly in Android Studio
  • Counting problem C#
  • Deploying a CodeRush plugin from the Community Site
  • apply a javascript function to draggable copy
  • Aptana 3 remove bundle (jquery)
  • netsh acl setting (need alternative method - registry settings?)
  • How to override value that appears in a dropdown in the rails_admin gem
  • Redux Form - Not able to type anything in input
  • Installing iPhone App to iPhone
  • MongoDB in PHP using aggregate to group by _id is null not working
  • Unanticipated behavior
  • VB.net deserialize, JSON Conversion from type 'Dictionary(Of String,Object)' to type '
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • How do I configure my settings file to work with unit tests?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Can't mass-assign protected attributes when import data from csv file
  • Binding checkboxes to object values in AngularJs
  • Unable to use reactive element in my shiny app
  • Net Present Value in Excel for Grouped Recurring CF
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How to load view controller without button in storyboard?