Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New to the CF scene
    Join Date
    Jul 2007
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How can I change field data ?

    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

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #3
    New to the CF scene
    Join Date
    Jul 2007
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #4
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    Change this
    PHP Code:
    echo "Roster ID row "$row['RosterID'] ." contains the text "$row['Lineage'] .; 
    to this
    PHP Code:
    echo "Roster ID row "$row['RosterID'] ." contains the text "$row['Lineage']; 
    You one concatenating dots to many.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #5
    New to the CF scene
    Join Date
    Jul 2007
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #6
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    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.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #7
    New to the CF scene
    Join Date
    Jul 2007
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #8
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    Try this
    PHP Code:
    <?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.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #9
    New to the CF scene
    Join Date
    Jul 2007
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #10
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •