php mysqli select resultset is not showing anything

Question

Ive been trying to crack this for 2 hours, but something is wrong. I am very much used to doing things without mysqli but read that there is a recommended shift towards it from regular mysql commands. Hence am stuck with following:

<?php   
           $mysqli = new mysqli('localhost', 'admin', 'test123', 'kadmindb'); 
            if ($result = $mysqli->query("SELECT * FROM records WHERE '$queryType' = '$keyword'")) {
                while ($row = $result->fetch_object()) {
                    echo "<h2>Result:</h2><br>";
                    echo "ID: " . $row->id . "<br>";                
                    echo "Name: " . $row->cust_name . "<br>";
                    echo "Invoice No: " . $row->invoice_num . "<br>";
                    echo "Date: " . $row->date_recorded . "<br>";   
                }   

            }

        ?>

This code is shown in the page where the result of the query should be displayed but nothing is displayed. I checked that both keyword and queryType variables are set and they contain the correct values. Any help would be greatly appreciated. All am trying to do is: select statement to retrieve all the details based on invoice_num submitted.

EDIT: from help I received, I was able to get this working:

$query = "SELECT * FROM records WHERE ".$queryType. " LIKE  '%$keyword%' ";
                if ($result = $mysqli->query($query)) {
                    while ($row = $result->fetch_object()) {
                        echo "<h2>Result:</h2><br><hr/> "; 
                        echo "ID: " . $row->id . "<br>";                
                        echo "Name: " . $row->cust_name . "<br>";
                        echo "Invoice No: " . $row->invoice_num . "<br>"; 
                        echo "Date: " . $row->date_recorded . "<br>";   
                        echo "<hr/>";
                    }   

                }

Are you sure there's data to select? THis code will only output data if there actually is. — deed02392, Mar 15 '12 at 16:06
i was more worried if i got something wrong in the syntax i am using with mysqli...it is hell of a leap from old syntax based on OOP. — sys_debug, Mar 15 '12 at 16:08
and yes to answer your question the data is there and displayed by old query — sys_debug, Mar 15 '12 at 16:08
they are defined top of the page and as I said I checked they have sane values...I also hardcoded the values in the query but no luck — sys_debug, Mar 15 '12 at 16:13
Is `$queryType` a column in your records table? If so, then your WHERE clause is the problem. Also, have you tried capturing errors? — Brian Driscoll, Mar 15 '12 at 16:15
true @deed02392 it is a column...it is set by a switch statement based on a hidden field from the submitting page. It will either be invoice_num, cust_name, or date_recorded. When I compensated $queryType with name of column such as invoice_num, still didn't work. Hence I assume that's not the problem. I am testing the query u suggested — sys_debug, Mar 15 '12 at 16:19
@deed02392 that did work! not sure why though so need to work around there...thanks mate for the great help :D — sys_debug, Mar 15 '12 at 16:20
@sys_debug `SELECT * FROM records` is going to give you a completely unfiltered result set. — Brian Driscoll, Mar 15 '12 at 16:24
Hence we now know that everything is fine except your query parameters, so you need to check why they're either not being set correctly or that the data you have actually returns results by those conditions. :) — deed02392, Mar 16 '12 at 08:48

deed02392 · Accepted Answer · 2012-03-16T09:04:49.477

1

Are you sure there's data to select? This code will only output data if there actually is.

Make sure that $queryType and $keyword are set and have sane values that will yield a result.

Use var_dump($queryType) and var_dump($keyword) immediately before the query. Now check your output. Are they both strings? Run this query directly in PHPMyAdmin and check how many rows you get.

If you can't do that try echo'ing the number of rows returned along with the query values:

if ($result = $mysqli->query("SELECT * FROM records WHERE $queryType = '$keyword'"))
{
    while ($row = $result->fetch_object())
    {
        echo "<h1>Query WHERE '$queryType' = '$keyword' yielded {$result->num_rows} rows!</h1>";
        echo "<h2>Result:</h2><br>";
    ...

Note, you should not have single quotes around the column ($queryType), if you insist you should use backtick quotes (`) but it's unnecessary really - if you're that pedantic you should be using prepared statements.

Also be sure to filter them for any potentially dangerous values that could allow for sql injections. See: mysqli::real_escape_string

edited Mar 16 '12 at 09:04

answered Mar 15 '12 at 16:11

deed02392

4,799
2
31
48

any idea what should I do? I still didnt get to work? because otherwise I will need to create functions for all three possibilities each with a hardcoded query – sys_debug Mar 16 '12 at 05:13
hey deed I did something too that worked...I guess similar to yours really. But thanks for the great help! I know that it has a vulnerability of using % but doesn't matter because it is secured search, not public. my code updated above – sys_debug Mar 17 '12 at 05:11
The vulnerability is in SQL injection, I'm not sure what you mean by % (LIKE queries?) but injection is a big issue that will give you problems later even if not caused by mal intent for example the inability to search with apostraphes in the keyword. – deed02392 Mar 17 '12 at 21:51

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

Assuming that $queryType is the name of a column in your records table, then I believe the problem is your WHERE clause.

Rather than:

$mysqli->query("SELECT * FROM records WHERE '$queryType' = '$keyword'")

You should have:

$mysqli->query("SELECT * FROM records WHERE {$queryType} = '{$keyword}'")

Note that I've removed the single quotes around $queryType and have used complex (curly) syntax

Also, in the future you might want to try using an else block to trap errors:

       $mysqli = new mysqli('localhost', 'admin', 'test123', 'kadmindb'); 
        if ($result = $mysqli->query("SELECT * FROM records WHERE {$queryType} = '{$keyword}'")) {
            while ($row = $result->fetch_object()) {
                echo "<h2>Result:</h2><br>";
                echo "ID: " . $row->id . "<br>";                
                echo "Name: " . $row->cust_name . "<br>";
                echo "Invoice No: " . $row->invoice_num . "<br>";
                echo "Date: " . $row->date_recorded . "<br>";   
            }   

        }
        else
        {
               echo "Error: " . $mysqli->error;
        }

that didn't work brian..sorry to disappoint you :( i changed the brackets but nothing showed again — sys_debug, Mar 15 '12 at 16:44

php mysqli select resultset is not showing anything

2 Answers2