Webslesson Tutorial | How to use Mysql View in PHP Code

Eloquent MySQL views

MySQL views are a way of storing queries on the database level, and producing virtual tables with them. In this post we’ll look at why you want to use them and how they can be integrated in Laravel with Eloquent models.

If you’re already convinced of the power of MySQL views, or just want to know how to implement them in Laravel, you’re free to skip ahead.

# Benefits of MySQL views

A view in MySQL stores the result of a query in a table-like structure. You’re able to query this view just like you would query a normal table.

The power of views is twofold:

• Complex queries with joins and unions can be represented as a queryable table on their own.
• MySQL is generally smarter than us when it comes to querying data. Compared to using collections or array functions in PHP, there’s a big performance gain.

There’s also a caveat to using views though. Depending on the kind of query, MySQL will need to construct an «in memory» table representing the view, at runtime. This operation is called table materialization and happens when using certain keywords like GROUP BY , or aggregated functions.

The takeaway is that views might actually hurt query performance, depending on the kind of query you’re executing. As with all things, views are a good solution for some problems, but a terrible idea for others. Use them wisely, and read up on their restrictions here.

# Views and their alternatives

Let’s look at a real-life example, to demonstrate how we could solve a given problem.

We’ve got a model MeterReading which logs a meter reading done in an apartment building. Every unit in the building has its own electricity, water and gas meters.

Every reading is listed in the database with a reference to the unit, the date, the user doing the reading, the type, and the actual meter value. Type in this example is electricity , water or gas .

This is what a simplified migration of this table looks like:

Schema::create('meter_readings', function (Blueprint $table) < $table->unsignedInteger('unit_id'); $table->unsignedInteger('user_id'); $table->string('type'); $table->dateTime('date'); $table->unsignedInteger('value'); >);

Now the client asks us to generate reports based on this raw data. He wants to see an overview of the units, where every row represents the readings for that unit, on that day, and whether all readings were done or not.

In short, he wants to see this:

+---------+---------+------------+-------------+-------+-----+ | unit_id | user_id | date | electricity | water | gas | +---------+---------+------------+-------------+-------+-----+ | 14 | 72 | 2018-08-19 | 0 | 1 | 0 | | 59 | 61 | 2018-08-06 | 0 | 0 | 1 | | 41 | 64 | 2018-08-02 | 1 | 1 | 1 | | 41 | 45 | 2018-08-02 | 1 | 1 | 1 | . | 41 | 51 | 2018-08-02 | 1 | 1 | 1 | +---------+---------+------------+-------------+-------+-----+

The report show a data set that is grouped by unit, user and day; and the corresponding readings done for at the time.

Here are a few ways of generating this report.

# On the fly

We always query all the data, and group it in our code. This is the most easy way of doing it, but has some downsides:

• PHP and Laravel collections are slow, compared to the optimised algorithms MySQL can use.
• Building a virtual data set means you’ll have to manually implement pagination. One row can represent multiple models.
• You’re adding a lot of code to manage that special collection of readings.

# Using a raw query

We can of course skip PHP and build the raw query to fully use the power of MySQL. While this solves the performance issue, we’re still working with a custom data set which can’t make use of standard pagination. Also, you’re now maintaining a big SQL query somewhere in your code. It’s probably a string somewhere in PHP, or –slightly better– a separate sql file.

# Projecting the changes

We could make a separte model called MeterReadingReport , and use event hooks on MeterReading to manage these reports.

Every time a reading is added, we can get or create a report for that unit, day and user; and update the data accordingly.

Now there’s a separate model that’s simple to query. There’s no more performance impact and the pagination issue is also solved.

But on the other hand, there’s a lot more code to manage these event hooks. Creating reports is one thing, but what if a reading is updated or deleted? That’s a lot of complexity we need to manage.

Projecting events into other models isn’t a bad idea though. It’s one of the key features in event sourcing. If you’ve got the right setup, making projectors would definitely be an option.

While we do have a package that handles this exact use case (laravel-event-projector), it seemed overkill for this use case; especially since there are a lot of other «normal» models in this project.

# Finding the middle ground

Looking at all the possible solutions, we can make a simple list of requirements:

• As less overhead as possible in the code base.
• Good performance.
• Be able to use the standard Laravel features without any workarounds.

MySQL views are this perfect middle ground. Let’s look at how they are implemented.

# SQL views in Laravel

To work with a view, we’ll have to first create a query that can build this view. While many people are scared of SQL –modern ORMs made us way too lazy– I find it a lot of fun.

Beware that I’m no SQL master, so there might be things that could be done better. I also won’t explain what this query does exactly, as it’ll be different for your use case.

In this case, it generates the table listed above. This is it:

SELECT unit_id , user_id , DATE_FORMAT(`date`, '%Y-%m-%d') AS day , COUNT(CASE WHEN type = 'electricity' THEN type END) AS `electricity` , COUNT(CASE WHEN type = 'water' THEN type END) AS `water` , COUNT(CASE WHEN type = 'gas' THEN type END) AS `gas` FROM meter_readings GROUP BY unit_id , user_id , day ;

