PHP - mysql_fetch_assoc and mysqli_fetch_assoc

I am not very experienced with PHP and I need this script to be mysqli_fetch_assoc instead of mysql_fetch_assoc.  I tried to make some changes but only got a blank screen.  As it is now it says it can't connect to local host but that is not the problem because I have other pages connecting to the same database with no problems.  
The problem is my connection is @$mysqli = new mysqli("stuff", "stuff", "stuff!", "stuff"); and it won't work with mysql_fetch_assoc

Any help is appreciated.

 $MySQL = '
            SELECT
                SQL_CALC_FOUND_ROWS
                name, story
            FROM
                guestbook WHERE active_account = 1 ORDER BY s_id DESC
            LIMIT
                ' . (($pagination->get_page() - 1) * $records_per_page) . ', ' . $records_per_page . '
        ';

        // if query could not be executed
        if (!($result = @mysql_query($MySQL))) {

            // stop execution and display error message
            die(mysql_error());

        }

        // fetch the total number of records in the table
        $rows = mysql_fetch_assoc(mysql_query('SELECT FOUND_ROWS() AS rows'));

        // pass the total number of records to the pagination class
        $pagination->records($rows['rows']);

        // records per page
        $pagination->records_per_page($records_per_page);

        ?>
        
        <table class="countries" border="1">

        <th>Readers Testimonies</th></tr>
            <?php $index = 0?>

            <?php while ($row = mysql_fetch_assoc($result)):?>

            <tr<?php echo $index++ % 2 ? ' class="even"' : ''?>>
            
                <td><?php echo $row['name']?></td> </tr>
				<td><?php echo $row['story']?></td> </tr>
            <?php endwhile?>
      <td>  <?php

        // render the pagination links
        $pagination->render();

        ?>
        </tr>
        </table>

Open in new window

JohnMac328Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
It won't work with 'mysql_query' or any other non-mysqli function either.  And putting the '@' in front of them does not stop errors, it merely stops PHP from telling you what they are.  You should remove all of the '@' symbols and put
// Report all PHP errors (see changelog)
error_reporting(E_ALL);

Open in new window

at the top of your pages.

This page http://php.net/manual/en/mysqli-result.fetch-assoc.php shows a fairly complete example of using all of the 'mysqli' functions together.

http://php.net/manual/en/mysqli.construct.php
http://php.net/manual/en/mysqli.query.php
0
JohnMac328Author Commented:
I was able to change all but one with the examples - I can't figure out the syntax on this one

     $rows = mysql_fetch_assoc(mysql_query('SELECT FOUND_ROWS() AS rows'));
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Ray PaseurCommented:
I think the syntax would go something like this:

$qry = 'SELECT FOUND_ROWS() AS rows';
$res = mysqli_query($connection, $qry);
var_dump($res);
$rows = mysqli_fetch_assoc($res);
var_dump($rows);

Open in new window

This is untested code.  The var_dump() statements are in there in case it does not work.  We will want to see what is in the variables after each function call.
0
JohnMac328Author Commented:
Thanks Ray! - I will reply with the results
0
JohnMac328Author Commented:
I get a blank page - I think the require 'Zebra_Pagination.php'; include is causing problems


 $records_per_page = 3;

        // include the pagination class
        require 'Zebra_Pagination.php';

        // instantiate the pagination object
        $pagination = new Zebra_Pagination();

        // the MySQL statement to fetch the rows
        // note how we build the LIMIT
        // also, note the "SQL_CALC_FOUND_ROWS"
        // this is to get the number of rows that would've been returned if there was no LIMIT
        // see http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
        $MySQL = '
            SELECT
                SQL_CALC_FOUND_ROWS
                name, story
            FROM
                guestbook WHERE active_account = 1 ORDER BY s_id DESC
            LIMIT
                ' . (($pagination->get_page() - 1) * $records_per_page) . ', ' . $records_per_page . '
        ';

        // if query could not be executed
        if (($result = mysqli_query($mysqli,$MySQL))) {

            // stop execution and display error message
            die(mysqli_error($mysqli));

        }

        // fetch the total number of records in the table
		$qry = 'SELECT FOUND_ROWS() AS rows';
