Jump to content

Problem, mysql_num_rows(): supplied argument is not a valid MySQL...


Kevsgreat

Recommended Posts

Hi guys, I'm new to all this php malarky and I've hit a problem. I had some code that was working fine until I wanted to add pagination to the page.

 

The pagination script is a fairly well known one I picked up online, and when I tried to alter the script a little it went off on one with an error of:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/sites/xxx.co.uk/public_html/test/data-pagination.php on line 39

 

This is my code anyway, can anyone help?

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link href="../media/pagination.css" rel="stylesheet" type="text/css" />
</head>

<body>
<?php

include('../config.php');	// include your code to connect to DB.

$tbl_name="gigs_list2";		//your table name
// How many adjacent pages should be shown on each side?
$adjacents = 1;

/* 
   First get total number of rows in data table. 
   If you have a WHERE clause in your query, make sure you mirror it here.
*/
$today = date('y-m-d');
$query = "SELECT COUNT(*) as num FROM $tbl_name WHERE gig_date >= '$today' ORDER BY gig_date";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];

/* Setup vars for query. */
$targetpage = "data-pagination.php"; 	//your file name  (the name of this file)
$limit = 5; 								//how many items to show per page
$page = $_GET['page'];
if($page) 
	$start = ($page - 1) * $limit; 			//first item to display on this page
else
	$start = 0;								//if no page var is given, set start to 0

/* Get data. */
$sql = "SELECT FROM $tbl_name WHERE gig_date >='$today' ORDER BY gig_date LIMIT $start, $limit";
$result = mysql_query($sql);
$num = mysql_num_rows($result);

/* Setup page vars for display. */
if ($page == 0) $page = 1;					//if no page var is given, default to 1.
$prev = $page - 1;							//previous page is page - 1
$next = $page + 1;							//next page is page + 1
$lastpage = ceil($total_pages/$limit);		//lastpage is = total pages / items per page, rounded up.
$lpm1 = $lastpage - 1;						//last page minus 1

/* 
	Now we apply our rules and draw the pagination object. 
	We're actually saving the code to a variable in case we want to draw it more than once.
*/
$pagination = "";
if($lastpage > 1)
{	
	$pagination .= "<div class=\"pagination\">";
	//previous button
	if ($page > 1) 
		$pagination.= "<a href=\"$targetpage?page=$prev\">« previous</a>";
	else
		$pagination.= "<span class=\"disabled\">« previous</span>";	

	//pages	
	if ($lastpage < 7 + ($adjacents * 2))	//not enough pages to bother breaking it up
	{	
		for ($counter = 1; $counter <= $lastpage; $counter++)
		{
			if ($counter == $page)
				$pagination.= "<span class=\"current\">$counter</span>";
			else
				$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
		}
	}
	elseif($lastpage > 5 + ($adjacents * 2))	//enough pages to hide some
	{
		//close to beginning; only hide later pages
		if($page < 1 + ($adjacents * 2))		
		{
			for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
			{
				if ($counter == $page)
					$pagination.= "<span class=\"current\">$counter</span>";
				else
					$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
			}
			$pagination.= "...";
			$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
			$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
		}
		//in middle; hide some front and some back
		elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
		{
			$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
			$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
			$pagination.= "...";
			for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
			{
				if ($counter == $page)
					$pagination.= "<span class=\"current\">$counter</span>";
				else
					$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
			}
			$pagination.= "...";
			$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
			$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
		}
		//close to end; only hide early pages
		else
		{
			$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
			$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
			$pagination.= "...";
			for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
			{
				if ($counter == $page)
					$pagination.= "<span class=\"current\">$counter</span>";
				else
					$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
			}
		}
	}

	//next button
	if ($page < $counter - 1) 
		$pagination.= "<a href=\"$targetpage?page=$next\">next »</a>";
	else
		$pagination.= "<span class=\"disabled\">next »</span>";
	$pagination.= "</div>\n";		
}
?>
<?php
	$i=0;
	while ($i < $num) {
	// get the stuff from database
	$band_name=mysql_result($result,$i,"band_name");
	$venue_name=mysql_result($result,$i,"venue_name");
	$g_date=mysql_result($result,$i,"gig_date");
	$g_time=mysql_result($result,$i,"gig_time");
	$g_price=mysql_result($result,$i,"gig_price");

	// make the dates make sense
	$gig_date=date('l jS F, Y',strtotime($g_date));
	$gig_time=date('g:ia',strtotime($g_time));

	// 	replace £0 with Free
	if ($g_price=="0") {
		$gig_price="Free";
	} else {
		$gig_price='£'.$g_price;
	}


	echo "<p>hello</p>
	<b>$band_name</b>
		 <ul>
		 <li>Venue: $venue_name</li><li>Date: $gig_date</li><li>Time: $gig_time</li><li>Entry: $gig_price</li>
		 </ul>
		 <hr />";

	$i++;
	}
?>

<?=$pagination?>
</body>
</html>

This is a common problem you could find a solution for by doing a forum search. That said, your query was probably invalid. add "or die(mysql_error());" after your query function. If you can't figure out what I mean, a forum search will find a post that has a more detailed solution.

in the following query

$sql = "SELECT FROM $tbl_name WHERE gig_date >='$today' ORDER BY gig_date LIMIT $start, $limit";

 

you arent selecting everything, you have to select something, IE

SELECT *

SELECT column

SELECT col1, col2, col4

 

etc.

 

in the following query

$sql = "SELECT FROM $tbl_name WHERE gig_date >='$today' ORDER BY gig_date LIMIT $start, $limit";

 

you arent selecting everything, you have to select something, IE

SELECT *

SELECT column

SELECT col1, col2, col4

 

etc.

Sorted! Thanks for your help!

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.