mysql – Why the value of the NUMERIC_SCALE column of the INFORMATION_SCHEMA.COLUMNS table for DATA_TYPE = "double" NULL?

I've created a column whose data type is DOUBLE.

alter table my_table add column my_column double;

When I asked about this column in INFORMATION_SCHEMA.COLUMNS table
I found that the numeric_scale for this column is NO?

select DATA_TYPE,
numeric_precision,
numeric_scale

of information_schema.columns

where table_schema = & # 39; my_schema & # 39;
and table_name = & # 39; my_table & # 39;
and column_name = my_column & # 39;

+ ----------- + ------------------- + --------------- +
| DATA_TYPE | NUMERIC_PRECISION | NUMERIC_SCALE |
+ ----------- + ------------------- + --------------- +
| double | 22 | NULL |
+ ----------- + ------------------- + --------------- +

Can any one explain this behavior?

mysql – Does the design of my database conform to real world standards?

I've read a lot, watched a lot of tutorials, practiced, created the first database and now I'm getting lost. I do not know whether it is a good practice or a good concrete example of designing a database as a practice by a professional from the database.

I've created a database below and I realize that it's difficult, in some tables, to avoid duplicates, for example, in table addresses. If the husband and wife are registered, they both have a similar address. Duplication can not be avoided in visitors to the table (first or last name), table agents (reputation), table lists (bedroom, bathroom, car).

Here is the code,

DROP DATABASE IF EXISTS test;
CREATE DATABASE test CHARSET = utf8 COLLATE = utf8_general_ci;
Test of use;

