2

How can I join two tables together in drupal 7.

I have a simple database schema and I want to join it to the user table could someone give me a coded example of joining a custom build table to the user table.

If I take a basic table example

$schema['location'] = array (

'descritiption' => 'location',

'fields' => array (
    'locationID' =>  array(
        'description' => 'primary key for patients table',
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE),
 ),

'addressone' => array (
 'description'    => 'this shows the first line of the address',
    'type'         => 'TEXT'
),
'county' => array (
    'description' => 'this is a county list'

),
'postcode' => array (
  'deescription' => 'stores the postcode'  
),

    'primary_key' => array('locationID')



    );

How would I join this to the user table. I would like to query data from both tables. So when I add a record into the user table I want the custom table to have the data added as well so there is a link between the two tables.

here is a high level example

user table data

uid 1

username alex

visitor

vis id 1

firstname Alexander

lastname Brown

So what i want to happen is when i add data into the user table I build a relationship to what is entered in the visitors table. I know you can use a join which has kindly been explained to me but I am not sure what is meant by building the relationships in code.

  • Just to clarify: do you want to query data from both tables by using the join syntax or are you actually trying to combine the two tables together into one? – Madis Apr 6 '12 at 16:42
  • Does that table definition even make sense? Have you read the DBTNG documentation? – Letharion Apr 6 '12 at 16:47
3

JOINs are a query-level concept and don't really make a lot sense in the context of a schema definition.

I suspect what you're asking is how to add a foreign key from your location table to the users table. If so I'm afraid you're out of luck; Drupal does not enforce foreign key relationships, so you'll need to define and maintain your relationships at a code level.

There is a foreign keys property that can be applied to tables defined by the schema API, but it is for documentation purposes only, no foreign keys are ever added at the database level.

Just in case you are asking how to join a table in a query:

$query = db_select('location', 'l')
  ->fields('l');

$query->join('users', 'u', 'u.uid = l.uid_column_name');

$results = $query->execute();

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.