Searching A Database
Moderator: General Moderators
Searching A Database
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
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
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Since you didn't post a table structure
and guess what? Your thread's being moved to Databases. 
Code: Select all
SELECT * FROM `table` WHERE `customer_code` = '$user_code' AND `date` BETWEEN '$start_date' AND '$end_date'- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
post your table structure.
Code: Select all
SHOW CREATE TABLE `table`When I run the code below nothing shows up?
Code: Select all
<?php
include("connect.php");
mysql_query("SHOW CREATE TABLE `cases`");
?>- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Code: Select all
$q = mysql_query(...);
while($r = mysql_fetch_row($q))
{
var_dump($r);
}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" }
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" }
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Help us help you please.
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
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
" }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.
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.
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

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
Last edited by icesolid on Fri Mar 10, 2006 5:36 pm, edited 1 time in total.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
the DATE type stores Y-m-d. You can format the output with MySQL by using DATE_FORMAT()
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.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
Ymd and you can use type int, plus it's the *standard* iso 8601 way.feyd wrote:the DATE type stores Y-m-d. You can format the output with MySQL by using DATE_FORMAT()