query from two tables?

Warboss_Alex

Hey everyone,

My problem is this. I'm building php forums, and, for site-specific reasons, users on the forums can have one 'main' name, their username which they register with, and also as many 'extra' names as they want.

For use on the forums, both the username and any extra names can have customised icons and signatures for that name.

Like, a user named 'Alex' might have Alex.jpg as his icon, and some text as a signature. Alex, might also have 'AlexTheGreat' as an extra name, with AlexG.gif as his icon, and some text2 as a signature.

On the posting screen, a user can choose which name to post under from a drop-down list. The script which handles the post must be able to get the userID from the users table (in which usernames are stored), and either the name, icon and signature from the users table, OR the name icon and signature from the extra names table.

Is there any way to do this with just one query? My old script checked if the name entered was a username or an extra name, and then took the information from whichever table was appropriate, but that took three queries.

Is there a way to do it with one? Any help would be most appreciated.

The extra names are linked to their owner username by the userID field, by the way ..

Thanks in advance!
Alex ...

Shrike

I think I would put the original name, plus any extra names in the same 2nd table. That should allow one query to return the required information.

Warboss_Alex

The variable would be the 'name'; the query should select information from a table which that name exists in.

Thinking again on it, you don't need a join between the tables. All you need is for the query to return the fields id, name, icon and sig from whichever table it finds the $name variable in.

Would this work?

SELECT icon, sig, id FROM users u, extras e WHERE (e.name = $posted_name OR u.name = $posted_name);

I can't check it right now. Would that select fields from both tables or just one, the one where it finds $posted_name ?

And the fields are called 'icon', 'sig' and 'id' in both tables, by the way ..

Shrike

That might work with a few syntax changes, but you might also get extra empty fields.

If you were to put the 'first' name in the 'extra names' table then you wouldn't have to worry about joining across tables.

Warboss_Alex

Maybe, but that'd go against normalisation, wouldn't it? Having 'username' in both tables is having it twice, therefore redundant data.

Oh, it's quite easy to solve. I just do a join across the two tables, getting the icon, sig, id etc from both tables with $post_name, then run some manual checks to see WHICH icon (since there'd be two in the returned array), sig etc. I want to use.

I was just wondering if there was some spiffy way to JUST select the icon, sig, id ONCE, with one query, and no further processing.

Why would I get empty fields, though? Would it look for 'id' in both tables, for example? And what syntax change d'you mean?

Shrike

Ah I meant remove it from the 1st table completely.

You can still tie the two tables together via ID 🙂

Warboss_Alex

Yes, that's what the id field is for ..

I still don't see how I'd get empty fields. Would I get two copies of 'icon', for example?

Shrike

I was thinking that the query might request each field from each table. In actual fact MySQL get's confused (ambiguity). You'd need to specify the table alias then the field e.g.

SELECT t1.icon, t1.sig, t1.id, t2.icon, t2.sig, t2.id FROM users AS t1, extras AS t2 ...

mrhappiness

and you should use some aliases for reading the values within php

(assuming you don't use the numeric indices)

Warboss_Alex

I had that from the start ..

But I'd either have to ..

do something like [.. t1.icon as icon, t2.icon as icon ..] to get just one row called 'icon', but that always overwrites t1.icon with t2.icon, whereas I might need t1.icon ..

or [.. t1.icon as t1icon, t2.icon as t2icon ..] and then run some more tests to see if we need $row['t1icon'] or $row['t2icon'] blah, blah, blah ..

I was hoping there was a third way, which ONLY returned icon etc. from the table I wanted ..

Shrike

I think that would only be possible with sub-querying - could be wrong though 🙂

Warboss_Alex

Doesn't a subquery return data, though?

Now if it returned a reference to a table, though, then we'd be cooking with charcoal!

tsinka

Hi,

maybe a slight redesign of the drop down field and/or the extras table will help, like this:

Add an auto_increment field to extras so each record gets a unique id.

When populating the drop down you could first read out the data of users and do something like

Then read out the corresponding data of extras including the auto_increment id and do

In the script that does the POST stuff examine the value submitted. If it starts with u you know that you need to fetch the record from users with id=$id. If it starts with e you know that you need to fetch the record from extras with auto_increment id $autoid.

This would require two query strings but only one query execution.

Thomas

tsinka

One error in my last post since you need to read data from users in either case but you can still do it with one query using a join.

Something like:

$postedUser = $_POST['user'];
$postedID = substr($postedUser,2);
$sql = "";

if (strstr($postedUser,"e_") === false) {
  $sql = "SELECT somefields,icon,sig,id FROM users WHERE id = '''.$postedID."'";
} else {
  $sql = "SELECT somefieldsfromu,e.icon,e.sig,e.id FROM extras e,users u WHERE e.autoid=$postedID AND e.id = u.id";
}

mysql_query($sql);

Thomas

Warboss_Alex

Woo! That's an idea, tsinka.

Of course, I needn't use the ids, I could just do something like ..

<option value="u$username">$username</option>
<option value="e$extraname">$extraname</option>

Instead of all the ids, since the names are unique fields anyway. And then I'd just get the first character of the $_POST field, and that'd tell me where to get the data from.

An excellent idea, indeed. And I could do a UNION query to get the username and the extra names, using the user's id, which is common across both tables ..

Sounds good to me, anyway!

Thanks!
Alex ...

tsinka

Hi,

I edited my last post to insert an example but seems like I was to slow 🙂

I'd prefer to use numeric id's since queries are faster in many cases.

But this is a matter of what you like more.

Thomas

Warboss_Alex

In fact, tsinka, it's simpler than that!

For the query, I only want the id, icon and sig fields. The id'd be the same across both tables, but we wouldn't use that in the WHERE clause because the name we were checking is unique.

I'd do the query like this:


$check = substr($_POST['name'],  2);

if ($check = 'u') { $table = 'users'; $field = 'username'; }

else { $table = 'extras'; $field = 'extraname'; }

$name = substr($_POST['name'], 2, strlen($_POST['name']));

$sql = "SELECT icon, sig, id FROM $table WHERE $field = '$name'";

Thanks! 😃