Search Form

[Zergman]

Its a very simple search form.  Just want to allow the user to search by date which also has their username in there from the session variable.

 

This is what I have so far

SELECT *
FROM `data`
WHERE uname = colname AND `data`.tdate LIKE coldate
ORDER BY id ASC

 

Search works when they set a specific search, like "2008-07-26" but I want them to be able to do a wild card search like "2008-07-*"

 

Not sure how to make that happen.

[toivo]

It is not clear which variables in your sample are columns in your table 'data'.  You need to have variables, too.

 

I would use something like this, after setting the variables with values to be searched, depending on the format of the date:

 

$username = 'whatever';
$partial_date = substr($date, 0, 7);
$q = "SELECT * FROM `data` ";
$q .= "WHERE uname = '" .$username . "' AND tdate LIKE '". $partial_date . "%' ";
$q .= "ORDER BY id";

 

 

[Zergman]

I have colname set to a session variable in the column uname.

 

coldate is set to search the tdate field where the dates are stored.

 

The date column is formatted like yyyy-mm-dd.  I just let mysql format it.

 

These are how I have my 2 variables setup right now.

$colname_rssearchresults = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_rssearchresults = $_SESSION['MM_Username'];
}
$coldate_rssearchresults = "-1";
if (isset($_GET['datefield'])) {
  $coldate_rssearchresults = $_GET['datefield'];
}

[Zergman]

Okay, so i tried changing my variable to

$searchterm = $_POST['datefield'];
trim ($searchterm);

 

And my sql to

SELECT * FROM `data` WHERE uname = %s AND `data`.tdate LIKE '%$searchterm%' ORDER BY id ASC

 

But this just displays everything in the database table without using the search form

 

Here's my search form

<form id="form1" name="form1" method="POST" action="stats_search.php">
<input name="datefield" type="text" class="inputbox" id="datefield" />
<span class="style1">* yyyy-mm-dd format</span><br />
  <input name="button" type="submit" class="inputbox" id="button" value="Search" />
  <input name="button2" type="button" class="inputbox" id="button2" onclick="MM_goToURL('parent','stats.php');return document.MM_returnValue" value="Back" />
</form>

[Attila]

I would suggest to make the form a PHP code and have it echo to the screen and chance your action to this:

 

 action=\"$_SERVER[php_SELF]\">

 

Here is an example of a form I use on my website to search through the database and display what it found on the same page:

 

<php
     $top_form = "<form method=\"post\" action=\"{$_SERVER['PHP_SELF']}\">
     <table border=\"2\" cellpadding=\"2\" cellspacing=\"2\" bordercolor=\"#333333\" bgcolor=\"#CCCCCC\">
        <tr align=\"center\" valign=\"middle\">
          <td colspan=\"1\">";
   $bottom_form = "</td>
        </tr>
      <tr>
          <td>Enter your search word, name, or phrase</td>
          <td><input type=\"text\" value=\"{$_POST['search']}\" name=\"search\" /></td>   

        </tr>
        <tr align=\"center\" valign=\"middle\">
          <td colspan=\"2\">
        <input type=\"hidden\" name=\"op\" value=\"ds\">
        <input type=\"submit\" name=\"Submit\" value=\"Search\"></td>
        </tr>
      </table>
     </form>";
     
if ($_POST['op'] != "ds") 
   { 
      echo "$top_form"."$bottom_form";
   } 
   else
      {
   echo "$top_form"."$bottom_form";
   $item = $_POST['search'];
   $sql = "SELECT * FROM ddoitems WHERE ItemDescription LIKE '%$item%' OR ItemName LIKE '%$item%' OR ItemType LIKE '%$item%' OR QuestName LiKE '%$item%' ORDER BY ItemName ASC";
  echo '</table>'; 
  echo '<table>';
  include ('includes/tindex.php');
  database_connect();
  tables($sql);
  echo '</table>';
  }
php>

[Zergman]

Thanks a lot Attila, but i'm just too much of a newb and wouldn't know how to implement that into my page

 

I'm guessing my problem has something too do with the variable im setting up.  It seems that the form will just ignore it and take my first variable only.

 

The way I had it in the first post worked, but wouldn't allow for wildcard search.

[Zergman]

Just thought, could the problems be caused by the mysql column format?  its set to DATE

[Attila]

OK not sure how exact my programming is here because I am a noob as well.  So here is some code for you to copy and try out.

 

first thing is the function with this code you will need to make a function I call it AllFunctions.php and I save it in an includes directory.

This is what the AllFunctions.php code should look like for this case:  You will need to edit this just a touch.

 

<?
function database_connect()
{
$dbhost     = "your database host address";	// Database host
$dbname     = "database name";		// Database name
$dbusername = "database user name";	// Database user name
$dbuserpw   = "the user name password";	// Database password

mysql_pconnect($dbhost, $dbusername, $dbuserpw);  	//Connects to the mysql database area
$result = mysql_select_db($dbname);   //Connects to the specific database and saves the result as result.
if (!$result)
return false;  //Connected
else
return true;  //not connected
}
?>

 

Now for your form page it should like like this and once again you will need to edit it just a touch.

 

<?
// start the session 
session_start();
ob_start();
include ('includes/AllFunctions.php');
header("Cache-control: private"); //IE 6 Fix 
?>
<title>If you want one</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<body>

