I already have data I scraped from a web table and I've noticed some leading spaces and nbsp in some entries. I realize I should have cleaned the data while scraping before inserting it into MySQL but it was a while ago and I don't feel like repeating the process if I don't have to. I came up with this PHP script (late) last night and it works up until I try to update the entries.
<?php
require_once("login.php");
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
mysql_select_db($db_database);
$query = "SELECT * FROM ingredients;";
$result = mysql_query($query);
$i = 1;
$e = array();
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
//echo $row[1];
$str = trim(mysql_real_escape_string($row[1]));
$e[] = $str;
$i++;
}
//print_r($e);
/*
$i = 1;
foreach($e as $entry) {
$query = "UPDATE ingredients
SET ing_name = $entry
WHERE ing_id = $i;";
mysql_query($query);
$i++;
}*/
?>
Couple of questions:
- Is there a way to do this strictly in MySQL without using PHP?
- What function/s should I use to strip the strings in order to convert to plaintext and remove all leading, trailing, and nbsp spaces?
- The PHP script seems to work until updating the data, what gives?
Thanks