question

[Dayo]

im thinking of using something like this in my next project.

would it speed up a page or slow it down

function user($sql, $user='-1') {
global $connect;
$sql=mysql_real_escape_string($sql);
if ($user=='-1') {$user=$_SESSION['id'];}
$user=mysql_real_escape_string($user);
$qry = mysql_query("SELECT `".$sql."` FROM `users` WHERE id='".$user."'");
return mysql_fetch_array($qry);
}

so all you have to do is

$optional_user = 2; // used if you want to look at another users details eg a profile
$user=user('`name`, `id`', $optional_user);
echo $user['name'].' ['.$user['id'].']';

is this an ideal way of doing things, or just stick with the normal select * ...

[Floydian]

That's a good way to do things. Selecting all the fields from the users table might be inefficient if there are lots of fields. What really costs the most is doing the query in the first place, regardless of whether you select 2 or 20 fields. But if your users table has 100 fields, and you can select just 10 of them, that might start getting into a situation where there's a good savings in not selecting everything.

Now your script could use some improvements though. Number one, instead of using -1 as the default, null is prefered. null is the default value for unset php variables, and it really makes sense to use that. And secondly, you run a mysql escape on the userid. What you really should do is run abs(intval()) on that. Lastly, that concatenation you're doing in that query is not doing anything for ya there. It'd be easier to read the code if you didn't use concatenation there. Now if that concatenation was having an effect, I'd say keep it, but it really isn't doing anything different from just plugging the variable into a string.

 

function user($sql, $user=null) {
global $connect;
$sql=mysql_real_escape_string($sql);
if (is_null($user)) {
   $user=$_SESSION['id'];
} else {
   $user=abs(intval($user));
}

$qry = mysql_query("SELECT `$sql` FROM `users` WHERE id=$user");
return mysql_fetch_array($qry);
}

[Dayo]

thanks for that i think i will be using it for my project after a few other edits (like being able to update the user feild) eg user('`name`=dayo')

thanks for the feedback floydian :thumbsup:

[a_bertrand]

As this question is not new, I made my own trials... of course you can do the same on your side and get different results:

 

<?PHP
include "../rpg/config.php";
include "../rpg/db_conn.php";
include "../rpg/misc_util.php";

function getmicrotime()
{
     list($usec, $sec) = explode(" ",microtime());
     return ((float)$usec + (float)$sec);
}

function read_db_entry($db,$sql)
{
$res=array();

$r=mysql_query($sql,$db->conn);
if($r === false)
{
	echo "BAD Query: $sql";
	return null;
}
$res=mysql_fetch_assoc($r);
mysql_free_result($r);

if($res === false)
	return array();

return $res;
}

for($j=0;$j < 9;$j++)
{
       echo "--------------------------------\n";

$start=getmicrotime();
for($i=0;$i < 1000;$i++)
{
       $val=read_db_entry($db,"SELECT * FROM PLAYER ORDER BY ID LIMIT ".mt_rand(0,1000).",1");
}
$end=getmicrotime();
echo "Time: ".($end-$start)."\n";
echo "Columns: ".count($val)."\n";

$start=getmicrotime();
for($i=0;$i < 1000;$i++)
{
       $val=read_db_entry($db,"SELECT ID,USERNAME,PASSWORD FROM PLAYER ORDER BY ID LIMIT ".mt_rand(0,1000).",1");
}
$end=getmicrotime();
echo "Time: ".($end-$start)."\n";
echo "Columns: ".count($val)."\n";
}
?>

 

Result:

--------------------------------
Time: 1.744500875473
Columns: 109
Time: 1.4345109462738
Columns: 3
--------------------------------
Time: 2.1714589595795
Columns: 109
Time: 1.9231040477753
Columns: 3
--------------------------------
Time: 2.2312161922455
Columns: 109
Time: 2.1061990261078
Columns: 3
--------------------------------
Time: 2.3801500797272
Columns: 109
Time: 2.0650768280029
Columns: 3
--------------------------------
Time: 1.9368641376495
Columns: 109
Time: 1.992063999176
Columns: 3
--------------------------------
Time: 2.2415399551392
Columns: 109
Time: 1.9894790649414
Columns: 3
--------------------------------
Time: 2.061381816864
Columns: 109
Time: 2.028737783432
Columns: 3
--------------------------------
Time: 2.2158668041229
Columns: 109
Time: 2.127660036087
Columns: 3
--------------------------------
Time: 2.2819759845734
Columns: 109
Time: 2.0801141262054
Columns: 3

 

So... as you can see, there is just a LITTLE difference between the two... using a 1000 loop. Now... Does it make any difference in real life? Well yes and no. If you are sure you will NOT need any of the other columns, then maybe, if not, then MUCH better to read once all the columns, and keep them into an array and use it during your page load.

Also think that if MySQL find the exact same query, it may cache both the query and the result. Which means 2x the same query will be much faster than something which varies all the time.

[Dayo]

yea but our user db is pottentialy gona hit 200+ fields, were intergrating our database into vBulletins so the two work together and for example one page all it has is a list ofusers feinds so i just want to do somethinglike this

$freinds=explode('-', user('`freinds`')); 
//then print out there name and id while in a loop 
$u=user('`name`, `id`');
echo $u['name'].' '.$u['id'];

so if a user had 150 freinds it wont have to get 198 x 150 un-nessasary fields

same with the userlist

[Zeggy]

You could split your user table into two tables with commonly accessed information such as username, and data that is displayed on each page like player stats.

Then in your secondary user table you store information that is rarely used (compared to your main user table), containing info such as password, email, profile data, and other page-specific data.

 

As for user's friends, you should normalise your tables - take the users and users' friends and put them in their own table.

Columns: user_id, friend_id

Primary key: (user_id, friend_id)

This way you can let your database do the hard work. You are also able to cache results, sort friends, search friends, order friends, join data and more.

You can also take advantage of the fact that this is the kind of operation a database was made for... There's no point shoving all your data into a single column in a database, otherwise you might as well store it in a flat file.

Having a variable-length column like a delimited friends list means that the columns will grow from length 0 to any size, and every player will have friends data of different length. The database will need to do extra work to read/write this column of data because variable-length data is stored and read differently from fixed-size data. This means that every read and write operation on this data will be slower.