Forum Moderators: coopster
I am presently attempting to make a search engine for my work where people will be able to make submissions using a form, then search for those submissions.
I have the posting part of the form all done, and everything is working properly.
For some reason when I do a search, it's not taking certain things into account. (like the username and date)
My MYSQL table has a date field in it (actually set as a date field) using the timestamp method of YYYY-MM-DD. The company wanted to have a little calendar you can press which sets the date automatically.. that is the reason for the big mess of fixing the variables to fix mysql for the date.
Can I please get some insight as to why the username is not being grabbed properly from the database table and why the date BETWEEN function is not working?
Thanks
=============================================
==================================================
include ("inc/sqlcon.php"); // includes my connection parameters for mysql. Trust me this works, i use it everywhere :P (much easier then writing it each time)
//$_SESSION['username'] was set during login authentication which is required to preform searches on this page.
$username = $_SESSION['username'];
$submit = $_POST['Submit'];
$error = 0;
$sqlquery = mysql_query("SELECT * FROM users") or die (mysql_error());
$sqlquery2 = mysql_query("SELECT * FROM users WHERE username = '$username'") or die (mysql_error());
$userinfo = mysql_fetch_object($sqlquery2);
if ($submit){
///////////////////
//Form Variables//
/////////////////
$usersearch = $_POST['usersearch'];
$search = $_POST['search'];
$search = mysql_escape_string($search);
$date_now =$_POST['date_now'];
$date_nowsplit = split("-",$date_now); // Original Method is MM-DD-YYYY
if ($date_nowsplit[0] == '1'){$date_nowsplit[0] = '01';}
if ($date_nowsplit[0] == '2'){$date_nowsplit[0] = '02';}
if ($date_nowsplit[0] == '3'){$date_nowsplit[0] = '03';}
if ($date_nowsplit[0] == '4'){$date_nowsplit[0] = '04';}
if ($date_nowsplit[0] == '5'){$date_nowsplit[0] = '05';}
if ($date_nowsplit[0] == '6'){$date_nowsplit[0] = '06';}
if ($date_nowsplit[0] == '7'){$date_nowsplit[0] = '07';}
if ($date_nowsplit[0] == '8'){$date_nowsplit[0] = '08';}
if ($date_nowsplit[0] == '9'){$date_nowsplit[0] = '09';}
if ($date_nowsplit[1] == '1'){$date_nowsplit[1] = '01';}
if ($date_nowsplit[1] == '2'){$date_nowsplit[1] = '02';}
if ($date_nowsplit[1] == '3'){$date_nowsplit[1] = '03';}
if ($date_nowsplit[1] == '4'){$date_nowsplit[1] = '04';}
if ($date_nowsplit[1] == '5'){$date_nowsplit[1] = '05';}
if ($date_nowsplit[1] == '6'){$date_nowsplit[1] = '06';}
if ($date_nowsplit[1] == '7'){$date_nowsplit[1] = '07';}
if ($date_nowsplit[1] == '8'){$date_nowsplit[1] = '08';}
if ($date_nowsplit[1] == '9'){$date_nowsplit[1] = '09';}
$date_now = $date_nowsplit[2] . "-" . $date_nowsplit[0] . "-" . $date_nowsplit[1];
$date_then = $_POST['date_then'];
$date_thensplit = split("-",$date_then);
if ($date_thensplit[0] == '1'){$date_thensplit[0] = '01';}
if ($date_thensplit[0] == '2'){$date_thensplit[0] = '02';}
if ($date_thensplit[0] == '3'){$date_thensplit[0] = '03';}
if ($date_thensplit[0] == '4'){$date_thensplit[0] = '04';}
if ($date_thensplit[0] == '5'){$date_thensplit[0] = '05';}
if ($date_thensplit[0] == '6'){$date_thensplit[0] = '06';}
if ($date_thensplit[0] == '7'){$date_thensplit[0] = '07';}
if ($date_thensplit[0] == '8'){$date_thensplit[0] = '08';}
if ($date_thensplit[0] == '9'){$date_thensplit[0] = '09';}
if ($date_thensplit[1] == '1'){$date_thensplit[1] = '01';}
if ($date_thensplit[1] == '2'){$date_thensplit[1] = '02';}
if ($date_thensplit[1] == '3'){$date_thensplit[1] = '03';}
if ($date_thensplit[1] == '4'){$date_thensplit[1] = '04';}
if ($date_thensplit[1] == '5'){$date_thensplit[1] = '05';}
if ($date_thensplit[1] == '6'){$date_thensplit[1] = '06';}
if ($date_thensplit[1] == '7'){$date_thensplit[1] = '07';}
if ($date_thensplit[1] == '8'){$date_thensplit[1] = '08';}
if ($date_thensplit[1] == '9'){$date_thensplit[1] = '09';}
$date_then = $date_thensplit[2] . "-" . $date_thensplit[0] . "-" . $date_thensplit[1];
///////////////////
//Error Catching//
/////////////////
if ($date_now==""){echo "You must provide a START date when doing a search."; $error=1;}
if ($date_then==""){echo "You must provide an END date when doing a search."; $error=1;}
/////////////////////
//Search Execution//
///////////////////
if ($error!=1)
{
if ($userinfo->access == 2) // Regular users (assigned access level 2 cannot search by any user. They can only search entries they did)
{
$user = $_SESSION['username'];
}
$result = mysql_query("SELECT * FROM leads WHERE username = '$usersearch' AND company LIKE '%$search%' OR phone LIKE '%$search%' OR contact LIKE '%$search%' OR result LIKE '%$search%' AND date BETWEEN '$date_now' AND '$date_then'") or die(mysql_error());
}
////////////////////
//Results Section//
//////////////////
?><style type="text/css">
<!--
.style1 {font-size: 10px}
-->
</style>
<?
echo "You Searched For: " . $search . "<br>By: " . $usersearch . "<br>Between " . $date_now . " & " . $date_then . "<br>It Produced " . mysql_num_rows($result) . " Results.<br><br>"; ?>
<form>
<table width="100%" border="0">
<tr>
<td><strong>Company Name</strong></td>
<td><strong>Contact</strong></td>
<td><strong>Telephone</strong></td>
<td><strong>Details</strong></td>
</tr>
<? while($row=mysql_fetch_object($result)){ ?>
<tr>
<td><? echo $row->company; ?></td>
<td><? echo $row->contact; ?></td>
<td><? echo $row->phone; ?></td>
<td><? echo "<a href='details.php?id=$row->id' target='_blank'>Details"; ?></a></td>
</tr>
<? } ?>
</table>
</form>
<?
}
else{
.... the rest of the form (where people can search)
<form name="search" method="post" action="<? $_SERVER['PHP_SELF'] ?>?p=searchleads">
<table width="100%" border="0">
<tr>
<td width="27%">
<?
if ($userinfo->access == 3){
echo "User: <label>"; ?></label></td>
<td width="69%"><select name='usersearch' id='usersearch'>
<option value="">Select One</option>
<?
while($userlist = mysql_fetch_object($sqlquery)){
?>
<option value='<? echo $userlist->username ?>'><? echo $userlist->username . "</option>";
}
?>
</select>
<? }; ?></td>
<td width="4%" colspan="2" rowspan="8"> </td>
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
<tr>
<td>Enhanced Search:</td>
<td><label>
<input name="search" type="text" id="search" size="35">
</label></td>
</tr>
<tr>
<td colspan="2"><p></p>
</td>
</tr>
<tr>
<td colspan="2"> </td>
</tr>
<tr>
<td>Date:</td>
<td><a href="javascript:NewCal('date_now','mmddyyyy')"><img src="images/cal.gif" width="16" height="16" /></a>
<input type="text" name="date_now" id="date_now" readonly="readonly"/>
to
<a href="javascript:NewCal('date_then','mmddyyyy')"><img src="images/cal.gif" width="16" height="16" /></a>
<input type="text" name="date_then" id="date_then" readonly="readonly" /></td>
</tr>
<tr>
<td colspan="2"> </td>
</tr>
<tr>
<td> </td>
<td><label>
<input type="submit" name="Submit" id="Submit" value="Submit">
</label></td>
</tr>
</table>
</form>
From my understanding, you are having an issue with the following query:
SELECT * FROM leads WHERE username = '$usersearch' AND company LIKE '%$search%' OR phone LIKE '%$search%' OR contact LIKE '%$search%' OR result LIKE '%$search%' AND date BETWEEN '$date_now' AND '$date_then'
Are you getting any errors from this query given by the mysql_error() function? Have you tried running this query manually through the command-line to see if it is actually returning what it should be returning? Have you tried echoing out the query with the variables to see if the query contains that variables and formatting that you think it does?
Try these first and see what you can work out.
It seems if I break the query down it works.
example
SELECT * FROM leads WHERE date BETWEEN '$date_now' AND '$date_then'
and it worked
SELECT * FROM leads WHERE username = '$usersearch'
and it worked
SELECT * FROM leads WHERE company LIKE '%$search%' OR phone LIKE '%$search%' OR contact LIKE '%$search%' OR result LIKE '%$search%'
and it worked
Perhaps the query is having a problem switching between AND's and OR's.. perhaps it is not interpeting it the same way as I intend.
My goal is to have 1 textbox which searches all fields except for username and date... then I have a username box (for those with correct access) and 2 date boxes.
Is there a better way I should be writing the SQL query?
SELECT * FROM leads WHERE (username = '$usersearch') AND (company LIKE '%$search%' OR phone LIKE '%$search%' OR contact LIKE '%$search%' OR result LIKE '%$search%') AND (date BETWEEN '$date_now' AND '$date_then' )
(Also, I did change the variables for actual words)
