Page 1 of 1
One last question to finish up my search
Posted: Sun Jan 27, 2008 9:48 pm
by micknc
I am pretty new to php and have been developing a search page for our company over the weekend. It searches MySql and is doing a good job but I need to add one more feature.
I know there is lots of information about pagination and sessions out there and I have tried about 10 tutorials and read countless threads but I just can't get it right.
Here is the problem:
I am using a form to post the variable to a results page. On the form page there are three fields you can choose from so my query in the results page looks like this:
Code: Select all
SELECT * FROM SOHEAD WHERE UPPER($field)='$find' AND STATUS IN('P','V') ORDER BY SO_NO DESC
On most of the tutorials I have tried the first page of the pagination will turn up fine but the second will give me an error stating that there is problem near the UPPER($field)='$find' text.
I have tried different methods to "re-post" the field and find fields but nothing seems to work. I can get the search and pagination to work if I get rid of the drop down menu in my form and limit it to a single parameter search but I need the multiple fields.
Any ideas?
Re: One last question to finish up my search
Posted: Sun Jan 27, 2008 11:57 pm
by JAM
Post the code that you are trying when getting the error. Likely just malformed sql clause, so post some code. Pagination should not be that hard to use as youre not changing the main sql-clause itself in such way that it should break.
Re: One last question to finish up my search
Posted: Mon Jan 28, 2008 12:41 am
by Christopher
The part of your SQL that confuses me is the
UPPER($field)='$find' part. First, since UPPER() works on strings it should be
UPPER('$field'). Second, why are you comparing two PHP variables in a SQL statement?
Typically for pagination you would do something like this:
Code: Select all
SELECT * FROM SOHEAD WHERE STATUS IN('P','V') ORDER BY SO_NO DESC LIMIT $start_record, $records_per_page
Re: One last question to finish up my search
Posted: Mon Jan 28, 2008 1:03 am
by s.dot
What arborint said, plus you shouldn't need to POST.. seems to me that would just be more confusing. Is there a reason why you can't/don't want to use $_GET variables?
Re: One last question to finish up my search
Posted: Mon Jan 28, 2008 9:43 am
by micknc
The reason I was using POST is because I am new and the tutorial that I was using did it that way. Here is what I have right now:
This is the search form:
Code: Select all
<form name="search" method="post" action="oakstsearchresults.php">
<Select NAME="field">
<Option VALUE="PO_NO"><font color="ffffff" face="arial">PO Number</option>
<Option VALUE="CUST_ID"><font color="ffffff" face="arial">Bill ID</option>
<Option VALUE="SO_NO"><font color="ffffff" face="arial">SO Number</option>
</Select>
<input type="text" name="find" />
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>
Here is the results page:
Code: Select all
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Search Results</title>
</head>
<body bgcolor="#36393D" link="white" vlink="ffefab" alink="ffefab" >
<?
//This is only displayed if they have submitted the form
if ($searching =="yes")
{
?>
<table width='700' align='center'><tr><td align='center' border="1" bgcolor="#D01F3C" width='700'><font color="ffffff" face="arial"><h2>Here is the list of orders we have on file for   <? echo $find;?>:</h2></td></tr></table>";
<?
//If they did not enter a search term we give them an error
if ($find == "")
{
echo "<p><font color='ffffff' face='arial'>You forgot to enter a search term";
exit;
}
// Otherwise we connect to our Database
mysql_connect("localhost", "user", "password") or die(mysql_error());
mysql_select_db("ncfarmsi_oakst") or die(mysql_error());
// We preform a bit of filtering
$find = strtoupper($find);
$find = strip_tags($find);
$find = trim ($find);
echo "<table width='650' align='center'>
<tr align='center' bgcolor='#D01F3C'>
<td width='150'><font color='ffffff' face='arial'>PO Number</td>
<td width='150'><font color='ffffff' face='arial'>Our SO Number</td>
<td width='200'><font color='ffffff' face='arial'>Scheduled Ship Date</td>
<td width='150'><font color='ffffff' face='arial'>Order Status</td>
</tr>";
//Now we search for our search term, in the field the user specified
$data = mysql_query("SELECT * FROM SOHEAD WHERE UPPER($field)='$find' AND STATUS IN('P','V') ORDER BY SO_NO DESC");
$status_codes = array(
'S' => 'Open Order',
'P' => 'Order Invoiced',
'V' => 'Order Cancelled',
);
$color1 = "#515458";
$color2 = "#86888b";
$row_count = 0;
//And we display the results
while($result = mysql_fetch_array( $data ))
{
$row_color = ($row_count % 2) ? $color1 : $color2;
echo "<tr align='center' bgcolor='$row_color'>";
?>
<td width='150'><font color="ffffff" face="arial"><?php echo $result['PO_NO'];?></td>
<td width='150'><font color="ffffff" face="arial"><?php echo "<a target=_top href=oakstsoviewpre.php?id=".$result['SO_NO'].">".$result['SO_NO']."</a>";?>
<td width='200'><font color="ffffff" face="arial"><?php echo $result['REQRD_DATE'];?></td>
<td width='150'><font color="ffffff" face="arial"><?php echo $status_codes[$result['STATUS']];?></td>
</td>
</tr>
<?php
// Add 1 to the row count
$row_count++;
}
//This counts the number or results - and if there wasn't any it gives them a little message explaining that
$anymatches=mysql_num_rows($data);
if ($anymatches == 0)
{
echo "<font color='ffffff' face='arial'>Sorry, but we can not find an entry to match your query<br><br>";
}
}
?>
</td></tr></table>
</body>
</html>
I am not experienced enough to know if that is a mess or not but I started with a tutorial on about.com and then by reading and asking questions I have adapted what I want it to look like. The problem is the database will return hundreds of rows if you were to search bill id and you were a broker of ours so i need to cut it down to little chunks. I am open to any suggestions that you guys have.
Re: One last question to finish up my search
Posted: Mon Jan 28, 2008 1:28 pm
by micknc
So I have had to change the code. Because of an unrelated issue I am now using $id=$_GET['id']; to pass the variable. I needed to search several tables at once so it was easier for me at my stage to use this.
That makes the pagination problem a lot easier to solve. I will let you know if I hit another wall.