Sorry for the title if it's not accurate.
What I'm trying to do is make a dynamic query for searching. The prepared statement will receive the column and table name as a parameter and optionally conditions (WHERE, AND) as php variable:
I'm looking for something like this to work?
$optional_cond = ' WHERE city = ' . $city . 'AND zone = ' . $zone; // int
if ($stmt = $mysqli->prepare("SELECT COUNT(?) as count, ? FROM ?" . $optional_cond) {
$stmt->bind_param('sss', $column, $column, $table);
$stmt->execute();
$row = $stmt->fetch();
}
$total_results = $row['count'] // here i need the count
Now I need to grab the data:
if (!isset($page_number))
$page_number = (int)$_GET['page'] <= 0 ? 1 : (int)$_GET['page']; // grab the page number
$perpage = 4; // number of elements perpage
if ($page_number > ceil($total_results/$perpage))
$page_number = ceil($total_results/$perpage);
$start = ($page_number - 1) * $perpage;
// here i need the id's one by one
while ($row = $stmt->fetch()) {
$id = $row[$column]; // i think bind_param does not accept arrays
if ($stmt = $mysqli->prepare("SELECT * FROM ? Where id = ? LIMIT ? ?") {
$stmt->bind_param('siii', $table2, $id, $start, $perpage);
$stmt->execute();
}
while ($row = $stmt->fetch()) {
// print the data
}
}
I'm not sure if this is the best way to do this, I'd appreciate any advise and improvement.
I will use ajax to send the variables $city and $zone, do I need to do something else for this to work? (Aside from check if post isset).
