Tag: MySQL

How to: Convert MySQL Hierarchical Data Flat List Result to Multi-Dimensional PHP Array

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 »

Move rows between tables, avoid duplicate and update auto increment value.

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 »

How to move MySQL datadir to another drive

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 »