0

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..

  • also if you do queries like this use Parameterized Queries. – tenken Mar 7 '14 at 16:01
  • What 'start_processing_agent' and 'end_processing_agent' are? Because i can't see how the first query can work – Astrayus Mar 7 '14 at 16:06
  • @Astrayus They are both fields in the table. – slpcc63 Mar 7 '14 at 16:14
3

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.

  • Thank you for the help. I don't understand the 't'? Also, what you have as my $values are the field_names, I am trying to search for either/or, would I do a second condition or put in || and put both in the same condition? – slpcc63 Mar 7 '14 at 16:17
  • The 't' is just the alias for your selection table. If you don't mind my saying, you're using IN in a peculiar way there. Usually one would express that as WHERE (start_processing_agent = ? OR end_processing_agent = ?). IN is usually used the other way round, to see if a list of given parameters is in a particular field. If you re-think that part of the approach, it should be easy enough to construct your query using db_or(). Maybe give it a go, and edit your question with the code if it doesn't work? – Clive Mar 7 '14 at 16:33
-1

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

  • Link-only answers are normally not considered answers, on Stack Exchange. This answer is also wrong: db_select() is used for dynamic queries, and not all the queries are dynamic. – kiamlaluno Mar 7 '14 at 17:07
-1

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();
  • 1
    I would rather not concatenate strings to build SQL queries, especially when the variable contains input from users. – kiamlaluno Mar 7 '14 at 17:09
  • Yep, well, only with user input. – Astrayus Mar 7 '14 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.