<table width="100%" border="1" cellspacing="1" cellpadding="1">
  <tr> 
  This is where I keep my header information.  The banner across the top of the screen
  </tr>
  <tr> 
    <td width="100%" height="600" align="center" valign="middle">
<? //Create Form

$top_form = "<form method=\"post\" action=\"$_SERVER[php_SELF]\"> <br>
<table width=\"45%\" border=\"2\" cellspacing=\"2\" cellpadding=\"2\" bordercolor=\"#000000\" bgcolor=\"#CCCCCC\">
  <tr>
    <td colspan=\"2\">";

$form_block = "</td>
  </tr>
  <tr>
    <td><strong>Day of the week:</strong></td>
    <td><input type=\"text\" name=\"day\" value=\"$_POST[day]\" size=30></td>
  </tr>
  <tr>
    <td><strong>Month:</strong></td>
    <td><input type=\"text\" name=\"month\" value=\"$_POST[month]\" size=30></td>
  </tr>
  <tr>
    <td><strong>Year:</strong></td>
    <td><input type=\"text\" name=\"year\" value=\"$_POST[year]\" size=30></td>
  </tr>
  <tr align=\"center\" valign=\"middle\">
    <td colspan=\"2\">
      <input type=\"submit\" name=\"submit\" value=\"Send this form\">
      <input type=\"hidden\" name=\"op\" value=\"ds\">
      <input type=\"reset\" name=\"reset\" value=\"Reset\">
    </td>
  </tr>
</table>
</form>"; 
if ($_POST[op] != "ds") 
{ 
	// they need to see the form 
	echo "$top_form"."$form_block"; 
} 
else if ($_POST[op] == "ds") 
	{ 	

		//Connect to the database all fields are good so far				
		database_connect();

		if ($complete != "no") 
			{ 

			//lets re put the form up again so they do not have to go back a page to search again they can just do another search if needed
			echo "$top_form"."$form_block"; 


			// Register session key with the value 
			$_SESSION['day'] = $day; 
			$_SESSION['month'] = $month; 
			$_SESSION['year'] = $year;

			//Put the variables together.
			$mydate = "$day"." $month"." year";          //you might have to format this string depending on how you have the date saved?  
                                                                                                             //IE:$mydate = "$day"."-"." $month"."-"." year";
                                                                                                             //IE:$mydate = "$day"."/"." $month"."/"." year";

			//create the select query
			$sql = 'SELECT * FROM `your table name` WHERE `the field name date` LIKE \'%mydate%\' ORDER BY ItemName ASC';
			$result = mysql_query($sql) or die(mysql_error());
			while($row = mysql_fetch_array($result)) {
				//echo "<pre>";  These three lines are error checking code if you are not seing what is being put out.
				//print_r($row);
				//echo "</pre>";
				//lets put the data retreived on the page  I am not sure how much info you have here you will need to adjust this.
				//Just remember if you delete any of this stuff ensure you finish of your table or it will look funny.
				echo '<tr><div align="center">';
        			                                 echo '<td>'.$row['the field name date'].'</td>';
				echo '<td>'.$row['any other info so field name'].'</td>';
				echo '<td>'.$row['any other info so field name'].'</td>';
				echo '<td>'.$row['any other info so field name'].'</td>';
				echo '<td>'.$row['any other info so field name'].'</td>';
				echo '<td>'.$row['any other info so field name'].'</td>';
        			                                 echo '</div> </tr>';
				}  //end for the while loop
			}  //end for the if
?>
</td>
</tr>
</table>
</body>

[Zergman]

Awesome stuff but this is where my newbie shows through

 

Did the page as you suggested but im getting this.

 

Parse error: syntax error, unexpected $end in /path/to/page.php

 

Says the line is the very last line of my page right after the </html> tag

[Attila]

I missed an end for an IF statement.  find the code that looks like this:  and look for my comment that says I missed ending this here.  Add that and that should fix your problem

 

 

				echo '<td>'.$row['any other info so field name'].'</td>';
				echo '<td>'.$row['any other info so field name'].'</td>';
				echo '<td>'.$row['any other info so field name'].'</td>';
				echo '<td>'.$row['any other info so field name'].'</td>';
				echo '<td>'.$row['any other info so field name'].'</td>';
        			                                 echo '</div> </tr>';
				}  //end for the while loop
			}  //end for the if
		} //I missed ending this here...
?>

 

 

[Zergman]

So I got most everything working with my search page, now a new issue.

 

I got some columns that have null in them since not every field in my insert form needs to be filled in.  Problem is that when I do a search for 1 value, entries that have null in them are not listed in the results.

 

Example

 

2 columns in my database, PhoneNumber and Notes.  PhoneNumber has a value, Notes is NULL.  If I do a search for a phone number, nothing shows up.  If I put ANY value in Notes, it shows.

 

Why is this?

[Attila]

I had a similiar instance where one item was displayed and the next wasn't and that was a problem with my coding.  I was having PHP echo out my fields and I missed a > to close off a <div>.

 

If you can post your code so we can see if it is a simple error and provide us the link to see what it is doing as well.  Another thing you can do is the first line after the while loop put this code to ensure it is being pulled from the database and what is actually being pulled as well:

echo "<pre>";
print_r($row);
echo "</pre>";