Search a MySQL table in PHP

Question

I am building a script where a user can query (search) a MySQL database.

The user firstly selects the table from a drop down list, and then they can choose upto 4 'filters' for example userID=001.

Here is my code:

      $con=mysqli_connect("localhost","Username","Password","DBname");
           // Check connection
           if(mysqli_connect_errno()) {
              echo "Failed to connect to MySQL: " . mysqli_connect_error();
           }

$query = "SELECT * FROM ".$table." WHERE 1=1 ";

if($filter1 != "" or $filter1v  != "" )
{
    $query .= " and $filter1 LIKE'%$filter1v%'";
}

if($filter2 != "" or $filter2v  != "" )
{
    $query .= " and $filter2 LIKE'%$filter2v%'";
}

if($filter3 != "" or $filter3v  != "" )
{
    $query .= " and $filter3 LIKE'%$filter3v%'";
}

if($filter4 != "" or $filter4v != "")
{
    $query .= " and $filter4 LIKE'%$filter4v%'";
}

$query .= ";";    

$resultRAW = mysqli_query($con, $query); 
echo mysqli_error($con);
$result = array();
while($data = mysqli_fetch_array($resultRAW, MYSQLI_ASSOC))
{
    $result[] = $data;
}
echo "<table class='table table-striped' id='tableWithExportOptions'>";

$amountRows = count($result);
for($i = 0; $i < $amountRows; $i++)
{
    $keys = array_keys($result[$i]);
    $amountColumns = count($keys);

    if ($i == 0)
    {
         echo "<thead><tr>";
         //I replaced the foreach clause because of performance reasons but they would work as well
         for($j = 0; $j < $amountColumns; $j++)
         {
             echo "<th>".$keys[$j]."</th>";
         }

         echo "</tr></thead>";
     }

     echo "<tr>";
     for($j = 0; $j < $amountColumns; $j++)
     {
         echo "<th>".$result[$i][$keys[$j]]."</th>";
     }
     echo "</tr>";
}
echo "</table>";

?>

If the user doesn't choose any filters the script works fine, however when using a filter it doesn't show any results?

But if there are no filters the query is `"SELECT * FROM $table WHERE"` and does not work — Shane, Jun 02 '16 at 08:26
can you update with your full code. So that can find what is the issue, — Kaja Mydeen, Jun 02 '16 at 08:32
Why need the full code? It's a so obvious mistake.. He isn't appending the sql query string. — Viktor Koncsek, Jun 02 '16 at 08:33

score 1 · Answer 1 · edited May 23 '17 at 11:45

1

Depending on your database this may vary. But you can not append a string to the result. $result is a MySQL Result object. You need to fetch the result for example with this code:

$array = array();
while($data = mysqli_fetch_array($result, MYSQLI_ASSOC))
{
    $array[] = $data;
}

Then you can work with your result array $array and do whatever you want to do

If you want to create a query this way you need to call the mysqli_query later and build the query which could look like this:

$con = mysqli_connect("localhost","Username","Password","DBname");
// Check connection
if(mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: ".mysqli_connect_error();
}


$query = "SELECT * FROM ".$table." WHERE 1=1 ";
if($field != "" or $fieldvalue != "" )
{
    $query .= " and ".$field." LIKE'%".$fieldvalue."%'";
}

if($filter1 != "" or $filter1value  != "" )
{
    $query .= " and ".$filter1." LIKE'%".$filter1value."%'";
}

if($filter2 != "" or $filter2value  != "" )
{
    $query .= " and ".$filter2." LIKE'%".$filter2value."%'";
}

if($filter3 != "" or $filter3value  != "" )
{
    $query .= " and ".$filter3." LIKE'%".$filter3value."%'";
}

if($filter4 != "" or $filter4value  != "")
{
    $query .= " and ".$filter4." LIKE'%".$filter4value."%'";
}

$query .= ";";    

$resultRAW = mysqli_query($con, $query);

$result = array();
while($data = mysqli_fetch_array($resultRAW, MYSQLI_ASSOC))
{
    $result[] = $data;
}

