473,325 Members | 2,816 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

PHP/MySQL Functions

TheServant
1,168 Expert 1GB
Hi guys,
I have a function to do on every row of a MySQL database, which could end up over 2000 entries, and it will be done every 10 minutes indefinately. The function will be something like:
Expand|Select|Wrap|Line Numbers
  1. for ( $counter = 1; $counter <= 1000; $counter += 1 ) {
    $result = mysql_fetch_array(mysql_query("SELECT * FROM table WHERE id="$counter"));
  2. $new_variableC = $result['variableA'] * $result['variableB'];
  3. mysql_query("UPDATE table SET variableA='$new_variableC' WHERE id='$counter'");
  4. }
This has been simplified, but is there an easier way? It will be making two queries for every entry, so I wondered if anyone had an idea how to get it done with less?
Jan 28 '09 #1
9 1629
dlite922
1,584 Expert 1GB
See if I understand correctly:

Your table has at least 3 (relevant) fields
You have up to 1000 records in the table
Each record's fieldA is a product of the previous version record's A and B field

For example if your table initially looked like this:

id A B
1 5 1
2 4 3
3 10 7
4 15 6
.
.
.

You want it to end up like this after the first pass:

id A B
1 5 1
2 12 3
3 70 7
4 90 6
.
.
.

with a second traversal A would equal 36 WHERE id = 2;


If this is the case than you don't need PHP to do any logic at all just call an update query like so:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Update myTable SET fieldA = fieldA * fieldB WHERE id <= 1000 AND id >= 1;
  3.  
  4.  
That should work. I've done it before.

Cheers,







Dan


EDIT: Something to notice if fieldB is 1 or 0, fieldA would not change or remain zero, respectively.
Jan 28 '09 #2
TheServant
1,168 Expert 1GB
Thanks for your reply. The function is not important, and the number of fields in the table is about 50. The actual function will be more involved for example:
A = E*F*J+A
B = E*G*K+B
C = E*H*L+C
D = E*I*M+D

I wasn't aware of SQL able to do logic within a query, so I guess my next question is, how much more processing power does SQL logic take? Is it quicker to do logic in SQL than to parse it through PHP first, do the logic and send it back to SQL? I presume so after writing that sentence.

Regardless, if I did want to parse it through PHP, because I have another application which would require complex functions and I have a similar problem, is there any other way than dealing with one record at a time?
Jan 28 '09 #3
dlite922
1,584 Expert 1GB
@TheServant
MySQL would is faster in my experience. I've built queries with several joins, a union and a couple of inline views.

As far as PHP doing logic on many, well yes of course it can /grab/ many records but you still have to write them one at a time unless multiple rows have the same values (which isn't your case).

I highly recommend you go with MySQL logic, especially simple arithmetic like you have here.


Good luck,




Dan


Edit: Curious, what does your application do?
Jan 29 '09 #4
TheServant
1,168 Expert 1GB
Cheers Dan,
My application is an online game so this particular one is a cron job which will update user information every 10mins or so. It will be user resources and ranks. I will give it a go, but I have a feeling I will have more questions on these logic possibilities ;) Thanks again.
Jan 29 '09 #5
Dormilich
8,658 Expert Mod 8TB
@dlite922
If you use one of PHP's database extensions, you are able to dump the whole result set in one go (unless I completely misunderstood you).
Jan 29 '09 #6
TheServant
1,168 Expert 1GB
@Dormilich
*Interested* Could you elaborate?
Jan 29 '09 #7
Dormilich
8,658 Expert Mod 8TB
PDO
MDB2
MySQLi (the class, not the functions. look at the MySQLi_Result class)

both PDO and MySQLi require PHP 5. and before I forget, all require a minimum understanding of OOP.

usually, the method you are interested in is called fetchAll(), but there are many other interesting ones.
Jan 29 '09 #8
TheServant
1,168 Expert 1GB
I have started using MySQL logic to do everything I need, but this is interesting. Thanks for the links.
Jan 29 '09 #9
Dormilich
8,658 Expert Mod 8TB
I especially like PDO's ability to fetch a result into a given object class.
Jan 29 '09 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Philip Stoev | last post by:
Hi all, Please tell me if any of this makes sense. Any pointers to relevant projects/articles will be much appreciated. Philip Stoev http://www.stoev.org/pivot/manifest.htm ...
0
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest...
3
by: slash | last post by:
Hi , Im new to MySql and i have no idea how to convert a sql server user defined function to MySql. Can any one help me.. CREATE FUNCTION ChildCategories (@categoryId int, @taxId int,...
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to...
221
Atli
by: Atli | last post by:
You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t. In situations where your PHP application...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.