Recently, for a fun stuff, I came across a need of an infinite level of categories. I stumbled uponGijs Van Tulder‘s article of “Storing Hierarchical Data in a Database” at SitePoint. Based of Van Tulder article I’m experimenting on my localhost. Below is the contents of my ‘categories’ table:+—–+——-+——–+———-+——+——+ | id | title | parent | parentid | lft |…
Read More »
Table “books” and “books_tmp” is identical. “books” table is production state.“books_tmp” is sandbox/testing/pre-production state. Data comes in csv files >> dumped to “books_tmp” >> checked, sorted, etc. >> good data moved to “books”, row by row. Before moved, “books_tmp” data is checked for duplicates in “books”, using ISBN (unique).Upon inserted into “books”, (auto increment) id is checked for duplicate, if…
Read More »Is your MySQL database consuming a significant amount of space, and your current drive is running out of storage? Moving your MySQL database to another drive can be a viable solution, minimizing downtime while potentially boosting MySQL performance, particularly on high-speed drives like Raptor or SCSI. In this guide, we’ll walk you through the process. I’m assuming: – the second…
Read More »IP address: Use: INT 4 Unsigned Convert the IP address to 32-bit unsigned integer before storing it to table. You can use built in MySQL INET_ATON() and INET_NTOA() function to convert this. Sample: INSERT INTO table SET ip = INET_ATON(‘192.168.0.1’) —————————— Unix timestamp: Use: INT ——————————
Read More »MySQL is a powerful database engine (from my side of view ;-)) But it also might have several problem. Ie: a corrupted table after database crash. Here the simple way to backup your MySQL database or table from command line. Let’s start… To backup: Login to your server via SSH. I’m usually using the following format: mysqldump -u [username] -p…
Read More »