And I would be extremely careful with $table. in the query. This looks like a very good point to start an SQL Inejction attack. To prevent those I recomment the use of prepared statements. More can be found here: Prevent SQL Injection. Unfortunalty this does not work with tablenames so you need to manually test it for any malicios input. If you "trust" this variable then it might be ok but if it is a use rinput I would AT LEAST call:

 $table = mysqli_real_escape_string($table);

EDIT:

echo "<table class='table table-striped' id='tableWithExportOptions'>";

$amountRows = count($result);
for($i = 0; $i < $amountRows; $i++)
{
    $keys = array_keys($result[$i]);
    $amountColumns = count($keys);

    if ($i == 0)
    {
         echo "<thead><tr>";
         //I replaced the foreach clause because of performance reasons but they would work as well
         for($j = 0; $j < $amountColumns; $j++)
         {
             echo "<th>".$keys[$j]."</th>";
         }

         echo "</tr></thead>";
     }

     echo "<tr>";
     for($j = 0; $j < $amountColumns; $j++)
     {
         echo "<th>".$result[$i][$keys[$j]]."</th>";
     }
     echo "</tr>";
}
echo "</table>";

If this does not work please tell me, I have not tested this.

edited May 23 '17 at 11:45

Community

1
1

answered Jun 02 '16 at 08:28

JRsz

2,891
4
28
44

This doesnt work with the rest of my code under the `if` statements – Shane Jun 02 '16 at 08:35
If the filters are blank it displays the whole table – Shane Jun 02 '16 at 08:37
What do you mean if it is in an if staement? And yes, this is expected. If you have no filter it selects everything. That is what the filters are for I assume, not to display everything – JRsz Jun 02 '16 at 08:40
1

This is the correct answer if something does not work, look for flaws in other parts of your code. – K.I Jun 02 '16 at 08:40
On a further note, please look at the updated answer ESPECIALLY the end which might save you a lot of trouble. – JRsz Jun 02 '16 at 08:44
See my updated question, this is your code along with mine, specifically towards the end, this isnt displaying any data? – Shane Jun 02 '16 at 08:45
I see. Give me a few minutes – JRsz Jun 02 '16 at 08:47
Thank you @JRsz. Sorry I may not have asked the questions correctly :/ – Shane Jun 02 '16 at 08:48
That is fine, as long as I can earn an "answer accepted" :D See updated answer – JRsz Jun 02 '16 at 08:57
Hmm, getting there I think, So if no filters are applied everything shows as it should. If a filter is applied, its just showing that column, I still want to see all columns for that row... if that makes sense? – Shane Jun 02 '16 at 09:01
Yes, then everything shows. And no, if filters are applied you are merely filtering rows. Read more about this here: http://www.w3schools.com/sql/sql_like.asp (Very good for beginner) Like searches for pattern in a specific column. So not all rows are returned, only those which apply to the given pattern – JRsz Jun 02 '16 at 09:04
Is there a way to show all columns when a filter is applied though? – Shane Jun 02 '16 at 09:08
That is what I wrote. If a filter is applied all columns are returned because of the star in `SELECT * FROM`. The filter will only have an effect on the rows which are returned – JRsz Jun 02 '16 at 09:10
It isnt working in that way.... If a filter is applied, it just shows that column, and it shows all rows that are not empty within that column?? – Shane Jun 02 '16 at 09:12
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/113602/discussion-between-jrsz-and-user3092953). – JRsz Jun 02 '16 at 09:14

score 0 · Accepted Answer · answered Jun 02 '16 at 08:27

0

Because you concatenate string to $result = mysqli_query

$result = "SELECT * FROM $table WHERE 1=1";
if ($field != "" or $fieldvalue != "") {
    $result .= " and $field   LIKE'%$fieldvalue%'";
}

if ($filter1 != "" or $filter1value != "") {
    $result .= " and $filter1 LIKE'%$filter1value%'";
}

if ($filter2 != "" or $filter2value != "") {
    $result .= " and $filter2 LIKE'%$filter2value%'";
}

if ($filter3 != "" or $filter3value != "") {
    $result .= " and $filter3 LIKE'%$filter3value%'";
}

