[RESOLVED] Please help

[cretam]

I am trying to display the pubname but can only display the id, can anyone look at my code please.
Also what is the best pagination code to use for this page? I would like a message to display "Sorry no records found" when there isn't any matching the search.

<?php 

include("Connections/conn.php");



if (isset($_GET['by'])) {
    $sort = $_GET['by'];
    $query = "SELECT id, name FROM org WHERE name LIKE '$sort%' ORDER BY name";
	$result = mysql_query($query) or die ("$Query failed: ".mysql_error());

/*Printing results in HTML*/
print "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {


echo "<a href=\"org.php?by=".$line['id']."\">".$line['name']."</a> </br> ";
}

}

if (isset($_GET['letter'])) {
$sort = $_GET['letter'];
$query = "SELECT id, name FROM author WHERE name LIKE '$sort%' ORDER BY name";
$result = mysql_query($query) or die ("$Query failed: ".mysql_error());
/*Printing results in HTML*/
print "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {


echo "<a href=\"author.php?by=".$line['id']."\">".$line['name']."</a> </br> ";
}

}

if (isset($_GET['cat'])) {
$sort = $_GET['cat'];
$query = "SELECT pubid, categoryid FROM pubcategory WHERE categoryid LIKE $sort ";
$result = mysql_query($query) or die ("$Query failed: ".mysql_error());


/*Printing results in HTML*/
	print "<table>\n";
	while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

echo "<a href=\"cat_pub.php?by=".$line['id']."\">".$line['pubid']."</a> </br> ";
}

}

?>

[greenie2600]

None of your SQL queries are asking for a field named "pubname". In fact, the string "pubname" doesn't appear anywhere in your code. I'm not sure what you're asking. Can you please clarify?

What's a pagination code?

[cretam]

Sorry I have been playing with this code all evening and forgot to send you the updated code.

<?php 

include("Connections/conn.php");



if (isset($_GET['by'])) {
    $sort = $_GET['by'];
    $query = "SELECT id, name FROM org WHERE name LIKE '$sort%' ORDER BY name";
	$result = mysql_query($query) or die ("$Query failed: ".mysql_error());

/*Printing results in HTML*/
print "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {


echo "<a href=\"org.php?by=".$line['id']."\">".$line['name']."</a> </br> ";
}

}

if (isset($_GET['letter'])) {
$sort = $_GET['letter'];
$query = "SELECT id, name FROM author WHERE name LIKE '$sort%' ORDER BY name";
$result = mysql_query($query) or die ("$Query failed: ".mysql_error());
/*Printing results in HTML*/
print "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {


echo "<a href=\"author.php?by=".$line['id']."\">".$line['name']."</a> </br> ";
}

}

if (isset($_GET['cat'])) {
$sort = $_GET['cat'];
$query = "SELECT pubid, categoryid FROM pubcategory WHERE categoryid LIKE $sort ";
$result = mysql_query($query) or die ("$Query failed: ".mysql_error());


mysql_fetch_array($result, MYSQL_ASSOC);

$query2 = "SELECT id, pubname FROM pub WHERE id = 'pubid'";
$result2 = mysql_query($query2) or die ("$Query failed: ".mysql_error());

/*Printing results in HTML*/
	print "<table>\n";
	while ($line = mysql_fetch_array($result2, MYSQL_ASSOC)) {

echo "<a href=\"cat_pub.php?by=".$line['id']."\">".$line['pubname']."</a> </br> ";
}

}

?>

The pubcategory table is a joining table with 2 fields pubid and categoryid. When the user selects a category the categoryid is passed on but then we need to query the database to get the pubname from the pub table where the id = pubid.
I know I am close.
Pagination code is only display 10 records at a time with numbers to next pages.

[cretam]

Another friend has helped me out and managed to get this to work
Here is the code for anyone following this post.

<?php 

include("Connections/conn.php");



