MySQL user info help

Show off new creations, get help, or just chat about all aspects of web development and design.

MySQL user info help

Postby caseyw on Wed Dec 23, 2009 1:05 am

Hi,

I'm making a small PHP and MySQL site sort of thing, right now I'm have a bit of a setback. I can't figure out how to properly link items.

For example. Say I wanted to display a user's name, then his email address. The username is in a table named 'users' under a field named 'username' and the email address is a table named 'users_information' under the field 'email'.

How would I go about selecting and displaying the information for one user?

I've tried looking at the FULL JOIN command, but I'm not sure how to use it properly. I've also looked at the SQL view but I'm not sure how to use that properly either.

Thanks for any help.
User avatar
caseyw
Regular
Regular
 
Joined: Sun Feb 01, 2009 2:02 am

Re: MySQL user info help

Postby Head on Wed Dec 23, 2009 1:16 am

You should really use one table i think.
like "id","username","email"

i don't know if got users logging in and how you will mange profile viewing but this is the basic code i think of right of the bat like this.

Code: Select all
$username = "head"; (depends on if you use session etcs.)
$getuser = mysql_query("SELECT username FROM users WHERE username='$username'");
$username2= mysql_result($link, 0);

$getemail = mysql_query("SELECT email FROM users WHERE username='$username'");
$email= mysql_result($link, 0);

Image
User avatar
Head
1337 p0st3r
1337 p0st3r
 
Joined: Fri Apr 29, 2005 3:21 pm
Location: Sweden ;(

Re: MySQL user info help

Postby caseyw on Wed Dec 23, 2009 1:22 am

I'm not sure, but that doesn't answer my question. My problem is that the login information is in one table (users) and their information (ex: email address) is in another (users_information). And I need a way to link the info from one (using their username) so when I call for a username and I can also get the corresponding email address and other info. I think I forgot to mention that in the initial post.
User avatar
caseyw
Regular
Regular
 
Joined: Sun Feb 01, 2009 2:02 am

Re: MySQL user info help

Postby Head on Wed Dec 23, 2009 1:24 am

caseyw wrote:I'm not sure, but that doesn't answer my question. My problem is that the login information is in one table (users) and their information (ex: email address) is in another (users_information). And I need a way to link the info from one (using their username) so when I call for a username and I can also get the corresponding email address and other info. I think I forgot to mention that in the initial post.


Sorry. if you use ids and auto-increment you might be able to check them.
but really you should merge them into make shit like this alot easier since ids are really trustworthy.

EDIT: Also i don't see any reason to have those in separate tables since obviously username pass and email goes together.
Image
User avatar
Head
1337 p0st3r
1337 p0st3r
 
Joined: Fri Apr 29, 2005 3:21 pm
Location: Sweden ;(

Re: MySQL user info help

Postby caseyw on Wed Dec 23, 2009 1:30 am

Okay. Yes I do use auto_incremental ids. The real reason I'm just making two tables is I want to find out how to do this with other information later on. So later on I will replace email with just extra info about a user, like his social networks and junk. I just need to figure out how do I call for the info in one table about a user in another.

I've also been looking at the foreign key command, would that work in this situation?

EDIT: I'm kind of looking for how I would put that into PHP terms.
User avatar
caseyw
Regular
Regular
 
Joined: Sun Feb 01, 2009 2:02 am

Re: MySQL user info help

Postby HeroinRob on Wed Dec 23, 2009 5:38 am

I run a punk girl porn site and this is how I deal with the model application script to add it to my database in one table. I am not sure that I understand your question but I hope what I provide below helps you a bit. :)

Code: Select all
<?
function process_pass_data( $text, $strip_tags = 0 )
{
   if ( $strip_tags )
      $text = strip_tags($text);

   if ( !get_magic_quotes_gpc() )
      return $text;
   else
      return stripslashes($text);
}

$nickname=$_POST['nickname'];
$email=$_POST['email'];
$password=md5(process_pass_data($_POST['password']));
$sex=$_POST['sex'];
$year=$_POST['year'];
$month=$_POST['month'];
$day=$_POST['day'];
$date=$year."/".$month."/".$day;
$dob = date('Y-m-d', strtotime($date));
$description=$_POST['description'];
$country=$_POST['country'];
$datereg=date('Y-m-d H:i:s');
$ip=$HTTP_SERVER_VARS['REMOTE_ADDR'];
$name=$_POST['name'];
$nude=$_POST['nude'];
$feet=$_POST['feet'];
$inches=$_POST['inches'];
$weight=$_POST['weight'];
$hair=$_POST['hair'];
$eyes=$_POST['eyes'];
$tats=$_POST['tats'];
$ref=$_POST['ref'];
$alt=$_POST['alt'];
$why=$_POST['why'];
$myspace=$_POST['myspace'];
$facebook=$_POST['facebook'];
$aim=$_POST['aim'];



$user="USERNAME";
$pass="DATABASEPASSWORD";
$database="DATABASENAME";
mysql_connect('localhost',$user,$pass);
mysql_select_db($database) or die(mysql_error());


$query = "INSERT INTO application_data(nickname,email,password,sex,dob,description,country,ProfileType,Status,DateReg,ip,name,nude,feet,inches,weight,hair,eyes,tats,ref,alt,why,myspace,facebook,aim) VALUES ('$nickname','$email','$password','$sex','$dob','$description','$country','4','Active','$datereg','$ip','$name','$nude','$feet','$inches','$weight','$hair','$eyes','$tats','$ref','$alt','$why','$myspace','$facebook','$aim')";
mysql_query($query) or die(mysql_error());

mysql_close();
header( 'Location: http://GGGGGGG.COM/html/ubr_file_upload.php' );
?>
HeroinRob
Regular
Regular
 
Joined: Mon Mar 17, 2008 3:35 pm

Re: MySQL user info help

Postby zombie@computer on Wed Dec 23, 2009 9:18 am

if your table structure is similar to this

users
id | username
0 | admin
1 | wally

and users_info
id | email | shiz
0 | r@h.com | 14

you can use one of these queries

SELECT i.email, i.shiz FROM users_info i, users u WHERE i.id = u.id AND u.username=$username
SELECT users_info.email, users_info.shiz FROM users_info INNER JOIN users_info ON users_info.id = users.id WHERE users.username=$username

Allthough i believe the JOIN() commands are preferred, i hate them. I can never remember when to use JOIN, LEFT JOIN, RIGHT JOIN, INNER JOIN and whatever. The first option is very easy to understand and to remember :)
When you are up to your neck in shit, keep your head up high
User avatar
zombie@computer
Forum Goer Elite™
Forum Goer Elite™
 
