mysql – Print totals in bd

I have a question and I hope you can help me with my query. It turns out that I am doing a management panel and I have to print out the total number of registered users in a dashboard widget. I get it, but I'm interested in seeing the sum of the records of the day, not the grand total that exists in the database, how would this be done? I hope you can support me

PS: In the Bd there is the column Created_At, in which the date and the time, at which a new user is registered, are inserted.

The controller "DashboardController":

    User :: count (),
];


Return view (& # 39; admin.dashboard & # 39 ;, $ data);
}
}

and print the variable as:

{{$ $ userCount}}

Create new secure MySQL password on the cPanel server and change .php file (cPanel)

Hello,

MySQL removed the support for 4.1-style passwords from 5.6 and therefore caused a lot of problems when migrating old MySQL accounts to new MySQL.

I wonder how to update mysql passwords as well as .php files that contain old passwords in bulk updates to reflect the change.

My ideas are:
1. Can I discover a MySQL user plaintext password as server root? If this is not the case, find the list of users and find for each user their .php files that contain both "username_" and "localhost" – since these files are probably the DB configuration file. .php files.

2. In the future, generate a new string that meets the high security standards of mysql (I still do not know how the command line in Linux generates such a string to meet password complexity, or whether password complexity is required for the latest MySQL password accepts the password.)

3. Replace the password in the above file with a newly generated one
SEMrush

4. Change the current MySQL password

Do you have input to my mentioned steps to automate this process, or is there already a script for it?

mysql – Perfomants, slow loading the query with multiple links

I have a lot to deal with a big doctrine query, there are 17000 contacts in the database and almost 1 million lines for statistics and orders.

As you can see, there are many calculated values. I need these values ​​to filter.

First I tried to do this query with DQL, but it's just as bad in terms of performance, the query takes more than 15 or 30 seconds.

The goal is to have the 17000 lines for each contact with the calculated values.

Can someone give me some advice? I'm sure I'm not the only one who has to ask such questions!

Thank you in advance 🙂

This is the Doctrine Query Builder

    $ qb = $ this-> createQueryBuilder (& # 39; contact & # 39;);

$ qb-> select ("contact.id");
$ qb-> addSelect ("contact.source");
$ qb-> addSelect ("contact.updatedAt");
$ qb-> addSelect ("contact.createdAt");

// Assignments
$ qb-> addSelect ("COUNT (distinct shop_order.id) AS totalOrders");
$ qb-> addSelect ("SUM (unique shop_order.price) AS totalSpentOrders");
$ qb-> addSelect ("AVG (clear shop_order.price) AS averageCart");
$ qb-> addSelect ("MAX (shop_order.createdAt) AS lastOrderDatedAt");
$ qb-> addSelect ("MIN (shop_order.createdAt) AS firstOrderDatedAt");
$ qb-> addSelect ("last_shop_order.price AS totalSpentLastOrder");

// Order status
$ qb-> addSelect ("COUNT (DISTINCT (CASE WHEN shop_order.status =". Order :: STATUS_WAITING. "THEN shop_order.id ELSE: empty END)) AS totalOrdersWaitingOrError");
$ qb-> addSelect ("COUNT (DISTINCT (CASE WHEN shop_order.status =". Order :: STATUS_PAID. "THEN shop_order.id ELSE: empty END)) AS totalOrdersPaid");
$ qb-> addSelect ("COUNT (DISTINCT (CASE WHEN shop_order.status =". Order :: STATUS_DELIVERED. "THEN shop_order.id ELSE: empty END)) AS totalOrdersDelivered");
$ qb-> addSelect ("COUNT (DISTINCT (CASE WHEN shop_order.status =". Order :: STATUS_PAYMENT_ERROR. "THEN shop_order.id ELSE: empty END)) AS totalOrdersPaymentError");
$ qb-> addSelect ("COUNT (DISTINCT (CASE WHEN shop_order.status =". Order :: STATUS_SHIPPED. "THEN shop_order.id ELSE: empty END)) AS totalOrdersShipped");
$ qb-> addSelect ("COUNT (DISTINCT (CASE WHEN shop_order.status =". Order :: STATUS_ABORTED. "THEN shop_order.id ELSE: blank END)) AS totalOrdersAborted");

$ qb-> leftJoin ("contact.orders", "shop_order", Join :: WITH, "shop_order.isValid = 1 AND shop_order.contact = contact");
$ qb-> leftJoin ("contact.orders", "last_shop_order", Join :: WITH, "last_shop_order = FIRST (SELECT lso FROM App: Order lso.isValid = 1 and lso.contact = contact ORDER BY lso.createdAt DESC ) ");

// order shopping cart
$ qb-> leftJoin ("shop_order.cart", "cart");
$ qb-> leftJoin ("cart.productCarts", "product_carts");
$ qb-> leftJoin ("product_carts.product", "product");

// abandoned carts
$ qb-> addSelect ("COUNT (distinct abandonned_cart.id) AS totalAbandonnedCarts");
$ qb-> addSelect ("SUM (clearly posted_product_price) AS totalAmountAbandonnedCarts");

$ qb-> leftJoin ("contact.carts", "abandonned_cart");
$ qb-> leftJoin ("abandonned_cart.shopOrder", "abandonned_shop_order");
$ qb-> leftJoin ("abandonned_cart.productCarts", "abandonned_product_cart");
$ qb-> leftJoin ("abandonned_product_cart.product", "abandonned_product");
$ qb-> andWhere ("abandonned_shop_order.id IS NULL");

$ qb-> addSelect ("SUM (clear last_abandonned_cart.amount) AS totalAmountLastAbandonnedCart");

$ qb-> leftJoin ("contact.carts", "last_abandonned_cart", Join :: WITH, "last_abandonned_cart = FIRST (SELECT lac FROM App: Basket lac JOIN LINKS lac.shopOrder lacso WO lacso NULL AND lac.contact = Contact ORDER From lac.createdAt DESC) ");
$ qb-> leftJoin ("last_abandonned_cart.productCarts", "last_abandonned_product_cart");
$ qb-> leftJoin ("last_abandonned_product_cart.product", "last_abandonned_product");

// Behavior
$ qb-> addSelect ("COUNT (DISTINCT (CASE WHEN contact_stat.type =". ContactStat :: TYPE_EMAIL_SENT. "THEN contact_stat.id ELSE: blank END)) AS totalEmailSent");
$ qb-> addSelect ("COUNT (DISTINCT (CASE WHEN contact_stat.type =". ContactStat :: TYPE_EMAIL_CLICKED. "THEN contact_stat.id ELSE: blank END)) AS totalEmailClicked));
$ qb-> addSelect ("COUNT (DISTINCT (CASE WHEN contact_stat.type =". ContactStat :: TYPE_EMAIL_OPENED. "THEN contact_stat.id ELSE: blank END)) AS totalEmailOpened");
$ qb-> addSelect ("(COUNT (DISTINCT (CASE WHEN contact_stat.type =". ContactStat :: TYPE_EMAIL_CLICKED. "THEN contact_stat.id ELSE: empty END)) / COUNT (DISTINCT (CASE WHEN contact_stat.type =". ContactStat . :: TYPE_EMAIL_SENT. THEN contact_stat.id ELSE: empty END))) * 100 AS emailClickedRate ");
$ qb-> addSelect ("(COUNT (DISTINCT (CASE WHEN contact_stat.type =". ContactStat :: TYPE_EMAIL_OPENED. "THEN contact_stat.id ELSE: blank END)) / COUNT (DISTINCT (CASE WHEN contact_stat.type =". ContactStat :: TYPE_EMAIL_SENT. "THEN contact_stat.id ELSE: empty END))) * 100 AS emailOpenedRate");
$ qb-> leftJoin ("contact.contactStats", "contact_stat");

// other contact information
$ qb-> leftJoin ("contact.address", "address");
$ qb-> leftJoin ("contact.contactOperations", "contact_operation");
$ qb-> leftJoin ("contact_operation.operation", "operation");
$ qb-> leftJoin ("contact.accountTypes", "account_type");
$ qb-> leftJoin ("contact.medicalInformation", "medical_information");

