Page 1 of 3

using 2 databases

Posted: Sat Dec 30, 2006 3:01 pm
by ekosoftco
i have a website im making for a game, and i have 2 databases, one for guilds and one for the users. i was wondering if there would be a way to do what i need like this. i have a field in the user database that is called guild, and either has the guild name or is null. i need it to where that person can login, and be able to go to the guild section of the site and have the guild they are in pop up, so i was wondering if theres a way you can use the guild field in the user database, take the guild name (ex: SLX), and search for the guild SLX in the guild database, find it, and have the user view thier guild's info, rank, ect.

Posted: Sat Dec 30, 2006 3:02 pm
by ekosoftco
i forgot to add, i also need to know if theres a way when filling out a form to create a guild if you can send the guild name to the user database guild field, and the other information for the guild to the guild database

Posted: Sat Dec 30, 2006 3:59 pm
by Kieran Huggins
I think what you want is ONE database with TWO tables... then you just use a JOIN in SQL.

Read up on SQL some more, my soft friend.

Posted: Sat Dec 30, 2006 10:22 pm
by ekosoftco
Ok, i read up some on joins and things, but all i read or could find were examples or tutorials on how to match info from 2 tables and display them on a table, matched together. I was wondering if theres a way i can have the guild field in the user table say SLX, and the name field in the guild table with SLX be found, then the user be sent to the Guild Control Panel for SLX members only. Also, i was wondering if i could get some help on the best way to use ranks with this too in the guild, for ranks that can recruit, edit members, and others who cant do either. Im lost on which way to go :x

Posted: Sat Dec 30, 2006 11:16 pm
by feyd
Why is this in PHP - Code? Moved to Databases.

Queries are allowed to span databases too.

Code: Select all

SELECT foo FROM someDatabase.someTable WHERE bar = 1

Posted: Sun Dec 31, 2006 2:48 am
by ekosoftco
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Im not exactly getting how do to the query with that, i read a little on the bar =1 thing but dont quite understand. my database is loginphp, the table for a guild would be slx, i think it would be best for each guild to have its own table, but if thats not a good idea, please let me know, the guild i created is SLX, so i have something like this

Code: Select all

