PHP/MYSQL update file help

Misterpat · #1 08-20-2011, 06:21 PM

Hey all,

I'm in the process of updating my site and need to update the users id# in one column. I have about 110 users, so i'm trying to get a php script to update them for me instead of running sql commands for each.

What I came up with so far is a combination of stuff I found googling this. I was wondering if someone could help me straighten out this code. Basically, I want to put in the users old user id along with their new one, hit update and have it search and replace the old with the new. I know I will have to do this for each user, but I don't mind that.

here is the code I put together so far.

Code:

<?php

$hostname = "localhost"; // usually is localhost
$db_user = "******"; // change to your database password
$db_password = "******"; // change to your database password
$database = "*******"; // provide your database name
$db_table = "users"; // leave this as is


# STOP HERE
####################################################################
# THIS CODE IS USED TO CONNECT TO THE MYSQL DATABASE
$db = mysql_connect($hostname, $db_user, $db_password);
mysql_select_db($database,$db);
?>
<html>
<head>
<title>User Changer</title>
</head>
<body>

<?php
if (isset($_REQUEST['Submit'])) {
# THIS CODE TELL MYSQL TO INSERT THE DATA FROM THE FORM INTO YOUR MYSQL TABLE
$sql = "UPDATE $db_table
SET userid = REPLACE(userid, "old_user", "new_user")
WHERE column LIKE "%old_user%";";
if($result = mysql_query($sql ,$db)) {
echo '<h1>Thank you</h1>Your info has been changed in this database<br><br>';
} else {
echo "ERROR: ".mysql_error();
}
} else {
?>
<h1><hr>
<form method="post" action="">
Old User Number:<br>
<input type="text" name="old_user">
<br>
New user Number: <br>
<input type="text" name="new_user">
<br><br>
<input type="submit" name="Submit" value="Submit">
</form>
<?php
}
?>
</body>
</html>

Thanks for any help with this.

Misterpat · #2 08-20-2011, 07:47 PM

I think Im getting somewhere.

Code:

$db = mysql_connect($hostname, $db_user, $db_password);
mysql_select_db($database,$db);
?>
<html>
<head>
<title>User Changer</title>
</head>
<body>

<?php
if (isset($_REQUEST['Submit'])) {
# THIS CODE TELL MYSQL TO INSERT THE DATA FROM THE FORM INTO YOUR MYSQL TABLE
 $sql = "UPDATE $db_table
$strquery = "SET userid = REPLACE(userid, "$old", "$new")
WHERE column LIKE "%$old%";
mysql_query($strquery);

if($result = mysql_query($sql ,$db)) {
echo '<h1>Thank you</h1>Your stuff has been changed in this database<br><br>';
} else {
echo "ERROR: ".mysql_error();
}
} else {
?>
<h1><hr>
<form method="post" action="">
Old User Number:<br>
<input type="text" name="old_user" value="<?=$new;?>" />>
<br>
New user Number: <br>
<input type="text" name="new_user" value="<?=$old;?>" />>
<br><br>
<input type="submit" name="Submit" value="Submit">
</form>
<?php
}
?>
</body>
</html>

Still not there yet.

justMe · #3 08-21-2011, 01:48 PM

if you are willing to submit a form 110 times, why not just do this job with phpmyadmin? just edit the id of each user in the users table.

otherwise, your query should be something like this.

Code:

$query = "UPDATE tablename SET tablename.userid = $newId where tablename.userid = $oldId;";
mysql_query($query);

of course you need to supply the $newId and $oldId variables as well as the 'tablename'...

Misterpat · #4 08-21-2011, 07:39 PM

Quote:

Originally Posted by justMe

if you are willing to submit a form 110 times, why not just do this job with phpmyadmin? just edit the id of each user in the users table.

otherwise, your query should be something like this.

Code:

$query = "UPDATE tablename SET tablename.userid = $newId where tablename.userid = $oldId;";
mysql_query($query);

of course you need to supply the $newId and $oldId variables as well as the 'tablename'...

Thank You for that code. Does this look right?

Code:

$strquery = "UPDATE $db_table SET $db_table.$db_column = $new where $db_table.$db_column = $old;";
$result = mysql_query($strquery);

Your right about the phpmyadmin. As I come to realize it would be just as quick that way. BUT, once this is done, I have to change a few variables and change another table with 12607 rows. using the form 110 times would be easier that searching with phpmyadmin, no?

Thanks for the help.

justMe · #5 08-21-2011, 10:18 PM

Yes that query looks fine.
You will yet need to assign values to those $variables before the query is executed.

You could run the same query from within phpmyadmin.

You may need to modify your query to update other tables/values depending on the 'datatype' of the value being replaced.

I hope you don't need to update all 12607 rows.

bcaa8ra · #6 08-22-2011, 02:11 PM

What is the value for the new userid? Is it being generated from the old ID or is it a serial number ?

If it can be calculated, you can do it all in one query...

twohawks · #7 08-24-2011, 01:12 PM

Just passing thru and saw this. Although I prefer to run sql commands from something like sql manager, phpmyadmin, etc... once in a while I find it better/faster/easier to simply export the relevant tables as a single sql file to my local PC, and then run grep / replace (search replace) in a competent text editor. Then restore the database/tables and test.

Just some 2-sense there. ;^)

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Update servers with new PHP 5.3	brocilybeef	Suggestions	2	08-28-2009 01:56 AM
PHP MYSQL file editing question	Misterpat	Webhosting	25	04-04-2009 08:54 AM
How do i update my .htaccess file?	daabomb2002	Shared Hosting Support	4	05-25-2007 09:44 PM
PHP errors = prompt to download php file?	FragMaster B	Shared Hosting Support	0	11-03-2006 12:41 PM