Page 1 of 1

Coding help on search and display pages

Posted: Fri Jun 14, 2002 8:25 am
by reeljustice
I know many of you spend a great deal of time helping newbies. I have been reading "tutorials" for weeks now, off and on, trying to find out how to (1) query a db table(s) and display the results in manner remotely acceptable to users.

I have read "previous and next" tutorials as well as various and sundry "search" tutorials, and, read the "understanding search" thread on another site. I believe my basic problem is I don't know enough about "regular expressions" so I don't really know exactly what is going wrong when it does go wrong.

I would really appreciate your help. If you are able to help me, I will do my best to give the tutorial, with my experiences, back to the community. If you are still willing to help, please read on.

I have a db table named "coords" with data in the following schema:



PHP:--------------------------------------------------------------------------------
#
# Table structure for table `coords`
#

CREATE TABLE coords (
coord_id int(10) NOT NULL default '0',
st varchar(6) NOT NULL default '',
type varchar(100) default NULL,
coord_area varchar(255) default NULL,
area varchar(100) NOT NULL default '',
name varchar(100) NOT NULL default '',
material varchar(100) default NULL,
latitude varchar(11) NOT NULL default '',
longitude varchar(11) NOT NULL default '',
description varchar(255) default NULL,
depth varchar(5) default NULL,
from1 varchar(255) default NULL,
heading1 varchar(5) default NULL,
distance1 varchar(5) default NULL,
from2 varchar(255) default NULL,
heading2 varchar(5) default NULL,
distance2 varchar(5) default NULL,
comments longtext,
DATE date NOT NULL default '0000-00-00',
PRIMARY KEY (coord_id)
) TYPE=MyISAM;

--------------------------------------------------------------------------------


I would like to create a "search page" similar to http://www.rodnreel.com/gps/GPSSearch.asp , a site which is done in ASP, where the user could search for different variables, such as
PHP:--------------------------------------------------------------------------------
SELECT * FROM coords WHERE coords.st LIKE $st AND coords.type LIKE $type

--------------------------------------------------------------------------------


Where the $st and $type are provide from a drop-down list of variables from an HTML form, then have those results appear on gps_search_results.php page. I've created a very crude display page at http://www.coastaloutdoors.com/fishing_log/display.php (Note: This one calls "coords" which only has a little over 100 records for testing. This one has over 15,000 records http://www.coastaloutdoors.com/fishg_lo ... y_many.php ) As you can see, the display is horrible and does not display all the results from the db. I have pasted both of those files below for your review.

I have attempted to utilize one of the "Previous/Next" tutorials. I've pasted that effort also http://www.coastaloutdoors.com/fishing_ ... lay_al.php It shows a wonderful "Previous and Next" link, but, not data. I've fooled around with the script that lloydie-t was working with but can't get it to work, except to show your "Previous/Next" links. http://www.coastaloutdoors.com/fishing_ ... die_al.php

I would really appreciate your help.
Joel

test_display.php

PHP:--------------------------------------------------------------------------------

<!doctype html public "-//W3C//DTD HTML 4.0 //EN">
<html>


<!---Head--->
<head>
<title>Test display</title>
</head>

none
<!---Body--->
<?
//mysql_connect ("localhost","username","password");
//mysql_select_db ("coords");
include('include/dbconnect.inc');

$sql = "select * from coords";
$result = mysql_query ($sql);

while ($row = mysql_fetch_array($result))
{
$field1= $row["latitude"];
$field2= $row["longitude"];

echo "$field1<br>";
echo "$field2<p>";

}
?>

</body>
</html>
--------------------------------------------------------------------------------


display.php

PHP:--------------------------------------------------------------------------------

<?
include('include/dbconnect.inc');

$query="SELECT * FROM coords";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";

?>
<table border="1" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">State</font></th>
<th><font face="Arial, Helvetica, sans-serif">Type</font></th>
<th><font face="Arial, Helvetica, sans-serif">Coord Area</font></th>
<th><font face="Arial, Helvetica, sans-serif">Area</font></th>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Material</font></th>
<th><font face="Arial, Helvetica, sans-serif">Latitude</font></th>
<th><font face="Arial, Helvetica, sans-serif">Longitude</font></th>
<th><font face="Arial, Helvetica, sans-serif">Description</font></th>
<th><font face="Arial, Helvetica, sans-serif">Depth</font></th>
<th><font face="Arial, Helvetica, sans-serif">From1</font></th>
<th><font face="Arial, Helvetica, sans-serif">Heading1</font></th>
<th><font face="Arial, Helvetica, sans-serif">Distance1</font></th>
<th><font face="Arial, Helvetica, sans-serif">From2</font></th>
<th><font face="Arial, Helvetica, sans-serif">Heading2</font></th>
<th><font face="Arial, Helvetica, sans-serif">Distance2</font></th>
<th><font face="Arial, Helvetica, sans-serif">Comments</font></th>
<th><font face="Arial, Helvetica, sans-serif">Date</font></th>
</tr>

<?
$i=0;
while ($i < $num) {
$st=mysql_result($result,$i,"st");
$type=mysql_result($result,$i,"type");
$coord_area=mysql_result($result,$i,"coord_area");
$area=mysql_result($result,$i,"area");
$name=mysql_result($result,$i,"name");
$material=mysql_result($result,$i,"material");
$latitude=mysql_result($result,$i,"latitude");
$longitude=mysql_result($result,$i,"longitude");
$description=mysql_result($result,$i,"description");
$depth=mysql_result($result,$i,"depth");
$from1=mysql_result($result,$i,"from1");
$heading1=mysql_result($result,$i,"heading1");
$distance1=mysql_result($result,$i,"distance1");
$fFrom2=mysql_result($result,$i,"fFrom2");
$heading2=mysql_result($result,$i,"heading2");
$distance2=mysql_result($result,$i,"distance2");
$comments=mysql_result($result,$i,"comments");
$date=mysql_result($result,$i,"date");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$st"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$type"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$coord_area"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$area"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$name"; ?>"></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$material"; ?>"></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$latitude"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$longitude"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$description"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$depth"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$from1"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$heading1"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$distance1"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$fFrom2"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$heading2"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$distance2"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$comments"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$date"; ?></font></td>
</tr>
<?
++$i;
}
echo "</table>";


?>

--------------------------------------------------------------------------------


display_lloydie_al.php

PHP:--------------------------------------------------------------------------------

<table>
<?php

include('include/dbconnect.inc');
//$connection = @mysql_connect($host, $user, $pass) or die ("Unable to connect to database");
//mysql_select_db($db) or die ("Unable to select database: $db ");

$query = "select * from coords";

$queryResult = mysql_query($query);

$i=0;

while($row =mysql_Fetch_array($queryResult))
{

if (is_int($i/2))
{

$bgcolor = "#cccccc";

}

else
{

$bgcolor = "#eeeeee";

}

print "<tr>";
print "<td bgcolor=\"$bgcolor\">$row[st]</td>";
print "<td bgcolor=\"$bgcolor\">$row[type]</td>";
print "</tr>";

$i++;
}

?>



<?php
include('include/dbconnect.inc');

$perPage=1;
$query=$HTTP_GET_VARS['q'];
$start=$HTTP_GET_VARS['s'];
$totalRows=$HTTP_GET_VARS['t'];
$PHP_SELF=$HTTP_SERVER_VARS['PHP_SELF'];
if(!$totalRows) {
$results=mysql_fetch_row(mysql_query("select count(*) from coords"));
$totalRows=$results[0];
//echo $totalRows;
}

if (!$start) $start=0;

$result=mysql_query("select * from coords limit $start,$perPage");

while ($data=mysql_fetch_assoc($result)) {
echo $data['a']."<br>";
}

$startP=$start-$perPage;
echo ($startP>=0)? "<a href='$PHP_SELF?q=".urlencode($query)."&t=$totalRows&s=$startP'>PREV</a> " : "PREV ";

$startN=$start+$perPage;
echo ($startN<$totalRows) ? "<a href='$PHP_SELF?q=".urlencode($query)."&t=$totalRows&s=$startN'>NEXT</a> " : "NEXT ";
?>