0

So I am working on search engine for mySQL database on a website. And I Want the user to be able to select how they are going to be searching. Like so...

$searchBy=mysqli_real_escape_string($conn, $_POST['recordType']); 
$searchText=mysqli_real_escape_string($conn, $_POST['searchText']); 

$getRecordsSQL = "SELECT * FROM weighs WHERE ID='$searchText'"; 
$recordsQuery = mysqli_query($conn,$getRecordsSQL);

The above code works, but only for searching by ID. How would I get it to look something like this..

$searchBy=mysqli_real_escape_string($conn, $_POST['recordType']); 
$searchText=mysqli_real_escape_string($conn, $_POST['searchText']); 

$getRecordsSQL = "SELECT * FROM weighs WHERE '$searchBy'='$searchText'"; 
$recordsQuery = mysqli_query($conn,$getRecordsSQL);

That code does not work, but you get the point. Is there a way to format that query so the user can pick the column they would like to look at, Aka WHERE?

Deshler Scales
  • 31
  • 6
  • You really should not be using escape strings. Use a prepared statement with a parameter for searchText and use a whitelist for searchBy. Once you have that in place, use backticks, not quotes for column names. – Devon Bessemer Jul 06 '18 at 19:37
  • 2
    If you want to make a PHP variable be a column, it must be in ticks. And you should also whitelist it before using it in a query! – Qirel Jul 06 '18 at 19:38
  • 3
    You're already using an API that supports **prepared statements** with bounded variable input, you should utilize parameterized queries with placeholders (prepared statements) to protect your database against [SQL-injection](http://stackoverflow.com/q/60174/)! Get started with [`mysqli::prepare()`](http://php.net/mysqli.prepare) and [`mysqli_stmt::bind_param()`](http://php.net/mysqli-stmt.bind-param). – Qirel Jul 06 '18 at 19:38
  • Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. – tadman Jul 06 '18 at 19:46
  • `real_escape_string` is for *user data only* and not column names. – tadman Jul 06 '18 at 19:47

1 Answers1

-2
 //this must be in selection for search

  <form method="post" action="#">
    <input type="text" id="searchText">
   <select id="searchBy">
    <option value="table name">table name</option>
    <option value="table name 1">table name 1</option>
    <option value="table name 2">table name 2</option>
    <option value="table name 3">table name 3</option>
    <input type="submit" id="submit">
  </select>
 </form>







<script>
//after you have to send value from from select to php with ajax or jquery
 $(document).ready(function(){
   $("#submit").on("click" ,function(){
     var searchBy= $("#searchBy").val();
     var searchText=  $("#searchText").val();

    $.ajax({
       url: 'link_page_to_php_sql.php', 
        dataType: 'text', 
        cache: false,
        contentType: false,
        processData: false,
        data: {searchBy: searchBy, searchText : searchText},
        type: 'POST',
         success: function (response) {
            //some cod here if you wont after succes 
         }
     });
   });
   });


  <?php
     //and this for sql  link_page_to_php_sql.php
   if (isset($_POST['searchBy'])){
    $searchBy = $_POST['searchBy'];
    $searchText= $_POST['searchText'];
  }
     $getRecordsSQL = "SELECT * FROM weighs WHERE '$searchBy'='$searchText'"; 
     $recordsQuery = mysqli_query($conn,$getRecordsSQL); 
  ?>
alex
  • 16
  • 5