if ($filter4 != "" or $filter4value != "") {
    $result .= " and $filter4 LIKE'%$filter4value%'";
}

mysqli_query($con, $result);

answered Jun 02 '16 at 08:27

Saty

22,443
7
33
51

Still bad. You should append the WHERE parameters to the SQL query STRING, before you query it. – Viktor Koncsek Jun 02 '16 at 08:32
I know this is not enough. See my answer for a detailed description – JRsz Jun 02 '16 at 08:34
I know it is but it is not neccessary. You could also write `if(true){echo "Hello";}` or simple `echo "Hello";` – JRsz Jun 02 '16 at 08:45

score 0 · Answer 3 · answered Jun 02 '16 at 10:35

Few things I can see that give me pause here.

But firstly, to Answer your question:

The mysqli_query(); method executes the query you pass to it. In your code you're executing the basic query with mysqli_query(); before you check for and add the filters and their values. So no matter what the user selects on your drop downs, that query without filters will always be executed first. You need to build your whole query string first, then execute the query with mysqli_query(); after all the checking and possible additions to your query.

Additionally, things that might break things later on:

Also, you might want to use and/&& in your if statements. or like you have it will allow your SELECT statement to break if you have the $filter1value populated with a value and $filter1 not, it will test true in your if and the WHERE clause will be concatenated to your query with a value but no field.

TIPS: echo your SQL command out to see what your php code has generated to see if it's valid SQL before running it while you develop.

Myself and many other PHP developers prefer to use PDO to interact with Databases personally, but that's just my preference.

I wanted to give you a code example of how I would have done it, but I honestly would change too much of your code, so I left it.

Side-note: I'm not sure what levels of security you have on the inputs but what you're doing by including your input variables directly into you SQL command string like that leaves you open to SQL injection attacks. Very dangerous depending on who will be able to access your script. Perhaps try using a prepared statement with parameters to keep security up a bit. Please look at mysqli_prepare(); it's friend, the mysqli_stmt_bind_param(); method in this case where you're using mysqli. Always use prepared statements on the database libraries you use if you're accepting external inputs to your system. It'll save your job one day.

Just my two cents use it, don't use it. :)

On the side, @ those who are advocating for `WHERE 1=1`, it's horrible practise and hacky. I'd personally not encourage redundant code. — PieterLourens, Jun 02 '16 at 10:41

Xen · Answer 4 · 2016-06-02T08:28:27.073

-1

I guess you should add the filters on the query string before you execute the query, instead of adding the filter to the results? E.g.

$query = "SELECT * FROM $table WHERE 1=1";
if (...) {
    $query .= ...
}

// some more ifs...

$result = mysqli_query($con, $query);

edited Jun 02 '16 at 08:28

answered Jun 02 '16 at 08:27

Xen

164
6

Hmm, that could work, could you update your answer with how I would do this with my code in the question? – Shane Jun 02 '16 at 08:28
I suggest you try the idea out first and let us know again if you still can't get it working? – Xen Jun 02 '16 at 08:33
I dont know how to, newbie here :) – Shane Jun 02 '16 at 08:35
You can see JRsz 's answer then – Xen Jun 02 '16 at 08:39
That doesnt work with the rest of my code, if the user chooses no filters, the whole table id displayed – Shane Jun 02 '16 at 08:39
what is the expected output if the user chooses no filters? – Xen Jun 02 '16 at 08:42
The whole table is displayed – Shane Jun 02 '16 at 08:43
I saw your updated question, I think you need to merge the two while loops. – Xen Jun 02 '16 at 08:47
How do I do that please? – Shane Jun 02 '16 at 08:48
I believe JRsz will finish your work for you soon in the other answer. – Xen Jun 02 '16 at 08:50
However I think "WHERE 1=1" is still necessary otherwise your query with AND conditions will be without a WHERE clause. (Unfortunately I'm a newbie too so I can't comment on the other answer) – Xen Jun 02 '16 at 08:58
Exactly, Thats why I added it in – Shane Jun 02 '16 at 08:59

Search a MySQL table in PHP

4 Answers4