It’s very easy to build this query in your favourite SQL browser, and afterwards plug it into your project.

How to plug it in, you ask? Very simple, with a migration.

public function up() < DB::statement($this->dropView()); DB::statement($this->createView()); >

First of all, dropView is required, because Laravel only drops tables when doing a fresh migration. It’s as simple as this:

 private function dropView(): string < return  >

You notice I prefer Heredoc in these cases, a separate SQL file is of course equally good.

Michael Dyrynda pointed out to me that there’s a —drop-views flag you can pass to the migrate command. So, technically, this manual dropping isn’t required. I prefer this way though, because now we don’t have to remember to add the extra flag.

Next up, the createView method returns the query, with some added syntax. I’ve shortened the sample a bit, but you get the point.

 private function createView(): string < return  >

Sidenote: I’m very much looking forward to PHP 7.3 and flexible Heredoc syntax.

Now that we have a migration in place, all else just works like normal Laravel!

class MeterReadingReport extends Model < protected $casts = [ 'day' => 'date', ]; public function unit(): BelongsTo < return $this->belongsTo(Unit::class); > public function user(): BelongsTo < return $this->belongsTo(User::class); > >

We’re using a simple model, without any workarounds whatsoever. Relations work just like normal, casting like you’re used to, pagination works like it should be, and there no more performance impact.

The only thing that’s not possible is of course writing to a view. It is actually possible to do it in MySQL, but completely irrelevant to our use case.

Maybe you can already see some use cases where MySQL views might be useful? Maybe you have a followup question or remark? I’d love to hear from you! You can reach me on Twitter or via e-mail.

Источник

Webslesson

PHP, MySql, Jquery, AngularJS, Ajax, Codeigniter, Laravel Tutorial

Tuesday, 3 May 2016

How to use Mysql View in PHP Code

This is most useful concept on Mysql database this is because in this post we have learn how to use of mysql view within php code. First of what is view. View is a virtual table based on result set of an sql statement. A view has rows and columns just like a real table. We can add fields in view from one or more tables in database. We can also use sql function and WHERE condition and even we can use JOIN statement in view. In this post for describing view I have join two tables. View can be create by using CREATE VIEW statement and then after writing simple query. View is store on to database table just like other tables. Suppose you want to called view from php code then you have to write simple select with name of view. This way you can called view from PHP code. In this post you can find source code under this post and for detail description you can also watch video of this post.

Create View

 CREATE view productlist AS SELECT brand.brand_name, product.product_name FROM brand INNER JOIN product ON product.brand_id = brand.brand_id 

Update View

 CREATE OR REPLACE view productlist AS SELECT brand.brand_id, brand.brand_name, product.product_name FROM brand INNER JOIN product ON product.brand_id = brand.brand_id 

mysql_view.php

          
 
How to Use Mysql View in PHP Code
 
 
    ?>
 
Brand ID
 
Brand Name
 
Product Name
 
  
 
 
 
  

Источник

Very Simple Add, Edit, Delete, View (CRUD) in PHP & MySQL [Beginner Tutorial]

In this article, I will be presenting simple PHP & MySQL code to add, edit, delete and view data. This kind of system is also referred to CRUD (Create, Read, Update, Delete).

Here is a step-by-step guide on creating a CRUD system using PHP & MySQL:

First of all, we will create a new MySQL database. Let us name the database as ‘test‘.

Then, we will create a new table in database ‘test’. Let us name the table as ‘users‘.

Now, we will create a config.php file which contains database connection code. This code connects to the MySQL database. This file is included in all PHP pages where database connection is necessary.

dbConnection.php

In below code, the database host name is localhost where username=root and password=root . The database test has been selected.

To add data to the database, we need an html form.

     
Add Data
 
Home 
 
  Name Age Email 
   

Form action on add.php is addAction.php . It means that the submitted form data will go to addAction.php .

In addAction.php , we do a simple validation of checking if the entered name, email & age are empty or not. If they are all filled then the data will be inserted into database table.

addAction.php

  Name field is empty.
"; > if (empty($age)) < echo "Age field is empty.
"; > if (empty($email)) < echo "Email field is empty.
"; > // Show link to the previous page echo "
Go Back"; > else < // If all the fields are filled (not empty) // Insert data into database $result = mysqli_query($mysqli, "INSERT INTO users (`name`, `age`, `email`) VALUES ('$name', '$age', '$email')"); // Display success message echo "
Data added successfully!
"; echo "View Result"; > > ?>  

     
Homepage
 
Add New Data 
  "; echo ""; echo ""; echo ""; echo ""; > ?>
 
Name
 
Age
 
Email
 
Action
 
".$res['name']."
".$res['age']."
".$res['email']."
Edit | Delete
   

     
Edit Data
 
Home 
 
 
 
Name
 
">
 
 
Age
 
">
 
 
Email
 
">
 
 
>