I have created a set of functions to retrieve and display data from a database using a mysqli connection.
The functions basically do the same thing but for different fields of data requested. The functions below retrieve data from one table but i also need to get data from another table but would rather not just rewrite the same functions with a different query for each table.
For example the functions below display data for breakfast menu, i now need to create the evening menu which will use similar data but stored in a different table.
Im sure there is a much more efficient way of doing this but i'm inexperienced and would like some guidance or pointers towards a better solution. I am not asking for someone to do it for me, i want to learn for me myself with guidance :)
I guess the SQL query needs to be made dynamic where it selects a group and the function somehow needs to able to call the required group but i'm not sure how: function call would end up looking like this:
get_menu(breakfast_menu);
Where get_menu retrieves all the data and then the argument is where the group in this case breakfast_menu is selected.
function connect () {
include_once ("config.php");
static $conn;
if ($conn===NULL){
$conn = mysqli_connect ("$host", "$username", "$password", "$db");
}
return $conn;
}
function get_breakfast_menu() {
$conn = connect();
$query = "SELECT * FROM menuitems where `group`='breakfast_menu'";
if ($result = mysqli_query($conn, $query)) {
/* fetch associative array */
while ($row = mysqli_fetch_assoc($result)) {
// data inside html goes here
}
}
/* free result set */
mysqli_free_result($result);
}
else{
echo "ERROR: Could not able to execute $query. " . mysqli_error($conn);
}
}
function get_sandwiches() {
$conn = connect();
$query = "SELECT * FROM menuitems where `group`='sandwiches'";
if ($result = mysqli_query($conn, $query)) {
/* fetch associative array */
while ($row = mysqli_fetch_assoc($result)) {
// data inside html goes here
}
}
/* free result set */
mysqli_free_result($result);
}
else{
echo "ERROR: Could not able to execute $query. " . mysqli_error($conn);
}
}
function get_lunch_favourites() {
$conn = connect();
$query = "SELECT * FROM menuitems where `group`='lunch_favourites'";
if ($result = mysqli_query($conn, $query)) {
/* fetch associative array */
while ($row = mysqli_fetch_assoc($result)) {
// data inside html goes here
}
}
/* free result set */
mysqli_free_result($result);
}
else{
echo "ERROR: Could not able to execute $query. " . mysqli_error($conn);
}
}
function get_light_bites() {
$conn = connect();
$query = "SELECT * FROM menuitems where `group`='light_bites'";
if ($result = mysqli_query($conn, $query)) {
/* fetch associative array */
while ($row = mysqli_fetch_assoc($result)) {
// data inside html goes here
}
}
/* free result set */
mysqli_free_result($result);
}
else{
echo "ERROR: Could not able to execute $query. " . mysqli_error($conn);
}
}
function get_sides() {
$conn = connect();
$query = "SELECT * FROM menuitems where `group`='sides'";
if ($result = mysqli_query($conn, $query)) {
/* fetch associative array */
while ($row = mysqli_fetch_assoc($result)) {
// echo'd data inside html goes here
}
}
/* free result set */
mysqli_free_result($result);
}
else{
echo "ERROR: Could not able to execute $query. " . mysqli_error($conn);
}
}
This should be pretty simple assuming that you are specifying $group and it's not user supplied data:
function get_menu($group) {
$conn = connect();
$query = "SELECT * FROM menuitems where `group`='$group'";
//other code
}
If $group is user supplied ($_GET, $_POST etc..) then:
function get_menu($group) {
$conn = connect();
$query = "SELECT * FROM menuitems where `group`= :group";
$stmt = $conn->prepare($query);
$stmt->bind_param('group', $group);
$stmt->execute();
//other code
}
Call with: get_menu('breakfast_menu');
You would be better off refactoring into a class so that you can do other things like only call connect() once:
class myClass {
private $conn;
public function __construct() {
$this->conn = $this->connect();
}
//other functions that use $this->conn
}
Also you really should separate the logic and the presentation, so maybe build a menu_template to display.
Pass group as a parameter to your function, and then build your query with a bind for that value. Something like this:
function getItemsForGroup($group)
{
$query = "SELECT * FROM menuitems where `group`= :group";
$stmt = $db->prepare($query);
$stmt->bind_param('group', $group);
$stmt->execute();
$items = $stmt->fetchAll();
foreach ($items as $item) {
// draw the table
}
}
(This is PDO style, you'll have to adjust the function calls a bit for procedural style, but you should get the idea. I'm not as familiar with the old procedural style so you'll have to look up the docs for the right names.)
Note, you will be tempted to do something like this:
$query = "SELECT * FROM menuitems where `group`= '$group'";
Don't do that. This leaves you open to SQL injection attacks.