The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 23, 2019, 02:28:59 AM

Login with username, password and session length


Pages: [1] 2
  Print  
Author Topic: Perk's MySQL Connection Class Updated  (Read 8572 times)
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« on: September 25, 2008, 11:18:32 AM »

Hey all -

I have updated my dbConnection class to use mysqli_ functions rather than the old mysql_ funcs. It's a straight retrofit - all functions behave exactly the same as the original class. But more importantly (and the reason for this upgrade) is the addition of a new method, multiQuery. Works like this: pass a series of SQL queries separated by semicolons to multiQuery (just like you'd pass a single statement to query) and it executes all of them via mysqli_multi_query. It's way fast and clean. Results come back in a 2 dimensional array: the first index is based on the order of statements in the multi query, the second dimension is the results of that query.

So, if you did this:
Code:
$sql = <<<SQL
select * from contacts limit 3;
select * from transactions limit 4;
select * from memos limit 2;
SQL;

$answerArray = $db->multiQuery($theAboveSQL);

your $answerArray would look something like this:
  • $answerArray[0][0] // contact data record #1
  • $answerArray[0][1] // contact data record #2
  • $answerArray[0][2] // contact data record #3
  • $answerArray[1][0] // transaction data record #1
  • $answerArray[1][2] // transaction data record #2
  • $answerArray[1][3] // transaction data record #3
  • $answerArray[1][4] // transaction data record #4
  • $answerArray[2][0] // memo record #1
  • $answerArray[2][1] // memo record #2

multiQuery can be called with an optional second parameter - the array you'd like to have filled with the answers. So you could do any of the following:
  • $newArray = $db->multiQuery($sql);
  • $myArray = new Array();
  • $myArray = $db->multiQuery($sql, $myArray);
  • $db->multiQuery($sql, $myArray);

It's handy and fast. How fast you ask? Well, I'm writing a test case for my own purposes ATM and will post my results.

Enjoy!

/perk

Code:
<?php