$res = mysqli_query($mysqli, $qry);
var_dump($res);
$rows = mysqli_fetch_assoc($res);
var_dump($rows);
		
    //  $rows = mysql_fetch_assoc(mysql_query('SELECT FOUND_ROWS() AS rows'));

        // pass the total number of records to the pagination class
        $pagination->records($rows['rows']);

        // records per page
        $pagination->records_per_page($records_per_page);

        ?>
        
        <table class="countries" border="1">

        <th>Readers Testimonies</th></tr>
            <?php $index = 0?>

            <?php while($row = $result->fetch_assoc()):?>

            <tr<?php echo $index++ % 2 ? ' class="even"' : ''?>>
            
                <td><?php echo $row['name']?></td> </tr>
				<td><?php echo $row['story']?></td> </tr>
            <?php endwhile?>
      <td>  <?php

        // render the pagination links
        $pagination->render();

        ?>
        </tr>
        </table>

Open in new window

0
Ray PaseurCommented:
Try adding these lines to the top of your PHP scripts so we can see what is happening.
ini_set('display_errors', TRUE);
error_reporting(E_ALL);

Open in new window

The "blank page" is something that should only happen in a deployed script -- when we are in development, we want to see all of the errors, messages, etc.
0
JohnMac328Author Commented:
It is local but I still have a blank page which makes no sense
0
JohnMac328Author Commented:
Just as an example this page works fine

<?php 
include('admin/connection.php');
  $query = "SELECT * FROM guestbook WHERE active_account = 1 ORDER BY s_id DESC";
?>
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Jesus Help Wanted</title>
<link type="text/css"  rel="stylesheet" href="admin/stylesheet.css" />
</head>
<body>

<br/>
<br/>
<table class="display_table">
  <thead>
    <tr>
      <th width="5%">No</th>
      <th width="26%">Name</th>
      <th width="69%" >Title</th>
    </tr>
  </thead>
  <tbody>
    <?php
                   if($result = $mysqli->query($query)){
				       $no = 1;
                        while($row = $result->fetch_assoc()){
						  echo "<tr>
						         <td>$no</td>
						         <td><a href='admin/editdetails.php?s_id=".$row['s_id']."'>".$row['name']."</a></td>
						         <td>".$row['title']."</td>
						       </tr>"
							   ;
						   $no++;	   
						}
                     } 
                ?>
  </tbody>
</table>
</body>
</html>

Open in new window

0
Ray PaseurCommented:
Interesting.  I've never heard of Zebra Pagination before today and now that I see the code, it looks very old and not very well-written at all.  But it might not be the problem.

The best thing to do is deconstruct the script and isolate the errors.  Start with the mySQLi connection - make sure it works.  Then add a simple query and make sure it works, moving on a step at a time.  That way when something fails, you will know exactly which step induced the failure.
0
JohnMac328Author Commented:
My previous comment has the code from another page that works fine connecting
0
JohnMac328Author Commented:
It is from 2013 so I hope it isn't too outdated.


Pagination
0
Ray PaseurCommented:
Most of it looks more like 2009.  When you find a script with die() in it, it's a sure sign that the author has not kept up with modern error handling techniques!
0
JohnMac328Author Commented:
I commented it out and I got this error

Fatal error: Class 'Zebra_Pagination' not found in D:\Hosting\3134487\html\view.php on line 55

I don't think it likes the mysqli connection - I may have to find another pagination script
0
Ray PaseurCommented:
According to the author's web site, Zebra is agnostic about its data source.  And IIRC it's available on Github in open source, so you could check that out.

Also, there is this. It is old, chronologically, but of relatively modern design and implementation compared to Zebra:
http://www.sitepoint.com/perfect-php-pagination/
0
JohnMac328Author Commented:
They provided the example as a download - I tried the example without changing anything and this is what comes up.  That code is from the Paginated.php include