$result = mysql_query("SELECT . $_SESSION['Guild'] . FROM loginphp WHERE bar = 1") 
or die(mysql_error());
$row = mysql_fetch_array( $result ); //set $row to result
so the result is taking the session from the guild the member is in, under the guild field in the user table, and takes that value, which is SLX, but thats where i get lost, and then start to wonder if i even got that right. Ive messed around with php off and on, i know enough to make an... ok site, but im still pretty new to databases and learning as i run into things.
im actually really kinda getting lost on how to do guilds in a database at all.
i need it where someone can be in a guild and go to guild cp and see members, and if their rank in the guild lets them, be able to add new members, or edit members. basically i need them to have to login, go to guild cp, have their guild cp specified for the guild theyre in (EX: SLX), then, say the leader logs in, have the ability to view all members, some guild info, add/remove members, and change their ranks. if anyone has any idea on how to go about this, please help. :/ ive confused myself too far i think...


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Mon Jan 01, 2007 3:55 pm
by ekosoftco
:(
anyone por favor?

Posted: Mon Jan 01, 2007 4:05 pm
by jayshields
I only read to the code, but I have some pointers for you.

- You shouldn't be dynamically creating tables (for the guilds). Have a table called guilds and add to that when a new guild is created.
- You need to read up on how to build SQL queries. You do not SELECT * FROM databasename. You SELECT * FROM tablename.
- feyd gave you an example WHERE clause, which was bar = 1. This is not an essential part of the query; you won't need it in yours.

If you're trying to fetch guild information you might end up with a query like this:

Code: Select all

SELECT * FROM `guilds` WHERE `guild_id` = $_SESSION['Guild']

Posted: Mon Jan 01, 2007 5:48 pm
by ekosoftco
loginphp is the table name inside the databse 988_login
i have a guild field for the members to specify what guild they are in.

Code: Select all

<?php
	  include("configlogin.php");
	  if($_SESSION['Uname'] == "")
	  	{
	  	echo error();
	  	}
	  	else
	  	{	 
	  	$result = mysql_query("SELECT * FROM guilds WHERE guild_tag = " . $_SESSION['Guild'] . "") or die(mysql_error());
	  	$row = mysql_fetch_array( $result ); //set $row to result
	  	$enc = $_SESSION['Guild'];
	  		if($row['guild_tag'] == $enc)
			{
			echo "<center><font color=CCCCCC><b>Welcome to " . $_SESSION['Guild'] . " " . $_SESSION['Uname'] . "!</b></font>";
			}
	}

function error()
{
echo "<b>You must login to use this feature!</b>";
}
?>
thats the code im using, but i keep getting this error
"Unknown column 'SLX' in 'where clause"
so what i did was take the query and put it in phpmyadmin

Code: Select all

SELECT * FROM guilds WHERE guild_tag = SLX
SLX is the name of the sample guild im using. I get the same error, but when i put this in the phpmyadmin

Code: Select all

SELECT * FROM guilds WHERE guild_tag = "SLX"
it works, so i was wondering how to make the query in the php code work like the second query i put in phpmyadmin, i tried a few things, some dumb things like putting extra "" in it, which obviously didnt work

Posted: Mon Jan 01, 2007 6:19 pm
by jayshields
You need to read up on some fundamental rules of PHP.

In this particular case, you will need to choose one of the following options:
- Use single quotes around the string in your query.
- Use single quotes around your entire query string.
- Leave the $_SESSION variable inside the query string, but surround it with curly brackets (make sure the query string is in double quotes).
- Use the escape character on your nested double quotes.

Search for the page on strings in the PHP manual.

Posted: Mon Jan 01, 2007 8:51 pm
by ekosoftco

Code: Select all

<?php
	  include("configlogin.php");
	  if($_SESSION['Uname'] == "")
	  	{
	  	echo error();
	  	}
	  	else
	  	{	 
	  	$result = mysql_query("SELECT * FROM `guilds` WHERE `Tag` = '{$_SESSION['Guild']}'") or die(mysql_error());
	  	$row = mysql_fetch_array( $result ); //set $row to result
	  	$enc = $_SESSION['Guild'];
	  		if($row == $enc)
			{
			echo "<center><font color=CCCCCC><b>Welcome to " . $_SESSION['Guild'] . " " . $_SESSION['Uname'] . "!</b></font>";
			}
	}

function error()
{
echo "<b>You must login to use this feature!</b>";
}
?>
This is my code now, the error went away, and now its just bringing up a blank page, not showing the

Code: Select all

echo "<center><font color=CCCCCC><b>Welcome to " . $_SESSION['Guild'] . " " . $_SESSION['Uname'] . "!</b></font>";
i know im coding something wrong here, i dont know why im missing what im missing...its probably simple, as usual for me. ive got the whole site done besides this stuff :(

Posted: Mon Jan 01, 2007 8:54 pm
by feyd
hint: $row will likely never equal $enc.

Posted: Mon Jan 01, 2007 9:51 pm
by ekosoftco
yep, thanks. i am officially retarded though.
i have a different question now. in the guild table, there is a field i have called members. is there a way to have every members name in one field, then be able to pull out each individual name onto a row? like the members field has cyril, decus, jim, tank, joe in it, id need it put out on the site like
cyril
decus
jim
tank
joe
there is also the field in loginphp table for members called guild, that specifies what guild they are in if the above way cannot work. im starting to hate this i keep getting stumped too easily

Posted: Mon Jan 01, 2007 10:47 pm
by volka

Code: Select all

CREATE TABLE `guilds`(
  guildid INT(11) auto_increment,
  guildname VARCHAR(32) NOT NULL,
  guildtag VARCHAR(8) NOT NULL,
  PRIMARY KEY(`guildid`)
);

CREATE TABLE `users`(
  userid INT(11) auto_increment,
  username VARCHAR(32) NOT NULL,
  userpass VARCHAR(32) NOT NULL,
  PRIMARY KEY(`userid`)
);

INSERT INTO guilds (guildid,guildname,guildtag) VALUES
  (1,'Guild1', '#1'),
  (2,'GuildABC', 'ABC');

INSERT INTO users (userid,username,userpass) VALUES
  (1,'Justus', 'xyz'),
  (2,'Peter', 'xyz'),
  (3,'Bob', 'xyz');
two guilds and three users.
Justus(userid=1) and Peter(userid=2) are members of Guild1(guildid=1)
Bob(userid=3) is a member of GuildABC(guildid=2)
All you need to store are the numbers
Justus -> (1,1)
Peter -> (2,1)
Bob -> (3,2)
in a separate table

Code: Select all

CREATE TABLE `guildmembers`(
  guildid INT(11) auto_increment,
  userid INT(11) auto_increment,
  KEY(`guildid`),
  KEY(`userid`)
);

INSERT INTO guildmembers (userid,guildid) VALUES
  (1, 1),
  (2, 1),
  (3, 2);
see http://www.w3schools.com/sql/sql_join.asp and http://dev.mysql.com/tech-resources/art ... ation.html

Posted: Tue Jan 02, 2007 12:15 am
by ekosoftco
ok i made an extra table, and i read both those websites, but it seems theyre telling you how to link 2 things of information onto a table. Im just trying to pull all the members of a guild onto a list. :/
im willing to bet im missing something really easy, but im missing it nonetheless
:(