class dbConnection
{
var $__connected;
var $__host;
var $__user;
var $__password;
var $__database;
var $__myConnection;
var $__lastQuery;
var $dataSet;
var $row;
var $silent;

function dbConnection($host$user$password$database$doConnect=false)
{
$this->__host $host;
$this->__user $user;
$this->__password $password;
$this->__database $database;
$this->__connected false;
$this->silent false;
if ($doConnect) { $this->connect(); }
}

function &cloneConnection()
{
if (!$this->__connected) { $this->connect(); }
return new dbConnection($this->__host$this->__user$this->__password$this->__database);
}
function close()
{
if($this->__connected) { mysqli_close($this->__myConnection); }
}
function connect()
{
$this->__myConnection mysqli_connect($this->__host$this->__user$this->__password$this->__database);
if (!$this->__myConnection) { die('class DBConnection2 cannot connect'); }
$this->__connected true;
}
function connected() { return $this->__connected; }
function error()
{
if (!$this->__connected) { return ''; }
return mysqli_error($this->__myConnection);
}
function fetchArray()
{
$this->row mysqli_fetch_array($this->dataSet);
if ($this->row) { return true; } else { return false; }
}
function fetchAssoc()
{
$this->row mysqli_fetch_assoc($this->dataSet);
if ($this->row) { return true; } else { return false; }
}
function fetchRow()
{
$this->row = @mysqli_fetch_row($this->dataSet);
if ($this->row) { return true; } else { return false; }
}

function fieldCount() { return mysqli_num_fields($this->dataSet); }
function fieldName($idx) { throw new Exception('fieldName function has not been upgraded'); } // return mysql_field_name($this->dataSet, $idx);
function lastInsertID() { return $this->singleAnswer('select LAST_INSERT_ID(' $this__myConnection ')'); }
function lastQuery() { return $this->__lastQuery; }

function &multiQuery($queryStr, &$outArr=false)
{
if (!$outArr$outArr = array();
if (!$this->__connected) { $this->connect(); }
$this->__lastQuery $queryStr;
$this->__lastError '';
$ptr 0;
if(mysqli_multi_query($this->__myConnection$queryStr))
{
do 
{
if ($res mysqli_store_result($this->__myConnection))
{
while($row mysqli_fetch_assoc($res)) $outArr[$ptr][] = $row;
$res->close();
} else $outArr[] = "Store/Fetch problem: {$this->__myConnection->error}";
$ptr++;
} while(mysqli_next_result($this->__myConnection));
} else $outArr[] = "Query problem: {$this->__myConnection->error}";

return $outArr;
}

function query($queryStr$ignore=false)
{
if (!$this->__connected) { $this->connect(); }
$this->__lastQuery $queryStr;
$this->__lastError '';
$this->dataSet mysqli_query($this->__myConnection$queryStr) or ($this->__lastError mysql_error($this->__myConnection));
if (($this->__lastError ' ') && (!$ignore))
{
if (!$this->silent)
print "MySQL Error on query('{$this->__lastQuery}') - {$this->__lastError}";
}
}

function rowCount() { return mysqli_num_rows($this->dataSet); }
function rowToXML(&$xml)
{
$max mysql_num_fields($this->dataSet);
for ($i=0$i<$max$i++)
{
$xml->addChild(mysql_field_name($this->dataSet$i), $this->row[$i], false);
}
}
function seek($rowNum) { return mysqli_data_seek($this->dataSet$rowNum); }
function selectDB($dbName)
{
if (!$this->__connected) { $this->connect(); }
mysqli_select_db($dbName$this->__myConnection);
}
function singleAnswer($queryStr)
{
if (!$this->__connected) { $this->connect(); }
$this->query($queryStr);
$this->fetchRow();
return $this->row[0];
}
function singleArray($queryStr)
{
if (!$this->__connected) { $this->connect(); }
$this->query($queryStr);
$this->fetchRow($this->__myConnection);
return $this->row;
}
function singleAssoc($queryStr)
{
if (!$this->__connected) { $this->connect(); }
$this->query($queryStr);
$this->row mysqli_fetch_assoc($this->dataSet);
return $this->row;
}
}

?>

Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #1 on: May 19, 2009, 07:35:50 AM »

Awesome, I can think of a few places this would be handy.

Code:

So, if you did this:

$sql = <<<SQL
select * from contacts limit 3;
select * from transactions limit 4;
select * from memos limit 2;
SQL;

$answerArray = $db->multiQuery($theAboveSQL);
your $answerArray would look something like this:

    * $answerArray[0][0] // contact data record #1
    * $answerArray[0][1] // contact data record #2
    * $answerArray[0][2] // contact data record #3
    * $answerArray[1][0] // transaction data record #1
    * $answerArray[1][2] // transaction data record #2
    * $answerArray[1][3] // transaction data record #3
    * $answerArray[1][4] // transaction data record #4
    * $answerArray[2][0] // memo record #1
    * $answerArray[2][1] // memo record #2


  • I would know to look for three records on elements
Code:
$answerArray[0][0]
$answerArray[0][1]
$answerArray[0][2]

and four on the second query, etc .... what if the program didnt
know how many records were coming back how would you address
each query and the individual results and how would you count the amount
of records that came back from each query?



  • also are
Code:
$answerArray[0][0]
$answerArray[0][1]
$answerArray[0][2]

each entire records that might contain multiple fields?
how would you count those fields and iterate through them
without knowledge of how many fields there were or how many
records came back?

Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #2 on: May 19, 2009, 09:11:51 AM »

Consider this code. It will bake your noodle a bit, but there are some interesting mechanics presented here:

Code:
<?php

$sql 
= <<<SQL
select * from contacts;
select * from addresses;
select * from memos;
SQL;

$result $db->multiQuery($sql);

foreach(
$result as $idx=>$resultSet)
{
echo "$idx{$sql[$idx]}\n";
foreach($resultSet as $rows)
{
print_r($rows);
}
}

?>

Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
isthisthingon
Global Moderator
Lifer
*****
Offline Offline

Posts: 2879



View Profile
« Reply #3 on: May 19, 2009, 11:24:34 AM »

Nice work Perk!  This would be great to call from a worker thread.  In Apex it would look something like this:

Code:
class PerksMultiFun
{
   public List<List<String>> multiReturnArray {get; set;}

