Searching A Database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Searching A Database

Post by icesolid »

How would I search a database by start and end date?

I want to pull all of the results on the start date and the end date and anything in between.

My search criteria:

Customer Code: $user_code
Start Date: $start_date
End Date: $end_date
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Since you didn't post a table structure

Code: Select all

SELECT * FROM `table` WHERE `customer_code` = '$user_code' AND `date` BETWEEN '$start_date' AND '$end_date'
and guess what? Your thread's being moved to Databases. :roll:
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

Thanks!

Yeah, sorry about that I forgot to post it in the Database section.
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

That code above doesent produce an error, but it doesent produce a result either.

Let's say my user_code is 001 and the date_ordered is 02-27-2006 and I enter start_date = 02-26-2006 and end_date = 02-28-2006 I should get 1 result, but I am getting nothing.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

post your table structure.

Code: Select all

SHOW CREATE TABLE `table`
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

When I run the code below nothing shows up?

Code: Select all

<?php
include("connect.php");

mysql_query("SHOW CREATE TABLE `cases`");
?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

$q = mysql_query(...);
while($r = mysql_fetch_row($q))
{
  var_dump($r);
}
:roll:
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

Here it is:

array(2) { [0]=> string(5) "cases" [1]=> string(2912) "CREATE TABLE `cases` ( `id` int(11) NOT NULL auto_increment, `customer` text NOT NULL, `user_code` text NOT NULL, `control_number` text NOT NULL, `inspector_code` text NOT NULL, `viewed` text NOT NULL, `assigned` text NOT NULL, `printed` text NOT NULL, `saved` text NOT NULL, `inpurgatory` text NOT NULL, `sent_to_reviewer` text NOT NULL, `sent_to_customer` text NOT NULL, `ecd` text NOT NULL, `date_ordered` text NOT NULL, `date_assigned` text NOT NULL, `date_printed` text NOT NULL, `date_sent_to_reviewer` text NOT NULL, `date_completed` text NOT NULL, `policy_number` text NOT NULL, `name_of_insured` text NOT NULL, `mailing_address` text NOT NULL, `mailing_city` text NOT NULL, `mailing_state` text NOT NULL, `mailing_zip` text NOT NULL, `location_address` text NOT NULL, `location_city` text NOT NULL, `location_state` text NOT NULL, `location_zip` text NOT NULL, `location_county` text NOT NULL, `survey_type` text NOT NULL, `restaurant_supplement` text NOT NULL, `rush` text NOT NULL, `agency_name` text NOT NULL, `agency_phone` text NOT NULL, `agency_person` text NOT NULL, `contact_name` text NOT NULL, `contact_phone` text NOT NULL, `coverages_building` text NOT NULL, `coverages_contents` text NOT NULL, `coverages_liability` text NOT NULL, `special_instructions` longtext NOT NULL, `brief_description` text NOT NULL, `type_of_policy` text NOT NULL, `policy_year_built` text NOT NULL, `policy_construction` text NOT NULL, `policy_gross` text NOT NULL, `effective_date` text NOT NULL, `note` longtext NOT NULL, `drawing` text NOT NULL, `photo1` text NOT NULL, `caption1` text NOT NULL, `photo2` text NOT NULL, `caption2` text NOT NULL, `photo3` text NOT NULL, `caption3` text NOT NULL, `photo4` text NOT NULL, `caption4` text NOT NULL, `photo5` text NOT NULL, `caption5` text NOT NULL, `photo6` text NOT NULL, `caption6` text NOT NULL, `photo7` text NOT NULL, `caption7` text NOT NULL, `photo8` text NOT NULL, `caption8` text NOT NULL, `photo9` text NOT NULL, `caption9` text NOT NULL, `photo10` text NOT NULL, `caption10` text NOT NULL, `photo11` text NOT NULL, `caption11` text NOT NULL, `photo12` text NOT NULL, `caption12` text NOT NULL, `photo13` text NOT NULL, `caption13` text NOT NULL, `photo14` text NOT NULL, `caption14` text NOT NULL, `photo15` text NOT NULL, `caption15` text NOT NULL, `photo16` text NOT NULL, `caption16` text NOT NULL, `photo17` text NOT NULL, `caption17` text NOT NULL, `photo18` text NOT NULL, `caption18` text NOT NULL, `photo19` text NOT NULL, `caption19` text NOT NULL, `photo20` text NOT NULL, `caption20` text NOT NULL, `charge` text NOT NULL, `reviewer_initials` text NOT NULL, `recommendations` text NOT NULL, `billing_note` text NOT NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM" }
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Help us help you please.

Code: Select all

