How to export some rows of a MySQL table using WHERE clause - Selectively dumping data with mysqldump feature - MySQL export few rows from selected table as SQL file

How to export some rows of a MySQL table with where clause? I have a MySQL say test and I want to create a importable .sql file for rows where id are between 10 and 100. The way is to use mysqldump feature of MySQL server.

This is the command which will export few rows (for my case where id>=1 and id<=10) into a file: c:\xampp\mysql\bin\mysqldump.exe -uroot DatabaseName TableName --where="id>=1 and id<=10" > c:\Users\HP\Downloads\Export.sql

You can also use the –where flag to selectively dump data from more than one table, but obviously the columns specified in the where clause need to be in both tables LIKE c:\xampp\mysql\bin\mysqldump.exe -uroot DatabaseName TableName1 TableName2 --where="id>=1 and id<=10" > c:\Users\HP\Downloads\Export.sql

MapBox Implementation of Map: How to plot multiple points on MapBox using a for each statement - multiple points with exactly the same location using MapBox

Below is code example of plot multiple points on MapBox:

<div id='map' class="map"></div> <script type="text/javascript"> mapboxgl.accessToken = "pk.your-access-token"; const map = new mapboxgl.Map({ container: 'map', style: 'mapbox://styles/mapbox/streets-v9', center: [6.055737, 46.233226], zoom: 13, scrollZoom: true }); map.on('load', function(){ console.log('map loaded'); const locations = [ [6.055737, 46.233226], [6.0510, 46.2278], [6.0471, 46.23336], [6.0371, 46.23336] ]; locations.forEach(function(coords) { new mapboxgl.Marker().setLngLat(coords).addTo(map); }); }); </script>

Output is as:

Free Map Integration - Leaflet.js with OpenStreetMap tiles - Display multiple point locations as map markers - Plot multiple points on Map view - Display multiple point locations as map markers

Here is the documantation https://leafletjs.com/examples/quick-start/

Code snippet (Download full code from here):

<script src="//code.jquery.com/jquery-3.6.1.min.js"></script> <link rel="stylesheet" href="leaflet.css" /> <script src="leaflet.js"></script> <!-- https://leafletjs.com --> <div id="leaflet-js-map"></div> <div> <button id="moveToPoint">Move to specific point</button> </div> <script type="text/javascript"> const map = L.map('leaflet-js-map').setView([46.241226, 6.051737], 14); L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', { minZoom: 0, maxZoom: 18, attribution: 'Map data © <a href="https://openstreetmap.org">OpenStreetMap</a> contributors' }).addTo(map); const leafletMarkers = L.layerGroup([ new L.marker([46.233226, 6.055737]), new L.marker([46.2278, 6.0510]), new L.marker([46.23336, 6.0471]) ]); leafletMarkers.addTo(map); var LeafIcon = L.Icon.extend({ options: { shadowUrl: 'images/leaf-shadow.png', iconSize: [38, 95], shadowSize: [50, 64], iconAnchor: [22, 94], shadowAnchor: [4, 62], popupAnchor: [-3, -76] } }); var greenIcon = new LeafIcon({iconUrl: 'images/leaf-green.png'}), redIcon = new LeafIcon({iconUrl: 'images/leaf-red.png'}), orangeIcon = new LeafIcon({iconUrl: 'images/leaf-orange.png'}); L.icon = function (options) { return new L.Icon(options); }; L.marker([46.234226, 6.055737], {icon: greenIcon}).addTo(map).bindPopup("I am a green leaf."); L.marker([46.237226, 6.052737], {icon: redIcon}).addTo(map).bindPopup("I am a red leaf."); L.marker([46.240226, 6.065737], {icon: orangeIcon}).addTo(map).bindPopup("I am an orange leaf."); [{T: 46.249226, N: 6.056937, D: '1'}, {T: 46.250226, N: 6.066937, D: '2'}].forEach(function (t) { const el = document.createElement('div'); L.marker([t.T, t.N]).addTo(map).on("click", function () { console.log(this); var popup = L.popup() .setLatLng(this._latlng) .setContent('<p>Hello world!<br />This is a nice popup-' + t.D + '</p>') .openOn(map); }) }); $("#moveToPoint").on("click", function () { map.flyTo(L.latLng(46.249226, 6.056937), 15); const popUps = document.getElementsByClassName('leaflet-popup'); if (popUps[0]) popUps[0].remove(); L.popup() .setLatLng(L.latLng(46.250226, 6.056937)) .setContent('<p>Hello world!<br />This is a nice popup-Self</p>') .openOn(map); }); </script> <style type="text/css"> #leaflet-js-map { width: 100%; height: 500px; } </style>

Output is as below:

Where can I find the MySQL log file in XAMPP - Activate logs in Xampp-Mysql

I use PHP (PDO) to access MySQL in XAMPP. My question is where I can find the MySQL query log, exact query that executed on MySQL server?

You need to run these two queries: SET GLOBAL general_log = 'ON'; SET GLOBAL general_log_file = 'my_log.log';

First will enable loging (which may be off by default) and the second select updates the preferred file (by default under C:/xampp/mysql/data/)

NOTE: On windows 8 you may have to run your SQL IDE as ADMINISTRATOR for this commands to get saved.

You can also set this in the config, go to path_to_xampp/mysql/ and edit my.ini (copy from my-default.ini if it does not exists) and add the settings there:

[mysqld] general_log = 'ON'; general_log_file = 'my_log.log'; sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

You can view your query logs like below window: Step1: You need to install Git Bash Step2: Open Git Bash terminal and navigate to xampp_install_directory/mysql/data directory Step3: Run command tail -f my_log.log

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