The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. October 16, 2019, 05:42:36 AM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: PHP5: MySQL + Theory  (Read 1904 times)
nathanr
n00b
*
Offline Offline

Posts: 7


View Profile
« on: October 27, 2007, 01:19:33 PM »

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)

Code:
<?php

/* nathanr: db handler php5*/
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..
Logged

No links in signatures please
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!