Requirements & good practices

Your Database

• The database name, table names & fields names:
  • *MUST use only lowercase/uppercase alphanumeric characters and underscores
    (no hyphens, spaces or special characters)
  • *MUST NOT start with a number
• *Each table MUST have an auto-incremented primary key.
  The auto-incremented primary key allows MySQL to get the LAST_INSERT_ID in INSERT statements and throw an error if the insert failed.
• *Each table MUST use InnoDB Engine in order to support foreign key relationship
• **Relationships between tables must be correctly defined.
  If your database shema is built with the correct foreign keys, PHPCG will detect them and allow you for example to display the fields of your choice from the related table in the list of the original table.
  
  You will also be able to choose the fields to display in your forms.
  
  For example, a "products" table connected to a "categories" table with products.categories_id = categories.id will allow you to:
  
  
  • display category names in product lists instead of their id
  • use a drop-down list in your forms whose values will be categories_id and the values displayed will be the categories names.
• **Field types must match expected values

* Absolutely required
** Highly desirable

Why not allow the possibility of using other characters?

There are two reasons for this:

1. Although there is no official standard, some good practices are commonly accepted for naming tables and fields.
   Respecting these conventions helps to have a more solid code and avoid some unnecessary problems.
   For example, capital letters are a source of errors. They're often difficult to detect because they are taken into account by some systems (case sensitive) but not by others (case insensitive). Moreover, they are not recognized in the URLs.
2. PHPCG analyzes your database intelligently. Using a consistent naming system avoids many analyses and conversions.
   The table names are also used to generate PHP objects, forms, and many other things.

Tools to help you create your database

There are many applications, free or paid, to build your database and manage the types of fields, constraints and relationships between tables, the most common being phpmyadmin, directly accessible on PHP servers.

You can of course use the application/software of your choice according to your own preferences.

However, we recommend the excellent MySQL Workbench (the official software released by MySQL team), available free of charge here: https://www.mysql.com/products/workbench/

MySQL Workbench allows you to build your databases simply, and synchronize them with your server

Once installed and connected with your server, you will benefit from a reliable and efficient tool.

MySQL Workbench - Main features:

• Create/Edit/Delete tables & fields
• Create Diagrams using drag and drop
• Create foreign keys and relationships from diagrams using drag and drop
• Synchronize with your server database
• Save & export to several formats

Open the video tutorial about MySQL Workbench

PHP CRUD tutorial main page