if (isset($_GET['by'])) {
    $sort = $_GET['by'];
    $query = "SELECT id, name FROM org WHERE name LIKE '$sort%' ORDER BY name";
	$result = mysql_query($query) or die ("$Query failed: ".mysql_error());

/*Printing results in HTML*/

  print "<table>\n";
	while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {


echo "<a href=\"org.php?by=".$line['id']."\">".$line['name']."</a> </br> ";
}

}

if (isset($_GET['letter'])) {
$sort = $_GET['letter'];
$query = "SELECT id, name FROM author WHERE name LIKE '$sort%' ORDER BY name";
$result = mysql_query($query) or die ("$Query failed: ".mysql_error());
/*Printing results in HTML*/
print "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {


echo "<a href=\"author.php?by=".$line['id']."\">".$line['name']."</a> </br> ";
}

}

if (isset($_GET['cat'])) {
$sort = $_GET['cat'];

$query = "SELECT pub.id,pub.pubname FROM pub LEFT JOIN pubcategory ON (pubcategory.pubid = pub.id) WHERE categoryid LIKE $sort ";
	$result = mysql_query($query) or die ("$Query failed: ".mysql_error());


/*Printing results in HTML*/
	print "<table>\n";
	while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {


echo "<a href=\"cat_pub.php?by=".$line['id']."\">".$line['pubname']."</a> <br> ";
	}

}


?>

[cretam]

Can anyone tell me how to add pagination to this page so it only displays 10 records at a time with link previous and next links.

[revcom]

This is what I did to break results down into pages:

Use this to determine variables:

<?

// how many rows to show per page
$rowsPerPage = 5; 
// by default we show first page 
$pageNum = 1; 
// if $_GET['page'] defined, use it as page number 
if(isset($_GET['page'])) 
{ 
    $pageNum = $_GET['page']; 
} 
// counting the offset 
$offset = ($pageNum - 1) * $rowsPerPage;
$itemcount = 0;

?>

Add this to your $query:

" LIMIT  $offset, $rowsPerPage"

Put this where you want the numbers / previous + next to appear.

This will produce Previous, next links, and also page 1 of ...:

// how many rows we have in database 

$query = "SELECT COUNT(id) AS numrows FROM author WHERE name LIKE '$sort%' ORDER BY name"; 
$result = mysql_query($query) or die ("$Query failed: ".mysql_error());

$row     = mysql_fetch_array($result, MYSQL_ASSOC); 
$numrows = $row['numrows']; 


// how many pages we have when using paging? 
$maxPage = ceil($numrows/$rowsPerPage); 

$self = $_SERVER['PHP_SELF']; 

// creating 'previous' and 'next' link 
// plus 'first page' and 'last page' link 

// print 'previous' link only if we're not 
// on page one 
if ($pageNum > 1) 
{ 
    $page = $pageNum - 1; 
    $prev = " <a href=\"$self?page=$page&id=$id\">[Prev]</a> "; 

$first = " <a href=\"$self?page=1&id=$id\">[First Page]</a> "; 
} 
else 
{ 
    $prev  = ' [Prev] ';       // we're on page one, don't enable 'previous' link 
    $first = ' [First Page] '; // nor 'first page' link 
} 

// print 'next' link only if we're not 
// on the last page 
if ($pageNum < $maxPage) 
{ 
$where1 = str_replace( " \ " , "" , $where1);
    $page = $pageNum + 1; 
    $next = " <a href=\"$self?page=$page&id=$id\">[Next]</a> "; 

$last = " <a href=\"$self?page=$maxPage&id=$id\">[Last Page]</a> "; 
} 
else 
{ 
    $next = ' [Next] ';      // we're on the last page, don't enable 'next' link 
    $last = ' [Last Page] '; // nor 'last page' link 
} 


// print the page navigation link 


echo $first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last;


?>

[cretam]

Thanks for replying Revcom. I tried what you suggested but it didn't work properly. I will keep trying though.

[revcom]

what part did not work?

error messages?

Added:

If you didnt change the last part of the code, you will need to change 'author' to 'org' to apply to your code.

[cretam]

Thanks for replying Revcom. Once I separated the authors, orgs and cats to their own pages I got it to work. Here's the finished code for the authors page.

<?php 

 include("../Connections/conn.php");



if (isset($_GET['letter'])) {
$sort = $_GET['letter'];
$query = "SELECT id, name FROM author WHERE name LIKE '$sort%' ORDER BY name";
$result = mysql_query($query) or die ("$Query failed: ".mysql_error());
$totalrows = mysql_num_rows($result);
$pagelimit = "10"; 
/*Printing results in HTML*/
if ($totalrows > 0)
{

// determine how many pages there will be by using ceil() and dividing total rows by pagelimit 
   $pagenums = ceil ($totalrows/$pagelimit); 

// if no value for page, page = 1 
    if ($_GET['page']==''){ 
        $page='1'; 
    } 
    else
      $page = $_GET['page'];
// create a start value 
    $start = ($page-1) * $pagelimit; 

// blank matches found 
echo "<b>" . $totalrows . " matches found</b><br>\n"; 

// Showing Results 1 to 10 (or if you're page limit were 15) 1 to 15, etc. 
$starting_no = $start + 1; 

if ($totalrows - $start < $pagelimit) { 
   $end_count = $totalrows; 
} elseif ($totalrows - $start >= $pagelimit) { 
   $end_count = $start + $pagelimit; 
} 


echo "Results $starting_no to $end_count shown.<br>\n"; 

// create dynamic next, previous, and page links 

/* lets say you're set to show 10 results per page and your script comes out with 12 results. 
this will allow your script to say next 2 if you're on the first page and previous 10 if you're on the second page. */ 

if ($totalrows - $end_count > $pagelimit) { 
   $var2 = $pagelimit; 
} elseif ($totalrows - $end_count <= $pagelimit) { 
   $var2 = $totalrows - $end_count; 
} 

$space = "&nbsp;"; 

$query = "SELECT id, name FROM author WHERE name LIKE '$sort%' ORDER BY name LIMIT ".$start." ,".$pagelimit; 

//echo $strquery; // check the correct query is being written 

$result = mysql_query($query) or die('Database error: '.mysql_error()); 


print "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 
  {
	echo "<a href=\"edit_author.php?by=".$line['id']."\">".$line['name']."</a> <br> ";
	 }
echo "<a href='javascript:history.go(-1)'>Previous</a>&nbsp;&nbsp;<a href=\"index.php\">Search again</a></table>"; 

// previous link (if you're on any page besides the first, create previous link) 
if ($page>1) { 
        echo "« <a href='" . $PHP_SELF . "?page=".($page-1)."&letter=$sort' class=main>Previous" . $space . $pagelimit . "</a>" . $space . $space . ""; 
    } 

// dynamic page number links 

for ($i=1; $i<=$pagenums; $i++) { 
    if ($i!=$page) { 
        echo " <a href='" . $PHP_SELF . "?page=$i&letter=$sort' class=main>$i</a>"; 
    } 
    else { 
        echo " <b>[".$i."]</b>"; 
    } 
} 


// next link (if the page you are on is less than the total amount of page numbers, there are more pages left) 

if ($page<$pagenums) { 
    echo "" . $space . $space . $space . $space . " <a href='" . $PHP_SELF . "?page=".($page+1)."&letter=$sort' class=main>Next " . $var2 . "</a> »"; 
} 

// LIMIT 0,10 will start at 0 and display 10 results 
// LIMIT 10,5 will start at 10 and display 5 results 

/* now you can do whatever you'd like with this query. it will only output ten results per page. 
change the $pagelimit variable to whatever to output more than 10 result per page. */ 
}

else

echo "No results were found. <br/><a href='javascript:history.go(-1)'>
Previous</a>&nbsp;&nbsp;<a href=\"index.php\">Search again </a>";
}

?>

Thanks for your help.