How do I port this depreciated mysql script to mysqli

Question

I posted this script earlier to fix a pagination error, now I need to update it to get away from the depreciated mysql but I am having trouble with the ceil(mysql_result) and mysql_fetch_assoc($query) parts.

$per_page = 25;
$pages_query = mysql_query("SELECT count(*) FROM my_db WHERE StartDate >= CURDATE()");
$pages = ceil(mysql_result($pages_query, 0) / $per_page);
$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$start = ($page - 1) * $per_page;
$query = mysql_query("SELECT *, DATE_FORMAT(StartDate, '%d/%m/%Y') StartDate, CompName, HostState, Location,  competitiontypedesc FROM my_db WHERE StartDate >= CURDATE() LIMIT $start, $per_page"); 

while($query_row = mysql_fetch_assoc($query)) {
echo"
<li class='kleo-masonry-item event-item'> 
    <div class='member-inner-list animated animate-when-almost-visible bottom-to-top start-animation grey-border'> 
        <div class='event-cell event-date-cell'>
            <p class='no-margin'>" . $query_row["StartDate"] . "</p>
        </div>
        <div class='event-cell name'>
            <p class='no-margin'>" . $query_row["CompName"] ." " .$query_row["LastName"]. "</p>
        </div>
        <div class='event-cell'>
            <p class='no-margin'>" . $query_row["Location"] . "</p>
        </div>
        <div class='event-cell'>
            <p class='no-margin'>" . $query_row["HostState"] . "</p>
        </div>
</li>
"
;   
}
            $prev = $page - 1;
            $next = $page + 1;

            if(!($page <= 1)){
            echo "<a href='?page=$prev'><</a> ";}

            if($pages >= 1){
            for($x=1; $x<=$pages; $x++){
            echo ($x == $page) ? '<b><a href="?page='.$x.'">'.$x.'</a></b> ' : '<a href="?page='.$x.'">'.$x.'</a>';}
            }

            if(!($page >= $pages)){
            echo "<a href='?page=$next'>></a> ";}
            }
            }

Hi Phiter, I left out my attempts at mysqli as I had only changed the db connection and the following query, very basic understanding of changing over to mysqli $pages_query = mysqli_query("SELECT count(*) FROM my_db WHERE StartDate >= CURDATE()"); — Dexx, Oct 11 '16 at 22:16

score 0 · Accepted Answer · answered Oct 11 '16 at 11:14

0

get used to name variables clearly: $pages_query should be $pagesResultset. here's the essence:

$dbCon = new mysqli(host,user,pas,db);
$resultset = $dbCon->query("select ...");
if($resultset->num_rows == 0) die('issues');
$pageSize = 25;
$pageCount = ceil($resultset->num_rows / $pageSize);
while($row = $resultset->fetch_assoc()) {
    /* do something */
}
$dbCon->close();

answered Oct 11 '16 at 11:14

Paul T

396
3
7

addenda: for your own sake, switch to twig or any other templating engine or you'll hate yourself in 6 months. takes 1h to understand how to use it and it saves nights tears and pulled hairs. – Paul T Oct 11 '16 at 11:17
Thanks Paul, got this working however I left my pagination code out of the question and am having issues getting it to work properly. Pagination looks like this $prev = $page - 1; $next = $page + 1; if(!($page <= 1)){ echo "< ";} if($pages >= 1){ for($x=1; $x<=$pages; $x++){ echo ($x == $page) ? ''.$x.' ' : ''.$x.'';} } if(!($page >= $pages)){ echo "> ";} } } – Dexx Oct 18 '16 at 04:10
work with me here, what exactly goes awry? :) also ... be smart, use $pagesCount, $nextPageIndex, etc ... you can't imagine how easy debugging becomes once you can read your code like a page of a book – Paul T Mar 09 '17 at 11:14

How do I port this depreciated mysql script to mysqli

1 Answers1