A better way than foreach?

JDcrack

I am looking to find a more efficient way of doing this:
Do i need to make a mysql_query call for every value in the $ar array, as I have it now?
Or is there some clever way to do this in less queries?

// a really, really large array
$ar = array("a","b","v");

//mysql table is large > 500,000 records
//$somotherval is going to be different for each mysql update
foreach($ar as $val){
@mysql_query("update table set col2='$somotherval' where col1='$val'");
}

thorpe

$arraystring = implode(",",$arr);
mysql_query("UPDATE TABLE SET col2 = '$somevalue' WHERE col1 HAVING($arraystring);");

JDcrack

Hi Thorpe,

Thanks for the response but that solution will not work for me.
Your suggested solution will only work if $somevalue is the same for every row.

//$somotherval is going to be different for each mysql update

thorpe

Well then you'll obviously need a loop.

JDcrack

Hi thorpe,

Well then you'll obviously need a loop.

again, thank you for your quick reponse, however your last comment was neither productive or relevant

Perhaps I did not explain my question clearly enough:
Allow me to clarify:

I already have a loop in the form of foreach, where foreach value in my large array I am making a seperate mysql update query.
I am looking for a better way (if there is one) to do this.
Is there a more efficient way of writing these update queries? each $somotherval is different for each row, so am i stuck with doing it this way?

for the sake of illustration here is a new snippet of code

// a really, really large array
$ar = array("a","b","v");

//mysql table is large > 500,000 records
//$somotherval is going to be different for each mysql update, its not a random number, or basic integer but for the sake of illustration i'm making it one here

 foreach($ar as $val){
  $somotherval = rand(0, 9000000);
 @mysql_query("update table set col2='$somotherval' where col1='$val'");
 }

Roger_Ramjet

//$somotherval is going to be different for each mysql update, its not a random number, or basic integer but for the sake of illustration i'm making it one here

That is not the smart way to ask a question like this. Why not just tell us what the data is and where it comes form and then we'll be able to tell you jow to do it - instead of ***** in the wind.

The basic answer to your question is no, you do not need to run endless queries inside aloop to acheive this. How it should be done depends on a) what the update values are b) where they come from c) how they relate to the exisitng data in the table. That is why hiding the truth is a dumb way to ask a question.

If both are coming from outside the database then create a work table with 2 columns: the values in the array, and the $someotherval. Then run an update query with a join to the work table.

JDcrack

Roger Ramjet wrote:
That is not the smart way to ask a question like this. Why not just tell us what the data is and where it comes form and then we'll be able to tell you jow to do it - instead of ***** in the wind.

.......That is why hiding the truth is a dumb way to ask a question.

When I originally made this post, I felt that Where the $someotherval data is coming from and what it is completely irrelevant its a small string of text, and has no bearing on providing logic or idea behind a new programming direction for me to take.
I was asking for basic logic here, not for someone to write me new source code.

Roger Ramjet wrote:
If both are coming from outside the database then create a work table with 2 columns: the values in the array, and the $someotherval. Then run an update query with a join to the work table.

This answer could have been your entire post without the PMS, as it was actually the logic I was looking for, I just couldn't think of it due to a massive brain fart. I was not in the market for anyone to supply me with a code solution, just some ideas thrown out, like you did here, so thanks, to avoid all the tude respones in the future, i will make sure I qualify the post as being "ISO logic"

Not everyone on this forum is looking for some kind of "free-ride" or have others do thier work for them. I contribute plenty to this community (granted I havent been around for a little while) so responding to my question with all the tude is really unecessary

Roger_Ramjet

Alright, cop the hump. It was still a dumb way to ask the question.

Plasma

I dont think there would be a better way.

One thing you may want to do is (if the data is not going to be accessed at the same time) is lock the table/s beforehand, do the updates, then unlock.

This will let mysql run the queries faster (im not entirely sure on benchmarks) since it wont need to let other threads use the same table while your doing your processing.