Mark Hensler — Sat, 20 Mar 2004 01:38:28 +0000

Yes. If you move the username's to another table, you can remove the original field after you update all your SQL to use the new table.

Also note, if any two tables in a join query contain fields with the same name, you will need to use table.field syntax for that field.

m3rajk, I'll make a new thread about my situation. But basically, I've changed careers and I've lost a lot of free time.

m3rajk — Sat, 20 Mar 2004 00:49:50 +0000

i wonder where mark is. the thread's been here for over 24 hours i think without a responsse from him. i've been to about a dozen sites. no one seems to be better than him with db answers. in the past the response time varied from me lucking out and being here when he's here to getting a response sometime the next day.

i've only seen one borads owner that's more involved than mark. it's a windows os issue board and the owner is there answering everything new every few hours (i swear he must have found a way to get paid for it). my guess is that whatever mark does for a job has him bogged down lately. i know i've ben working 50+ hour weeks due to people being on vacation lately. at least i'm hourly i guess...

m3rajk — Sat, 20 Mar 2004 00:46:03 +0000

ok. here's an example. how i call the top users....

<?php
if($gen==='women'){
  $selopts='<option value=\"all\">Men & Women</option><option value=\"women\" selected=\\"selected\\">Ladies</option><option value=\"men\">Men</option>';
  $findtops=mysql_query(\"SELECT memname,score,gender FROM tops WHERE gender='F' ORDER BY score DESC\", $db); # get women by score
}elseif($gen==='men'){
  $selopts='<option value=\"all\">Men & Women</option><option value=\"women\">Ladies</option><option value=\"men\" selected=\\"selected\\">Men</option>';
  $findtops=mysql_query(\"SELECT memname,score,gender FROM tops WHERE gender='M' ORDER BY score DESC\", $db); # get men by score
}else{
  $selopts='<option value=\"all\" selected=\\"selected\\">Men & Women</option><option value=\"women\">Ladies</option><option value=\"men\">Men</option>';
  $findtops=mysql_query('SELECT memname,score,gender FROM tops ORDER BY score DESC', $db); # get people by joining date
}
?>

to change these to use the username feild of the users table (the main table) it would be:
$findtops=mysql_query("SELECT username,score,gender FROM tops INNER JOIN users ON tops.memuid=users.uid WHERE gender='F' ORDER BY score DESC", $db);
or:
$findtops=mysql_query("SELECT username,score,gender FROM tops INNER JOIN users ON tops.memuid=users.uid WHERE gender='M' ORDER BY score DESC", $db);
or:
$findtops=mysql_query('SELECT username,score,gender FROM tops INNER JOIN users ON tops.memuid=users.uid ORDER BY score DESC', $db);
respectively. and then i can go and remove the memname feild from tops, thus eliminating a redundancy?

forwardtrends — Fri, 19 Mar 2004 23:45:30 +0000

oh.. yeah that might be tough lol

m3rajk — Fri, 19 Mar 2004 23:34:25 +0000

only issue with that-- i'm using mysql and php

forwardtrends — Fri, 19 Mar 2004 20:25:41 +0000

yes you should be able to implement it easily.. an easy way of validating your SQL strings (at least what I do) - is connent to your database via Access (ODBC connections) - then you can use the query designer to visually create your query, convert to SQL mode and theres your code (basically)

m3rajk — Fri, 19 Mar 2004 19:59:19 +0000

yes! i beleive that's what i was seeking. the correct format. i've seen a variety of examples, all specifiying left and right inner and outer and it had me unsure of what was going on. i wanted to know that i can have an archetype (such as what you provided) that would work.

now i can stremaline the db and modify the scripts so everything is more efficient.. maybe give users the ability to change hteir usernames....

forwardtrends — Fri, 19 Mar 2004 02:18:25 +0000

This is the basic format I'd use.. if I'm right you don't need to create a new table with this join, you just need to pull info from two..

SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

Is this what you needed?