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

Question

Closed. This question needs to be more focused. It is not currently accepting answers.

Want to improve this question? Guide the asker to update the question so it focuses on a single, specific problem. Narrowing the question will help others answer the question concisely. You may edit the question if you feel you can improve it yourself. If edited, the question will be reviewed and might be reopened.

Closed 11 years ago.

Improve this 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.

Little confused on what you mean for user choices from a drop down, and what database if you're using the csv? Is the csv the database you'd look for, and the user fields would people searching for State, City, and Job within the CSV? — Dan H
– Dan H, Commented Jan 17, 2014 at 22:24
Sorry about not being clear on database. It's held in phpmyadmin with wpengine. The csv = database, and database = csv, for simplification purposes. Basically the finished result would allow a user to select the state, city, and job title they are interested in, and after hitting submit, be provided with all of the relevant data. Does that help? — Zander Fields
– Zander Fields, Commented Jan 17, 2014 at 22:36
Have you been unable to find tutorials on using databases from PHP? I'll bet quite a few of them demonstrate exactly this type of thing, since it's so common. — Barmar
– Barmar, Commented Jan 17, 2014 at 22:42
I work in SEO so I like to think my Google-fu is strong, and despite that have been at a loss to find anything that could guide me through this on my own. It's mostly been about how to populate drop-down menus with specific data from columns, or how to select a single row. What's been missing is the methodology for incorporating 3 of those selections, for a larger-than-normal database. — Zander Fields
– Zander Fields, Commented Jan 17, 2014 at 23:03
“I work in SEO so I like to think my Google-fu is strong” – sounds like rather wishful thinking to me, than based on any facts. *SCNR* — C3roe
– C3roe, Commented Feb 28, 2014 at 22:12

Barmar · Accepted Answer · 2014-01-17 22:41:46Z

1

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);

answered Jan 17, 2014 at 22:41

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

7 Comments

Zander Fields Over a year ago

Thank you so much! This looks exactly like what I need for functions. There's one last complicating factor. Because there are so many cities and states, it's not really feasible to hand write each of the cities. How would I go about populating the "City" drop-down after the user selects the city? I thought about having a separate db that is only for this purpose, but again I'm at somewhat of a loss.

Barmar Over a year ago

You mean populate the city after the user selects the state?

Barmar Over a year ago

Please don't try to put code in comments, it's totally unreadable without formatting.

Zander Fields Over a year ago

That's correct. Populate the "City" drop-down menu after the "State" selection is made. It'd be pretty easy to populate the "State" drop-down form, and I've done just that already.

Zander Fields Over a year ago

Sorry, I'll pull that.

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Linked

Related