array(2) { [0]=> string(5) "cases" [1]=> string(2912) "
CREATE TABLE `cases` ( `id` int(11) NOT NULL auto_increment,
 `customer` text NOT NULL,
 `user_code` text NOT NULL,
 `control_number` text NOT NULL,
 `inspector_code` text NOT NULL,
 `viewed` text NOT NULL,
 `assigned` text NOT NULL,
 `printed` text NOT NULL,
 `saved` text NOT NULL,
 `inpurgatory` text NOT NULL,
 `sent_to_reviewer` text NOT NULL,
 `sent_to_customer` text NOT NULL,
 `ecd` text NOT NULL,
 `date_ordered` text NOT NULL,
 `date_assigned` text NOT NULL,
 `date_printed` text NOT NULL,
 `date_sent_to_reviewer` text NOT NULL,
 `date_completed` text NOT NULL,
 `policy_number` text NOT NULL,
 `name_of_insured` text NOT NULL,
 `mailing_address` text NOT NULL,
 `mailing_city` text NOT NULL,
 `mailing_state` text NOT NULL,
 `mailing_zip` text NOT NULL,
 `location_address` text NOT NULL,
 `location_city` text NOT NULL,
 `location_state` text NOT NULL,
 `location_zip` text NOT NULL,
 `location_county` text NOT NULL,
 `survey_type` text NOT NULL,
 `restaurant_supplement` text NOT NULL,
 `rush` text NOT NULL,
 `agency_name` text NOT NULL,
 `agency_phone` text NOT NULL,
 `agency_person` text NOT NULL,
 `contact_name` text NOT NULL,
 `contact_phone` text NOT NULL,
 `coverages_building` text NOT NULL,
 `coverages_contents` text NOT NULL,
 `coverages_liability` text NOT NULL,
 `special_instructions` longtext NOT NULL,
 `brief_description` text NOT NULL,
 `type_of_policy` text NOT NULL,
 `policy_year_built` text NOT NULL,
 `policy_construction` text NOT NULL,
 `policy_gross` text NOT NULL,
 `effective_date` text NOT NULL,
 `note` longtext NOT NULL,
 `drawing` text NOT NULL,
 `photo1` text NOT NULL,
 `caption1` text NOT NULL,
 `photo2` text NOT NULL,
 `caption2` text NOT NULL,
 `photo3` text NOT NULL,
 `caption3` text NOT NULL,
 `photo4` text NOT NULL,
 `caption4` text NOT NULL,
 `photo5` text NOT NULL,
 `caption5` text NOT NULL,
 `photo6` text NOT NULL,
 `caption6` text NOT NULL,
 `photo7` text NOT NULL,
 `caption7` text NOT NULL,
 `photo8` text NOT NULL,
 `caption8` text NOT NULL,
 `photo9` text NOT NULL,
 `caption9` text NOT NULL,
 `photo10` text NOT NULL,
 `caption10` text NOT NULL,
 `photo11` text NOT NULL,
 `caption11` text NOT NULL,
 `photo12` text NOT NULL,
 `caption12` text NOT NULL,
 `photo13` text NOT NULL,
 `caption13` text NOT NULL,
 `photo14` text NOT NULL,
 `caption14` text NOT NULL,
 `photo15` text NOT NULL,
 `caption15` text NOT NULL,
 `photo16` text NOT NULL,
 `caption16` text NOT NULL,
 `photo17` text NOT NULL,
 `caption17` text NOT NULL,
 `photo18` text NOT NULL,
 `caption18` text NOT NULL,
 `photo19` text NOT NULL,
 `caption19` text NOT NULL,
 `photo20` text NOT NULL,
 `caption20` text NOT NULL,
 `charge` text NOT NULL,
 `reviewer_initials` text NOT NULL,
 `recommendations` text NOT NULL,
 `billing_note` text NOT NULL,
 PRIMARY KEY (`id`) ) TYPE=MyISAM
" }
Your choice of column tyes for dates is unfortunate (TEXT). By continuing to insist on storing dates in a nonstandard way you're making for a lot more work. Why are you storing 20 captions and photos? I'd suggest reading up on Database Normalization
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

What would be an easier way of storing my date so that I could search it easier in the future?

My client wants the date format to be 00-00-0000 month-day-year with trailing zeros in the final output.

I tried using DATE as the field type but then I had trouble formatting it when I wanted to output it.

I also have a program that outputs how many days away "TODAY" is from a "Estimated Completion Date" and I could not figure out how to make the DATE field type work, but I was able ot figure it out using TEXT as the field type.
Last edited by icesolid on Fri Mar 10, 2006 5:13 pm, edited 2 times in total.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

feyd wrote:and guess what? Your thread's being moved to Databases. :roll:
Yay Can I come too :lol:
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

Nice web site agtlewis, good concept.

Only suggestion would be showing examples of some of your work, you talk a lot about "good web site practices" but one of those good practices is showing proof of your work and visuals, a lot of dry reading on your site, in other words its a very boring web site.

This is an example of showing examples: http://www.2advanced.com

:lol:
Last edited by icesolid on Fri Mar 10, 2006 5:36 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the DATE type stores Y-m-d. You can format the output with MySQL by using DATE_FORMAT()
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

icesolid wrote:Nice web site agtlewis, good concept.

Only suggestion would be showing examples of some of your work, you talk a lot about "good web site practices" but one of those good practices is showing proof of your work and visuals, a lot of dry reading on your site, in other words its a very boring web site.

This is an example of showing examples: http://www.2advanced.com

:lol:
Thats a little bit off topic dontcha think? I'm familiar with 2advanced, flash can be a good thing but it's not something I am interested in. I'll put a portfolio up when I have time. I'm too busy at the moment and probably will be for at least 8 months.
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

feyd wrote:the DATE type stores Y-m-d. You can format the output with MySQL by using DATE_FORMAT()
Ymd and you can use type int, plus it's the *standard* iso 8601 way.
Post Reply