Help with mysql php update

[Tekton]

Hiya. Basically what I'm trying to do now is grab a

$upstck = $DB_site->query("SELECT stock FROM user");

What is being grabbed is a string and I need to split it, grab the first two chars, change the next two to 00, and then grab the rest, combine it and put it back :P

So for '12345678', I'd need to grab '12' as the first, '34' and change it to '00' and then grab whatever is left over and throw it all back together and get '12005678'

I'm totally lost or baffled by what I'm trying to do atm. This is for like 40,000 things, not just one btw. :P :ermm:

[Colin F]

OK, so you'll have to run through all those 40'000 things with a while() clause:

while($stock = $DB_site->fetch_array($upstck))
{


Then, parse the string:
fetch the first two chars:

$firstpart = substr($stock[stock],0,2);

fetch the last part

$lastpart = substr($stock[stock],4);

and put it all together:

$wholething = $firstpart . "00" . $lastpart;

all together that makes this:

PHP Code:

$upstck = $DB_site->query("SELECT stock FROM user");
while($stock = $DB_site->fetch_array($upstck))
{
$firstpart = substr($stock[stock],0,2);
$lastpart = substr($stock[stock],4);
$wholething = $firstpart . "00" . $lastpart;
//here's the place
} 


now I don't know what you want to do with the data, but do whatever you want where I placed the comment.

[Tekton]

Then would something like this put them all back in their right spots?

$DB_site->query("UPDATE user SET stock = '$wholething'");

That'd get them all back where they are supposed to be? =/

or perhaps I'd need to take another field like userid to make sure they all match back up?

[Tekton]

Ok, I'm pretty sure what I just said is bad. So how do I set it correctly? :P

[Colin F]

exactly, you need another field (like userid) to only update the one you changed (and not all of them).

$DB_site->query("UPDATE user SET stock = '$wholething' WHERE userid = $stock[userid]");

you'll also have to fetch the userid first, making it all together:

PHP Code:

$upstck = $DB_site->query("SELECT userid,stock FROM user"); 
while($stock = $DB_site->fetch_array($upstck)) 
{ 
$firstpart = substr($stock[stock],0,2); 
$lastpart = substr($stock[stock],4); 
$wholething = $firstpart . "00" . $lastpart; 
$DB_site->query("UPDATE user SET stock = '$wholething' WHERE userid = $stock[userid]");
} 


[Tekton]

Ah, worked. You're my php/mysql hero Colin! :P

[Colin F]

Glad to have helped