Search Script

[MsShelle]

Ok, I've been reading and trying to give this a go on my own but I am failing miserably, any help is greatly appreciated....

I have a table in my DB named loads, I have created the form for users to search the db for available loads. The options are as follows:

Search By date
Search by Origin State
Search By Destination State
Search By Equipment Type

Now I need to create the script that actually retrieves the data based upon the user selected options. How do I even begin to write this??

I left the search by date (on the form) field empty for users to input a date of their choosing because I am not sure how I can set it up so they can only choose by the most current date?!

The Search By Origin State and the Search by Destination State is a drop down box which lists all the states (In the US) to choose one.

The Search By Equipment Type is also a drop down with several options.

If someone can at least help me to get started out, I would appreciate it sooooo much.

Thanks In Advance
MsShelle

[nnichols]

What is the structure of table "loads" - field names, data types

What have you got so far for your input form?

[MsShelle]

Thanks nnichols so much for replying it is greatly appreciated!!!

#

Table structure for table loads

#

CREATE TABLE loads (
id int(11) NOT NULL auto_increment,
load_number varchar(255) default NULL,
IBA_Number varchar(255) default NULL,
load_date date NOT NULL default '0000-00-00',
Origin_City varchar(55) default NULL,
Origin_State varchar(50) default NULL,
Origin_Zip varchar(10) default NULL,
Destination_City varchar(50) default NULL,
Destination_State varchar(55) default NULL,
Destination_Zip varchar(10) default NULL,
Miles varchar(5) default NULL,
Product varchar(55) default NULL,
Equipment varchar(80) default NULL,
Weight varchar(9) default NULL,
Rate varchar(6) default NULL,
Dispatch_Number varchar(20) default NULL,
Contact varchar(45) default NULL,
Comments text NOT NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;

You can view the actual form here:-> http://www.midwestlogisticsllc.com/searchload.html

I hope I have given you what u were asking for, if not please let me know.

TIA
MsShelle

[nnichols]

Do you require the user to fill in all the fields?

[MsShelle]

I would like to but do not know how so on the form, no.

[nnichols]

Try this -

<?PHP
// this section of code is to go in your search form page - must change extension to .php
// build day select
$day_select = '<select size="1" name="day"><option value="">Day</option>';
for ($i=1; $i<32; $i++) {
	if ($i<10)
		$i = '0' . $i;
	$day_select .= "<option value='$i'>$i</option>\n";
	}
$day_select .= '</select>';
// end build day select

// build month select
$month_select = '<select size="1" name="month"><option value="">Month</option>';
for ($i=1; $i<13; $i++) {
	if ($i<10)
		$i = '0' . $i;
	$month_select .= "<option value='$i'>$i</option>\n";
	}
$month_select .= '</select>';
// end build month select

// build year select
$cur_year = date("Y");
$year_select = '<select size="1" name="year"><option value="">Year</option>';
$year_select .= "<option value='" . $cur_year . "'>" . $cur_year . "</option>\n";
$year_select .= '</select>';
// end build year select
?>
<!-- for your existing select fields in your form you must set the value for the first option to NULL - <option value=''> -->
<!-- use this to add the date drop down boxes to your form -->
<tr><td><?=$day_select?> <?=$month_select?> <?=$year_select?></td></tr>


<?PHP
// this section of the code is for the processing script
if ($_POST['year'] 
	&& $_POST['month'] 
	&& $_POST['day'] 
	&& $_POST['Origin_State'] 
	&& $_POST['Destination_State'] 
	&& $_POST['Equipment']) {


	$sql = 'SELECT * FROM loads WHERE load_date="' . $_POST['year'] . '-' . $_POST['month'] . '-' . $_POST['day'] . '" ';
	$sql .= 'AND Origin_State="' . $_POST['Origin_State'] . '" ';
	$sql .= 'AND Destination_State="' . $_POST['Destination_State'] . '" ';
	$sql .= 'AND Equipment="' . $_POST['Equipment'] . '"';

// then run query and display results in normal way

}
else {
echo "You did not fill in all the form fields.<br>Please use your browser's back button to return to the search form.";
}
?>

The first section of code is to go into your search page to create select boxes for entering the date. The second block of code is for processing the form data and building the db query.

If you struggle with this, PM me.

Hope this helps 😉

[MsShelle]

Thanks NNichols,

Quick question before I start at trying this on my own 😃 this part of the code:

$sql = 'SELECT * FROM loads WHERE load_date="' . $_POST['year'] . '-' . $_POST['month'] . '-' . $_POST['day'] . '" ';
        $sql .= 'AND Origin_State="' . $_POST['Origin_State'] . '" ';
        $sql .= 'AND Destination_State="' . $_POST['Destination_State'] . '" ';
        $sql .= 'AND Equipment="' . $_POST['Equipment'] . '"';

will this reguire the user to select all options? I think I want the user to be able to search by one or a combination of options. For example: a user wants to search by date and origin state only or by equipment and date.

Sorry, if I am being a pain and thanks for all your help.

MsShelle

[nnichols]

The code as it is, requires all fields to be selected/filled in. To make all fields except date optional you could replace the previous query with -

$sql = 'SELECT * FROM loads WHERE load_date="' . $_POST['year'] . '-' . $_POST['month'] . '-' . $_POST['day'] . '"';
if ($_POST['Origin_State'])
	$sql .= ' AND Origin_State="' . $_POST['Origin_State'] . '"';
if ($_POST['Destination_State'])
	$sql .= ' AND Destination_State="' . $_POST['Destination_State'] . '"';
if ($_POST['Equipment'])
	$sql .= ' AND Equipment="' . $_POST['Equipment'] . '"';

Hope this helps 😉

[MsShelle]

I've been so wrapped up in creating the script to search the db , I forgot that I would need to display the results.....

How do I accomplish that?

TIA
MsShelle

[nnichols]

something like this -

// connect to db - substitute your connection details
$db = mysql_connect('localhost','user','pass') or die('Failed to connect to db server.<br>Error: ' . mysql_error());
mysql_select_db('db_name') or die('Failed to select db.<br>Error: ' . mysql_error());

// run query
$res = mysql_query($sql, $db) or die('Failed to execute query.<br>Error: ' . mysql_error() . '<br>Query: ' . $sql);

echo '<table border=1>';

// display results
while ($row = mysql_fetch_row($res)) {

echo "<tr>";
$field_count = count($row);

// loop through fields
for ($j=0 ; $j<$field_count ; $j++) {
echo "<td>$row[$j]</td>";
}
echo '</tr>';
}
echo '</table>';

Hope this helps 😉

[MsShelle]

Nnichols, check your pm's

Thanx

MsShelle

[MsShelle]

Thanks so much NNichols

Your help was so greatly needed and appreciated!! I am now off trying to conquer another MySQL & PHP task...I think I'm getting it, I actually wrote a script that actually works 😃

I;m glad you were here!

MsShelle