Back To Programming / Scripting / Coding Forum

Prepending and Appending to Database Fields

  • benwade9721
  • Novice
  • Novice
  • User avatar
  • Joined: Jan 20, 2009
  • Posts: 17
  • Status: Offline

Post January 20th, 2009, 4:02 pm

Hi everyone,

I was trying (2 hours) to write a script to append or prepend a value to a field in a MySQL database.
The following script works:
Code: [ Select ]
<?
$conn = mysql_connect('localhost', 'root', '');
if (!$conn) die('Could not connect: ' . mysql_error());

mysql_select_db('db_name', $conn);

$result = mysql_query("SELECT * FROM table_one");
while($row = mysql_fetch_array($result)) {
    $id = $row['SupplierID'];
    $updatedAddress = 'ca_' . $row['Address'];
    mysql_query("UPDATE table_one SET Address = '$updatedAddress' WHERE SupplierID=$id AND country='France'");
}

mysql_close($conn);
?>
  1. <?
  2. $conn = mysql_connect('localhost', 'root', '');
  3. if (!$conn) die('Could not connect: ' . mysql_error());
  5. mysql_select_db('db_name', $conn);
  7. $result = mysql_query("SELECT * FROM table_one");
  8. while($row = mysql_fetch_array($result)) {
  9.     $id = $row['SupplierID'];
  10.     $updatedAddress = 'ca_' . $row['Address'];
  11.     mysql_query("UPDATE table_one SET Address = '$updatedAddress' WHERE SupplierID=$id AND country='France'");
  12. }
  14. mysql_close($conn);
  15. ?>


but I am curious does anyone know how to do it entirely in SQL or if there is a better way to do it in PHP???

I am using the sample Northwind db from MS Access.

Cheers,

Ben
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post January 20th, 2009, 4:02 pm

  • joebert
  • Sledgehammer
  • Genius
  • No Avatar
  • Joined: Feb 10, 2004
  • Posts: 13455
  • Loc: Florida
  • Status: Offline

Post January 20th, 2009, 11:45 pm

Here's an example from scratch including a potentially major screwup with a way to prevent it.

First we create ourselves an example table to work with in my test database.
Code: [ Select ]
mysql> USE test;
 
Database changed
 
mysql> CREATE TABLE `prepend_example`(
    -> `col` VARCHAR(8)
    -> );
Query OK, 0 rows affected (0.18 sec)
 
mysql> INSERT INTO `prepend_example`
    -> VALUES ('one'),('two'),('three');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM `prepend_example`;
+-------+
| col   |
+-------+
| one   |
| two   |
| three |
+-------+
3 rows in set (0.02 sec)
  1. mysql> USE test;
  2.  
  3. Database changed
  4.  
  5. mysql> CREATE TABLE `prepend_example`(
  6.     -> `col` VARCHAR(8)
  7.     -> );
  8. Query OK, 0 rows affected (0.18 sec)
  9.  
  10. mysql> INSERT INTO `prepend_example`
  11.     -> VALUES ('one'),('two'),('three');
  12. Query OK, 3 rows affected (0.00 sec)
  13. Records: 3  Duplicates: 0  Warnings: 0
  14.  
  15. mysql> SELECT * FROM `prepend_example`;
  16. +-------+
  17. | col   |
  18. +-------+
  19. | one   |
  20. | two   |
  21. | three |
  22. +-------+
  23. 3 rows in set (0.02 sec)


Now it's as simple as using the CONCAT string function to prepend a value to our column.

Code: [ Select ]
mysql> UPDATE `prepend_example`
    -> SET `col` = CONCAT('prefix_', `col`)
    -> WHERE `col` = 'two';
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1
  1. mysql> UPDATE `prepend_example`
  2.     -> SET `col` = CONCAT('prefix_', `col`)
  3.     -> WHERE `col` = 'two';
  4. Query OK, 1 row affected, 1 warning (0.02 sec)
  5. Rows matched: 1  Changed: 1  Warnings: 1


Or is it ?

