Member Avatar for aizel

here is the database


here is the form

if the search click i want the selected month in the combobox search from the database table under departure field month and it will fetch on the table..

for example:
<--departure-->
09/10/2010
10/10/2010
10/11/2010

<select name=\"month\">
<option value=\"01\">January</option>
<option value=\"02\">February</option>
<option value=\"03\">March</option>
<option value=\"04\">April</option>
<option value=\"05\">May</option>
<option value=\"06\">June</option>
<option value=\"07\">July</option>
<option value=\"08\">August</option>
<option value=\"09\">September</option>
<option value=\"10\">October</option>
<option value=\"11\">November</option>
<option value=\"12\">December</option>
</select>
<input type=\"submit\" name=\"search\" value=\"Submit\">

example selected month is october it should fetch the following:

10/10/2010
10/11/2010

i hope you get it..sorry for my bad instruction :)

  • 3 Contributors
  • 4 Replies
  • 131 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by mschroeder

Dani AI

Generated

Quick summary of options — is storing departure as strings like mm/dd/yyyy and wants rows for the selected month.

A fast, low‑risk fix (no schema change): match the two leading characters of the departure string and validate the form input. This assumes every value is zero‑padded (01..09). Example PHP pattern and query flow:

$month = $_POST['month'];                // e.g. "10"
if (!preg_match('/^(0[1-9]|1[0-2])$/', $month)) { /* bad input */ }

$stmt = $pdo->prepare(
  'SELECT id, departure, ... FROM trips WHERE LEFT(departure,2) = ? ORDER BY departure'
);
$stmt->execute([$month]);
$results = $stmt->fetchAll();

Notes and pitfalls: malformed rows (like 9/1/2010 without a leading zero) will not match; empty or non-date strings must be ignored. Always parameterize the query to avoid injection and log or fix rows that don’t match the expected pattern.

A robust, long‑term solution (recommended): convert the column to a real DATE type, backfill values and index the new column. Backfilling can be done in a small script that parses mm/dd/yyyy into YYYY-MM-DD and updates a newly added DATE column; once complete, add an index and change application inserts to write a DATE. This makes month/year queries fast and correct and avoids applying string functions to every row (which hurts performance). See MySQL DATE type reference and string functions for details (DATE types, LEFT / substring).

Tie‑in to earlier posts: ’s pattern idea and ’s “convert to date then extract month” both point to these same choices — quick string matching versus proper date storage. The migration route is worth the extra work for correctness and speed.

Recommended Answers

All 4 Replies

Member Avatar for chrishea

If you want to search on the month, then you probably need something like:

Select * from $table where departure like '$month/%' order by departure
Member Avatar for aizel

thanks for the reply but it is not what i want..
i want to extract the date in the database( month/ day/ year) then fetch all the month then that month will be the target of the month selected in the form..

for example:
<--departure-->
09/10/2010
10/10/2010
10/11/2010

after extracting:
09
10
10

example input in the form:
October which value is 10

then the result will be:
10
10

Member Avatar for chrishea

Sorry, I don't understand what you are trying to accomplish so unless you can explain it differently, I give up.

Member Avatar for mschroeder

If I follow correctly, when the user selects a month from the drop down, a two digit representation of the month is passed to the script. 01, 02, 03, etc. and you wish to then pull anything departing in that month.

You can do this relatively easily in SQL using something like this:

SELECT * FROM table WHERE MONTH( STR_TO_DATE(departure, '%m/%d/%Y') ) = 10

Where 10 is the integer value of the month supplied but the form. Beware that this is going to pull anything with month = 10 for ANY year.

***I assume you're departure column is stored as a string and is not using the DATE column type. Since it is not a supported MySQL date representation.

Edited by mschroeder because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.