Help with looping through and processing data stored in mysql

[idontknowphp]

Here is the breakdown for the issue i am having...

 

I want to loop through the db and convert the strings & add to the new column so i do not need to do it manually as there are thousands of entries.

I have figured out some stuff about while loops and i think that is what i need to do...

 

1. Get data from each row in oldNum

2. Remove the special characters from the string

3. Add the new clean data to the same row in newNum

 

example of what results should look like in the sql db:

 

id  |  oldNum  |  newNum

-----------------------------------

1    |  3.12(a)  |    312a

 

You dont need to write the solution for me, if you can point me in the right direction that would be great too.

thanks in advance guys!

 

[Psycho]

Using a loop in PHP to iterate over every record is inefficient and not needed. You can do what you need with a single query using one of the mysql string functions. So, is the example you provided above the same situation for all the values you need to update, i.e. you want to remove the parens?

[Psycho]

OK, if all you need to do is take the value from "oldNum", remove the parens and place that value in "newNum" you just need to run this query to process all records

UPDATE `table_name`
SET `newNum` = REPLACE(REPLACE(`oldNum`, '(', ''), ')', '')

 

If you have other conversions that need to be handled provide a detailed explanation of all the different scenarios and/or provide some more examples.

 

EDIT: Moving thread to MySQL forum

[idontknowphp]

How can i process it using this regex?

 

$string = ""

$new_string = preg_replace(“/[^a-zA-Z0-9s]/”, “”, $string);

[idontknowphp]

Not trying to bump, there is no edit button on the previous reply....I actually am working on this still, however my new focus is how to implement the cleaning properly...I cant seem to pass the 'oldNum' column data to the cleanString function appropriately here is what i have now

 

 

function cleanString($string) {
   $new_string = preg_replace("/[^a-zA-Z0-9\s]/", "", $string);
   return $new_string;
}

if(isset($_POST['submit'])) {
   $db = @mysql_select_db($db, $connection) or die(mysql_error());
   $sql = "UPDATE theTable SET newNum = " . cleanString(oldNum);
   $result = @mysql_query($sql, $connection) or die(mysql_error());
}  

 

Edit: Trying to give better explanation

[idontknowphp]

Not bumping, once again, I am just updating my progress on the issue...

 

function cleanString($string) {
   $new_string = preg_replace("/[^a-zA-Z0-9\s]/", "", $string);
   return $new_string;
}

if(isset($_POST['submit'])) {
   $connection = @mysql_connect($db_server, $db_user, $db_pass) or die(mysql_error());
   $db = @mysql_select_db("the_db", $connection) or die(mysql_error());
   $old = mysql_fetch_assoc(mysql_query("SELECT oldNum FROM theTable")); 
   $sql = 'UPDATE theTable SET newNum="' . cleanString($old) . '"';
   $result = @mysql_query($sql, $connection) or die(mysql_error()); 
}

[Psycho]

You can't put a PHP function inside a query like that. A query is run on the MySQL server THEN it returns the results to PHP. Let me take a look and see if that can be done within MySQL. I'll post back shortly.

[Psycho]

OK, it doesn't look like you will be able to do that with just a query. So you will need to do a select query that pull the oldNum and the unique ID field for each record (I hope they have one). Then process all the record and generate/run the UPDATE queries. You could do a loop and do a single update for each record, but that will be less efficient.

 

A couple notes on your last bit of code. You were using the "@" before the sql calls - that will suppress errors. but, then you had an "or die()" in case the process failed. So, that doesn't make sense.

 

Anyway, this should do what you need - I also included a report feature to display all the results of the updates. But, if you have hundreds/thousands of records you may want to comment that out.

 

<?php

function cleanString($string)
{
   return preg_replace('/[^a-z0-9\s]/i', '', $string);
}

if(isset($_POST['submit']))
{
    //Connect to DB
    $connection = mysql_connect($db_server, $db_user, $db_pass) or die(mysql_error());
    mysql_select_db("the_db", $connection) or die(mysql_error());
    //Create and run SELECT query
    $query = "SELECT id, oldNum FROM theTable";
    $result = mysql_query($query) or die(mysql_error());
    //Process the results
    $resultsHTML = '';
    while($row = mysql_fetch_assoc($result))
    {
        $newNum = cleanString($row['oldNum']);
        $query = "UPDATE theTable SET newNum='{$newNum}' WHERE id = {$row['id']}";
        $result = mysql_query($query);
        //Create HTML for report output
        $resultText = (!$result) ? mysql_error() : $newNum;
        $resultsHTML .= "<tr><td>{$row['id']}</td><td>{$row['oldNum']}</td><td>{$resultText}</td></tr>\n";
    }
}
?>
<html>
<body>
Results<br>
<table>
  <tr>
    <th>Record ID</th>
    <th>Old Number</th>
    <th>New New Number</th>
  </tr>
  <?php echo $resultsHTML; ?>
</table>
</body>
</html>

[idontknowphp]

Thank you for all your help....I really appreciate it....I got this error.

 

"Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home1/oregoner/public_html/test.php on line 33"

 

Line 33:      while($row = mysql_fetch_assoc($result))

 

 

Not sure what is going on, it gave me one result...

 

some of the rows do not have any special characters, could that potentially be where the problem lies?

 

I only ask because the first row has S.C. and the second does not...

[Psycho]

Ah, I used $result a 2nd time before I was done with the first one.

<?php

function cleanString($string)
{
   return preg_replace('/[^a-z0-9\s]/i', '', $string);
}

if(isset($_POST['submit']))
{
    //Connect to DB
    $connection = mysql_connect($db_server, $db_user, $db_pass) or die(mysql_error());
    mysql_select_db("the_db", $connection) or die(mysql_error());
    //Create and run SELECT query
    $query = "SELECT id, oldNum FROM theTable";
    $result = mysql_query($query) or die(mysql_error());
    //Process the results
    $resultsHTML = '';
    while($row = mysql_fetch_assoc($result))
    {
        $newNum = cleanString($row['oldNum']);
        $query = "UPDATE theTable SET newNum='{$newNum}' WHERE id = {$row['id']}";
        $result2 = mysql_query($query);
        //Create HTML for report output
        $resultText = (!$result2) ? mysql_error() : $newNum;
        $resultsHTML .= "<tr><td>{$row['id']}</td><td>{$row['oldNum']}</td><td>{$resultText}</td></tr>\n";
    }
}
?>
<html>
<body>
Results<br>
<table>
  <tr>
    <th>Record ID</th>
    <th>Old Number</th>
    <th>New New Number</th>
  </tr>
  <?php echo $resultsHTML; ?>
</table>
</body>
</html>