$ qb-> setParameter ("empty", null);

$ qb-> orderBy ("contact.updatedAt", "DESC");
$ qb-> groupBy ("contact.id");

I tried to remove some joins and the calculated values. This is the SQL query:

CHOOSE
c0_.id AS id_0,
c0_.email AS email_1,
c0_.erst_name AS firstname_2,
c0_.lastname AS lastname_3,
c0_.type AS type_4,
c0_.updated_at AS updated_at_5,
c0_.source AS source_6,
c0_.created_at AS created_at_7,
a1_.postcode AS postcode_8,
a1_.land AS land_9,
m2_.skin_type AS skin_type_10,
c0_.is_optin_sms AS is_optin_sms_11
FROM
Contact c0_
LEFT JOIN address a1_ ON c0_.address_id = a1_.id
CONNECT LINKS medical_information m2_ ON c0_.id = m2_.contact_id
LEFT JOIN contact_operation c3_ ON c0_.id = c3_.contact_id
LEFT JOIN operation o4_ ON c3_.operation_id = o4_.id
LEFT JOIN account_type_contact a6_ ON c0_.id = a6_.contact_id
LEFT JOIN account_type a5_ ON a5_.id = a6_.account_type_id
LEFT JOIN contact_stat c7_ ON c0_.id = c7_.contact_id
LEFT JOIN shop_order s8_ ON c0_.id = s8_.contact_id
AND (
s8_.is_valid = 1
AND s8_.contact_id = c0_.id
)
LEFT JOIN shop_order s9_ ON c0_.id = s9_.contact_id
AND (
s9_.id = (
CHOOSE
s10_.id
FROM
shop_order s10_
FROM WHERE
s10_.is_valid = 1
AND s10_.contact_id = c0_.id
SORT BY
s10_.created_at DESC
BORDER
1
)
) LINKS JOIN shopping cart c11_ ON s8_.cart_id = c11_.id
LEFT JOIN product_cart p12_ ON c11_.id = p12_.cart_id
LEFT JOIN product p13_ ON p12_.product_id = p13_.id
LINKS JOIN shopping cart c14_ ON c0_.id = c14_.contact_id
LEFT JOIN shop_order s15_ ON c14_.id = s15_.cart_id
AND (
s15_.id is NULL
AND s15_.contact_id = c0_.id
)
GROUP BY
c0_.id
SORT BY
c0_.updated_at DESC

The query takes more than 8 seconds.

It gives the explanation of the query

Enter the image description here


mysql – Why is the value of the NUMERIC_SCALE column in the INFORMATION_SCHEMA.COLUMNS table for DATA_TYPE =? double & # 39; ZERO?

I have created a column whose data_type is DOUBLE,

alter table my_table add column my_column double;

When I look for that column in information_schema.columns table
I found that NUMERIC_SCALE for this column is ZERO?

Select DATA_TYPE.
NUMERIC_PRECISION,
NUMERIC_SCALE

from information_schema.columns

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

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

Can someone explain this behavior?

mysql – Does my database design conform to the standard?

I read a lot, look a lot to the tutorial, practice, create the first database and now I'm losing myself. I do not know if this is a good practice or a real example of database design, as practiced by database experts.

In the following, I've created a database that states that in some tables, it's difficult to avoid duplication; For example, with table addresses. When the husband and wife register, both have a similar address. Duplication can not be avoided with table visitors (first_name or last_name), with table agents (reputation), even with table lists (bedroom, bathroom, car).

Here is the code

DROP DATABASE IF EXISTS TEST;
CREATE DATABASE test CHARSET = utf8 COLLATE = utf8_general_ci;
USE test;