Joined: Fri Dec 31, 2004 5:58 pm
Location: Lent, Netherlands

Re: MySQL user info help

Postby evochinima on Wed Dec 23, 2009 9:38 am

Rob always use <?php and not <? :) But uhm i have to agree with head here, why two seperate tables? It'll increase the ammount of code and cluster it up pretty badly, durr.
Image
User avatar
evochinima
Regular
Regular
 
Joined: Fri Nov 06, 2009 7:43 am
Location: Sweden (There's a polarbear outside my window!)

Re: MySQL user info help

Postby zombie@computer on Wed Dec 23, 2009 9:58 am

evochinima wrote:Rob always use <?php and not <? :) But uhm i have to agree with head here, why two seperate tables? It'll increase the ammount of code and cluster it up pretty badly, durr.

nothing wrong with shorttags. Servers that havent enabled them are about as rare as windows 95.
When you are up to your neck in shit, keep your head up high
User avatar
zombie@computer
Forum Goer Elite™
Forum Goer Elite™
 
Joined: Fri Dec 31, 2004 5:58 pm
Location: Lent, Netherlands

Re: MySQL user info help

Postby evochinima on Wed Dec 23, 2009 10:20 am

Ive been told that <? r bad during my entire learning process. I am wrong obviously, looking at some google results and some writings from the php dev meeting. Darn.
Image
User avatar
evochinima
Regular
Regular
 
Joined: Fri Nov 06, 2009 7:43 am
Location: Sweden (There's a polarbear outside my window!)

Re: MySQL user info help

Postby zombie@computer on Wed Dec 23, 2009 10:35 am

evochinima wrote:Ive been told that <? r bad during my entire learning process. I am wrong obviously, looking at some google results and some writings from the php dev meeting. Darn.

its considered 'bad' because not all servers have it enabled. Its like using windows api's in c++, cuz, omg, you may want to compile for linux one day (yeah, right)
When you are up to your neck in shit, keep your head up high
User avatar
zombie@computer
Forum Goer Elite™
Forum Goer Elite™
 
Joined: Fri Dec 31, 2004 5:58 pm
Location: Lent, Netherlands

Re: MySQL user info help

Postby Head on Wed Dec 23, 2009 12:27 pm

Another way to do it is to set up your tables like this.

users
[0]usernames
[1]password
[2]crap

users_info
[0]usernames
[1]email
[2]crap

if you don't wanna use ids. :[]:
Image
User avatar
Head
1337 p0st3r
1337 p0st3r
 
Joined: Fri Apr 29, 2005 3:21 pm
Location: Sweden ;(

Re: MySQL user info help

Postby caseyw on Thu Dec 24, 2009 1:32 am

Thanks for the help. I've finally sorted out my issues. I've decided to simply use two auto ids that are the same for each table and use subqueries and such to find the other info on extra tables. Later on though, I may use ids and usernames together.

The reason I want two tables is for practice and I want to keep things somewhat tidy. I'd like to have login information in one table, then use that as a basis to get other information from other tables. So I can go 'timmy just logged in, use his info from the first table to get all his info from the others'. That and I don't want a table that has 50 columns when I could create five tables that have different groups making it easier to sort through.
User avatar
caseyw
Regular
Regular
 
Joined: Sun Feb 01, 2009 2:02 am

Re: MySQL user info help

Postby zombie@computer on Thu Dec 24, 2009 5:19 pm

caseyw wrote:Thanks for the help. I've finally sorted out my issues. I've decided to simply use two auto ids that are the same for each table and use subqueries and such to find the other info on extra tables. Later on though, I may use ids and usernames together.

The reason I want two tables is for practice and I want to keep things somewhat tidy. I'd like to have login information in one table, then use that as a basis to get other information from other tables. So I can go 'timmy just logged in, use his info from the first table to get all his info from the others'. That and I don't want a table that has 50 columns when I could create five tables that have different groups making it easier to sort through.

problem there is that you need to write code to delete stuff from table2 if a row has been deleted in table1. A lot of 'extra' work, especially since you can put it in one table.
When you are up to your neck in shit, keep your head up high
User avatar
zombie@computer
Forum Goer Elite™
Forum Goer Elite™
 
Joined: Fri Dec 31, 2004 5:58 pm
Location: Lent, Netherlands

Return to Web Design & Development

Who is online

Users browsing this forum: No registered users