mysql_fetch_row Error, pls Help 2

WilliamMute007 · Sep 16, 2009

Hello again,

Am back again with help need

Can anybody please point me to what is wrong with the code below?

Code:

			mysql_select_db($database_Connection, $Connection);
$sql = "Select count(*) as cnt FROM directors";
$result = mysql_query($sql);

$row = mysql_fetch_row($result);
$numrows = $row['cnt'];

It is having problem with the last paragraph

Code:

$row = mysql_fetch_row($result);

I keep getting the error "Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/fhlinux132/m/miraclemakers.co.uk/user/htdocs/preview/directors.php on line 192"

Any help is much valuable.

Thank you!

jpadie · Sep 16, 2009

mysql_fetch_row fetches a numerical array. you will either have to address $row[0] or switch to using mysql_fetch_assoc() to return an associative array.

however the error message suggests that you have either not correctly connected to the DB server or your query is incorrect.

remember to use mysql_error() to debug these issues.

Code:

mysql_select_db($database_Connection, $Connection) or die(mysql_error());
$sql = "Select count(*) as cnt FROM directors";
$result = mysql_query($sql) or die (mysql_error());

i note that you have not included your database connection command. i assume that you also use mysql_error() on that.

Code:

mysql_connect($host, $user, $pass) or die (mysql_error());

WilliamMute007 · Sep 16, 2009

Hi Justin,

Thank you for your response. I have tried addressing the issues that you raised but still no luck. Here is what the code looks like currently

Code:

<?php require_once('connection.php');?>                    
                     <?php
	$maxperpage = 1;
mysql_select_db($database_Connection, $Connection);
				
						//This checks to see if there is a page number. If not, it will set it to 		page 1
						if (!(isset($pagenum)))
						{
						$pagenum = 1;
						} 
						
						
						//Here we count the number of results
						$sql = mysql_query("Select count(*) as cnt FROM directors") or die(mysql_error());
