PHP & MySQL PDO: PDO binding values for MySQL IN statement - Bind an array to an IN() condition?

The answer there was that, for a variable sized list in the in clause, you'll need to construct the query yourself.

Consider your array like:

$values = array(1, 2, 3, 4, 5); $count = count($values); $criteria = sprintf("?%s", str_repeat(",?", ($count ? $count - 1 : 0))); $sql = sprintf("DELETE FROM table where column NOT IN(%s)", $criteria); $pdo->sth = prepare($sql); $pdo->sth->execute($values); SQL should be: DELETE FROM table where column IN (?,?,?,?,?)

Rollback or Commit with PDO transaction using PHP and MySQL - SELECT query with PDO - PDO with INSERT INTO through prepared statements

A transaction should end with either a rollback() or a commit(), (only one of them). In case you are using MySQL, make sure you are not using MyISAM engine for tables, as it doesn't support transactions. It usually support InnoDB. Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.

<?php class DB { public static function transactional($closure) { $dbh = null; try { $dbh = new PDO("mysql:host=HOST_NAME;dbname=DB_NAME", "DB_USER_NAME", "DB_USER_PASSWORD"); /*** Set the PDO error mode to exception (will throw exception if any error occurred) ***/ /*** Follow the link for more info: http://php.net/manual/en/pdo.setattribute.php ***/ $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $dbh->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_EMPTY_STRING); /*** Transaction block starting here ***/ $dbh->beginTransaction(); $closure($dbh); $dbh->commit(); } catch(Throwable $ex) { $dbh->rollback(); throw $ex; } } } DB::transactional(function (PDO $PDO) { $PDO->prepare("INSERT INTO records (`RegistrationID`) VALUES(:RegistrationID)")->execute(['RegistrationID' => 'RegistrationID 1']); $PDO->prepare("INSERT INTO records (`RegistrationID`) VALUES(:RegistrationID)")->execute(['RegistrationID' => 'RegistrationID 2']); // $PDO->query used to get results $result = $PDO->prepare("SELECT * FROM records WHERE `RegistrationID` LIKE :RegistrationID"); $result->execute(['RegistrationID' => '%RegistrationID%']); echo "<pre>"; print_r($result->fetchAll()); echo "</pre>"; Below line will throw an exception and data will not persist into database. To persist data into database, comment below line. $x = null + null * $PDO->dd(); });

Output be as follows: Array ( [0] => Array ( [id] => 12 [0] => 12 [RegistrationID] => RegistrationID 1 [1] => RegistrationID 1 ) [1] => Array ( [id] => 13 [0] => 13 [RegistrationID] => RegistrationID 2 [1] => RegistrationID 2 ) )

How to Drop Decimal Places Without Rounding, How to Round a Number to N Decimal Places in Java, Java – How to round double / float value to 2 decimal places, We can use DecimalFormat('0.00') or BigDecimal to round float / double to 2 decimal places.

So the problem is rounding number without rounding, for example we have a number 5.789 and if we want to keep 2 decimal places so that number would be 5.78, it's bit difficult. Because we have to use round number and the value would be 5.79 after rounding. Below is a code snippet to drop decimal places without rounding:

public class NumberRounding { private static final DecimalFormat formatter = new DecimalFormat("#.####################"); public static BigDecimal roundingAtFixedPrecision(Number number, Integer precision) { String[] parts = null; try { parts = formatter.format(number).split("\\."); if (parts.length == 1) { return new BigDecimal(parts[0]); } return new BigDecimal(parts[0] + "." + (parts[1].length() > precision ? parts[1].substring(0, precision) : parts[1])); } finally { number = null; precision = null; parts = null; } } }

And output should be as below: 678.789 converted to 678.78 -39.9899 converted to -39.98 33 converted to 33 -40 converted to -40 3.9 converted to 3.9 9.009 converted to 9.00

MySQL "Group By" and "Order By"

A simple solution is to wrap the query into a subselect with the ORDER statement first and applying the GROUP BY later:

SELECT * FROM ( SELECT `timestamp`, `fromEmail`, `subject` FROM `incomingEmails` ORDER BY `timestamp` DESC ) AS tmp_table GROUP BY LOWER(`fromEmail`)

This is similar to using the join but looks much nicer. Using non-aggregate columns in a SELECT with a GROUP BY clause is non-standard. MySQL will generally return the values of the first row it finds and discard the rest. Any ORDER BY clauses will only apply to the returned column value, not to the discarded ones. IMPORTANT UPDATE Selecting non-aggregate columns used to work in practice but should not be relied upon. Per the MySQL documentation "this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."

Java - Always Name Your Thread Pools - Naming threads and thread-pools of ExecutorService - set name of thread

You could supply a ThreadFactory to newSingleThreadScheduledExecutor(ThreadFactory threadFactory). The factory will be responsibe for creating threads, and will be able to name them.

ExecutorService is a JDK API that makes asynchronous task execution easier. ExecutorService offers a pool of threads and an easy-to-use API for assigning tasks. The ExecutorService gives the name of the threads in the thread pool. This shot discusses how we can assign custom names to the threads of the thread pool of the ExecutorService.

BasicThreadFactory An ExecutorService employs a ThreadFactory to create its threads to execute tasks. In many circumstances, users do not need to worry about a ThreadFactory because the ExecutorService's default one will suffice. A custom ThreadFactory must be constructed with particular needs, such as thread naming.

import java.util.TimerTask; import java.util.concurrent.*; public class Main { public static void main(String[] args) { // instant thread execution ExecutorService executorService = Executors.newFixedThreadPool(3, namedThreadFactory("test-thread")); for (int i=0; i < 5; i++) { executorService.submit(() -> System.out.println(Thread.currentThread().getName())); } executorService.shutdown(); // scheduled thread execution ScheduledExecutorService executor = Executors.newSingleThreadScheduledExecutor(namedThreadFactory("scheduled-thread")); TimerTask repeatedTask = new TimerTask() { @Override public void run() { try { System.out.println(Thread.currentThread().getName()); } finally { executor.shutdownNow(); } } }; executor.schedule(repeatedTask, 1L, TimeUnit.MINUTES); } static ThreadFactory namedThreadFactory(String name) { return new YourThreadFactory(name); } } class YourThreadFactory implements ThreadFactory { private String name = "[No Name]"; YourThreadFactory(String name) { this.name = name; } public Thread newThread(Runnable r) { return new Thread(r, name); } }

How to convert result table to JSON Array or JSON Object or JSON String in MySQL

I'd like to convert result table to JSON Array of Object in MySQL using preferably only plain MySQL commands. For example with query SELECT name, phone FROM person; | name | phone | | Jack | 12345 | | John | 23455 | the expected JSON output would be as below as per my requirement: [ { "name": "Jack", "phone": 12345 }, { "name": "John", "phone": 23455 } ]

The maximum value for group_concat_max_len is 18446744073709551615. To set the variable forever use SET GLOBAL group_concat_max_len=4294967295;

Example 1: SELECT i.id, JSON_OBJECT('id', ig.id, "name", ig.name) AS 'group_json' FROM item i LEFT JOIN item_group ig ON i.item_group_id=ig.id ORDER BY i.id DESC LIMIT 10;

Example 2 (Group Concat): SELECT ig.item_sales_rate_id, GROUP_CONCAT(DISTICT JSON_OBJECT('id', ig.id, "status", ig.status)) AS 'group_json' FROM item_sales_rate i LEFT JOIN item_sales_uom ig ON i.id=ig.item_sales_rate_id WHERE ig.item_sales_rate_id IS NOT NULL GROUP BY ig.item_sales_rate_id ORDER BY i.id ASC LIMIT 10;

MySQL casting from decimal to string - mysql cast to varchar - mysql convert decimal to string char varchar

MySQL - casting from decimal to string select CAST(ROUND(345345345345345345345353453453453454.4345345,6) AS CHAR(65)) AS 'big_number'; don't try to cast to varchar, there is a bug at MySQL end