setRs($obj); $this->setPageSize($displayRows); $this->assignPageNumber($pageNum); $this->setRowNumber(0); $this->setOffSet(($this->getPageNumber() - 1) * ($this->getPageSize())); } //implement getters and setters public function setOffSet($offSet) { $this->offSet = $offSet; } public function getOffSet() { return $this->offSet; } public function getRs() { return $this->rs; } public function setRs($obj) { $this->rs = $obj; } public function getPageSize() { return $this->pageSize; } public function setPageSize($pages) { $this->pageSize = $pages; } //accessor and mutator for page numbers public function getPageNumber() { return $this->pageNumber; } public function setPageNumber($number) { $this->pageNumber = $number; } //fetches the row number public function getRowNumber() { return $this->rowNumber; } public function setRowNumber($number) { $this->rowNumber = $number; } public function fetchNumberPages() { if (!$this->getRs()) { return false; } $pages = ceil(count($this->getRs()) / (float)$this->getPageSize()); return $pages; } //sets the current page being viewed to the value of the parameter public function assignPageNumber($page) { if(($page <= 0) || ($page > $this->fetchNumberPages()) || ($page == "")) { $this->setPageNumber(1); } else { $this->setPageNumber($page); } //upon assigning the current page, move the cursor in the result set to (page number minus one) multiply by the page size //example (2 - 1) * 10 } public function fetchPagedRow() { if((!$this->getRs()) || ($this->getRowNumber() >= $this->getPageSize())) { return false; } $this->setRowNumber($this->getRowNumber() + 1); $index = $this->getOffSet(); $this->setOffSet($this->getOffSet() + 1); return $this->rs[$index]; } public function isFirstPage() { return ($this->getPageNumber() <= 1); } public function isLastPage() { return ($this->getPageNumber() >= $this->fetchNumberPages()); } /** *  * @return PageLayout  */ public function getLayout() { return $this->layout; } /** *  * @param PageLayout  */ public function setLayout(PageLayout $layout) { $this->layout = $layout; } //returns a string with the base navigation for the page //if queryVars are to be added then the first parameter should be preceeded by a ampersand public function fetchPagedNavigation($queryVars = "") { return $this->getLayout()->fetchPagedLinks($this, $queryVars); }//end writeNavigation }//end Paginated ?>
0
Ray PaseurCommented:
Please use the code snippet feature.  Just click "Code" in the formatting bar.  You will get a pair of BBCode tags.  Paste the code inside the tags.  This gives us a unispace font and preserves the formatting.
0
JohnMac328Author Commented:
Just click "Code" in the formatting bar.  You will get a pair of BBCode tags.  - I am using Dreamweaver and do not see these options - I just copied the code snippet and still get the same result

<?php 
require_once "Paginated.php"; 
require_once "DoubleBarLayout.php"; 
 
//create an array of names in alphabetic order 
$names = array("Andrew", "Bernard", "Castello", "Dennis", "Ernie", "Frank", "Greg", "Henry", "Isac", "Jax", "Kester", "Leonard", "Matthew", "Nigel", "Oscar"); 
 
$page = $_GET['page']; 
 
$pagedResults = new Paginated($names, 10, 1); 
 
echo "<ul>"; 
 
while($row = $pagedResults->fetchPagedRow()) { 
  echo "<li>{$row}</li>"; 
} 
 
echo "</ul>"; 
 
$pagedResults->setLayout(new DoubleBarLayout()); 
echo $pagedResults->fetchPagedNavigation(); 
?>

Open in new window

0
Ray PaseurCommented:
Thanks.  The code snippet feature is here at E-E, not in Dreamweaver.
0
Ray PaseurCommented:
Please see the grading guidelines, then explain what you were looking for that you didn't get.
http://support.experts-exchange.com/customer/portal/articles/481419
0
JohnMac328Author Commented:
Please ask them to reset the question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.