The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 21, 2019, 05:55:42 AM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: best way to talk to mysql from php?  (Read 3407 times)
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« on: June 11, 2008, 08:10:16 AM »

OK, let me preface with that although I now have a complete handle on PHP, i do not seem to have a handle on the most efficient and correct ways to do things. PHP, though it is a full featured language, seems to have been produced by retarded monkeys, and as such, the quirk-volume is gigatastically large it seems.

SO i have 1 major question, because frankly its bugging me and ALL the work i do needs to do this.

What is the most concise example (no error checking, or good practice crap, just the meat) of at least a very efficient way of doing the following? I say very efficient instead of best, because best is a fallacy. The short question is how to talk to a database, but its a little more detailed than that. Lets just assume the simplest of examples.

a single table in mysql.
these columns: id, value1, value2, total

lets assume millions of rows
lets assume the rows get populated 1 at a time from multiple sources and only value1 and value2 get populated, with numbers.

this is the starting point. table has a shitload of records. total is empty. value1 has a randomish number, as does value2.

I previously would do something like
Code:
$db = mysql_connect( $GLOBALS['dbaddress'], $GLOBALS['dbuser'], $GLOBALS['dbpass'],true) or die('Could not connect to database server.' );
mysql_select_db($GLOBALS['dbname'], $db) or die('Could not select database.');
$sql = "select * from table where total='' limit 100";
$rs = mysql_query($sql,$db);
while ($row = mysql_fetch_assoc($rs))
{
$sql = "update table set total=value1+value2 where id=".$row['id'];
mysql_query($sql,$db);
}

however, that blows up in some situations, such as calling a function that opens a new connection, seems to reset the original select, and as a result, you get thousands of records that get affected when only 100 should have been.

So, the other method has been
Code:
$db = mysql_connect( $GLOBALS['dbaddress'], $GLOBALS['dbuser'], $GLOBALS['dbpass'],true) or die('Could not connect to database server.' );
mysql_select_db($GLOBALS['dbname'], $db) or die('Could not select database.');
$counter = 0;
while ($counter<100)
{
        $sql = "select * from table where total=''";
        $rs = mysql_query($sql,$db);
        if (mysql_numrows($rs)>0)
        {
            $row = mysql_fetch_assoc($rs)
    $sql = "update table set total=value1+value2 where id=".$row['id'];
    mysql_query($sql,$db);
            $counter++;
        }
        else
        {
            break;
        }
}
This is 100 queries against the database compared to what I assumed was only 1, but at least is not prone to any error because the counting is outside of mySQL.

Assume no use of Stored Procedures, because I am trying to learn in this case.

is this basically what you gotta do? or did i miss something in the pile of shit Internets.

also a specific question. is mysql_fetch_assoc the appropriate tool or is there something else? assume that I need to access everything by column name, not column position (since many times the positions change in my work).

Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #1 on: June 11, 2008, 09:43:49 AM »

Nuts the biggest issue here for you is not using the connection handle param in the mysql functions so that you *can* do more than one thing at once.

This is, in fact, almost exclusively the reason I wrote the dbConnection class, available in the repository by the way - it handles that stuff easily.

But the way to get around it is rather simple:

Code:
<?php

$master 
mysql_connect($host$user$pw$theDB);
$detail mysql_connect($host$user$pw$theDB);
$rs mysql_query("select id from the table"$master)
while (
$row mysql_fetch_row($rs))
mysql_query("update table set total=value1+value2 where id={$row[0]}"$detail);

?>


Note that in this example I feel a tiny time saver is also to only select what you need, and then only select it as a row rather than an association. Also, a dereferenced string is often faster than a concatenation and, IMO reads easier as well.

With the dbConnection lib it'd look like this:
Code:
<?php

require "$classLib/class.dbconnection.php";
$master = new dbConnection($host$user$pw$theDB);
$detail $master.cloneConnection();
while (
$id $master->singleAnswer("select id from table where blah blah blah"))
$detail->query("update table set total=value1+value2 where id=$id");

?>


Hope that helps. Of course, I'd now do this work as a SP for speed, but I'm gonna wager that Mosso has not given you any joy yet...
« Last Edit: June 11, 2008, 10:31:45 AM by perkiset » 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.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #2 on: June 11, 2008, 10:20:39 AM »

ok. first. WTF is mysql_connection?

second. is the gist that you are creating 2 seperate handles to use?

i fully agree and understand that mysql_fetch_row is faster than assoc, but assume the worst case most horrific databases known to man... so thats why i basically do assoc as a matter of course since almost everything i deal with for clients is utter shit.

did you mean to do
while ($row = mysql_fetch_row($rs))
instead of what you had
while ($row = mysql_fetch_row($master))

if not, i am now totally confused and I am just going to become a poolboy.

Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #3 on: June 11, 2008, 10:31:21 AM »

ok. first. WTF is mysql_connection?
That'd be a syntax error.  Roll Eyes I use dbConnection so in going fast I fupped duck.


second. is the gist that you are creating 2 seperate handles to use?
Zactly - that's what the return value is from mysql_connect... it's a resource identifier.


i fully agree and understand that mysql_fetch_row is faster than assoc, but assume the worst case most horrific databases known to man... so thats why i basically do assoc as a matter of course since almost everything i deal with for clients is utter shit.
But if you know the field name to select it from the association, then you can use it in a SQL and then select only the first returned value, which is always in order of your selected fields ergo, if you say select id from then row[0] will ALWAYS be id...

did you mean to do
while ($row = mysql_fetch_row($rs))
instead of what you had
while ($row = mysql_fetch_row($master))

if not, i am now totally confused and I am just going to become a poolboy.
Indeed ... see, you're all PHPexpert on me now. I was just typing fast while making my point Wink but it sounds like you've gotten it...

<edit>Edited my original post to cleanse the Undocumented Features you found</edit>
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.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #4 on: June 11, 2008, 11:42:02 AM »

ok, so i been doin it right then.

two handles. 1 for row crawling, 1 for updating.

yea your right about the [ 0 ], i guess my example was tooooooooo simple. I actually would mostly be talking about select *.
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 #5 on: October 26, 2008, 12:30:07 PM »

Ive been doing something like this:

Code:
CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;
  OPEN cur2;

  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
       ELSE
          INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  CLOSE cur2;
END
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 #6 on: October 26, 2008, 01:03:07 PM »

Lookitchu all bad the the phpMyIDE bone... nice work JM!
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.
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!