CREATE TABLE visitors (
visitors_id INT (12) NOT NULL AUTO_INCREMENT,
first_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,
position_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,
Biotext,
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,
registration_number VARCHAR (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 entries (
Listings_id VARCHAR (12) NOT NULL,
agents_id INT (20) NOT NULL,
Title TEXT NOT NULL,
Description TEXT NOT NULL,
location CHAR (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 (listing ID),
FOREIGN KEY (agents_id) REFERENCES agents (agents_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

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

The relationship that I expected is explained below

visitors <-> Positions ONE TOO MANY

A visitor can only have one position and a position can have many visitors.

positions <-> Customers too many

A position can have many customers and a customer can only have one position.

positions <-> Agents too many

A position can have many agents and an agent can only have one position

agents <-> Company ONE TO ONE

An agent can only be registered in one company, and a company can have many agents, but only one agent. For example, if an agent with ID 123 (UNIQUE) is already registered as a member of Company Y, the agent can not register as a member of Company Z. To ensure that another company registers this agent, it is rejected by the database.

agents <-> Address one too many

An agent can have many addresses. An address belongs to only one agent

agents <-> Advertisements ONE TOO MANY

An agent can have many entries and only one agent belongs to an agent

My questions:

  1. Is it a database design for a good practice?
  2. What kind of normalization 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 database standard (if any)?

If you have questions, please ask in plain English, which is easy to understand.

Query – Error creating a MySQL table?

I made a diagram of a database using MySQL WorkBench and let the script generate it. When I run it, the following error occurs:

Run SQL script on the server
ERROR: Error 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MariaDB server version for the correct syntax to be used near & # 39; & # 39 ;.
INDEX `fk_carro_leilao1_idx` (` leilao_idleilao` ASC) VISIBLE,
CONSTRAINT `f & # 39; in line 15

SQL code:

                Eur-lex.europa.eu eur-lex.europa.eu -----
- Table "Cars"
Eur-lex.europa.eu eur-lex.europa.eu -----
CREATE TABLE, IF NOT "carrole".
"idcarro" INT NOT NULL AUTO_INCREMENT,
`template` VARCHAR (45) NOT NULL,
"mark" VARCHAR (45) NOT NULL,
"year" VARCHAR (5) NOT NULL,
"Fuel" VARCHAR (45) NOT NULL,
`precoFIPE` VARCHAR (45) NOT NULL,
"Reference" VARCHAR (45) NOT NULL,
`preco_idpreco` INT NOT NULL,
`leilao_idleilao` INT NOT NULL,
PRIMARY KEY ("idcarro", "preco_idpreco", "leilao_idleilao"),
INDEX `fk_carro_preco_idx` (` preco_idpreco` ASC) VISIBLE,
INDEX `fk_carro_leilao1_idx` (` leilao_idleilao` ASC) VISIBLE,
CONSTRAINT `fk_carro_preco`
Foreign key (`preco_idpreco`)
REFERENCES `carlosleilao`.`preco` (` idpreco`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_carro_leilao1`
Foreign key (`leilao_idleilao`)
REFERENCES `carsleilao`.`leilao` (` idleilao`)
ON DELETE NO ACTION
UPDATE NO ACTION)
ENGINE = InnoDB

I could not identify the problem because the script was generated by the tool itself

Oracle – problem with Tabla Mysql and Xampp

In the first ten sections with a single table memory and a small problem, it is possible to import the administrator of the PhpMyAdmin database. This is an important mistake.

error
consulta SQL:


-
- Table structure for the table "Alumno"
-

DROP TABLE IF EXISTS "Alumno" exists
MySQL has dicho: Documentación

# 1046 - Basic data not seleccionada
- MySQL Dump 10.13 Distrib 5.7.17 for Win64 (x86_64)
-
- host: 127.0.0.1 database: cphpmysql
- ---------------------------------------------- --- ---
- Server version 5.7.18 protocol

/ *! 40101 SET @OLD_CHARACTER_SET_CLIENT = @@ CHARACTER_SET_CLIENT * /;
/ *! 40101 SET @OLD_CHARACTER_SET_RESULTS = @@ CHARACTER_SET_RESULTS * /;
/ *! 40101 SET @OLD_COLLATION_CONNECTION = @@ COLLATION_CONNECTION * /;
/ *! 40101 SET NAMES utf8 * /;
/ *! 40103 SET @OLD_TIME_ZONE = @@ TIME_ZONE * /;
/ *! 40103 SET TIME_ZONE = & # 39; + 00: 00 & # 39; * /;
/ *! 40014 SET @OLD_UNIQUE_CHECKS = @@ UNIQUE_CHECKS, UNIQUE_CHECKS = 0 * /;
/ *! 40014 SET @OLD_FOREIGN_KEY_CHECKS = @@ FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0 * /;
/ *! 40101 SET @OLD_SQL_MODE = @@ SQL_MODE, SQL_MODE = & # 39; NO_AUTO_VALUE_ON_ZERO & # 39; * /;
/ *! 40111 SET @OLD_SQL_NOTES = @@ SQL_NOTES, SQL_NOTES = 0 * /;

-
- Table structure for the table "Alumno"
-

DROP TABLE IF EXISTS "alumno" exists;
/ *! 40101 SET @saved_cs_client = @@ character_set_client * /;
/ *! 40101 SET character_set_client = utf8 * /;
CREATE TABLE `alumno` (
`id` int (11) NOT NULL AUTO_INCREMENT,
`nombres` varchar (255) DEFAULT NULL,
`apellidos` varchar (255) DEFAULT NULL,
`estado` varchar (1) DEFAULT NULL,
Primary key ("id")
) ENGINE = InnoDB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
/ *! 40101 SET character_set_client = @saved_cs_client * /;

-
- Dumping data for the table "Alumno"
-

LOCK TABLES `Alumno` WRITE;
/ *! 40000 ALTER TABLE `alumno` DISABLE KEYS * /;
INSERT in `alumno` VALUES (1, El Elivar Oswaldo,, # Largo Rios #, # 1 #), (5, Juan Juan & # 39 ;, & lgr; , & Quot; Vargas & # 39 ;, & # 39; 1 & # 39;);
/ *! 40000 ALTER TABLE `alumno` ENABLE KEYS * /;
UNLOCK TABLES;
/ *! 40103 SET TIME_ZONE = @ OLD_TIME_ZONE * /;

/ *! 40101 SET SQL_MODE = @ OLD_SQL_MODE * /;
/ *! 40014 SET FOREIGN_KEY_CHECKS = @ OLD_FOREIGN_KEY_CHECKS * /;
/ *! 40014 SET UNIQUE_CHECKS = @ OLD_UNIQUE_CHECKS * /;
/ *! 40101 SET CHARACTER_SET_CLIENT = @ OLD_CHARACTER_SET_CLIENT * /;
/ *! 40101 SET CHARACTER_SET_RESULTS = @ OLD_CHARACTER_SET_RESULTS * /;
/ *! 40101 SET COLLATION_CONNECTION = @ OLD_COLLATION_CONNECTION * /;
/ *! 40111 SET SQL_NOTES = @ OLD_SQL_NOTES * /;

- Dump on 2017-12-11 22:53:21 completed

mysql – Manually set a return value for a subquery with multiple results

I have a subquery that can return multiple results when a customer duplicates sales codes.

I just LIMIT 1 To return only the last, but … Are there any NULL or "DUPLICATE" if RESULT> 1?

I use this field to override a field that already has a default value. Therefore, I would prefer to use the default value or trigger a warning to correct.

PHP – Generating a download from a mysql database from a web-based application

Well, I'm a newbee here on this site, and although I've programmed many years ago, I've only been involved in recent projects. So if you do not have a valid question, please contact me.

I have a WEB-based application with a Raspberry Pi 3 that uses Python code, PHP, and JS files to provide full functionality and GUI to the user.

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

would it be possible to get suggestions for coding?
The appearance and positioning of the button is irrelevant, only the functionality is important.

I hope it is alright to ask such a question here, and I am sure there are many bright people who are able to describe exactly how they are done.

Now we will see.
Thank you in advance.

MySQL – automatic increment between tables

Can the auto-increment value of two tables be shared?

Example:

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

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