Update (Replace or Delete) MySQL Data
Change repetitive db data with one single syntax command. Rather than manually find & alter each instance, isolate the lot & update multiple content.
This is handy when you want to swap or lose an identical content string across all the data entries in a db table field.
Use it with care but, hey, talk about a time saver.
Setup Unmanaged VPS: The Ubuntu-Nginx Guide
Take your virtual private server from zero to hero
.. from blank box to cute-as server ..
with this easy-to-follow copy/paste guide.
22+ parts with video, here’s the index.
Log into your CLI, then into mysql like this:-
.. and input your mysql password.
Now, let’s open the database we want to manipulate:-
.. where database is the name of your db.
The command itself:-
“Update” Syntax Broken Down
- UPDATE tells mysql we want to change something
- table is the database table in which we want to change something
- SET field will isolate the specific field, within the table we specified already, for alteration
- = tells mysql we’re about to equate the actual change within that specified field within that specified table
- field is where we remind mysql of the field. I dunno why we repeat ourselves but we just do
- old is the string of data we want to change
- new is the content to replace the old string
NB we use backticks (`) to target tables and fields, and quotation marks (“) to specify the content
OK. Here’s an example.
Let’s say you’ve got a WordPress blog littered with references to some bloke called Jack, but Jack just had a change of mind and now answers to the name of Jill. OK, sorry, but bear with me, it’s a spur of the moment example. So you want to swap Jack for Jill.
The string Jack can be found in a specific field within a specific table. In this case, and with WordPress, the table is wp_posts and the field within is post_content. We’ve already specified our db. When making the content change, we must now isolate the field, within its table.
Here’s the code:-
MySQL will come back and tell you it’s done with something like:-
And to Delete a String of Content in MySQL?
Probably there are other ways to do this, but this related method works for me:-
.. Now we’ve replaced Jack with nothing. He’s gone.
Setup Unmanaged VPS: The Ubuntu-Nginx Guide
Take your virtual private server from zero to hero
with this easy-to-follow copy/paste guide.
“My local PC runs Windows” Show me for Linux
“My local PC runs Linux” Show me for Windows
22+ parts with video, here’s the index ..
-
Setup Unmanaged VPS: The Ubuntu-Nginx Guide
Introducing vpsBible’s how-to guide for setting up a super-charged unmanaged VPS featuring Ubuntu and Nginx.
-
VPS (Virtual Private Server) vs Shared vs Dedicated
Before getting stuck in, let’s compare the web host types in detail and weigh up the differences between managed & unmanaged VPS.
-
Set Up a VPS Linux Distribution
Opening a VPS hosting account, we’ll choose, configure & launch a Linux server ‘distribution’.
-
Set Up Command Line Interface (CLI) using PuTTY
Now we’ve got a server to play with, we’ll connect to it, using the ’ssh’ protocol for a super-secure link.
-
Create a Linux User & Set Permissions
Initially you’ve logged into your remote Linux machine as ‘root’. We’ll add you as a user now, with root or SuperUser permissions.
-
Encrypt Data with OpenSSH & Auto-Login with PuTTY
Now we can use ‘authentication keys’ to shore up our connection while simplifying login to an automated, password-free yet secure process.
-
Harden the Secure Shell (SSH) & Create a Firewall
The final security step: galvanizing the OpenSSH protocol and setting up an ‘iptables’ firewall. Now the server is rock ruddy solid.
-
Edit bashrc for User-Friendly Linux, plus System Updates
We use the terminal a whole lot so let’s cut ourselves a break and create some command shortcuts, then update the server.
-
PHP5, MySQL and Xcache (for Platforms like WordPress)
We have our foundation, let’s lay some bricks: PHP for server-side web applications, Xcache to speed things up & MySQL for databases.
-
Add a Domain Zone to Your VPS
To make your server aware of yourDomain.com you’ll need a few ‘DNS’ records. Damn this is simple I’m practically asleep!
-
Prepare Linux Server for Email with Postfix
In order to send and receive email from our web applications we’ll install some software and tweak the ‘RDNS’ record.
-
Nginx (better than Apache) Web Server
OK I woke up .. let’s install this supersonic web server, tweaking its file structure & adding default configuration files.
-
Setup FileZilla for Secure FTP (SFTP)
There’s FTP, and then there’s ‘Secure FTP’. We’ll set up FileZilla using SFTP – and those authentication keys – for secure file transfers.
-
Serve Multiple Sites & Blogs with Virtual Hosts
We want a website folder structure, some permissions, some site-specific configuration files and, hey, we’ll hook up ‘FastCGI’.
-
Adding Sub-Domains with Nginx
Need a sub-domain? Here’s how to add www.sub.domain.com with an ‘A record’ and a configuration file. (I’ll allow you to skip this bit.)
-
Integrate A CMS Like WordPress, WP MU, Drupal etc
For those of you wanting to use a specific content framework, such as a CMS, blogging or forum software, pop in here and we’ll sort that out.
-
Configure phpMyAdmin for Nginx to Maintain MySQL
Rather than have to crunch databases via the command line, we can make life easier by adding this splendid tool to manage them.
-
Google Apps for Domain-Specific Email
GA’s free Standard Edition for catchall webmail – POP-ping or IMAP-ped to your desktop client – saves precious server resources for your webs.
-
Moving Day! How to Move Your Blog or Site
Nearly there. Here’s the web site or blog migration guide, relocating to your VPS with minimal or no downtime.
-
Appendix 1a: Nginx Control Panel
You’re migrating shared-to-VPS and want a GUI like cPanel? No you don’t, not after you read this! What’s more, you don’t need one either.
-
Appendix 1b: Nginx Control Panel Workarounds
For every control panel module there’s an equivalent terminal command. They’re all linked from here so, hey, no excuses!
-
Appendix 2: Add Web Sites Nginx Cheatsheet
Once you’ve set up your first site, adding more gets easier. Especially when you can refer to this. (Let’s face it, I’m just too good to you.)
Got a Question? Want to Comment?
This site has no comments system. We've got a better system!
Please use the link at the top of the post and goto this topic's forum thread:-
For those posts ported from Guvnr.com (pre-Feb/2010), archive comments remain.
Handy info from these comments is in the process of being ported to the forum and attributed to the original author.
If you think this idea sucks, let me know.
Thank you.