Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

I'm barely learning PHP and MYSQL so I don't know much about performance, I've wrote this script to fetch and format content from my DB, it also counts how many result are and separate them in to pages. I would appreciate your feedback n.n

function fetch_content($section, $subsection, &$count, $page = 0){
    $section = substr($section, 0,8);
    $subsection = substr($subsection, 0,8);
    require_once("system/config.php");
    //Initiate connection 
        $mysqli = new mysqli($db_host, $db_user, $db_password, $db_database);
        if ($mysqli->connect_error) {
            die('Connect Error (' . $mysqli->connect_errno . ') '. $mysqli->connect_error);
        }
    //Select page
        $limit = 2;
        $start = $page * $limit ;

    //select query
        if($section == 'home' || ($section != 'home' && $subsection == NULL)){
            $selection =  "WHERE section = ?";
            }
        else
            $selection = "WHERE section = ? AND subsection = ?";

    //Fetch data
        $stmt = $mysqli->stmt_init();
        $qry= "SELECT * FROM public
        $selection
        ORDER BY id DESC LIMIT ?,?";
        $stmt->prepare($qry);
        if($section == 'home' || ($section != 'home' && $subsection == NULL))
            $stmt->bind_param("sss", $section, $start , $limit);
        else
            $stmt->bind_param("ssss", $section, $subsection, $start , $limit);
        $stmt->execute();
        $result = $stmt->get_result();
    //Format the data
        while( $row =  $result->fetch_assoc()){
            format_home($row, $mysqli);
        }
        $stmt->close();
    //Count result
        $stmt = $mysqli->stmt_init();
        $qry= "SELECT COUNT(*) AS count FROM public  $selection";
        $stmt->prepare($qry);
        if($section == 'home' || ($section != 'home' && $subsection == NULL))
            $stmt->bind_param("s", $section);
        else
            $stmt->bind_param("ss", $section, $subsection);
        $stmt->execute();
        $result = $stmt->get_result();
        $count = $result->fetch_assoc();
        $count =  $count['count'];
        $stmt->close();
    //close connection
        $mysqli->close();
}
share|improve this question

1 Answer 1

I would make a single file with the MySQL server connection, so you can use the same connection in other functions, etc..

Also you should pass string variables directly.

$stmt->prepare("SELECT COUNT(*) AS count FROM public  $selection");

Did you know, that you can check every single called function if a error occurred? For example:

if(!($stmt->execute()) {
   die('Unable to execute!'. $mysqli->error);
}

The next point is, why do you execute another query to count the result rows? In the mysqli_stmt object $num_rows. http://php.net/manual/en/mysqli-stmt.num-rows.php

share|improve this answer
    
I tried using stmt->num_rows; as $count = stmt->num_rows but its returning 0, Idk if I'm calling it incorrectly –  Carlos Arturo Alaniz May 22 '13 at 19:21
    
//Format the data while( $row = $result->fetch_assoc()){ format_home($row, $mysqli); } $count = $stmt->num_rows; $stmt->close(); –  Carlos Arturo Alaniz May 22 '13 at 19:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.