listing help!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sirTemplar
Forum Commoner
Posts: 65
Joined: Wed Dec 18, 2002 1:57 am

listing help!

Post by sirTemplar »

i tried to use the code proposed by data. it seemed to work but

1) when you press the next page or page no. after the search, it will show the first ten data on the database and not of the search. then...

2) it will calculate the pages needed for the entire database and not only for the search! but whatever page you click on, it will still give you the first ten data on the database!

this is the code right now. can anyone help! please! :(
if ($CNOMEN == "")
{$CNOMEN = '%';}

if ($NOMRL == "")
{$NOMRL = '%';}

if ($PROVRELCD == "")
{$PROVRELCD = '%';}

if ($NATDAT1 == "")
{$NATDAT1 = '%';}

if ($PROFTDAT1 == "")
{$PROFTDAT1 = '%';}

if ($PROFSDAT == "")
{$PROFSDAT = '%';}

if ($SACDAT1 == "")
{$SACDAT1 = '%';}

if ($MORTDAT1 == "")
{$MORTDAT1 = '%';}

// Change this to fit your database

$result = mysql_query ("SELECT * FROM friarsofmconv
WHERE CNOMEN LIKE '%$CNOMEN%'
AND NOMRL LIKE '%$NOMRL%'
AND PROVRELCD LIKE '%$PROVRELCD%'
AND NATDAT1 LIKE '%$NATDAT1%'
AND PROFTDAT1 LIKE '%$PROFTDAT1%'
AND PROFSDAT1 LIKE '%$PROFSDAT1%'
AND SACDAT1 LIKE '%$SACDAT1%'
AND MORTDAT1 LIKE '%$MORTDAT1%'
AND SDATFIN1 = ' '
ORDER BY CNOMEN ASC, NOMRL
LIMIT 10
",$conn);

$rpp = 10; //results per page
$post_start = (($page*$rpp)-($rpp-1)); //work out which row to start with
$prev_page = $page-1; //next page number
$next_page = $page+1; //prev page number


$sql = ("SELECT * FROM friarsofmconv id>=$post_start and id<$post_start+$rpp order by post_no");

$numsql = ("SELECT * FROM friarsofmconv
WHERE CNOMEN LIKE '%$CNOMEN%'
AND NOMRL LIKE '%$NOMRL%'
AND PROVRELCD LIKE '%$PROVRELCD%'
AND NATDAT1 LIKE '%$NATDAT1%'
AND PROFTDAT1 LIKE '%$PROFTDAT1%'
AND PROFSDAT1 LIKE '%$PROFSDAT1%'
AND SACDAT1 LIKE '%$SACDAT1%'
AND MORTDAT1 LIKE '%$MORTDAT1%'
AND SDATFIN1 = ' '
ORDER BY CNOMEN ASC, NOMRL
");
$numquery = mysql_query( $numsql );
$num_rows = mysql_num_rows( $numquery ); //how many results?

$pages = ceil($num_rows/$rpp); //gives how many pages you will have

if ($row = mysql_fetch_array($result)) {

do {
echo "<table border=0 cellpadding=1 cellspacing=0 style=border-collapse: collapse bordercolor=#111111 width=100%>";
echo "<tr><td bgcolor=#E1FFFF align=left width=20% valign=top><b><font face=Verdana size=2 color=#0000FF>Full Name: </font></b></td>
<td bgcolor=#E1FFFF align=left width=80% valign=top><b><font face=Verdana size=2 color=#800000>{$row['CNOMEN']},</b> {$row['NOMRL']} ({$row['NOMBL']})
<b><font face=Verdana size=2 color=#0000FF></font></b>
<font face=Verdana size=2 color=#000000></td>
<tr><td align=left width=20% valign=top><b><font face=Verdana size=2 color=#0000FF>Birthplace/Date: </font></b></td>
<td align=left width=80% valign=top><font face=Verdana size=2 color=#000000>{$row['NATCITTA']} ({$row['NATPROV_S']} {$row['NATNAZEN']}) <b><font face=Verdana size=2 color=#008000>-</font></b> {$row['NATDAT1']}</td>
<tr><td align=left width=20% valign=top><b><font face=Verdana size=2 color=#0000FF>Jurisdiction: </font></b></td>
<td align=left width=80% valign=top><font face=Verdana size=2 color=#000000>{$row['PROVRELCD']} = {$row['PROVEN']}</td>
<tr><td align=left width=20% valign=top><b><font face=Verdana size=2 color=#0000FF></font></b></td>
<td align=left width=80% valign=top><font face=Verdana size=2 color=#000000>{$row['SERVSTAT']} / {$row['STUDIES_C']}</td>
<tr><td align=left width=20% valign=top><b><font face=Verdana size=2 color=#0000FF>Temporary Vows: </font></b></td>
<td align=left width=80% valign=top><font face=Verdana size=2 color=#000000>{$row['PROFTDAT1']}</td></table>";

if (isset($row["PROFSDAT1"]) && strcasecmp($row["PROFSDAT1"],""))
{
echo "<table border=0 cellpadding=1 cellspacing=0 style=border-collapse: collapse bordercolor=#111111 width=100%>
<tr><td align=left width=20% valign=top><b><font face=Verdana size=2 color=#0000FF>Solemn Vows: </font></b></td>
<td align=left width=80% valign=top><font face=Verdana size=2 color=#000000>{$row['PROFSDAT1']}</td></table>";
}

if (isset($row["SACDAT1"]) && strcasecmp($row["SACDAT1"],""))
{
echo "<table border=0 cellpadding=1 cellspacing=0 style=border-collapse: collapse bordercolor=#111111 width=100%>
<tr><td align=left width=20% valign=top><b><font face=Verdana size=2 color=#0000FF>Ordination: </font></b></td>
<td align=left width=80% valign=top><font face=Verdana size=2 color=#000000>{$row['SACDAT1']}</td></table>";
}

if (isset($row["EPISDAT1"]) && strcasecmp($row["EPISDAT1"],""))
{
echo "<table border=0 cellpadding=1 cellspacing=0 style=border-collapse: collapse bordercolor=#111111 width=100%>
<tr><td align=left width=30% valign=top><b><font face=Verdana size=2 color=#FF0000>Episcopal Consecration: </font></b></td>
<td align=left width=70% valign=top><font face=Verdana size=2 color=#000000>{$row['EPISDAT1']}</td></table>";
}

if (isset($row["EMAIL"]) && strcasecmp($row["EMAIL"],""))
{
echo "<table border=0 cellpadding=1 cellspacing=0 style=border-collapse: collapse bordercolor=#111111 width=100%>
<tr><td align=left width=20% valign=top><b><font face=Verdana size=2 color=#0000FF>Email Add.: </font></b></td>
<td align=left width=80% valign=top><font face=Verdana size=2 color=#000000><a href='mailto:{$row['EMAIL']}'>{$row['EMAIL']}</td></table>";
}

if (isset($row["URL"]) && strcasecmp($row["URL"],""))
{
echo "<table border=0 cellpadding=1 cellspacing=0 style=border-collapse: collapse bordercolor=#111111 width=100%>
<tr><td align=left width=20% valign=top><b><font face=Verdana size=2 color=#0000FF>URL: </font></b></td>
<td align=left width=80% valign=top><font face=Verdana size=2 color=#000000><a target=_blank href='{$row['URL']}'>{$row['URL']}</a></td></table>";
}

if (isset($row["MORTDAT1"]) && strcasecmp($row["MORTDAT1"],""))
{
echo "<table border=0 cellpadding=1 cellspacing=0 style=border-collapse: collapse bordercolor=#111111 width=100%>
<tr><td align=left width=30% valign=top><b><font face=Verdana size=2 color=#800080>Date Deceased/Place: </font></b></td>
<td align=left width=70% valign=top><font face=Verdana size=2 color=#000000>{$row['MORTDAT1']} / {$row['MORTLOC']}</td></table>";
}
echo ("<p>");
echo ("<p>");
} while($row = mysql_fetch_array($result));
} else {print "Sorry, no records were found!";}
?>
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

SELECT STATEMENT

Post by AVATAr »

Here:

Code: Select all

<?php
$result = mysql_query ("SELECT * FROM friarsofmconv 
WHERE CNOMEN LIKE '%$CNOMEN%' 
...
ORDER BY CNOMEN ASC, NOMRL 
LIMIT 10 
...");
?>
In the Select statement the LIMIT 10 -> in the last line limits the search to 10 results
sirTemplar
Forum Commoner
Posts: 65
Joined: Wed Dec 18, 2002 1:57 am

listing

Post by sirTemplar »

i tried to put what you said, it gave me an error! :(
Warning: Supplied argument is not a valid MySQL result resource in /var/www/html/ofmconv/friarsdbase/search.php on line 103
Sorry, no records were found!
--------------------------------------------------------------------------------

Currently viewing page of 2
< Prev [ 1 2 ] Next >
help! but thanks anyway.
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

?

Post by AVATAr »

that error shows up before you make the changes?
sirTemplar
Forum Commoner
Posts: 65
Joined: Wed Dec 18, 2002 1:57 am

error!

Post by sirTemplar »

yup! :( . any suggestion?
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

line 103

Post by AVATAr »

please copy the line 103
sirTemplar
Forum Commoner
Posts: 65
Joined: Wed Dec 18, 2002 1:57 am

Re: line 103

Post by sirTemplar »

AVATAr wrote:please copy the line 103
now it's showing:
Warning: Supplied argument is not a valid MySQL result resource in /var/www/html/ofmconv/friarsdbase/search.php on line 100
line 100 is:
$num_rows = mysql_num_rows( $numquery ); //how many results?
thanks for the patience. wait for your help! :?
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

$numquery

Post by AVATAr »

$numquery is the result of mysql_query?
does that mysql_query return something?

check them out
sirTemplar
Forum Commoner
Posts: 65
Joined: Wed Dec 18, 2002 1:57 am

Re: $numquery

Post by sirTemplar »

AVATAr wrote:$numquery is the result of mysql_query?
does that mysql_query return something?

check them out
i still cannot figure out what is wrong with the code :cry: . maybe you could just help me out with this:

could you give me a code to put at the end of the page to tell how may records were found during the searc? something like:

15 out of 5000 records found!

:wink:
Post Reply