Gtjkyu

Perform comparison(<=, >=) operation on alphanumeric string in mysql I have a table named "Test" contains following data Name A-00011 A-00012 A-00102 A-00202 and I want to fetch all record that are '<150' . '<150' so anyone can help how to perform the <= and >= operation on alphanumeric string in mysql via query. 2 Answers 2 This can be done by substringing to remove the first two chars, then cast to unsigned int: SELECT CAST(SUBSTRING("A-00012", 3) AS UNSIGNED) Your query would look something like: SELECT * FROM Test WHERE CAST(SUBSTRING(myColumn, 3) AS UNSIGNED) <= 150 thanks @emilpalsson. its works for me. – Vijay Kalathiya Jul 2 at 10:46 SELECT * FROM test WHERE name < 'A...

Backup MySQL tables in Laravel without third-party libraries I new to Laravel. I've been trying to create a controller that backups tables in the form of backup_date_.sql format without using any third-party library at all, but I'm getting frustrated. I've searched and I found some code examples. I've tried to use them within my BackupsController, but things are getting more and more difficult. Any help is really appreciated. This is my code, Thanks in advance. <?php public function query($data, $mode = PDO::FETCH_ASSOC) { $pdo = DB::connection()->getPdo(); $stmt = $pdo->query($data); $results = $stmt->fetchAll($mode); // $results = $stmt->fetch($mode); return $results; } public function backup(Request $request) { if ($request->all()) { $output = ''; foreach (request('table') as $table) { $show_table_query = $this->query("SHOW CREATE TABLE " . stripslashes($table) . "...

Normalized MySQL database not updating What I want to do: I have a normalized database containing 3 tables. All I want to do is replace all the MODEL values with the SERIES values where the OEMID equals 8. MODEL SERIES OEMID I've taken 5 rows from each table as an example: Series Table +--------+----------+ | series | seriesid | +--------+----------+ | 1001 | 7 | | 10036 | 8 | | 10067 | 9 | | 1007 | 10 | | 10076 | 11 | +--------+----------+ Model Table +---------+----------------------------------------+ | ModelId | Model | +---------+----------------------------------------+ | 6694 | 1001 - 71 Flexi Unit Planter - 30Aug01 | | 8264 | 10036 - 315SJ Backhoe Loader | | 8263 | 10067 - 310J Backhoe Loader - 20Jul17 | | 6693 | 1007 - 60 Lawn Tractor - 30Aug01 | | 8262 | 10076 - 853JH Tracked Harvester | +---------+----------------------------------------+ Masterdata Table +-...

Inner joining multiple columns to one ID [duplicate] This question already has an answer here: I'm not in charge of the database so I can't change the format, I realize it is a horrible database. I have two tables I want to join: Table1: address_book id | name | address | phone number | email Table2: team id | person1_id | person2_id | person3_id | person4_id | person5_id | person6_id I would like to join all the personX_id with the name from address_book. I can't seem to figure out how to join more than one column. Hoping someone here could help! Thanks This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question. 1 Answer 1 if you have only 6 colums for person_id you could use a multiple join select a1.name from team t left join address_book a1 on a1.name = t.person1_id left join address_book a2...

XML from URL into historical data graph What I'm after is a service or script that pulls XML from a URL once every x minutes, saves the data to a mysql database and then users are able to search for an item and it displays historical price data for that item. This is what the XML looks like for 1 item: <?xml version="1.0" encoding="UTF-8"?> <Success> <Result> <AccId>2075</AccId> <Type>2793</Type> <Price>100</Price> <SaleId>211557</SaleId> <Time>636662891054273275</Time> <Hours>47</Hours> </Result> <Result> <AccId>7911</AccId> <Type>2793</Type> <Price>100</Price> <SaleId>198528</SaleId> <Time>636662291658957640</Time> <Hours>31</Hours> </Result> <Result> <AccId>8839</AccId> ...

Set Outgoing ip for remote php mysql connection I have php application with separate host for both application and database, Lets say my application is hosted on Ip1, and db is hosted on Ip2, both apllication and Db is in separate network, now application server is behind the firewall, my firewall has two ips fw1,fw2 for failover purpose, for both firewall ips, rule has been set to allow connection from ip1 to ip2. Now the problem is one of the firewall ip fw1 is down, and my application is still going to connect db hosted on ip2 through fw1, resulting in fatal error PDO CONNECTION ERROR: SQLSTATE[HY000] [2002] A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. Is there any way in php,mysql, i can define Outgoing ip for remote mysql connection, so i can direct application to make connection through fw2 instead of fw1. I can do this in case of curl...

How to remove <br>s that are in the beginning of the string? Here is my table: -- mytb +----+---------------------------+ | id | content | +----+---------------------------+ | 1 | <br> whatever .. | | 2 | whatever <br> whatever .. | | 3 | <br> whatever .. | | 4 | <br> whatever <br> .. | +----+---------------------------+ I need to remove all <br> s that are in the beginning of the string. I can specify it using ^ in PHP regex. How can I do the same in MySQL? <br> ^ update mytb set content = trim(content) It just removes the surrounded spaces. wich version of mysql are you using ? – Daniel E. Jul 2 at 8:44 set content = REPLACE(content, '<br>', '') ? – Fred Jul 2 a...

Django and Mysql 8: 2012, 'Error in server handshake' I am trying to install Django on new developer machine running MacOS High Sierra with latest version of Mysql (Ver 8.0.11). When I try to: python manage.py runserver i get this error message: django.db.utils.OperationalError: (2012, 'Error in server handshake') Does anyone has any experience with this? I have installed and I am running virtual environment that I have created with python3.6. I have installed mysqlclient too but I can't find anyone with this issue.. If you have both MySQLdb and mysqlclient installed in the virtualenv, remove both, then install just the newest mysqlclient . – AKX Jul 2 at 6:48 MySQLdb mysqlclient mysqlclient Also, based on this Percona article, it seems MySQL 8 has changed authentication plugin defaults. per...

Creating MariaDB table with a foreignkey to varchar I am trying to create a table (yTable) which has a foreign key mapped to another table (xTable). I am using MariaDB. When I try to create it I get this error: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. When i dump existing table it gives me this description: CREATE TABLE `xTable` ( `id` varchar(25) NOT NULL, ... PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), ... The query that I try to use to create another table is: Create table `yTable` ( `xTable_id` varchar(25) NOT NULL, Primary Key (`xTable_id`), Foreign Key (`xTable_id`) references `xTable` (`id`) on delete cascade on update cascade); In both pasted queries I have removed some columns. They don't affect anything. The problem is with connecting the xTable_id from yTable to xTable.id. For some reason it doesn't...

Protecting SQL Server for Private Usage So I have an issue. I have a server, lets call it (testserver.net). Right now, to change the database, from my application, my app runs "testserver.netadd.php". The problem is anyone can run that and change things in my database. How do I make it that needs some sort of verification before running the code in add.php so no one can just have access to my server? (Like a password or something). Let your application access the add.php with some secret tokens(make it a long string). if the token is not present with the request, deny access to the script file. – Shan Jul 2 at 5:45 3 Answers 3 create a token : 1MBASFDFACAUYTUG^%(!@UUIASNSR*_-+LASQWFVSA4QWYUI12670 ,save this token safel...

How to put no limit in database- codeigniter I am using this to get results from a database, but it limits to 1 record. I don't want any limit; I tried removing it but that doesn't work . $this->db->from('links'); $this->db->where('uniq', $nnn[0]->uniq); $this->db->limit(1); $q = $this->db->get(); $nnn[0]->linkos=$q->result()[0]; return $nnn; what happens if you remove this line $this->db->limit(1); ? – shyammakwana.me Nov 8 '17 at 22:27 $this->db->limit(1); dont work , i mean that dont effect anything , – Asokh Roy Nov 8 '17 at 22:28 try printing sql query, and check what's final query ...

Having a problem getting mysqli_query to execute Here's the problem: I started a swap today to use mysqli. No biggie, just had to change a few statements. Everything went fine, no errors... Except that I can't get it to execute any queries at all. I have double and triple checked my syntax. I even started creating situations where it SHOULD return an error (Trying to get it to INSERT to Tables that don't exist or with values that exceeded column limits or didn't match type) and... nothing. It doesn't return an error, it doesn't write. It WILL complain if parameter one is NOT a mysqli type. Here's the relevant code: $con = mysqli_connect("localhost", "root", "","test"); if (mysqli_connect_errno()) { printf("Connect failed: %sn", mysqli_connect_error()); exit(); } $query = "INSERT INTO files VALUES (NULL, 5, 'hello')"; mysqli_query($con, $query); And nothing. It runs without a problem, but it ...

Error in connecting to DB: Error: Handshake inactivity timeout I am running a node.js server which is using mysql DB. I was working fine but suddenly it has started throwing following error. Below is the stacktrace of the error: Error in connecting to DB: Error: Handshake inactivity timeout at Handshake.<anonymous> (/home/ubuntu/backend/node_modules/mysql/lib/protocol/Protocol.js:164:17) at emitNone (events.js:86:13) at Handshake.emit (events.js:185:7) at Handshake._onTimeout (/home/ubuntu/backend/node_modules/mysql/lib/protocol/sequences/Sequence.js:129:8) at ontimeout (timers.js:365:14) at tryOnTimeout (timers.js:237:5) at Timer.listOnTimeout (timers.js:207:5) -------------------- at Protocol._enqueue (/home/ubuntu/backend/node_modules/mysql/lib/protocol/Protocol.js:145:48) at Protocol.handshake (/home/ubuntu/backend/node_modules/mysql/lib/protocol/Protocol.js:52:23) at PoolConnection.connect (/home/ubuntu/backend/node_modules/mysql/lib/Connection.js:130:18) at Pool.getConnect...

Laravel Query with Multiple Tables Join and Multiple Join Fields I'm trying to build a query to join multiple tables with multiple join fields. This is the code: $crew = DB::table('crew') ->join('aclist', function ($join){ $join->on(function($query){ $query->on('aclist.ac_config', '=', 'crew.config') ->on('aclist.ac_type', '=', 'crew.ac_type'); }); }) // Error happened after I add this indexform join script ->join('indexform', function ($join){ $join->on(function($query){ $query->on('indexform.config', '=', 'crew.config') ->on('indexform.ac_type', '=', 'crew.ac_type'); }); }) ->select('crew.*','ind...

A minimum of a 3 Node Galera Cluster is Recommended, but is it better to have 5 nodes? Right now I have a 400GB database, with a 5 node Galera Cluster. They are all RAID 10 SSDs. I've read the following: If the node goes missing due to a network problem or otherwise leaves without telling the rest of the cluster, then problems can arise. For the cluster to function, it needs a quorum, a majority of nodes active in the cluster. The two other nodes will continue to function normally since their partition has more than half of the known nodes but the node that left will stop accepting queries when it realizes that it is no longer in contact with the active partition. In this case, assuming an application can access the two active nodes, the failure can go mostly unnoticed. I am trying to reduce my cost and make things more optimal. My cluster is handling a few thousand queries per minute. Is it safe to have a 3 node cluster? What happens if 1 or 2 of the nodes go down,...

Command UPDATE using existing data I need to update the records of some tables, and I tried using the following command: UPDATE auction a JOIN (SELECT bidding_price, user_id, auction_id FROM bid_account WHERE auction_id = a.auctionID ORDER BY bidpack_buy_date DESC LIMIT 1) ult_lance_a SET a.auc_final_price=ult_lance_a.bidding_price, a.buy_user=ult_lance_a.user_id WHERE a.auctionID=ult_lance_a.auction_id; However I am getting the following error: #1054 - Column 'a.auctionID' unknown in 'where clause' what did I do wrong? Here is the return of the command: SELECT bidding_price, user_id, auction_id FROM bid_account WHERE auction_id = XXX ORDER BY bidpack_buy_date DESC LIMIT 1 SELECT bidding_price, user_id, auction_id FROM bid_account WHERE auction_id = XXX ORDER BY bidpack_buy_date DESC LIMIT 1 bidding_price = 6.23 user_id = 1720 auction_id = 1818 In the auction table, there are the columns: auction auc_final_price bidding_price auctionID There are several record...