$result = mysql_query($sql);
$rows = mysql_fetch_assoc($result);
$numrows = $rows['cnt'];						

						//This is the number of results displayed per page
						$page_rows = 1; 
						
						//This tells us the page number of our last page
						$last = ceil($numrows/$page_rows);
						
						//this makes sure the page number isn't below one, or more than our                           maximum pages
						if ($pagenum < 1)
						{
							$pagenum = 1;
						}
						elseif ($pagenum > $last)
						{
							$pagenum = $last;
						}  
						//This sets the range to display in our query
						$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows; 
						
						//This is the query again, the same one... the only difference is I add                        $max into it
						$sql2 = mysql_query("SELECT * FROM directors $max") or die(mysql_error());  
						$result2 = mysql_query($sql2) or die(mysql_error());
						//$row2 = mysql_fetch_assoc($result);
	
	$break = '<br/>';
							while ($rows2 = mysql_fetch_assoc($result2))
							{ ?>
{

I get the following error message

"Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/fhlinux132/m/miraclemakers.co.uk/user/htdocs/preview/directors.php on line 208
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1,1' at line 1"

Thanks for looking into it

WilliamMute007 · Sep 16, 2009

just to keep you posted, I have tried several codes found via google but no luck. I basically just want to query a DB, return the results with pagination depending on the set numbers of records per page that I set in my code. Here is what my code looks like now, but it returns no errors nor records neither. Please help.

Code:

 <?php require_once('connection.php');?>
      <?php
		$maxperpage = 6;
				mysql_select_db($database_Connection, $Connection);
$sql = "Select count(*) as cnt FROM directors";
$result = mysql_query($sql);

//we need to know how many rows are returned
$row = mysql_fetch_assoc($result);
$numrows = $row['cnt'];


//how many pages are we going to use
$numpages = ceil($numrows/$maxperpage);
//now work out where to start
$offset = isset($_GET['offset'])?$_GET['offset']:0;

//now do the query
$sql = "Select * FROM directors order by FirstName limit $maxperpage offset $offset";
$result = mysql_query($sql) or die(mysql_error());

if (mysql_num_rows($result) == 0) {
    die ('no records');
}
               
                    while ($row = mysql_fetch_assoc($result)){ ?>

   
						<li class="product">
                        
                        <? for($i=0;$i < $num_cols; $i++){?>
							<h4><? echo $row['Title'];?> &nbsp;<? echo $row['LastName'];?></h4>
	    		<a href="/preview/upload/upload/<? echo $row['File'];?>"><img src="/preview/upload/upload/small/<? echo $row['File'];?>" alt="<? echo $row['FirstName'];?>" /></a>
                       
	    			    	<p><? echo $row['summary'];?></p>
	    			    	<p class="price">Position: <span>Director</span></p>
	    			    	<p><a href="product.html" class="add">Career Path</a><a href="product.html" class="more">More info</a></p>
                            </li>
                            <?
                              if($i !== 2) {
            $row = mysql_fetch_assoc($result);  
            if (!$row){
                for ($j=0; $j < ($num_cols-$i-1);$j++){
                                 }
            break;
            }
        }        
   } 
   
   
}
                            
	?>				
					</ul>
					
					<div id="pagination">
     <? if ($numpages > 1): ?>

<?
for ($i=0; $i<$numpages; $i++):
$class = (($offset/$maxperpage) === $i) ? "current":"noncurrent";
?>

<a class="foliosubtitles" href="<?=$_SERVER['PHP_SELF']?>?offset=<?=$i*$maxperpage?>">Pages<?=$i+1?></a>

<?
endfor;
?>
</div>
<? endif; ?>

Thank you in advance for your help.

WilliamMute007 · Sep 17, 2009

Hi,

Would anyone have a clue as to why I am getting the following error?

"Fatal error: SQL in /home/fhlinux132/m/miraclemakers.co.uk/user/htdocs/preview/directors.php on line 202"

Line 202 being

Code:

$result2 = mysql_query($query2) or trigger_error("SQL", E_USER_ERROR);

Here is my SQL Statement...

Code:

$query2 = "SELECT * FROM directors ORDER by FirstName DESC $limit";

Thank you for your help in advance

WilliamMute007 · Sep 17, 2009

Please help with Pagination Nightmare. I've managed to get it all running with no errors BUT in a case where I have 2 or more pages, the next link doesn't take me to the 'next page'. On the link bar, it does say pageno=2, but its still shows me the result of page1 if am making sense. So in essence, even though its showing more pages, its not actually displaying the results of the other pages when .

Here is my complete code if it helps diagnosing

Code:

 <?php require_once('connection.php');?>
      <?php
		$maxperpage = 6;
				mysql_select_db($database_Connection, $Connection);
$query = "Select count(*) as cnt FROM directors";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

$rows_per_page = 1; // number of records to show per page
$lastpage = ceil($numrows/$rows_per_page);
if ($pageno < 1) {
   $pageno = 1;
} elseif ($pageno > $lastpage) {
   $pageno = $lastpage;
}


//This sets the range to display in our query 
$limit = 'limit ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

$query2 = "SELECT * FROM directors $limit";
//$result2 = mysql_query($query2) or trigger_error("SQL", E_USER_ERROR);
$result2 = mysql_query($query2) or die(mysql_error());



               
                    while($nt2=mysql_fetch_array($result2)){
					//while ($row = mysql_fetch_assoc($result)){
					
					   $Title = $nt2['Title'];
    				   $FirstName = $nt2['FirstName'];
    				   $LastName = $nt2['LastName'];
    				   $summary = $nt2['summary'];
					   $other = $nt2['other'];
					   $File = $nt2['File'];

					 ?>

Pagination Section

Code:

    		<h4><? echo $Title; ?> &nbsp;<? echo $LastName;?></h4>
	    		<a href="/preview/upload/upload/<? echo $File;?>"><img src="/preview/upload/upload/small/<? echo $File;?>" alt="<? echo $FirstName;?>" /></a>
                       
	    			    	<p><? echo $summary;?></p>
	    			    	<p class="price">Position: <span>Director</span></p>
	    			    	<p><a href="product.html" class="add">Career Path</a><a href="product.html" class="more">More info</a></p>
                            </li>

<? } ?>


<?
// This shows the user what page they are on, and the total number of pages
echo " --Page $pageno of $lastpage-- <p>";

// First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page.
if ($pageno == 1) 
{
} 
else 
{
echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'> <<-First</a> ";
echo " ";
$previous = $pageno-1;
echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$previous'> <-Previous</a> ";
} 

//just a spacer
echo " ---- ";

//This does the same as above, only checking if we are on the last page, and then generating the Next and Last links
if ($pageno == $lastpage) 
{
} 
else {
$next = $pageno+1;
echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$next'>Next -></a> ";
echo " ";
echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>Last ->></a> ";
} 
?>

Thank you for your help

jpadie · Sep 17, 2009

the reason why your code is not working is that you have not correctly filled the $pageno variable

Code:

$pageno = empty($_GET['pageno']) ? 1 : intval(trim($_GET['pageno']));

this code, obviously, should go at the top of the script.

WilliamMute007 · Sep 17, 2009

Thank you Justin. Always a star. Thank you

WilliamMute007 · Sep 17, 2009

Hi Justin,

Can I disturb you a little bit more? Can you please help me with listing out the page numbers as links? i.e [1] [2] [3]... as well as a next and previous button?

Please?

Thank you so much

jpadie · Sep 17, 2009

try this (not tested by me at all)

Code:

<?php
//database connection script
require_once('connection.php');
mysql_select_db($database_Connection, $Connection);

//variables needed for paging
$maxPerPage = 6;
$page = empty($_GET['page']) ? 1 : intval (trim ( $_GET['page']));
$offset = ($page - 1) * $maxPerPage;

//set up columns
//note the maxPerPage is still respected
$columns = 1;
$columnWidth = (100 / $columns) - 5;

//pull in data
$sql = "select sql_calc_found_rows * from directors limit $maxPerPage offset $offset";
$result = mysql_query($sql) or die (mysql_error());

//get row count
$countResult = (int) mysql_result(mysql_query("select found_rows()"),0,0);

//calculate number of pages
$numPages = ceil($countResult/$maxPerPage);
$pageLinks = getPageLinks($page, $numPages);

//output sample styles for page links
echo <<<CSS
<style type="text/css">
/* zero default behaviouts */
body,html, div, span, li, ul, ol, h1, h2, h3, 
a, table, tbody, thead,tfoot, th, td, tr, 
select, textarea, input, img {	margin:0; 	padding:0;	text-decoration:none;}

/*basic typography */
body, input, select, th, td{
	font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;
	font-size:67%;
	color: black;
}

/* page links styles */
.pageLinks{	width:80%; 	margin:0 auto; 	color:#5744ff; 	overflow-y:hidden; 	clear:both;}
.firstlast {	width:5em;}
.nextprev{	width: 5em;}
.pageLink{	width: 1.5em;	float:left;	border: 1px solid #040404;	text-align: center; margin-right: 0.7em;}
.curPage{ 	background-color:#7d74a2;}
.pageLink a{	text-decoration:none;	color: #5744ff;}
.pageLink a:hover{	background-color:#82fd36;}
.pageLink:hover{	border-color:#f63c45;	background-color:#82fd36;}
.pageLink a:visited{	text-decoration:none;	color; #5744ff;}

/* page styles */
.pageContents {	width:80%;	clear:both;	overflow-y:hidden;}
.pageContents .columnItem{ 	float:left;	width:{$columnWidth}%}
</style>
CSS;

//output the page links
echo $pageLinks;

//output the page content
echo <<<HTML
	<div class="pageContents">
HTML;

while ($row = mysql_fetch_assoc($result)){
	echo <<<HTML
	<div class="columnItem>
		<h4>{$row['Title']}&nbsp;{$row['LastName']}</h4>
		<a href="/preview/upload/upload/{$row['File']}">
			<img src="/preview/upload/upload/small/{$row['File']}" alt="{$row['FirstName']}"/>
		</a>
		<p>{$row['summary']}</p>
		<p class="price">
			Position: <span>Director</span>
		</p>
		<p>
			<a href="product.html" class="add">Career Path</a>
			<a href="product.html" class="more">More info</a>
		</p>
	</div>
HTML;
}

//close page content div
echo "</div>";
//add bottom pageLinks too
echo $pageLinks;

function getPageLinks($page, $numPages){
	$links = '';
	//plot individual pages
	for($i=1; $i<=$numPages; $i++){
		$class = ($page == $i) ? 'pageLink curPage' : 'pageLink';
		$links .= <<<HTML
<div class="$class"><a href="{$_SERVER['PHP_SELF']}?page=$i}">$i</a></div> 
HTML;
	}
	if ($page == 1){
		$first = '';
		$previous = '';
	} else {
		$first = <<<HTML
<div class="firstlast pageLink"><a href="{$_SERVER['PHP_SELF']}?page=1}">First</a></div> 
HTML;
		$previous = $page - 1;
		$previous = <<<HTML
<div class="nextprev pageLink"><a href="{$_SERVER['PHP_SELF']}?page=$previous}">Previous</a></div> 
HTML;
	}
	if ($page == $numPages){
		$last = '';
		$next = '';
	} else {
		$last = <<<HTML
<div class="firstLast pageLink"><a href="{$_SERVER['PHP_SELF']}?page=$numPages}">Last</a></div> 
HTML;
		$next = $page + 1;
		$next = <<<HTML
<div class="nextprev pageLink"><a href="{$_SERVER['PHP_SELF']}?page=$next}">Next</a></div> 
HTML;
	}
	return <<<HTML
<div class="pageLinks">{$first}{$previous}{$links}{$next}{$last}</div>
HTML;
}
?>

Geates · Sep 17, 2009

I've never seen <<<HTML. Is this a PHP tag or are you simply idenifying the need for php code tages (?>...<?)

-Geates

jpadie · Sep 17, 2009

it is the heredoc syntax

WilliamMute007 · Sep 17, 2009

Hi Justin,

Thank you for that. Wow! that is a heavy duty code. The HTML heredoc syntax I have never used or seen before so I don't understand how to use it and having a bit of difficulty customizing the script. Its completely turn all my other HTML codes on the rest of the page to php so basically malfunctioning.

WilliamMute007 · Sep 17, 2009

I have just had an attempt in converting everything back to php and HTML but it had a disastrous effect and a lot of errors.

jpadie · Sep 18, 2009

it's not heavy duty at all. there is very little difference between your code and mine. it's just that mine is a bit neater in terms of formatting.

i do not follow what you mean William, by

Its completely turn all my other HTML codes on the rest of the page to php so basically malfunctioning.

unless you understand the heredoc code, i would not fiddle with it. read up on the link i posted to Geates first.

the only change in the markup i made was to use divs rather than li elements to surround the database output. you will obviously have to remove the outer ol's too. i did not see the logic behind using lists to display items like this. if there is a logic then you should change the columnItem div to and li. you should then set the css of .columnItem to display:block as well.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

mysql_fetch_row Error, pls Help 2

WilliamMute007

Programmer

jpadie

Technical User

WilliamMute007

Programmer

WilliamMute007

Programmer

WilliamMute007

Programmer

WilliamMute007

Programmer

jpadie

Technical User

WilliamMute007

Programmer

WilliamMute007

Programmer

jpadie

Technical User

Geates

Programmer

jpadie

Technical User

WilliamMute007

Programmer

WilliamMute007

Programmer

jpadie

Technical User

Similar threads

Part and Inventory Search

Sponsor