One last question to finish up my search

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

One last question to finish up my search

Post 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?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Re: One last question to finish up my search

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: One last question to finish up my search

Post 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
(#10850)
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: One last question to finish up my search

Post 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?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: One last question to finish up my search

Post 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 &nbsp <? 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.
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: One last question to finish up my search

Post 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.
Post Reply