Page 1 of 1

Searching A Database

Posted: Fri Mar 10, 2006 3:08 pm
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

Posted: Fri Mar 10, 2006 3:10 pm
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:

Posted: Fri Mar 10, 2006 3:13 pm
by icesolid
Thanks!

Yeah, sorry about that I forgot to post it in the Database section.

Posted: Fri Mar 10, 2006 3:23 pm
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.

Posted: Fri Mar 10, 2006 3:38 pm
by feyd
post your table structure.

Code: Select all

SHOW CREATE TABLE `table`

Posted: Fri Mar 10, 2006 3:42 pm
by icesolid
When I run the code below nothing shows up?

Code: Select all

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

mysql_query("SHOW CREATE TABLE `cases`");
?>

Posted: Fri Mar 10, 2006 3:49 pm
by feyd

Code: Select all

$q = mysql_query(...);
while($r = mysql_fetch_row($q))
{
  var_dump($r);
}
:roll:

Posted: Fri Mar 10, 2006 3:52 pm
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" }

Posted: Fri Mar 10, 2006 4:15 pm
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

Posted: Fri Mar 10, 2006 5:06 pm
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.

Posted: Fri Mar 10, 2006 5:11 pm
by Benjamin
feyd wrote:and guess what? Your thread's being moved to Databases. :roll:
Yay Can I come too :lol:

Posted: Fri Mar 10, 2006 5:31 pm
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:

Posted: Fri Mar 10, 2006 5:35 pm
by feyd
the DATE type stores Y-m-d. You can format the output with MySQL by using DATE_FORMAT()

Posted: Fri Mar 10, 2006 5:48 pm
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.

Posted: Sat Mar 11, 2006 5:49 am
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.