Further to others' responses, you might also want to grab MySQL Workbench:
> https://www.mysql.com/products/workbench/
Mostly so that you can at least connect to the database that you're working with, in order to be able to see what's in there, without all of the pissing about with Excel at first.
At the very least, that'll help you confirm that you can actually connect to the MySQL server that you need from your machine, and will give you a clear picture of what tables and data are available. Then, use that information to make the connection from the Excel side.
Never used Excel's Power Query thing, though.
Using MySQL Workbench might help ease the pain. Anyway: Learn to code. Python is not difficult to pick up, and would lead you to projects in which you could leverage SQL in a concrete way which might be easier to grasp.
I'm not sure if this is what you're after, but check out mysql workbench. You still need to know a fair amout of SQL CLI but mysqlworkbench will autocomplete some things such as tablenames and warn you if your syntax is incorrect: https://www.mysql.com/products/workbench/
It's definitely not comprehensive but I wanted to be sure you were aware of it.
MySQL would do what you want and MySQL Workbench is a nice tool for building a database. It allows you to build a diagram and then forward engineer your database from there.
This site has a mysql tutorial
Backup the MySQL database on the Mac and restore it into the MySQL install on Windows? Seems pretty straight-forward.
Use something like MySQL workbench to make it a bit easier.
MySQL Workbench and MySQL Enterprise monitor are respectively a free and a paid first-party tool for MySQL administration and monitoring.
Both use the underlying performance schema to get profiling information about the server.
How big is the database, and how complicated is the schema? If you are planning to move it to wordpress, it will need to run on mysql / mariadb. If it's not huge, or using a bunch of stored procedures etc, it might be simple enough to migrate using mysql workbench : https://www.mysql.com/products/workbench/migrate/
If I were you, I’d use this as an opportunity to stop using PMA forever. Look into a tool like my MySQL Workbench instead:
https://www.mysql.com/products/workbench/
PMA attracts its own set of attacks and it’s another web surface to keep updated. Workbench and similar tools give you similar tooling without adding in an attack surface.
As for restrictions, when you check out your MySQL users, there will be symbols denoting where they can connect from. The most secure way is to only allow connections from localhost but that won’t work with tools like Workbench.
For a small project I usually run a MySQL server on the same machine as the Node.js server. It's much cheaper than having multiple servers and usually the performance impact isn't noticeable. Also use MySQL Workbench to connect remotely so you get a GUI.
Install Wamp Going to assume you use Windows. These instructions still apply to other OS
Run Wamp and find it in the Windows Task Trey. Left Click and Choose PHPmyAdmin.
Choose new from the left hand side. Name the Database Cheaters.
With the Database list selected choose "Cheaters"
Choose Import. The file(s) should easily import and format correctly.
Although I don't know as I wouldn't download it. That data dump contains partial credit card info. If you already downloaded, it doesn't matter anymore so you might as well look.
There are like 10 others ways to view it but if you wanted to learn some MySQL then this will work.
Once you have it imported and have the right DB chosen google how to sort the results where the email contains a ".edu" or ".gov".
Good luck
Edit: If you didn't want to download a whole webserver setup then get MySQL Workbench It's the same thing as PHPmyAdmin but it isn't in your browser.
> SQL Workbench
did you mean MySQL Workbench? that's just a front-end app
but it access MySQL database
and MySQL doesn't use LISTAGG, it uses GROUP_CONCAT
mysql workbench (https://www.mysql.com/products/workbench/) is a nice UI application to manage your DB, it does allow you to view the connections and I assume many other applications can do it, as you could simply get all the connections with a SQL query:
SHOW PROCESSLIST
WRT question #1: Yes, you definitely need to know SQL if you're going to query an RDBMS like MySQL. How advanced you need to be depends on the kinds of queries you'll need to write; you'll get good at it with practice though, so no need to be an expert from the get-go. Use the MySQL documentation to learn the basic SQL syntax for writing queries. Then refer back to the docs as the queries you need to write get more complex. There's really no substitute for getting your hands dirty! And, obviously, tap r/sql, r/database, and r/mysql when you need more help.
WRT #2, you can use a tool like MySQL Workbench to browse your database schema (and issue queries against the data). MySQL Workbench also has a feature that lets you "reverse engineer" a database and generate an ERD (entity/relationship diagram) from it to help you visualize the schema and the relationships between tables; here's a how-to article. BUT, no tool can tell you what the data is, or how it's collected and used; you really have no other option but to consult IT (the devs) for that.
HTH
You should really address the issue with phpMyAdmin, but as a local workaround: MySQL Workbench
Also, you don't need an external server to get MySQL as a database. You can easily set up a local MySQL (MariaDB) server via XAMPP
If you can afford to get it, buy the outright license, not the subscription. Like I said, I'm sill using the last update I purchased in 2011, haven't seen a feature yet to justify me updating.
I haven't used it myself, but there may be something like it in Workbench
to work with a database in a manner that's significantly easier than the command line, you need a "front end tool" such as MySQL workbench or HeidiSQL
What host are you specifying when you're connecting to the db? Server IP or localhost?
Could you install MySQL Workbench? https://www.mysql.com/products/workbench/
Try and connect to your DB server from your local machine and see if you can.
If you’re trying to learn MySQL and you just want to spin up a database you can use mamp or xamp depending on your system.
This has some good info on how to get started on a Mac. Xamp is pretty similar. https://documentation.mamp.info/en/MAMP-Mac/First-Steps/
From your local page you can add a user to your dB in phpmyadmin. Then you can use that user to sign into your dB. https://documentation.mamp.info/en/MAMP-Mac/Preferences/Open-WebStart-Page/
You might want to use a sql client. Unless you need to use VS, I’d recommend MySQL workbench. https://www.mysql.com/products/workbench/
You install it according to whatever instructions came with it. I don't have a mac handy at the moment, so I'm not going to be able to help much with that.
Generally, databases run as services. That is to say that they will start when the OS boots, and they will continue to run, silently in the background, until explicitly turned off or until the computer is powered down.
Code interacts with databases via drivers/TCP ports/sockets. Humans interact with databases via clients - at its simplest, that's the MySQL command line client (called 'mysql'). The sort of UI you're probably thinking of is more like MySQL Workbench: https://www.mysql.com/products/workbench/.
Either way, you're going to need at least one client, and probably two - the CLI and workbench are good places to start. You'll need to know where the DB is listening for client connections. For MySQL, that is usually on port 3306 or via a socket, depending on what you told the DB to do when you installed it.
Don't overthink it. Make a sql database: https://www.mysql.com/products/workbench/
Get the connection string from your database then plug it into your application: https://dev.mysql.com/doc/connector-cpp/1.1/en/connector-cpp-examples-complete-example-1.html
SQL is very much not my forte, but I found enabling database access and then using MySQL Workbench to connect helped a lot.
You can browse through all the tables and test your queries from there.
I also found a lot of example queries on itninja.com that helped get me started.
I would probably start with https://www.oracle.com/database/technologies/appdev/sql-developer.html
For the record, if you're going between two mysql databases then this could work, but it only supports mysql: https://www.mysql.com/products/workbench/design/
That's probably just because php is awful! :)
(Note: this kind of programming issue is not a math thing, this is just the way things go sometimes.)
How are you trying to connect to MySQL? Do you have MySQL Workbench installed? (If you don't you should. It's a really really helpful front end to the DB itself).
>Another commenter mentioned that I should start with the front-end, storing data locally, and then transition to the backend. I sort of assumed it would be more logical to go in the other direction, so what is your opinion on that front?
That's actually how I tend to develop as well: starting with the front end. It's just a little easier to get something up and running that way.
>Also, you mentioned several good languages to start looking at, but are there any issues to keep in mind with cross-compatibility with languages and other parts of the stack? I imagine MySQL plugs into any of the major ones pretty well, right?
You shouldn't have any problems with cross-compatibility. Same more MySQL-compatibility. Even a search like "[language] MySQL" brings up results on people asking how to use that language to connect to a MySQL database.
>I'm wondering if there's a GUI-based tool out there that lets you enter data as if you were working on an Excel sheet or something similar.
If I understand what you're looking for here, MySQL Workbench is likely your best option. You'll just need to make sure that the security settings are configured correctly in AWS to allow you to connect.
I would recommend learning SQL. You can easily set up Mysql, or PostgreSQL for free on your computer. And then connect up to them through an sql client (https://wiki.postgresql.org/wiki/PostgreSQL_Clients or https://www.mysql.com/products/workbench/ are both free). Any other software would most likely be built on top of something like this
I would go for scripts for the same reasons as mdaffin.
However if you still don't want to use scripts, I'd recommend MySQL Workbench (Obviously MySQL only).
Some suggestions, if you are set on using MySQL and PHP:
There are a number of products and services that help people generate a database-driven PHP site. I don't have experience with any of them though.
Don't know if they support Apple pencil, but you could try out:
"Im hiring a developer to create a modified PHPMyDataGrid front end for my database so I can easily manipulate data and export items to my local spreadsheet for storage and accounting."
Maybe I'm missing something, but why not just install MySQL Workbench? That's the official "front end GUI" interface for MySQL databases. If you need to locally (or even remotely) manipulate a MySQL database that's what you might want to use.
For my databases course, I used http://generatedata.com/ (I have no affiliation with the site), and MySQL Workbench, it's great as it allows line-by-line execution or full script running, and will also display the results in formatted tables, etc.
mySQL workbench provides a nice GUI for creating, viewing and editing schemas. I believe it also lets you run queries, and provides scope for more complicated database operations and cfgs.
I worked with this starting off, and can recommend it. https://www.mysql.com/products/workbench/
In my database I have a table for each event type. I store every event other than GainExperience because I don't care about it.
voidwell.com also uses nodejs for the site, updater, and websocket tracker with a mysql backend.
DB work in node is very simple, you can use https://github.com/felixge/node-mysql. A mySQL server is very easy to setup. There's an official tool you can download called MySql Workbench that's great for getting you acclimated. There are also plenty of tutorials online.
If you have any code questions I can share what I have tonight if i remember.
>My biggest challenge is I need to add it to the query I am already using to retrieve marks and messages; but I am not sure how to do that because this query is already so huge.
Use stored procedures/functions. That way your queries can be long and complex as you need, and you can call them from node with just a simple 'CALL procedureName(param)'
MYSQL Workbench is pretty good for creating/testing stored procedures quickly.
I think you are making more work for yourself while trying to organize your data.
https://www.mysql.com you can download the database program for free. https://www.mysql.com/products/workbench/ is a front end to work with your database
It WILL be a huge learning curve, but you can have all your data in one place as opposed to a huge repository of datafiles.
You can slowly move toward the database part while still working your data.
Personally I would stop using PMA and look into using other database managers like:
and unless someone else has access to your machine and changes the permission, you should be fine.