Quote:
Rows matched: 1  Changed: 1  Warnings: 1


What gives ?
 
Code: [ Select ]
mysql> SELECT * FROM `prepend_example`;
+----------+
| col      |
+----------+
| one      |
| prefix_t |
| three    |
+----------+
3 rows in set (0.00 sec)
  1. mysql> SELECT * FROM `prepend_example`;
  2. +----------+
  3. | col      |
  4. +----------+
  5. | one      |
  6. | prefix_t |
  7. | three    |
  8. +----------+
  9. 3 rows in set (0.00 sec)


The warning was telling us that the new string was truncated due VARCHAR(8) not being large enough.

Alrighty then.

Let's rewind back to the point right before doing the UPDATE and pretend we haven't done it yet.
First we need to know how long the longest entry that we will be working with is.

Code: [ Select ]
mysql> SELECT MAX(LENGTH(`col`)) FROM `prepend_example`;
+--------------------+
| MAX(LENGTH(`col`)) |
+--------------------+
|                  5 |
+--------------------+
1 row in set (0.01 sec)
  1. mysql> SELECT MAX(LENGTH(`col`)) FROM `prepend_example`;
  2. +--------------------+
  3. | MAX(LENGTH(`col`)) |
  4. +--------------------+
  5. |                  5 |
  6. +--------------------+
  7. 1 row in set (0.01 sec)


5, cool. We know that "prefix_" is 7 characters so we need at least a VARCHAR(12).

It's been awhile since we created this example table and my memory is rather short,
so we better check to see how big our column is real quick.

Code: [ Select ]
mysql> SHOW columns FROM `prepend_example`;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col   | varchar(8) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)
  1. mysql> SHOW columns FROM `prepend_example`;
  2. +-------+------------+------+-----+---------+-------+
  3. | Field | Type       | Null | Key | Default | Extra |
  4. +-------+------------+------+-----+---------+-------+
  5. | col   | varchar(8) | YES  |     | NULL    |       |
  6. +-------+------------+------+-----+---------+-------+
  7. 1 row in set (0.01 sec)


Well now VARCHAR(8) will never do, we better make that bigger.

Code: [ Select ]
mysql> ALTER TABLE `prepend_example`
    -> CHANGE `col` `col` VARCHAR( 16 );
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
  1. mysql> ALTER TABLE `prepend_example`
  2.     -> CHANGE `col` `col` VARCHAR( 16 );
  3. Query OK, 3 rows affected (0.02 sec)
  4. Records: 3  Duplicates: 0  Warnings: 0


Let's try that UPDATE again, but for fun we'll append something this time.

Code: [ Select ]
mysql> UPDATE `prepend_example`
    -> SET `col` = CONCAT(`col`, ' points')
    -> WHERE `col` = 'three';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
  1. mysql> UPDATE `prepend_example`
  2.     -> SET `col` = CONCAT(`col`, ' points')
  3.     -> WHERE `col` = 'three';
  4. Query OK, 1 row affected (0.00 sec)
  5. Rows matched: 1  Changed: 1  Warnings: 0


And from half-court at the buzzer.

Code: [ Select ]
mysql> SELECT * FROM `prepend_example`;
+--------------+
| col          |
+--------------+
| one          |
| two          |
| three points |
+--------------+
3 rows in set (0.00 sec)
  1. mysql> SELECT * FROM `prepend_example`;
  2. +--------------+
  3. | col          |
  4. +--------------+
  5. | one          |
  6. | two          |
  7. | three points |
  8. +--------------+
  9. 3 rows in set (0.00 sec)


Swish! :D
Strong with this one, the sudo is.
  • benwade9721
  • Novice
  • Novice
  • User avatar
  • Joined: Jan 20, 2009
  • Posts: 17
  • Status: Offline

Post January 22nd, 2009, 3:48 pm

What an example joebert, swish is an understatement

This is how you roll

Image

Thanks

Page 1 of 1

Post Information

  • Total Posts in this topic: 3 posts
  • Users browsing this forum: No registered users and 88 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.