Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    228
    Thanks
    45
    Thanked 3 Times in 3 Posts

    Help with converting page to MSQLI

    Howdy All, I require some help to convert this area of my pagination page to work with mysqli... for what ever reason I can not get it to work when I was messing with it.

    PHP Code:
    $conn mysql_connect('localhost','user','password') or trigger_error("SQL"E_USER_ERROR);
    $db mysql_select_db('database_name',$conn) or trigger_error("SQL"E_USER_ERROR);

    $sql "SELECT COUNT(*) FROM table";
    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
    $r mysql_fetch_row($result);$numrows $r[0];

    $rowsperpage 10;

    $totalpages ceil($numrows $rowsperpage);

    if (isset(
    $_GET['currentpage']) && is_numeric($_GET['currentpage']))
    {

    $currentpage = (int) $_GET['currentpage'];
    }
    else
    {

    $currentpage 1;
    }
    //end if

    if ($currentpage $totalpages
    {

    $currentpage $totalpages;
    }
    //end if

    if ($currentpage 1)
    {

    $currentpage 1;
    }


    $offset = ($currentpage 1) * $rowsperpage

    Thanks in advance..


    Slayer.

  • #2
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    228
    Thanks
    45
    Thanked 3 Times in 3 Posts
    I got it...

    PHP Code:
    $mysqli = new mysqli('localhost','username','pass','database');
    $query $mysqli->prepare("SELECT * FROM table");
     
    $query->execute();
     
    $query->store_result();
     
    $numrows $query->num_rows;
     
    echo 
    $numrows

    Thanks anyway.


    Slayer.

  • #3
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Quote Originally Posted by SlayerACC View Post

    Thanks anyway.
    Glad to be of help hehe

    Don't bother using the prepare/bind/execute though. Since you're not providing it with dynamic data, there really isn't a need to do so, and I wouldn't be surprised if it actually reduces your efficiency. Just a standard $mysqli->query() call would be sufficient for that.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    SlayerACC (05-08-2013)

  • #4
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    228
    Thanks
    45
    Thanked 3 Times in 3 Posts
    Is this what you are suggesting?

    PHP Code:

    $mysqli 
    = new mysqli('localhost','username','pass','database'); 
    $query $mysqli->query("SELECT * FROM table");
    $numrows $query->num_rows;
      
    echo 
    $numrows
    Slayer
    Last edited by SlayerACC; 05-08-2013 at 10:11 PM.

  • #5
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,091
    Thanks
    51
    Thanked 506 Times in 493 Posts
    Yes that is what Fou is suggesting. What Fou was trying to say is that unless you are inputtng data (eg from a web form OR in your where clause) then you don't need to use prepare() bind() or execute(). For selecting you can just run a query even if it has a hardcoded 'where' clause. Only use the functions if you're going to be using dynamically supplied data with the SQL from a form or some other source that could be vulnerable to injection.
    My helpful sig is on vacation trying to loose some weight. It got a bit fat and caused a few problems but it will be back at some point!

  • Users who have thanked tangoforce for this post:

    SlayerACC (05-08-2013)

  • #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    2,960
    Thanks
    2
    Thanked 304 Times in 296 Posts
    The original query you posted was SELECT COUNT(*) FROM table, then you were fetching the count value.

    Your query has morphed so that you are currently selecting and retrieving all the rows from your table just to count them. This takes a lot of time and memory.

    You need to go back to the original query and program logic, but using mysqli query and fetch statements.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #7
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    228
    Thanks
    45
    Thanked 3 Times in 3 Posts
    Hey CFMaBiSmAd

    What is your suggestion to fix this ? as I have always done it this way..

    if there is a better way please help me out.


    Thanks.

    Slayer

  • #8
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,091
    Thanks
    51
    Thanked 506 Times in 493 Posts
    Instead of using count(*) you could use count(<column_name).

    If you have an id column that is auto incremented, use that. That way you are selecting minimal data (using less memory) but still selecting and counting each row.

    If you have any disabled / enabled rows (eg a enabled column where you set it to 1 if that record is enabled) then you can still use a where clause too.
    My helpful sig is on vacation trying to loose some weight. It got a bit fat and caused a few problems but it will be back at some point!

  • #9
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    228
    Thanks
    45
    Thanked 3 Times in 3 Posts
    Thanks Tango..


    I will do that now..


    Slayer.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •