3

In order to help my wife , I have the ambition to create a software that would exploit a mysql database and allow her to save time in her job.

The design part is fine but I'm terrible at join between tables and wondered if there was a visual tool that ideally would make join in my stead or at least help me a bit.

I do not have Microsoft or Apple license on any of my computers, so any Gnu/Linux solution would be ideal.

Any advice or suggestion?

Precisions.

OK I admit, my question is not very clear and English is not my first language , sorry if it seems a bit confuse .

Let's say my database is already designed and also I can make basic requests and get results, no problem for that.

Like I said before I really suck at join tables , I find it very tedious at best plus I'm quite dyslexic and it is very difficult for me not to confuse queries lines, especially when they are numerous and repetitive.A huge waste of time

What I look for is a tool able to create complex queries and especially joins , provided of course I gave it the export of the database I want to work with. As if I could see my tables, connect the rows I want to join with a thread, pull the thread and voilà, my request is made in a few clicks, I can copy/paste it for any purpose

Improve this question
4
  • Using an ORM you can avoid writing joins. But in practice they're a complex tool that requires understanding of both the ORM and the SQL it produces. Departing from mysql there are also document databases (e.g. mongodb and object databases (e.g. db4o), but those come with their share of issues as well. If you don't like writing joins all the time, a few views which include common joins might be another approach.
    – CodesInChaos
    Jul 7, 2015 at 18:13
  • 1
    This question seems to cover your requirements plus a lot more things, maybe its worth taking a look: softwarerecs.stackexchange.com/q/7459/6957
    – Alejandro
    Jul 7, 2015 at 23:51
  • @Alejandro This could be a solution to my problem,i'll give a closer look
    – antares667367
    Jul 8, 2015 at 1:49
  • Try Myphpadmin and adminer
    – cybernard
    Jul 22, 2015 at 21:00

3 Answers 3

Reset to default
4

Taken from the Ubuntu SE, you can use OpenOffice/LibreOffice which a query designer as part of their database package. I've never used it, but here's a quick guide from the linked wiki page:

Selecting the Create View command from the Tables tab page of a database document, you see the View Design window that resembles the Query Design window described here.

Another potential option for you (though it's not free and might require running under mono; which used to be the case) is Navicat for MySQL. From their page:

Navicat for MySQL is the ideal solution for MySQL/MariaDB administration and development. Connect to MySQL and MariaDB databases simultaneously within a single application. This all-inclusive front-end provides an intuitive and powerful graphical interface for database management, development, and maintenance. It delivers a comprehensive set of tools for those new to MySQL/MariaDB as well as professional developers.

Navicat for MySQL connects to local/remote MySQL or MariaDB servers. It works with MySQL database servers from version 3.21 or above and MariaDB 5.1 or higher. It is also compatible with Drizzle, OurDelta, and Percona Server, and supports most of the latest features, including Tables, Views, Functions/Procedures, Events, and more.

Major features include the SQL Builder/Editor, a Data Modeling Tool, Data Transfer, Import/Export, Data/Structure Synchronization, Report, and much more.

Here's a quick picture of the SQL editor: enter image description here

Improve this answer
1
  • This is what i looked for.
    – antares667367
    Feb 18, 2016 at 8:30
2

I would suggest using python and the mysql connector library:

Example Code:

import datetime
import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()

query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")

hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)

cursor.execute(query, (hire_start, hire_end))

for (first_name, last_name, hire_date) in cursor:
  print("{}, {} was hired on {:%d %b %Y}".format(
    last_name, first_name, hire_date))

cursor.close()
cnx.close()

This link provides an example of running a Join query with a similar SQL library.

You might also like to take a look at Pandas and at this tutorial.

All the above is free, gratis and cross platform so you can develop and test on your machines and then run on just about any other without licence issues.

Improve this answer
1

If your software is written in PHP, one solution that could help you is the ListMembersByCategory PHP script.

It is not a visual tool, but you only have to pass to it a few parameters and it will generate the JOIN requests automatically for you.

English page: http://www.altipoint.ch/index.php?id=600

French page: http://www.altipoint.ch/index.php?id=602

N.B. I will describe later how it works, but my laptop currently has empty batteries. There is an example on the website about how to use the script.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.