Monday, December 24, 2018

sql : MYSQL Update Statement Inner Join Tables

https://stackoverflow.com/questions/8057565/mysql-update-statement-inner-join-tables

Wednesday, December 19, 2018

SQL | Join (Cartesian Join & Self Join)

CARTESIAN JOIN: The CARTESIAN JOIN is also known as CROSS JOIN. In a CARTESIAN JOIN there is a join for each row of one table to every row of another table. This usually happens when the matching column or WHERE condition is not specified.

In the absence of a WHERE condition the CARTESIAN JOIN will behave like a CARTESIAN PRODUCT . i.e., the number of rows in the result-set is the product of the number of rows of the two tables.
In the presence of WHERE condition this JOIN will function like a INNER JOIN.
Generally speaking, Cross join is similar to an inner join where the join-condition will always evaluate to True

Syntax:

SELECT table1.column1 , table1.column2, table2.column1...
FROM table1
CROSS JOIN table2;


table1: First table.
table2: Second table

https://www.geeksforgeeks.org/sql-join-cartesian-join-self-join/

Monday, December 17, 2018

PHP: Create Pagination in PHP, MYSQL, JQUERY and AJAX

https://www.skptricks.com/2018/01/create-pagination-in-php-mysql-jquery-and-ajax.html

PHP : DataTables - Server-side Processing using php and Ajax

https://github.com/sokchab/phpserverside

PHP: Simple pagination

<!DOCTYPE html>
	<html lang="en">
	<head>
	<meta charset="UTF-8">
	<title>Pagination</title>
	</head>
	<body>
	<?php
	// connect to database
	$con = mysqli_connect('localhost','root','');
	mysqli_select_db($con, 'pagination');

	// define how many results you want per page
	$results_per_page = 10;

	// find out the number of results stored in database
	$sql='SELECT * FROM alphabet';
	$result = mysqli_query($con, $sql);
	$number_of_results = mysqli_num_rows($result);

	// determine number of total pages available
	$number_of_pages = ceil($number_of_results/$results_per_page);

	// determine which page number visitor is currently on
	if (!isset($_GET['page'])) {
	$page = 1;
	} else {
	$page = $_GET['page'];
	}

	// determine the sql LIMIT starting number for the results on the displaying page
	$this_page_first_result = ($page-1)*$results_per_page;

	// retrieve selected results from database and display them on page
	$sql='SELECT * FROM alphabet LIMIT ' . $this_page_first_result . ',' . $results_per_page;
	$result = mysqli_query($con, $sql);

	while($row = mysqli_fetch_array($result)) {
	echo $row['id'] . ' ' . $row['alphabet']. '<br>';
	}

	// display the links to the pages
	for ($page=1;$page<=$number_of_pages;$page++) {
	echo '<a href="index.php?page=' . $page . '">' . $page . '</a> ';
	}

	?>
	</body>
	</html>

Sunday, December 16, 2018

Cakephp: Custom relation with table

$options['joins'] = array(
    array('table' => 'security_prices',
        'alias' => 'SecurityPrice',
        'type' => 'LEFT',
        'conditions' => array(
            'Security.ticker = SecurityPrice.ticker',
        )
    )
);

$Security->find('all', $options);

https://stackoverflow.com/questions/19092608/cakephp-linking-a-hasmany-relationship-with-a-different-index

Wednesday, December 12, 2018

Laravel : Pivot tables and many-to-many relationships

Pivot tables and many-to-many relationships

By: Povilas Korop

Today I want to talk about a feature of Laravel which is really useful but can be potentially difficult to understand at first. Pivot table is an example of intermediate table with relationships between two other “main” tables.

Real-life example of pivot tables

In official documentation they show the example of User-Role relationships, where user potentially can belong to several roles, and vice versa. So to make things clearer – let’s take another real-life example: Shops and Products.

Let’s say a company has a dozen of Shops all over city/country and a variety of products, and they want to store the information about which Product is sold in which Shop. It’s a perfect example of many-to-many relationship: one product can belong to several shops, and one shop can have multiple products.

So here’s a potential database structure:
shops
– id
– name

products
– id
– name

product_shop
– product_id
– shop_id

The final table in the list – product_shop is called a “pivot” table, as mentioned in the topic title. Now, there are several things to mention here.

Name of the pivot table should consist of singular names of both tables, separated by undescore symbole and these names should be arranged in alphabetical order, so we have to have product_shop, not shop_product.
To create a pivot table we can create a simple migration with artisan make:migration or use Jeffrey Way’s package Laravel 5 Generators Extended where we have a command artisan make:migration:pivot.
Pivot table fields: by default, there should be only two fields – foreign key to each of the tables, in our case product_id and shop_id. You can add more fields if you want, then you need to add them to relationship assignment – we will discuss that later.

Models for Many-to-Many Relationships: BelongsToMany

Ok, we have DB tables and migrations, now let’s create models for them. The main part here is to assign a many-to-many relationship – it can be done from either of “main” tables models.

So, option 1:
app/Shop.php:

1

2

3

4

5

6

7

8

9

10

11

12

class Shop extends Model

{

    /**

     * The products that belong to the shop.

     */

    public function products()

    {

        return $this->belongsToMany('App\Product');

    }

}

Or option 2:
app/Product.php:

1

2

3

4

5

6

7

8

9

10

11

12

class Product extends Model

{

    /**

     * The shops that belong to the product.

     */

    public function shops()

    {

        return $this->belongsToMany('App\Shop');

    }

}

Actually, you can do both – it depends on how will you actuall use the relationship in other parts of the code: will you need $shop->products or more likely to query $product->shops, or both.

Now, with such declaration of relationships Laravel “assumes” that pivot table name obeys the rules and is product_shop. But, if it’s actually different (for example, it’s plural), you can provide it as a second parameter:

1

2

3

4

5

6

public function products()

{

    return $this->belongsToMany('App\Product', 'products_shops');

}

https://laraveldaily.com/pivot-tables-and-many-to-many-relationships/

Mehedi Hasan's BLog