...

View Full Version : How can I change field data ?



StanLytle
07-22-2007, 09:58 PM
I bought an up and running website that uses PHP and MySQL, but I understand very little about them and need some help.

There is a table named "Roster" with a field named "Lineage". The data in "Lineage" is input by users, and not always in the same format. What I want to do, is search for specific wording, and change only that portion. For example, I want to search all records where part of the lineage is "ex-AT&SF" and change it to "ex ATSF" without changing anything else in the field. I understand str_replace, but I don't know how to write the query. How would the query be written?

Thanks,
Stan

Fumigator
07-23-2007, 06:27 AM
Do a SELECT query using the LIKE keyword to find all of the rows containing the string you want to replace, then UPDATE each row using your scripting language replace function.

StanLytle
07-23-2007, 06:27 PM
This stuff is all new to me. I thought I'd start with just trying to find what I wanted to change, before I tried changing it. So I've played with this:

<?
$query = "SELECT RosterID, Lineage FROM Rosters2 WHERE Lineage LIKE %ex-ATSF%";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
echo "Roster ID row ". $row['RosterID'] ." contains the text ". $row['Lineage'] .;
echo "<br />";
}
?>

This was altered from something that does work, but I get " Parse error: syntax error, unexpected ';' ". I've tried changing several things, but nothing helps.

Stan

_Aerospace_Eng_
07-23-2007, 06:39 PM
Change this

echo "Roster ID row ". $row['RosterID'] ." contains the text ". $row['Lineage'] .;
to this

echo "Roster ID row ". $row['RosterID'] ." contains the text ". $row['Lineage'];
You one concatenating dots to many.

StanLytle
07-23-2007, 07:18 PM
Thanks. That helped, but only pointed out another error I had made. With trial and error, I was able to make it work. For others looking for a LIKE %....% example, here's what works:

<?
$query = "SELECT RosterID, Lineage FROM Rosters2 WHERE Lineage LIKE '%ex-ATSF%'";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
echo "Roster ID row ". $row['RosterID'] ." contains the text ". $row['Lineage'];
echo "<br />";
}
?>

Now my next question. I'd like this to automatically replace "ex-ATSF" with "ex ATSF". How would this be done? Would str_replace("ex-ATSF","ex ATSF",$Lineage) do this? Or would you have to write the replacement string to another variable ($Lineage2) and then UPDATE the table?

Thanks,
Stan

_Aerospace_Eng_
07-23-2007, 07:34 PM
Yeah I think you would need to get the current $row['Lineage'], to a str_replace on it and then use an update query to update the value. You'll need to store the original value in a $temp variable so you know where to replace the value.

StanLytle
07-24-2007, 09:02 AM
After hours of trial and error, I'm stuck. Here's where I'm at:

<?
$query = "SELECT RosterID, Lineage FROM Rosters2 WHERE Lineage LIKE '%ex-ATSF%'";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$RosterID = $row['RosterID'];
$Lineage = $row['Lineage'];
$Lineage2 = str_replace("ex-ATSF", "ex ATSF", "$Lineage");
$sql = "UPDATE Rosters2 SET Lineage = '$Lineage2' WHERE RosterID = '$RosterID'";
$result = mysql_query($sql);
}
?>

This results in "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource". Now the interesting part, is that it changes the first row in the table correctly, as specified by str_replace, then hangs with this error message. If I continue to re-run it, it eventually changes all of the required rows. That makes me think it's a loop problem, but I don't get it. I need someone to play that '60s song, what was it, "Rescue Me"?

Stan

_Aerospace_Eng_
07-24-2007, 09:08 AM
Try this

<?php
$query = "SELECT RosterID, Lineage FROM Rosters2 WHERE Lineage LIKE '%ex-ATSF%'";
$result = mysql_query($query) or die(mysql_error());
echo "Number of results is : ". mysql_num_rows($result);
if(mysql_num_rows($result) > 0)
{
while($row = mysql_fetch_array($result))
{
$RosterID = $row['RosterID'];
$Lineage = $row['Lineage'];
$Lineage2 = str_replace("ex-ATSF", "ex ATSF", $Lineage);
$sql = "UPDATE Rosters2 SET Lineage = '$Lineage2' WHERE RosterID = '$RosterID'";
$result = mysql_query($sql) or die(mysql_error());
}
}
?>
What is the number when it says Number of results found is? If your query is returning results it should be greater than 0.

StanLytle
07-24-2007, 11:03 PM
What is the number when it says Number of results found is? If your query is returning results it should be greater than 0.

Well, that didn't do the trick. I get the message: Number of results is : 164
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/locophot/public_html/AlterLineageAuto.php on line 2

Here's the entire code as it stands:

<?php
$Page = "ChangeLineage";
require "Header.php";
require "UserLevel.php";
require ("DatabaseConnect.php");
?>
<?
$query = "SELECT RosterID, Lineage FROM Rosters2 WHERE Lineage LIKE '%ex-ATSF%'";
$result = mysql_query($query) or die(mysql_error());
echo "Number of results is : ". mysql_num_rows($result);
if(mysql_num_rows($result) > 0)
{
while($row = mysql_fetch_array($result))
{
$RosterID = $row['RosterID'];
$Lineage = $row['Lineage'];
$Lineage2 = str_replace("ex-ATSF", "ex ATSF", $Lineage);
$sql = "UPDATE Rosters2 SET Lineage = '$Lineage2' WHERE RosterID = '$RosterID'";
$result = mysql_query($sql) or die(mysql_error());
}
}
?>

Thanks,
Stan

Fumigator
07-24-2007, 11:54 PM
You are re-assigning the $result variable with an UPDATE query inside your mysql_fetch_array loop, which is in the middle of using $result from your SELECT query. Use another variable, such as $updateResult, with your UPDATE query.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum