PHP sql not showing result

Question

I've 2 input box, 'person.tag_name' and 'person.created_by', which I post 'person' from .js to .php.

The problem now is that it requires both input box to be fielded only the data will show up, however on my sql statement I only match against createdBy and tag_name wasn't use at all.

In summary:

If only 1 of 2 input box is fielded I will not received data.
I will only receive data if 2/2 input box is fielded.
Tried removing $tagName = $request->tag_name; and it will return data

php code:

$postdata = file_get_contents("php://input");
$request = json_decode($postdata);    
$createdBy = $request->created_by;
$tagName = $request->tag_name;

if (!empty($createdBy) && !empty($tagName)){
    $Where = "created_by LIKE '%$createdBy%' AND name LIKE '%$tagName%'";
}    
else if (!empty($createdBy)){
     $Where = "created_by LIKE '%$createdBy%'";
}    
else if (!empty($createdBy)){
     $Where = "name LIKE '%$tagName%'";
}



// Create connection
$con = new mysqli($servername, $username, $password, $db);

// Check connection
 if ($con->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM test.test_tag WHERE deleted_by is null AND $Where;" ;

$qry = $con ->query($sql);

$data = array();

if ($qry->num_rows > 0) {
    // output data of each row
    while($row = $qry->fetch_object()) {
        $data[] = $row;
    }
} else {
    $data[] = null;
}

$con->close();

Please assist.

You are not running an SQL query in this code. Neither are you echoing anything. — Gerald Schneider, Aug 01 '16 at 10:16
You need to create an SQL connection and use it... http://www.w3schools.com/php/func_mysqli_query.asp — M. I., Aug 01 '16 at 10:17
Also note that creating a query like that allows SQL injection attacks. — Gerald Schneider, Aug 01 '16 at 10:17
Hi, i'm making sql query, on the top i'm just showing part of my code the main relevant part, and as i said, i'm able to retrieve data just that i don't if both input box are left empty. — nicker, Aug 01 '16 at 10:19
That's not "only the relevant part", it's useless without context. — Gerald Schneider, Aug 01 '16 at 10:20
I think the question the OP is actually asking is _How do I add another `AND` clause to this query_ If thats the case **go read the manual thats SQL 101** If you dont know how to do that you probably should look for another task for today, involving a pencil — RiggsFolly, Aug 01 '16 at 10:22
Hi RiggsFolly, sorry that's not what i'm asking. Thanks for replying thou — nicker, Aug 01 '16 at 10:24
**FINALLY WE GET THE FULL CODE AND IT BEGINS TO MAKE SENSE** — RiggsFolly, Aug 01 '16 at 10:31
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared statement and parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Aug 01 '16 at 10:34
try an "echo $sql;" to show the query with date and run the query under an sql client to see if there is a result — Mimouni, Aug 01 '16 at 10:34
Hi, riggs actually that wasn't the code, i just added the `if else $where statement` so that you guys know adding an **and** in sql statement isn't what i'm asking. anyway all that matters is it made things clearer. Thanks — nicker, Aug 01 '16 at 10:34
I suspect `$request->tag_name` isn't really empty when you expect it to be. — Barmar, Aug 01 '16 at 10:39
Hi, thanks, i'd tried writing the $sql to the .txt, I've check the statement is ok, i copy and paste it on MySQL and execute it works fine. — nicker, Aug 01 '16 at 10:46
change your last `else if` clause from `!empty($createdBy)` to `!empty($tagName)`.It ,ay be worth checking you haven't done something similar in the original code? — Ryan Vincent, Aug 02 '16 at 01:47

score 0 · Accepted Answer · answered Aug 02 '16 at 02:49

I've found a way to fix it by including if condition to check $request->tag_name exist before assigning $tagName to it.

However I don't know why without this condition the data will not be retrieve as I'd check the code will still continue to run the sql with correct statement.

if (!empty($request->created_by)){
    $createdBy = $request->created_by;
}

if (!empty($request->tag_name)){
    $tagName = $request->tag_name;
}

Thanks for all your replies.

score -1 · Answer 2 · edited Aug 01 '16 at 10:23

-1

Data receive u mean from database, if

please check your query

$sql = "SELECT * 
        FROM piwik.piwik_tag 
        WHERE deleted_by is null 
          AND created_by LIKE '%$createdBy%';";

it's has only created_by, there is no tag_name in query.

SO here it's says only created_by is required to fetch data from database

edited Aug 01 '16 at 10:23

RiggsFolly

93,638
21
103
149

answered Aug 01 '16 at 10:21

AmmyTech

738
4
10

Actually "Nicker" is not clear, let him clear the question. – AmmyTech Aug 01 '16 at 10:30
Hi, thanks, and yes but this is not my point, my point is in my query i'm not even using tag_name, but why do i need to field in the tag_name input box to retrieve data. – nicker Aug 01 '16 at 10:31
yhea great, so you mean if data are retrieved with created_by why I need tag_name. No issues don't post tag_name. – AmmyTech Aug 01 '16 at 10:36
one more thing if you need from db with tag_name and created_by use the query with one more AND clause of tag_name i.e AND tag_name like '%$tag_name%' – AmmyTech Aug 01 '16 at 10:37
Hi, ok see my updated code now, it satisfy your concern, I've added the tag_name in query. But the issue still persist, so that's what i'm saying the tag_name isn't of concern. – nicker Aug 01 '16 at 10:39
ok, please change name to tag_name in table too and use tag_name instead of name, as name is a keyword, and please let me know what u need in output? if you please – AmmyTech Aug 01 '16 at 10:46

PHP sql not showing result

2 Answers2