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();
}