I'm new to object oriented php. Trying to query database but not getting any results

Question

I am trying to query a table in my DB and am not able to get any results back. Here is the code I am using:

function get_agent_info($agent) {
        $query = db_query("SELECT * FROM tls_connect_stage_timer WHERE '".$agent."' IN ('start_processing_agent' || 'end_processing_agent')");
        $rows = array();
        foreach ($query as $result) {
            $rows[] = array(
                $result     -> applicant_id,
                $result     -> stage,
                $result     -> event_start,
                $result -> event_end,
                $result     -> start_processing_agent,
                $result     -> end_processing_agent,
                $result     -> start_request_ip,
                $result     -> end_request_ip,
                $result     -> gwf,
            );
        }
        print_r($rows);

    }

I have already used a method like this to get info:

$results = db_query("SELECT * FROM tls_active_applicants WHERE `".$searchParams."`='".$input."'");
$rows = array();
                 foreach($results as $result){
                    $rows[] = array(
                        $result -> first_name,
                        $result -> last_name,
                        $result -> gwf,
                        $result -> visa_type,
                        $result -> current_stage,
                    );
                 }

The second one works fine, where with the first one, if I print_r($rows) it returns an empty array. Why is the foreach loop starting in the first example but not the second? I've tested the query, and it works fine. Also any links that could help me understand how the foreach loop is working would be greatly appreciated. I assumed that since it's saying $query as $result, I would be able to pull a single value saying something like $query->stage but that didn't work. I'm assuming because there are several entries I would need to do something like $query[0]->stage but I don't really know how that works when it comes to calling properties..

Answer 1

I won't address your question specifically...because you're doing it wrong, and that should stop here :)

This is very, very bad:

db_query("SELECT * FROM tls_connect_stage_timer WHERE '".$agent."' IN ('start_processing_agent' || 'end_processing_agent')");

You should never concatenate data into an SQL string, you'll leave yourself ripe for injection attacks. This one is even worse:

db_query("SELECT * FROM tls_active_applicants WHERE `".$searchParams."`='".$input."'");

As you're liable to injection attacks from both the field name and the argument.

If you want to use dynamic field names you should use the SelectQuery class, accessible via db_select(). E.g.

$values = array('start_processing_agent', 'end_processing_agent');
$query = db_select('tls_connect_stage_timer', 't')
  ->fields('t')
  ->condition($field_name, $values);

$rows = $query->execute()->fetchAll();

You'll need to make some changes to the above for your specific query, but that's the general idea.

Answer 2

You are messing things up. Build a query with db_select. https://drupal.org/node/310075

Answer 3

You are using the IN statement wrong.

If $agent has to be considered as a value, you should use the '=' like for the second query.

If $agent is a field name of your table instead, the query should be like this

"SELECT * FROM table WHERE $agent IN ( SELECT field FROM table)"

On a side note, when you create a string using the double quotes, you can directly use simple variables inside, like in the example above, as opposed to:

" WHERE ".$agent." IN "

About your use of foreach instead, it seem that what you are trying to do can be achieved using

$rows = $query->fetchAll();