0

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).

Chazy Chaz
  • 1,781
  • 3
  • 29
  • 48
  • Unfortunately, you cannot use table or column identifiers in prepared statements that way. – Don't Panic Oct 28 '15 at 16:26
  • 1
    Possible duplicate of [Can I parameterize the table name in a prepared statement?](http://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement) – Machavity Oct 28 '15 at 16:30
  • So, can I `"SELECT * FROM {$mytable} WHERE ..."` or `"SELECT * FROM " . $mytable . " WHERE ..."`. Is that the only way? – Chazy Chaz Oct 28 '15 at 16:55

0 Answers0