I have databases of users like below
good_user_mike
good_user_thomas
good_user_stevie
good_user_jhonathan
each database contains several tables eg cpd,ce,ct,cg,cb,fd,gf
... which are all similar they all contain a column names called
time,visitors,clicks,leads
1,12,42,12
2,12,45,25
3,42,45,43
..
.
.
Now my question is ,I want to make the row with time = 3
of all tables of all databases to zero so that becomes
time,visitors,clicks,leads
1,12,42,12
2,12,45,25
3,0,0,0
..
.
.
is it possible to make every row with time = 3 to zero?
The code below will do that to all databases, note: you need mysql 5.0+ for it to work
$result = mysqli_query($link, "SELECT distinct table_schema as database FROM information_schema.tables WHERE table_schema != 'information_schema'");
while($row = mysqli_fetch_assoc($result)) {
$tables = mysqli_query($link, "SELECT table_name FROM information_schema.tables WHERE table_schema = ".$row['database']);
while($row2 = mysqli_fetch_assoc($tables)) {
$update = mysqli_query($link, "UPDATE {$row2['table_name']} SET time = 0, visitors = 0, clicks = 0, leads = 0 WHERE time = 3");
}
}