Soa Technology

July 6, 2023 in MySql

query to select where count is less than or greater than 10 using group by in mysql

SELECT user_id,count(*) as usercnt FROM `user_activity` WHERE 1 group by user_id having usercnt > 10;

SELECT user_id,count(*) as usercnt FROM `user_activity` WHERE 1 group by user_id having usercnt <= 1;

SELECT user_id,count(*) as usercnt FROM `user_activity` WHERE 1 group by user_id having usercnt <> 10;

SELECT user_id,count(*) as usercnt FROM `user_activity` WHERE 1 group by user_id having usercnt > 10 and usercnt < 100;

SELECT user_id,count(*) as usercnt FROM `user_activity` WHERE 1 group by user_id having usercnt > 10 or usercnt < 100;

June 9, 2023 in MySql

mysql full backup all databases

mysqldump -u root -p --opt --all-databases -r backup.sql

To do a good BD restore without any problem with character sets use these commands (you can change the default-character-set as you need).

mysql -uroot -p --default-character-set=utf8
mysql> SET names 'utf8';
mysql> SOURCE backup.sql;

May 20, 2023 in MySql

update to null mysql

NULL is a special value in SQL. So to null a property, do this:

UPDATE table SET column = NULL;

The MySQL manual states that if the column does not allow NULL values, setting it to NULL will result in the default value for the data type (e.g. an empty string).

February 28, 2023 in MySql

get double value in MySQL with 2 decimal places

 public function getRatings(Request $request)
    {
      $data = array();
      $lang = $request->lang;
      $data['ratings']= DB::connection($lang)->table('review')->select(DB::raw('format(avg(rating),2) as `rate`'),  'review_id')->groupby('review_id')->get(); 
      return $this->sendResponse($data, 'Successfully'); 
    }

format(number, qtyDecimals)
sample: format(1000, 2)
result 1000.00

January 25, 2023 in MySql

meaning of 6 2 in double data type in MySQL

Assuming you mean that the database type is decimal(6, 2), then this means that your column is set up to store 6 places (scale), with 2 to the right of the decimal (precision). You should treat this as a decimal CLR type. A sample would be 1234.56 .

The syntax for the double data type is DOUBLE PRECISION(m,d) where ‘m’ is the total number of digits and ‘d’ is the number of digits following the decimal point. For example, DOUBLE(7,5) means that it will store a value with seven digits and five decimals.

January 12, 2023 in Laravel, MySql

group by in MySQL example only date from datetime

MySQL Example

SELECT date(created_at),count(*) FROM `users` WHERE 1 group by date(created_at) ORDER BY `date(created_at)` DESC

Laravel Example with groupByRaw

 $data = DB::connection('default')->table('users')->select(DB::raw('DATE(created_at) date'),DB::raw('count(created_at) as page_count'))->groupByRaw('DATE(created_at)')->get();

Laravel Example with groupBy

 $data = DB::connection('default')->table('users')->select(DB::raw('DATE(created_at) created_date'),DB::raw('count(created_at) as page_count'))->groupBy('created_date')->get();

January 9, 2023 in MySql, Php

create class file for connection in php MySQLi with Time zone

<?php
 class Connection
{
	var $link;
public	function Connecton()
{
        date_default_timezone_set('Asia/Kolkata'); 
	                $this->link=mysqli_connect("localhost","root","","mvc");	
return $this->link;

	}
	
	public function Query($cmd)
	{
			$query=mysqli_query($this->link,$cmd);
                       $data = array();
			while ($row = $query->fetch_assoc()) {
					$data[] = $row;
				}
	  			$result = new \stdClass();
				$result->num_rows = $query->num_rows;
				$result->row = isset($data[0]) ? $data[0] : array();
				$result->rows = $data;

				$query->close();

				return $result ;
		
	}
	public function NonQuery($cmd)
	{
			mysqli_query($this->link,$cmd);
			if(mysqli_insert_id($this->link)>0)
			{
				return mysqli_insert_id($this->link);
			}
			else
			{
				return mysqli_affected_rows($this->link);
			}
			
			
		
	}
}

?>

January 6, 2023 in MySql, Tips and Tricks

mysqli_real_connect(): (HY000/1045): Access denied for user ‘phpmyadmin’@’localhost’ (using password: YES)

Have you recently changed your MySQL Server root password? If answer is YES, than this is the cause of the error / warning inside phpMyAdmin console. To fix the problem, simply edit your phpMyAdmin’s config-db.php file and setup the proper database password.

/etc/phpMyAdmin/config-db.php

$dbuser='phpmyadmin';
$dbpass=''; // set current password between quotes ' '
$basepath='';
$dbname='phpmyadmin';
$dbserver='';
$dbport='';
$dbtype='mysql';

November 15, 2022 in Laravel, MySql

join table in laravel eloquent

$articles = DB::table('articles')
            ->select('articles.id as articles_id', ..... )
            ->join('categories', 'articles.categories_id', '=', 'categories.id')
            ->join('users', 'articles.user_id', '=', 'users.id')

            ->get();