SQL query with array
timlie - February 23, 2009 - 19:49
Hi,
I am searching for an SQL query for the following:
I store chosen user roles in a variable called account_roles.
Now I want a SELECT query that joins 3 tables, one of the tables is the user_roles table. In this select query one of the ON statements is account_role != user_role
I don't know how I can achieve this because account_roles could be an array (let's say authenticated and a custom role is stored in it).
How do I use this array in an SQL query??
Thanx a lot!
http://www.w3schools.com/sql/
http://www.w3schools.com/sql/sql_in.asp
Have a look at the SQL "IN" operator. You can use this to restrict roles to an array with a little string manipulation.
Thanx! What I try to do is
Thanx!
What I try to do is the following:
I am writing some custom code for the account_reminder module. I'd like to have a feature where I can choose which roles should get this reminder.
At this point I am struggeling with the query.
Account reminder has the following query:
$result = db_query("SELECT u.*, COALESCE(ar.msg_cnt, 0) AS msg_cnt, COALESCE(ar.msg_block, 0) AS msg_block, COALESCE(ar.last_reminder, %d) AS last_reminder FROM {users} u LEFT JOIN {account_reminder} ar ON u.uid = ar.uid WHERE u.uid <> 0 AND u.status = 1", time());I am trying to nest an other join (users_roles table) with the where clause, users_roles.rid IN $roles, but I don't get it to work.
Someone an idea to point me in the right direction?
Thanx!
http://www.lievenscomputerservice.be
Could you explain a little
Could you explain a little bit more on "with a little string manipulation". I managed to make the query with the 3 tables but I am still struggling over the SQL "IN" operator.
Thanx!
http://www.lievenscomputerservice.be
The syntax look like: SELECT
The syntax look like:
SELECT name, city FROM contacts WHERE city NOT IN ('London','New York');Assuming the array of roles to exclude is in the variable $user_roles ...
<?php
// 1. create a string that looks like: 'administrator','manager','beefeater' from the array
$roles_to_exclude="'".implode("','",$user_roles)."'";
// 2. use NOT and IN to exclude those roles
$sql_string="SELECT ... WHERE ... AND account_role NOT IN ({$roles_to_exclude});";
// 3. run the sql string as you normally would.
?>
Thanx a lot! Now it works!!!
Thanx a lot!
Now it works!!!
http://www.lievenscomputerservice.be
This is often wrong
This is often wrong. While in this case user_roles doesn't present an SQL injection hazard, never add 'SQL data' directly in you SQL queries. Instead, build the right amount of placeholders and pass all arguments in one array to db_query:
<?php
$excluded_roles = array('foo', 'bar', 'baz');
// Get a string with sufficient placeholders for the schema type 'varchar'.
// In our case this gets us the string: '%s', '%s', '%s'
$placeholders = db_placeholders($excluded_roles, 'varchar');
$sql = "SELECT ... WHERE whatever = %d AND something NOT IN ({$placeholders}) AND somethingelse = %d";
// Stuff everything in one array, order is the placeholder order.
$args = array_merge(array($whatever_value), $excluded_roles, array($somethingelse_value));
// We use the all values in one array form of db_query
$result = db_query($sql, $args);
?>
--
The Manual | Troubleshooting FAQ | Tips for posting | How to report a security issue.
Thanks, good (great) tip.
That's a much more "Drupal Way" of covering all bases (security wise).
My tiny head barely negotiates SQL and PHP. I suppose most of my custom code would need cleaning prior to public release (I always assume I'm the only one running the code).
Thanks for the sample code guru Heine! It seems db_query with arguments is a powerful function.
Hey Freddy
I was just trying to help, but my dopey brain doesn't have the Drupal skillz of a Heine.
I suggest you try and rework your code to conform to that Drupalish db_query magic.
At that point, I reckon you'd not only have a solution, but a massively over engineered behemoth capable of beating down script kiddies and North Koreans.
Good luck.