nathanr

I've been reading a few posts on here in regards to mysql and more specifically the best approach to getting data in and out of the database.

Here's some theory / logic
to keep db usage light then the best approach is to get everything out of the db as quickly as possible.
a bad yet frequently used method is as such:
open query handle
get first result
do lots of processing
repeat
close

obviously the do lots of processing stage consumes resources and means the handle can't move to the next row until processing is complete.. in all on a medium usage site your going to find it dies quickly.

next MAJOR factor.. there is NO substitute for good table design, optimised sql statements and INDEXES on the correct columns - ideally go for a normalised (3n) database layout, this will keep the server usage nice and light. hint: if you use any column in the WHERE of an sql statement, then it should have an index..

difference in mysql_fetch_xxxxx's:
_assoc is slightly slower than _row - but you'd never ever ever notice it, and the difference it makes to coding time let alone the extra lines of

php

  needed to reference the data by integer practically speaking makes _row slower - fetch_assoc everytime..

here's a handler for you (no connect or db select)


<?

php

 

/* nathanr: db handler

php

 5*/
class db_mysql_handler {
/* do_select : grab everything in one go, turn it into a nice array, free the resources and return */
public function do_select($query) {
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$result_rows = mysql_num_rows($result);
if ($result_rows !== 0) {
for ($r=0;$r<$result_rows;$r++) {
  $output[$r] = mysql_fetch_assoc($result);
}
} else {
$output = false;
}
mysql_free_result($result);
return $output;
}

/* inserts and returns the insert id, or false.. */
public function do_insert($query) {
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$last = mysql_insert_id();
if ($last !== 0) {
$output = $last;
} else {
$output = false;
}
return $output;
}

public function do_update($query) {
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
}

public function do_delete($query) {
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
}

/* internal use only */
public function magic_quote_trim($in) {
if (get_magic_quotes_gpc()) {
  $in = stripslashes($in);
  }
  return $in;
}

/* escape everything you put in the database (sql inject prevention) */
public function ins($in) {
return mysql_real_escape_string($this->magic_quote_trim($in));
}

/* store ips as integers */
public function ip2int($ip) {
return sprintf('%u', ip2long($ip));
}
}
?>


enjoy..


Perkiset's Place Home   Politics @ Perkiset's