   public PerksMultiFun()
   {
      // Initialize array
      multiReturnArray = new List<List<String>>();
   }

   @future // Run in separate thread and return immediately
   public static void multiFun(String theMultiQuery)
   {
      // Assuming multiQuery was native Apex
      multiReturnArray = multiQuery(theMultiQuery);

      // If you exposed this as a RESTful or web service,
      // you could call it directly from Apex.  Wanna try this
      // someday??  I'd love to package up your framework and
      // list it on the AppExchange as a composite app.  Even
      // a subset would be wicked cool.
   }
}

Then perhaps the Apex code that calls the class:

Code:
private List<List<String>> doMultiQuery(String qry)
{
   PerksMultiFun pm = new PerksMultiFun();
   return pm.multiFun(qry);
   // Or return pm.multiReturnArray after the multiFun call,
   // either way.
}

public PageReference doIt()
{
   // bodyStr is defined in the class and grabs the query(s) from
   // VF page.  User enters this and launches query with button
   asyncResults = doMultiQuery(bodyStr);
}


And the Visualforce page with the do-it button...

Code:
<apex:page controller="PerksMultiFun">
<apex:sectionHeader title="Perk's MultiQuery Spans the Clouds!" subtitle="bitches"/>
   <apex:form >
      <apex:pageBlock id="theBlock">
         <apex:pageBlockButtons location="bottom">
            <apex:commandButton action="{!doIt}" value="Do it!" rerender="results"/>
         </apex:pageBlockButtons>
         <apex:outputPanel id="input">
            <apex:pageBlockSection columns="1">
               <apex:inputTextarea id="querystring" value="{!bodyStr}" rows="5" style="width: 100%"/>
            </apex:pageBlockSection>
         </apex:outputPanel>
         <apex:outputPanel id="results">
            <apex:pageBlockSection columns="1">
               <apex:inputTextarea id="queryresults" value="{!asyncResults}" rows="10" style="width: 100%"/>
            </apex:pageBlockSection>
         </apex:outputPanel>
      </apex:pageBlock>
   </apex:form>
</apex:page>

 

Google Apps? CacheApps!
Logged

I would love to change the world, but they won't give me the source code.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #4 on: May 19, 2009, 12:24:30 PM »

Ah... at first I was like, WTF is going on ... then I remembered that Apex is actually "behind the wall" of SalesForce, so it doesn't need a database connection per se - it's already connected to the "database" that is 'force.

@ the Visual Force stuff - is that a specialized markup to exist *within* an HTML page, or is it stand-alone? (I assume if it's to be used within, you'd need some special type of processing to instantiate/execute it all...)
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
isthisthingon
Global Moderator
Lifer
*****
Offline Offline

Posts: 2879



View Profile
« Reply #5 on: May 19, 2009, 01:17:43 PM »

Correct, Apex is in the Force.com cloud along with the Visualforce markup.  They released Force.com Sites which allows full access to their platform and doesn't require the world to login to Salesforce (ie - no license fees for anyone you choose to authenticate - or not - to visit your pages).  Sites, like the rest of their cloud, is completely hosted on their servers.  The Force.com platform is 100% pure cloud computing.  There's a tremendous amount of support, tools, documentation, etc., to provide complete integration with the rest of the cloud world (such as..... Perk's cloud??)  The Force.com API exposes the whole banana through web services and their Metadata API describes it.  You can roll your own web services to expose custom apps to other clouds as well.  Apex provides a WSDL generator for consumption and supports RESTful (HTTP) callouts as well.

This is how Eclipse blends so nicely with the Force.  Using metadata web services under the hood, Eclipse allows you to create as many "orgs" (virtual organizations) as you want and keep them organized in your library.  Code is also stored locally and verified in their cloud upon saving to SF.  Syntactical errors are caught when saving and Test classes are executed in the cloud as well (through Eclipse).

I've totally transitioned to test-driven development.  It's truly the way to go IMO.  The larger the code base, the more of a life saver this approach is.  Test-driven development is strictly enforced if you want to get published on the AppExchange.  Speaking of, here's a link to that app I coded: http://sites.force.com/appexchange/listingDetail?listingId=a0N300000016gkMEAQ.  It's called SaaS TEa (Time and Expense application).  It's totally native force but I'm in the middle of a composite app (mentioned it this weekend) that's baking my possibilities noodle   Nerd

 Idea... Also, may I take you up on your offer for a Force.com discussion group if you think it would be of interest?  One thing that occurred to me is that the principles of test-driven development apply to all languages (excluding markup).  I think this would be a valuable subsection and I'd be happy to write what I've learned on the subject.  Let me know and I'll make it so  Wink



Logged

I would love to change the world, but they won't give me the source code.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #6 on: May 19, 2009, 05:43:31 PM »

Alrighty, we'll give it a go.

Here's the board: http://www.perkiset.org/forum/salesforce_apex-b81.0/

...nestled in the Level 2 Cache area (because it's a specialty item).
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
kurdt
Lifer
*****
Offline Offline

Posts: 1153


paha arkkitehti


View Profile
« Reply #7 on: May 25, 2009, 04:25:18 AM »

I added this function to help me to insert stuff little bit easier:
Code:
function insert($table,$data_array) {
$tmp_col = array();
$tmp_dat = array();

# Create column and data values for SQL command
foreach ($data_array as $key => $value)
{
$tmp_col[] = $key;
$value = strip_tags($value);
$tmp_dat[] = $value;
}
$columns = join(",", $tmp_col);
$data = join(",", $tmp_dat);

# Create and execute SQL command
$sql = "INSERT INTO ".$table."(".$columns.") VALUES (". $data.")";
$runsql = $db->multiQuery($sql);
return $runsql;
}
You can make it more efficient if you want, it's not my code originally but I've been using it for many years. Points to those who know where this code is from originally Wink

*edit* If you put it into live site to save user's input, you might want to sanitize inputs little bit better.

*edit 2* I just noticed that it of course doesn't work when you put it like that in Perk's class so you have to modify it a little bit to make it work. When I have the time, I'll finish this and post it here.
« Last Edit: May 25, 2009, 04:54:26 AM by kurdt » Logged

I met god and he had nothing to say to me.
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #8 on: May 27, 2009, 05:43:12 AM »

Okay my noodle is definitely baked.  Now i want to just go learn cloud computing..lol

This would be awesome for just inserting a mysql dump file,
split it on the semicolons and fire it away...

Consider this code. It will bake your noodle a bit, but there are some interesting mechanics presented here:

Code:
<?php

$sql 
= <<<SQL
select * from contacts;
select * from addresses;
select * from memos;
SQL;

$result $db->multiQuery($sql);

foreach(
$result as $idx=>$resultSet)
{
echo "$idx{$sql[$idx]}\n";
foreach($resultSet as $rows)
{
print_r($rows);
}
}

?>


Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #9 on: May 27, 2009, 09:02:42 AM »

ya except my Dump file...

splitting would require the whole thing loaded in memory.

my dump is 40gigs currently, LOL
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #10 on: May 27, 2009, 02:27:14 PM »

so how to you reinsert your dumps then?
Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #11 on: May 27, 2009, 02:53:10 PM »

command line only it would seem.

I also use mysqlhotcopy, but find that it brings my server to a grinding halt, so only use it on light load DBs.

I am considering the live cluster, master->slave option instead. Master dies, change some connection strings and bam, its all good.
Also I could read from the slave, only doing inserts and updates and deletes on master. That would split off 1 motherfuckerofagiantquery that is causing me problems and bring the master to a halt.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #12 on: May 27, 2009, 07:57:14 PM »

so from the command line how are you sure every line was inserted?

are you just using the source command?

Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #13 on: May 27, 2009, 08:13:14 PM »

im not positive, but considering it either finishes without error, or bombs out, its should be inserting every line.

all i do is:   mysql -u USER -p DBNAME < dump.sql
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #14 on: May 27, 2009, 08:16:57 PM »

so when it bombs out you go through every line and find out why?
error by error on a 40 gig file until it all flies?


Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
Pages: [1] 2
  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!