My supervisor gave me an assignment on PHP and SQL, and I've tried to solve it, but all to no avail. How can I update the records on my table, by adding a string into the specified field? For example, if the record in a column/field is 'P000M000D000'. How can I add a '0' before the 'M' or 'D' without replacing the entire string. An UPDATE query would replace the entire record in that column with the added string, and I don't want that.
Does anyone know the string function I need to use?
Kelvin
MySQL has a lot of nifty string functions: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html
The approach I would use is to use M and D as a separator and extract the part before and after using substring_index.
Note that substring_index is case-sensitive.
SET @sampledata = 'P111M2222D3333';
UPDATE table1
SET col1
= CONCAT(substring_index(@sampledata, 'M', 1), '0M' /*P1110M*/
,substring_index(substring_index(@sampledata, 'D', 1), 'M',-1) /*222*/
,'0D' ,substring_index(@sampledata, 'D',-1)) /*0D333*/
WHERE col2 = 1234;
Since you are a beginner I recommend that do it in three steps:
1. Retrieve the field from the table you want to update.
$query = "SELECT name FROM contact where uid=123";
$result = mysql_query($query);
$row=mysql_fetch_array($result);
$name=$row['name'];
2. Use PHP string functions to modify the value in whatever way you want to.
$fname = preg_split("/[\s]+/", $name); //splits on any number of space chars
3. Use a simple Update query.
$query = "update contact set name='$fname' where uid=123";
$result = mysql_query($query);
Use the replace function where you can specify a search string and a replacement string within a column. Be sure to constrain with a where clause.
update tablename set colname = replace(colname,searchstring,replacestring) where something=something
replace is documented in the MySQL documentation.