CREATE TABLE visitors (
Visitors_id INT (12) NOT NULL AUTO_INCREMENT,
name VARCHAR (15) NOT NULL,
last_name VARCHAR (15) NOT NULL,
mobile_number VARCHAR (20) NOT NULL,
nic VARCHAR (30) NOT NULL,
nic_image VARCHAR (30) NOT NULL,
profile_image VARCHAR (30) NOT NULL,
PRIMARY KEY (visitors_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE positions
positions_id VARCHAR (4) NOT NULL,
Visitors_id INT (12) NOT NULL,
positions_name VARCHAR (14) NOT NULL,
PRIMARY KEY (positions_id),
FOREIGN KEY (visitors_id) REFERENCES visitors (visitors_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE Agents
agents_id INT (20) NOT NULL,
positions_id VARCHAR (4) NOT NULL,
bio text,
reputation DECIMAL (2.1) NOT NULL,
PRIMARY KEY (agents_id),
FOREIGN KEY (positions_id) REFERENCES positions (positions_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE customers (
customers_id INT (20) NOT NULL,
positions_id VARCHAR (4) NOT NULL,
email VARCHAR (30) NOT NULL,
PRIMARY KEY (customers_id),
FOREIGN KEY (positions_id) REFERENCES positions (positions_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE companies (
companies_id INT (20) NOT NULL,
brand VARCHAR (30) NOT NULL,
register_name VARCHAR (30) NOT NULL,
VARCHAR registration number (30) NOT NULL,
phone number VARCHAR (26) NOT NULL,
PRIMARY KEY (companies_id),
FOREIGN KEY (companies_id) REFERENCES agents (agents_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE addresses (
address_id INT (12) NOT NULL AUTO_INCREMENT,
agents_id INT (20) NOT NULL,
companies_id INT (20) NOT NULL,
address VARCHAR (15) NOT NULL,
address2 VARCHAR (20) NOT NULL,
PRIMARY KEY (address_id),
FOREIGN KEY (agents_id) REFERENCES agents (agents_id),
FOREIGN KEY (companies_id) REFERENCES companies (companies_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE lists (
listings_id VARCHAR (12) NOT NULL,
agents_id INT (20) NOT NULL,
title TEXT NOT NULL,
description TEXT NOT NULL,
CHAR slot (20) NOT NULL,
bedroom INT (4) NOT NULL,
bathroom INT (4) NOT NULL,
garage INT (4) NOT Null,
image1 VARCHAR (30),
image2 VARCHAR (30),
image3 VARCHAR (30),
image4 VARCHAR (30),
PRIMARY KEY (listings_id),
FOREIGN KEY (agents_id) REFERENCES agents (agents_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

SELECT * FROM agents JOIN COMPANIES ON agents.agents_id = companies.companies_id

The relationship that I expected is explained below

visitors <-> ONE to many positions

A visitor can only have one post and only one can have multiple visitors.

positions <-> customers one to many

A position can have multiple clients and a client can only have one position.

positions <-> One-to-many agents

A post can have multiple agents and an agent can only have one post

agents <-> company one by one

Only one agent can be registered in one company and one company can have multiple agents, but only one UNIQUE agent. For example, if an agent whose ID is 123 (UNIQUE) is already registered as a member of company Y, the agent can not register as a member of company Z This helps to ensure that if another company registers this agent, the database will reject it.

agents <-> address ONE TO SEVERAL

An agent can have multiple addresses One address only belongs to one agent

agents <-> ONE TO MANY ads

An agent can have multiple ads and only one can belong to one agent.

My questions:

  1. Is this a database design good practice?
  2. What kind of standardization can be satisfied?
  3. Do you find a relationship weakness when I explain such a query problem?
  4. How can I improve this design to meet the real world database standard (if any)

If you have questions, ask them in plain English and easy to understand.

MySQL Why is the result of EXPLAIN confusing?

Below my request:

select * from (select * from the player in which Country_Name is located in (1,2)) as the internal join of country C on A.Country_Name = C.Country_Id;

When I execute this request I am 301 lines but when I use EXPLAIN
the exit shows Rows 42 and 2.

Below, the EXPLAIN output:

+ ---- + ------------- + -------- + ------------ + ------- + --------------- + -------------- + --------- + --------- -------------- + ------ + ---------- + ------------- +
| id | select_type | table | sheet music | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+ ---- + ------------- + -------- + ------------ + ------- + --------------- + -------------- + --------- + --------- -------------- + ------ + ---------- + ------------- +
| 1 | SIMPLE | C | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Use where |
| 1 | SIMPLE | Player | NULL | ref | Country name | Country name | 4 | database.C.Country_Id | 42 | 100.00 | NULL |
+ ---- + ------------- + -------- + ------------ + ------- + --------------- + -------------- + --------- + --------- -------------- + ------ + ---------- + ------------- +

Why is there a negative difference between Exit lines and Explain
rows
? Is not it that they will be the same or more?

php – Add an image to an existing MySql record

I am new to PHP and MYSQL.

I need to add an image to an existing record.

I have a list of records and when I click on the link to this page.

In this form, I would like to add an image.

Thanks in advance

<? php # Script 10.3 - edit_user.php

// This page is for editing a user record.
// This page is accessible via view_users.php.

$ page_title = & # 39; Edit country & # 39 ;;
include (includes / header.html & # 39;)
echo & # 39;

& # 39 ;;

// Search for a valid user ID, via GET or POST:
if ((isset ($ _GET[‘id’])) && (is_numeric ($ _GET[‘id’]))) {// From view_users.php
$ id = $ _GET[‘id’];
} elseif ((isset ($ _ POST[‘id’])) && (is_numeric ($ _ POST[‘id’]))) {// Submission of form.
$ id = $ _POST[‘id’];
} else {// no valid ID, kill the script.
echo & # 39; This page has been read in error.

& # 39 ;;
include (includes / footer.html & # 39;)
exit();
}

require (& # 39; ../ mysqli_connect.php & # 39;)

// Check if the form has been submitted:
if ($ _SERVER[‘REQUEST_METHOD’] == & # 39; POST & # 39;) {

$ errors = array ();

// Search a country name:
if (empty ($ _ POST['land'])) {
$ errors[] = & # 39; You forgot to enter a country. & # 39;;
} other {
$ fn = mysqli_real_escape_string ($ dbc, trim ($ _ POST['land']));
}


// Check the ID of a country:
if (empty ($ _ POST['id_land'])) {
$ errors[] = & # 39; You forgot to enter a country ID. & # 39 ;;
} other {
$ c = mysqli_real_escape_string ($ dbc, trim ($ _ POST)['id_land']));
}

if (empty ($ errors)) {// If all is well.

{

// Make the request:
$ q = "UPDATE landen SET land = $ fn WHERE id_land = $ id LIMIT 1";
$ r = @mysqli_query ($ dbc, $ q);
if (mysqli_affected_rows ($ dbc) == 1) {// If it worked fine.

// Print a message:
echo & # 39;

The user has been edited.

& # 39 ;; } else {// it did not work properly. echo & # 39;

The user could not be changed due to a system error. We apologize for any inconvenience.

& # 39 ;; // public message. echo & # 39;

& # 39 ;. mysqli_error ($ dbc). & # 39;
Query: & # 39; q $ & # 39;

& # 39 ;; // Debug message. } } } else {// Report the errors. echo & # 39;

The following errors occurred:
& # 39 ;; foreach ($ errors as $ msg) {// Print each error. echo "- $ msg
not "; } echo & # 39;

Try Again.

& # 39 ;; } // end of if (empty ($ errors)) IF.

} // End conditional submission.

// Always display the form …

// Retrieve user information:
$ q = "SELECT id_land, land, image FROM landen WHERE id_land = $ id";
$ r = @mysqli_query ($ dbc, $ q);

if (mysqli_num_rows ($ r) == 1) {// valid user ID, displays the form.

// Retrieve the user information:
$ row = mysqli_fetch_array ($ r, MYSQLI_NUM);


$ dob = "$ row[0]";
$ diff = (date (Y ') - date (Y', strtotime ($ dob)));

// Create the form:
echo & # 39;

Field ID:

Earth:

Flag:

& # 39 ;;

} else {// Invalid user id.
echo & # 39; This page was viewed by mistake because there is no valid user ID.

& # 39 ;;
}
// date in mm / dd / yyyy format; or it can be in other formats as well
echo "$ id";

mysqli_close ($ dbc);

include (includes / footer.html & # 39;)
?>

index – How can I optimize this MySql Select query?

I have the following query run every 1 minute on a cron job

CHOOSE ACCOUNT (*)
'Orders` AS` where
WHERE (`o`.`OrderStatus` = 0) AND (` o`.`CreatedAt``> DATE_ADD (UTC_TIMESTAMP (), INTERVAL -1.0 minutes))

Do I have to add an index on the Order status and Created to columns, and if so, what kind of index?

php – generate a download from a mysql database from a web application

Now, I am a Newbee here on this site, and although I programmed it many years ago, I only supervised newer projects, so be careful if this question does not occur. is not valid.

I have a web-based application with a Raspberry Pi 3, where we use python code, php files and js to make the full functionality and graphical interface to the user.

We have a Mysql database containing user information, power consumption and other details. I need a button on the page to get a complete download of this database on the device from which it is enabled.

would it be possible to get suggestions on how to code that?
The appearance and positioning of the button are unimportant, only the functionality is important.

I hope it's okay to ask such a question here, and I'm sure there are a lot of smart people here who can describe exactly how it's done.

We will see.
Thank you in advance.

MySQL – automatic incrementation shared between tables

Is it possible to share the automatic incrementation value of two tables?

Example:

Table 1
+ ---- + ------- + ----------- +
| id | domain | created_em |
| 1 | a | 00:00 |
| 2 | b | 01:00 |
| 5 | c | 04:00 |
+ ---- + ------- + ----------- +

Table 2
+ ---- + ------- + ----------- +
| id | domain | created_em |
| 3 | a | 02:00 |
| 4 | b | 03:00 |
| 6 | c | 05:00 |
+ ---- + ------- + ----------- +

Mysql sgbd does not leave the procedure edit

I used these procedures yesterday using HOSTGATOR, but today, when I came in, it gave me the problem of not editing, nor exporting, but I still have to have it. ######################################################## 39, run by code! I noticed that this was happening because of the user, but the hostgator provides a different user with each connection. Does anyone know if I have to export these procedures via code or something of the sort? Or arrange to be edited

insert the description of the image here

phpmyadmin – Workbench connects to mysql service but xampp does not connect

Workbench connects with mysql. I have several databases out there, for Windows cmd, I can also enter and modify the databases. But when I specifically use xampp with the xampp shell to enter the databases after entering the user and password (mysql -h localhost -u root -pmi_key), the following error appears: ERROR 2003 (HY000): Can not connect to MySQL server on localhost (10061)
The same goes for phpmyadmin. "The connection has not been established, the settings are incorrect." I want to manipulate the phpmyadmin databases but that will not allow it. As data, the mysql service runs on port 3306 while xampp is configured on port 3307. Previously, I had no problem, it all started when I changed the password in phpmyadmin and that I did not want to enter. To enter the database by cmd windows and workbench, I have a password and this does not cause me any problems.

mysql – How to create a formula with the data of a query?

I make a formula with a mysql query but that does not give me the result that I should not know in which part I'm wrong and hope you can help me it's an image of data: enter the description of the image here

the formula would be = ((300 + 21,000) / ((80,000 – 4,000) / 600 * 1,000)) * 60% the result you must give is 0,2773 provided that my request is good until This part:

SELECT the network name (Date_bio) as a month,
(SUM (CASE WHEN THE = 0 THEN 1 OTHER END OF THE) +
Sum (case where Thermodyne = 0 THEN 1 OTHER Thermodyne END)) /
(sum (case where DQO_entry = 0 then 1 ELSE DQO_entry end) -
sum (case where DQO_out = 0 then 1 ELSE DQO_output)) AS Result
`Biogas`
GROUP by year (Bio_date), month (Bio_date)