HiveBrain v1.2.0
Get Started
← Back to all entries
patternphpMinor

Fetching and formatting content from a database

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
fromformattinganddatabasefetchingcontent

Problem

I've wrote this script to fetch and format content from my DB. It also counts how many result there are and separates them into pages. I'm barely learning PHP and MySQL so I don't know much about performance.

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

Solution

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

Code Snippets

$stmt->prepare("SELECT COUNT(*) AS count FROM public  $selection");
if(!($stmt->execute()) {
   die('Unable to execute!'. $mysqli->error);
}

Context

StackExchange Code Review Q#26381, answer score: 2

Revisions (0)

No revisions yet.