Loop to update column in mysql database

Hi!

I have a database named "books_bok" and I want, from a php file update all values from colum "description_bok"

Can some expert help me with the right php code, to connect to database and loop the query ?

The new value for description_bok will be the same but filtered by a function, example

description_bok = clean($description_bok)

Any help with be greatly appreciated.

Thank you.
FernanditosAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Amar BardoliwalaCommented:
Hello Fernanditos,

Try following link should help you.

http://www.w3schools.com/php/php_mysql_update.asp

Hope it will help you.

Thank You.

Amar Bardoliwala

0
FernanditosAuthor Commented:
Sorry but it does not answer my question. Thank you anyway.
0
Amar BardoliwalaCommented:
Hello Fernanditos,

Sorry but it answers following

1. How to connect to database
2. partly to loop the query.

Regarding Looping query, I would like to ask following

1. what does a clean function do? is it PHP function?

if yes than you will need to fetch records first and loop them to update all one by one as shown in following code.

 
<?php
$con = mysql_connect("localhost","username","pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM mytable");

while($row = mysql_fetch_assoc($result))
  {
     mysql_query("update mytable set description_bok = '". clean($row['description_bok']) ."' where primary_key = " . $row['primary_key']);
  }

mysql_close($con);
?>

Open in new window


In code above you will need to replace database name, table name, fields name and primary key as per your current settings.

Hope this will help.

Thank You.

Amar Bardoliwala.

0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

venloftCommented:
this could works adjust as needed

<?
// Your sites MySQL host (typically localhost)
$mysqlHost = "localhost";
// Your sites MySQL databases name
$mysqlName = "mysqlDatabase";
// A MySQL Username that has full access to the above database
$mysqlUsername = "mysqlUsername";
// The MySQL password for the user entered above
$mysqlPass = "mysqlPassword";

// Connects to database
$dbh = mysql_connect($mysqlHost, $mysqlUsername, $mysqlPass) or die("Connection to the database failed because: ".mysql_error());
mysql_select_db($mysqlName,$dbh);

// Database Query
$query="SELECT * FROM yourTable";

//Execute Query
	$result=mysql_query($query,$dbh) or die(mysql_error());
	$filas=mysql_num_rows($result);
	//if results move them to array
	if($filas){
		//fetching to array
		$cuenta1=1;
		while($linea = mysql_fetch_array($result)) {
			foreach($linea as $key => $value){
					$dbConsulta[$cuenta1][$key]=$value;
				}
			$cuenta1++;
		}
	}else{
		mysql_free_result($result);
		
		die('Error fetching results (0) at:'.$query);
	}
	
//Free results
mysql_free_result($result);

$toCopy=$cuenta1;
$copyCount=1;
	
	while($copyCount<=$toCopy){
		$dbConsulta[$copyCount][description_bok] = clean($dbConsulta[$copyCount][description_bok]);
		$sqlUpdate="UPDATE TableName SET description_bok='".$dbConsulta[$copyCount][description_bok]."' WHERE tableId='".$dbConsulta[$copyCount][tableId]."'";
		mysql_query($sqlUpdate,$dbh) or die(mysql_error());
		$copyCount++;
	}
mysql_close($dbh);
?>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FernanditosAuthor Commented:
@amar, your solution returns this error: Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in...

@venloft, your solution is very close. It has updated all fields but with empty value.

We need to get first the current value of description_bok to pass it to my function and get the cleaned result.

why description_bok pass empty ?

Please check.

0
Amar BardoliwalaCommented:
Hello Fernanditos,

if it is giving following error

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean

it means that something is wrong with your query.

If you can paste your current code than it might help.

Look at following link for more help with mysql_fetch_assoc()

http://php.net/manual/en/function.mysql-fetch-assoc.php

Regarding problem with the code of venloft

I think you might need to change following line

$dbConsulta[$copyCount][description_bok] = clean($dbConsulta[$copyCount][description_bok]);

with following

$dbConsulta[$copyCount]['description_bok'] = clean($dbConsulta[$copyCount]['description_bok']);

Hope this will help.

Thank You.

Amar Bardoliwala






0
FernanditosAuthor Commented:
@venloft your solution works great, it was a problem with my function. I will award it.
0
venloftCommented:
the empty values were at the clean function?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.