Merge data from multiple tables

[binarylime]

Hi,

 

My site is running on the Drupal CMS with a MySQL database.

 

I'm trying to produce a new table with firstname, lastname and email address by merging data from several existing tables.

 

The 'users' table stores unique id: 'uid' and email address 'mail'.

 

The complication comes when I try to merge this with firstname and lastname. The firstname and lastname fields are custom profile fields created by the CMS, and they themselves are stored as records in the database: see profile_fields table.

 

I've tried creating temporary table and looping through results with PHP and JOIN but I'm having no luck.

 

I've had some experience with MySQL but this is beyond me. I've attached a zip file with some printscreens of the relevant tables and would really appreciate some help.

 

Thank you!

 

 

 

[attachment deleted by admin]

[binarylime]

OK I've managed to achieve what I wanted to but with about 50 lines of PHP and MySQL and by creating and dropping tables - probably not the most efficient way of doing things. My code is below.....any suggestions?

 

Take a look at the screenshots in my original post - that should help explain whats going on.

 

<table>
<tr>
<td>ID</td>
<td>User Name</td>
<td>Firstname</td>
<td>Lastname</td>
<td>Email</td>
<td>Organisation</td>
</tr>

<?php

//Create goodies_firstname table
db_query("drop table if exists goodies_firstname");
db_query("create table goodies_firstname (uid int(5), firstname varchar(40), primary key (uid))");

$query = db_query("select profile_values.uid, profile_values.value from profile_values where profile_values.fid=4");

while ($result = db_fetch_object($query))
{
$firstname = addslashes($result->value);
db_query("insert into goodies_firstname (uid, firstname) values ('$result->uid', '$firstname')");
} 

//Create goodies_lastname table
db_query("drop table if exists goodies_lastname");
db_query("create table goodies_lastname (uid int(5), lastname varchar(40), primary key (uid))");

$query = db_query("select profile_values.uid, profile_values.value from profile_values where profile_values.fid=5");

while ($result = db_fetch_object($query))
{
$lastname = addslashes($result->value);
db_query("insert into goodies_lastname (uid, lastname) values ('$result->uid', '$lastname')");
} 

//Create goodies_organisation table
db_query("drop table if exists goodies_organisation");
db_query("create table goodies_organisation (uid int(5), organisation varchar(40), primary key (uid))");

$query = db_query("select profile_values.uid, profile_values.value from profile_values where profile_values.fid=1");

while ($result = db_fetch_object($query))
{
$organisation = addslashes($result->value);
db_query("insert into goodies_organisation (uid, organisation) values ('$result->uid', '$organisation')");
} 

//Create full user details table
db_query("drop table if exists goodies_full_user_details");
db_query("create table goodies_full_user_details (uid int(5), username varchar(40),  firstname varchar(40), lastname varchar(40), mail varchar(40), organisation varchar(40), primary key (uid))");

$query = db_query("select users.uid, users.name, goodies_firstname.firstname, goodies_lastname.lastname, users.mail, goodies_organisation.organisation from goodies_firstname, goodies_lastname, users, goodies_organisation where goodies_firstname.uid = goodies_lastname.uid and goodies_lastname.uid = users.uid and users.uid = goodies_organisation.uid order by users.uid asc");

while ($result = db_fetch_object($query))
{
$username = addslashes($result->name);
$firstname = addslashes($result->firstname);
$lastname = addslashes($result->lastname);
$email = addslashes($result->mail);
$organisation = addslashes($result->organisation);

db_query("insert into goodies_full_user_details (uid, username, firstname, lastname, mail, organisation) values ('$result->uid', '$username', '$firstname', '$lastname', '$email', '$organisation')");

echo "<tr><td>" . $result->uid . "</td><td>" . $username . "</td><td>" . $firstname . "</td><td>" . $lastname . "</td><td>" . $email . "</td><td>" . $organisation . "</td></tr>";
} 

db_query("drop table if exists goodies_firstname, goodies_lastname, goodies_organisation");

?>

</table>

 

Thank you!

 

[binarylime]

Additional Info:

 

MySQL client version: 4.1.11

Server version: 4.1.25-Debian_mt1

PHP 5

 

 

[Mchl]

Do you know how to use SQL joins? Perhaps that's the way you should go?