Self-Powered Mission 01. Turning the Books at Home into a Database
3. Plan of Use
1. Goal
Use a search API to fill in the blank cells of the HTML table that originally held the Excel data from the early stage of this project.
2. Execution plan, part 1
1) Again, it's time to stop and think before coding.......
Hmm...
Before that) Review
At the very beginning... when I was doing Excel work... I didn't need to type out all of that.
I just needed to write down the book title.. Lesson learned ㅜㅜ Expensive study.. Trial by fire!
Option 1 )
As soon as the Excel file is uploaded (= as soon as the HTML table is generated),
use JavaScript to find the blank cells, grab the 'book title' from that row,
throw it at the Naver API, and pull the values back?
Option 2)
In the empty HTML, when you click a button,
upload the Excel, but instead of spitting it out directly to the page
-> first save it to the database,
then take the values back from the database
-> and load them into the HTML.
In the partially-filled HTML, clicking another button
pulls values from the Naver API
-> and fills in the rest of the partially-filled HTML.
Click Save and
-> it's written to the database.
I guess.. Option 2 is the right call?... Ugh.. ignorance is bliss, honestly...
Going with Option 2
(But.. ^^ today I'll stop here hehe)
Option 3)
From the morning, another thought hit me.
If I go like above, it's hard to use for any case other than the attached file.
So first,
(1) build a page and database where you can CRUD individual book records, and
(2) within that base feature set, add a bulk-add feature, and
(3) a feature that uses an API to fill in blank values.
This feature should also come with both per-record and bulk modes.
And the table should include checkboxes.
That should make it reusable for a long time.
2) Research
The goal of this research round is to use an external library.
I could code the table from scratch, but if I keep doing only what I already know, I'll stay stuck there.
I've never used an external library in PHP, so I'll give it a try this time.
The library I found is js-grid.
Why this one? It's structured around one-page form CRUD.
On top of that, it supports the backend too.
3) Let's try the examples. http://js-grid.com/samples/ has reference materials.
(1) First, the PHP version.
Usage: just download (or clone) the source provided via Git,
set up the MySQL table info in the console, and related
fill in the database-related info as an array inside db/config.php — done.
Watch out: for some reason 127.0.0.1 doesn't work... change it to localhost and it's fine.. weird.. thought they meant the same thing..
(2) I also tried the nodeJS version.
Node is even simpler. For running the example, at least.
Navigate to the project directory in the console and run node — that's it.
However, the Node here uses Jade in addition to Express,
so you don't start it the usual way with node index.js or node app.js —
you run node bin/www. (Paths may vary slightly depending on setup.)
And is this a Jade quirk?
Unlike normal Node runs, after you press Enter the console stays silent for a while;
once I hit port 3000 in the browser, the related content printed and the console responded.
(3) Takeaway from the examples.
Oh — so this is how MVC and IoC, which I did in Java, are implemented in PHP and Node. Nice!
3. Execution plan, part 2
1) I'll keep building in PHP for now.
2) Create a new project based on the example.
3) Add a button to the project table and use PHPExcel to drop Excel values into that area.
4) Add another button, wire in the Naver API to drop those values into that area.
4. Putting it into practice
(Added note 1.)
1. The coding process
This section will get long — I'll do a separate post on it in the future.. no idea when that'll be.. but probably after I refactor the current code.. ;D
1) I got stuck while customizing the reference library. I needed to learn more PHP. The object-oriented part ^^
Before, I was only doing basic-literacy-level coding practice.. but the library naturally uses the latest spec and OOP.
2) Did more practice in the Life Coding PHP track — especially the PDO section, class, and object orientation.
3) In the process I kept thinking, "Wow, this really looks just like Java."
4) After the practice, the library started to make sense.
5) Customize the library.
6) Add the Excel upload implementation on top of the customized code.
7) Inside the loop that extracts the book-title column from the Excel/sample-data upload, slot in the Naver API call.
8) Use part of the Excel/sample-data columns to pull the full info from Naver, and display the result in a temporary table.
9) Verify the data — if it looks good, INSERT it into the MySQL database.
10) Reset the existing table contents and try inserting the full Excel/data again.
2. First result — local
(Added note 2)
3. Second result — deployment
1) Research and sign up for free hosting
* For reference, I first tried the Korean service Dothome https://www.dothome.co.kr (MySQL 5.x / PHP 5.x). Adjusting storage, speed, version, and encoding took way more effort than expected. Through that process I learned you have to configure .htaccess instead of php.ini ~ nice. But there's still a long way to go on the details. For now, make it work first, refactor and optimize later.
I had signed up a long time ago for a host that somehow felt kind of shady, so I hadn't used it — ddd host (aka https://kr.000webhost.com, MySQL 5.x / PHP 7.x). I moved there. The PHP version is newer, the .htaccess defaults are well-set, and there's no Korean-character mojibake. The result matches local.
Also, with this host... you don't even need FileZilla — you can do everything from the browser.
2) Database setup
* I was just about to install and learn a tool to replace SQL Developer that I used to use in Java... when I connected to the default site they provide — phpMyAdmin — huh, this is more than enough.. heh, actually easier.. but.. I might get too lazy if I get comfortable with this.
3) Deployment https://thinknormalmybook.000webhostapp.com/
4. Greed starts creeping in
1) First implementation done... but I can already see the inconveniences. I'll improve them one by one in my free time.
(1) Accuracy improvements
Some Naver search results aren't quite accurate. I need to tweak the conditions to improve accuracy.
(2) Individual book registration UI
Since I'm using the Naver API anyway, there's no need to click + and fill every field by hand for individual registrations. Type in a title, show search results, pick one, and register it.
(3) User registration and roles
Heh... yeah... I knew all along, tried to dodge it because it'd blow up into a bigger job... but eventually had to face it lol
(4) Category addition
A key keyword for data analysis... except Naver doesn't provide this info. I'll have to plug in the National Library API as well.
(5) Data analysis and graphs
I plan to extract correlations between category keywords, word frequencies in the description column, prices of each book, etc. Actually.. this is what I started the whole thing for.. but.. long way to go lol.
2) Meanwhile, as a start...
I coded a bit of the item (2) above. Added a search input box at the top and a page that shows the search results, for a simple per-record lookup.
That's all for today...But.. as the code and pages grow, I'm starting to wonder...should I pause here and learn 'Laravel' first before coding more?
Thanks to the PHP I picked up for this self-powered mission, while digging around I learned that PHP's Laravel plays the same role as Java's Spring. It handles IoC and MVC. And with a framework like this, collaborating with others is much easier, and even when things get bloated, source management is easier.
Looking at it now, the library I'm using seems to be built with Laravel. Comparing the research material with the GitHub source, how functions and objects are structured sure looks like it.
Anyway, that's it for today, and for this post~
