Create a loop in a stored procedure to insert many rows with random data

A solution based on this Stack Overflow answer to insert many random data rows to given table in SQL:

TRUNCATE TABLE `devices`;

DROP PROCEDURE IF EXISTS InsertRand;

DELIMITER $$
CREATE PROCEDURE InsertRand(IN NumRows INT)
    BEGIN
        DECLARE i INT;
        SET i = 1;
        START TRANSACTION;
        WHILE i <= NumRows DO
            SET @dn = CONV(FLOOR(RAND() * 99999999999999), 20, 36);
            INSERT INTO `devs` VALUES (NULL, FLOOR((RAND() * 3)), CONCAT('Dev ', @dn));
            SET i = i + 1;
        END WHILE;
        COMMIT;
    END$$
DELIMITER;

To execute this little piece just call:

CALL InsertRand(77);

That’s all folks! :>

From Drupal-based blog to Yii-based application

If you are in need to migrate Drupal’s content data (articles, pages, events, etc.) to your own CMS or database structure then this article may be helpful for you.

Note however, that I have little to none knowledge of Drupal and that my MySQL experience is very limited. Therefore, information provided in this article may be useful, but on the other hand, I can write something wrong (even up to a complete bullshit) or mess things around.

Use this text with a caution and at your own risk. You have been warned. No money back guaranteed! :>

Read More “From Drupal-based blog to Yii-based application”

Importing large datasets to MySQL using command-line

Command-line MySQL client is way faster and more stable for importing large datasets into MySQL database. For all those Linux geeks, this is of course obvious. But, for use, lame Windowsers, using phpMyAdmin was nearly always a better choice, and this such articles like this still should be written! :>

In my case, it was 152 tables, nearly 120k records and 30 MB of uncompressed .sql file.

Read More “Importing large datasets to MySQL using command-line”

Cyclic jobs in Yii and MySQL

When we are talking about some cyclic tasks executed periodically in PHP, most of us automatically thinks about CRON. It is not surprising, since it is very popular, easy to manage (available often even on shared hostings) and easy to code (it simply fires given PHP script).

However, there are four other ways you can achieve exactly the same in Yii/PHP. And many Yii developers may not be aware about some of them.

Read More “Cyclic jobs in Yii and MySQL”

Open phpMyAdmin with preselected database

The correct URL should be like that:

http://[HOST_AND_PATH]/index.php?db=[DB_NAME]#PMAURL-1:db_structure.php?db=[DB_NAME]

I found this path to work in any conditions. After logging-in with this path, user should see phpMyAdmin open with pre-selected database ([DB_NAME]) and should see that database’s tables list, not pMA‘s home screen.

And, as I just verified this, it works even on as old phpMyAdmin as version 2.11.3.

“Class SQLiteDatabase not found” error after upgrading SQLite

Class SQLiteDatabase is an object from sqlite library, which support was dropped in PHP 5.4, but on various systems and configuration could be disabled in an earlier releases, as this library was long time marked as going to be deprecated.

Library php_sqlite.dll (Windows) or php_sqlite.so (Linux) is no longer supported in newer versions of PHP and was replaced with php_sqlite3.dll or php_sqlite3.so respectively.

Read More ““Class SQLiteDatabase not found” error after upgrading SQLite”

Some SQLite Rowid Issues

Rowid is a special, hidden column added to each SQLite database table (and to many more RDBMSes), which uniquely refer to a row. But, there are some things about rowid, that some people may not be aware of.

I know, that for some this could be a trivial. But it was not so obvious to me and since this is my blog, I can decide, what will be published here, right? :]

Read More “Some SQLite Rowid Issues”

Using PHP and Oracle database

I was working on bigger project using PHP (Yii) and Oracle database (through PDO and Yii). This isn’t a very common combination and many developers thinks, that writing PHP application to use Oracle database is a pure madness, mostly due to many strange behaviors and nasty bugs, that are present in PDO driver for Oracle. I can actually confirm this. Following article contains links to my Yii forum discussions on various aspects of using Oracle with Yii and PHP. You may find it useful, if you’re struggling with any problem.

Read More “Using PHP and Oracle database”