PHP query returns no results

Question

I'm probably missing something obvious but when I try to execute this query, it returns no results. I plugged it directly into MySQL and also tried replacing the variable with a valid row value and I get the correct output. When I use a variable, it gives me no results. Anyone have any thoughs?

    $query = "SELECT title FROM le7dm_pf_tasks WHERE project = (SELECT id FROM le7dm_pf_projects WHERE title = '".$ws_title."') ORDER BY title DESC LIMIT 1";

    $result_query = mysql_query($query) or die("Error: ".mysql_error());

    while ($row = mysql_fetch_assoc($result_query)) {
        $result_title = $row['title'];
    }

    $result_title = substr($result_title,0,6);
    echo $result_title;

First, don't do this. You have a SQL injection vulnerability here. There is a possibility that you have "safe mode" or something similar turned on that is changing your input to try to remedy SQL injection errors, but I am uncertain without knowing your input. — Andrew, Apr 30 '12 at 22:32
@zerkms var dump comes up with the following: string(317) "SELECT title FROM le7dm_pf_tasks WHERE project = (SELECT id FROM le7dm_pf_projects WHERE title = 'AAA') ORDER BY title DESC LIMIT 1" The input is the title of the page I'm on. It is always known and never user inputted. — Dave Rottino, Apr 30 '12 at 22:42
The logical answer is that your variable doesn't contain what you think it contains. — Sam Dufel, Apr 30 '12 at 22:42
@Dave Rottino: if you perform that query in PMA - does it return an expected result? — zerkms, Apr 30 '12 at 22:45
try to debug $ws_title before query, check whether it is empty or not — Moyed Ansari, May 01 '12 at 07:04
$ws_title will always have the project name as the title. I've echoed it out before the query and it contains AAA. If I do a query with AAA instead of $ws_title, it comes out fine. Otherwise, it's an empty result. Also, I'm PMA, it returns AAA275 which is the correct job number. — Dave Rottino, May 01 '12 at 14:10

score 1 · Answer 1 · answered May 01 '12 at 01:57

Your SQL could do with some rework (though not the reason for your issue). No need for the nested select (which can also cause an error if it returns > 1 row). Try a join.

$sql = "
    SELECT title FROM le7dm_pf_tasks t
        INNER JOIN le7dm_pf_projects p ON t.project = p.id
    WHERE p.title = '{$ws_title}'
    ORDER BY title DESC LIMIT 1
";

You are also iterating over an unknown number of rows using the while statement. And then you exit and attempt a substring. How do you know that the last row iterated in the while had a value.

Try outputting $result_title inside the while loop itself to confirm data.

echo $result_title;

If you truly only have a single row, there is no need for the while loop. Just do

$row = mysql_fetch_assoc($result_query);

score 0 · Answer 2 · answered May 01 '12 at 14:34

0

strip_tags($ws_title); - is what did it! The title was wrapped in an anchor tag that linked to that particular project page.

Thanks for all the good suggestions though. I'm gonna use some of them in the future when bug testing.

answered May 01 '12 at 14:34

Dave Rottino

1,037
3
22
49

PHP query returns no results

2 Answers2

Linked