Posts categorized “SQL”.

MySQL roughly random string generation for updating rows

Ever wanted to inject hashes into existing rows of a MySQL database? The code below allows you to generate a different string for each row affected by the update and choose the random string’s length from 1 to 32 character.

I’m aware it’s not the most random of generators but for url hashes etc, it works well. Be sure to then check for duplicates, which are possible!

Change 20 to a length between 1 and 32.

Update the WHERE condition to suit your needs


UPDATE table_name
SET column_name = (
SELECT substring(MD5(RAND()), -20)
)
WHERE condition_column = 1;

Export structure and data from Navicat Premium as SQL

I was trying to find the quickest way to export both structure and data from all tables of a database from Navicat Premium and came up with these steps:

* Use the backup tool to create a backup of your database
* Right click the backup and select to Extract SQL…
* Save to wherever you like

You can now use that .sql file to create a new database wherever you like.

Cloning rows in a mysql table

If you have a lot of data in a mysql table and you’d like to duplicate some of the rows – maybe with a change or two as well – then here is the sql you’ll need…

INSERT INTO table (column1, column2,column3, column4)
SELECT column1, 234, column4, NOW()
FROM table
WHERE id IN (6,7)

You have some choices…
You can copy or clone from one table to another by changing the table names above.
You can define your own values. Examples of 234 and NOW() are above.
You can include or drop the WHERE clause to choose which (if not all) rows to clone

MySQL Multiple Primary Key Columns In A Table

Some database management tools refuse to admit you can add multiple primary key columns to one table, so you might have to do it with direct sql syntax ( deep intake of breath ).

Multiple primary keys make ‘ON DUPLICATE KEY UPDATE column = column + 100’ type queries a shed load more flexible.

Anyway, on with the code:

ALTER TABLE tablename ADD PRIMARY KEY (column_one, column_two)

And there you have it. Robert’s your fathers brother.

Return Number Of Rows With PHP From SQL Database

Occasionally working with SQL databases (not my beloved MySQL) I always struggled getting the number of rows in a consistant manner.

Until now…

Continue Reading… »