Mysql query questions

[acctman]

Two questions

 

1. When is it okay to do a mysql_query("SELECT * ...) wildcard selecting all field? How many fields is consider to many for selecting to the point where it would affect performance on a community site?

 

2. Since a lot of the userdata store in the membertable is queried and used multiple times throughout the site, would it be wise to load all fields (roughly around 20-25 fields) into sessions when the user logins to the site? Meaning on the login script have one big query and for each field ( i.e. m_age = $_session['age'], m_city = $_session['city'] ... etc ). Is this a smart idea, it would limit any extra site queries for user data and just leave other queries for images and searches.

 

[JonnoTheDev]

1. When you want to select all rows ONLY! Each field type has a space in memory, the more fields you select the more memory used to return them.

 

2. If you login to a webiste and your user data is returned you are best storing this in a session. I prefer to create a user object that is returned on successful validation of a user login and stored in a session. I can then use its internal methods to obtain the user data I require on certain pages of the site. This approach means that I do not have to query the users table every time I need data, lets say if you only stored the users ID in a session.

[aschk]

Wow, 25 fields in 1 table. Almost sounds like you should have some form of normalisation...

As a rule of thumb, I recommend specifiying all fields explicitly (this is just my preference), as it has been shown in some old SQL tests that explicit field naming for SELECT statements is faster than *. Also * does NOT guarantee the order in which the fields return, so if you use mysql_fetch_array, field 0 might now be field 9... who knows

 

Basically, don't return any more fields than you need for your current action.

[acctman]

Wow, 25 fields in 1 table. Almost sounds like you should have some form of normalisation...

As a rule of thumb, I recommend specifiying all fields explicitly (this is just my preference), as it has been shown in some old SQL tests that explicit field naming for SELECT statements is faster than *. Also * does NOT guarantee the order in which the fields return, so if you use mysql_fetch_array, field 0 might now be field 9... who knows

 

Basically, don't return any more fields than you need for your current action.

 

25 fields was a rough guess, but i just counted and I have 50 fields, and 56 tables. I'll need about 15 of the fields on the various site pages, info ranging from username, userID, gender, age, zipcode, etc...

what's the max amount of fields that should be in a table?

[acctman]

1. When you want to select all rows ONLY! Each field type has a space in memory, the more fields you select the more memory used to return them.

 

2. If you login to a webiste and your user data is returned you are best storing this in a session. I prefer to create a user object that is returned on successful validation of a user login and stored in a session. I can then use its internal methods to obtain the user data I require on certain pages of the site. This approach means that I do not have to query the users table every time I need data, lets say if you only stored the users ID in a session.

 

right now i'm doing something like this after the user login has been validated. so it would be fast and a good idea to add on the other 10 or so fields that I'll need throughout the site. When a user updates there profile, should I just run the same query again and would it just overwrite the current session info for the fields being updated? I would write a piece of coding that would just rewrite the sessions for the fields that was updated. Does that sound about right? Nothing would have to be flushed or anything just a simple query and set the result for the correct session variable?

 

$sql = "SELECT m_id, m_user, m_pass, m_email, m_confirmed FROM $membtable WHERE m_user='{$en['user']}' AND m_pass='".$en['pass']."' AND m_confirmed > 0 AND m_del != 1";
$result = sql_query($sql);
$line = sql_fetch_assoc($result);

        session_start();
        $time_started = md5(mktime());
        $secure_session_user = md5($line['m_user'].$line['m_pass']);
        $_SESSION['userid'] = $line['m_id'];
        $_SESSION['user'] = $line['m_user'];
        $_SESSION['pass'] = md5($en['pass']);
        $_SESSION['pbbpass'] = $line['m_pass'];
        $_SESSION['username'] = $line['m_user'];
        $_SESSION['email'] = $line['m_email'];
        $_SESSION['confirmed'] = $line['m_confirmed'];
        $_SESSION['session_key'] = $time_started.$secure_session_user.session_id();
        $_SESSION['current_session'] = $line['m_user']."=".$_SESSION['session_key'];
...

[JonnoTheDev]

Yeah, you could do that but its a pain in the arse if you add extra fields, you would have to add these extra session variables all over your script. Thats why an OO approach is better.

If the user class is stored in a session you may take the following approach:

 

$_SESSION['user']->updateAddress();
$_SESSION['user']->updateUsername();
$_SESSION['user']->updateEmail();

 

etc...

 

The example methods will update the class variables and also update your database records. You may get data using the following:

print $_SESSION['user']->get("email");

[acctman]

Yeah, you could do that but its a pain in the arse if you add extra fields, you would have to add these extra session variables all over your script. Thats why an OO approach is better.

If the user class is stored in a session you may take the following approach:

 

$_SESSION['user']->updateAddress();
$_SESSION['user']->updateUsername();
$_SESSION['user']->updateEmail();

 

etc...

 

The example methods will update the class variables and also update your database records. You may get data using the following:

print $_SESSION['user']->get("email");

 

wow! that looks really cool and efficient but you lost me when you said "user class". can you write a quick example as to how you store all the info and user the update..() functions. Say a user changes something on the edit profile page the form post script would be something like

if $_POST['address'] != "" {
$_SESSION['user']->updateAddress();
}

 

and when i load the edit profile page i'll just do the session get for all the form values

$_SESSION['user']->get("address");

 

I'm a little excited about this method, it looks very clean and would save a lot of coding time expecially for someone like myself that is not advanced. Is it possible, if you could write up a quick example of how you store the user class, and how its updated and the info is retrieved. I think I understand the part for printing/echoing the info its just the being storing and updating and if there is any other special coding that would be needed.

 

thanks in advance

[JonnoTheDev]

Not really possible for me to put entire code here. If you are not familiar with OO its best to do some tutorials on classes, objects, etc

A user class may look along the following lines:

 

class login {

	public static function authenticate($username, $password) {
		// run an sql query here to get the user
		mysql_query();
		// if the user is found return a new user object
		if($numrows) {
			return new user($row['userId']);
		}
		else {
			// invalid login
			return false;
		}
	}

}


class user {
	public $name;

	public function __construct($id) {
		// query here to get user details from the user id
		mysql_query();
		// set the class variables
		$this->name = $row['name'];
	}


	public function updateUsername($name) {
		$this->name = $name;
		// sql to update users name
		mysql_query();
	}

}



// User has attempted login
if(!$user = login::authenticate($username, $password)) {
	print "Your login failed";
}
else {
	$_SESSION['user'] = $user;
}


// print the current users name
print $_SESSION['user']->name;
// set a new name for the user
$_SESSION['user']->updateUsername("Joe Bloggs");
// will now print joe bloggs
print $_SESSION['user']->name;

 

This is very rough. If you are storing lots of data about a user then you would probably store it in arrays within the class and have setter and getter methods.

[acctman]

Not really possible for me to put entire code here. If you are not familiar with OO its best to do some tutorials on classes, objects, etc

A user class may look along the following lines:

 

class login {

	public static function authenticate($username, $password) {
		// run an sql query here to get the user
		mysql_query();
		// if the user is found return a new user object
		if($numrows) {
			return new user($row['userId']);
		}
		else {
			// invalid login
			return false;
		}
	}

}


class user {
	public $name;

	public function __construct($id) {
		// query here to get user details from the user id
		mysql_query();
		// set the class variables
		$this->name = $row['name'];
	}


	public function updateUsername($name) {
		$this->name = $name;
		// sql to update users name
		mysql_query();
	}

}



// User has attempted login
if(!$user = login::authenticate($username, $password)) {
	print "Your login failed";
}
else {
	$_SESSION['user'] = $user;
}


// print the current users name
print $_SESSION['user']->name;
// set a new name for the user
$_SESSION['user']->updateUsername("Joe Bloggs");
// will now print joe bloggs
print $_SESSION['user']->name;

 

This is very rough. If you are storing lots of data about a user then you would probably store it in arrays within the class and have setter and getter methods.

 

I think I kind of understand most of it. It seems like i can apply the auth. login to my existing login script and then all the other class stuff i can add into an include. would i have to include the class file on each page that i want to use the info on, or do i just add a session_start(); and call the functions as needed.

 

everything seems to be covered I think, I know how to add the SELECT and UPDATE or INSERT queries, you showed how to verfiy login which i can apply to my current dologin.php file and or use the current code just to validate a successful login. You showed how to print out the information. Hmm is there anything else, oh the array can you show a quick example with an array updating and loading. I always seem to mess up when doing arrays.

 

is there anything else I'll need to read up on, or need to know in order to apply this to my site?

[acctman]

how much would you charge to setup a detailed example? The login part I under, I just want to make sure the sql insert/update and anything other info is coded properly