0

I am searching for a keyword in a database with many different tables and the results are too many, so I want to implement pagination. I run SQL queries in a loop on all the tables in database, therefore I don't know where to start and stop if I have a page number for running the SQL query. Any idea on how to implement this?

function search($conn, $search) {

  $row_result = array();
  $tables = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='google'";
  $tables_result = $conn - > query($tables);
  $tables_array = array();
  if (!$tables_result) {
    //echo $conn->error;
  } else {
    while ($row = $tables_result - > fetch_assoc()) {
      array_push($tables_array, $row['TABLE_NAME']);
    }
  }

  for ($y = 0; $y < count($tables_array); $y++) {
    $columns = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = \"".$tables_array[$y].
    "\" ";
    $columns_result = $conn - > query($columns);
    $columns_array = array();
    if (!$columns_result) {
      //echo $conn->error;
    } else {
      while ($row = $columns_result - > fetch_assoc()) {
        array_push($columns_array, $row['COLUMN_NAME']);
      }
    }

    $where = join("\n OR ",
      array_map(function($c) {
          global $search;
          return "$c LIKE '%$search%'";
        },
        $columns_array
      )
    );

    $sql = 'SELECT * FROM `'.$tables_array[$y].
    '` WHERE '.$where.
    '';
    $result = $conn - > query($sql);
    if (!$result) {
      echo $conn - > error;
    } else {
      foreach($result as $row) {
        array_push($row_result, $row);
      }
    }
    $columns_array = array();
  }
  return array($row_result);
}


//final result
$row_result = [];

if (isset($_POST["search"])) {
  $search = $_POST['search'];
}

$row_result = search($conn, $search);

if (!empty($row_result)) {
  echo json_encode($row_result);
} else {
  echo json_encode("No results found");
}
Stephan T.
  • 5,843
  • 3
  • 20
  • 42
Manoj
  • 13
  • 6
  • I found this post where they provide a good starting point to Pagination with PHP. Does this help you? https://stackoverflow.com/questions/2616697/php-mysql-pagination – Kevin Quinzel Mar 04 '20 at 17:28
  • it uses a single sql query where it uses limit for filtering the query reuslts, here i have multiple sql queries in a loop. suppose, if i want to search for page 5 results, how can i know in which sql query in loop should i search for? – Manoj Mar 04 '20 at 17:39
  • 1
    **WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add any data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or data *of any kind* directly into a query, it can be very harmful if someone seeks to exploit your mistake. – tadman Mar 04 '20 at 20:29

0 Answers0