I have a yearly list of sales of an item for the past 20 years. the data is like this. date ; sales value 2001-01-01 ; 423 2001-01-02 ; 152 2001-01-03 ; 162 2001-01-04 ; …
Tag: mysql
How to convert varchar to datetime, not working
I have imported some columns from a textfile into my table stuff. I had to import date as varchar(255). I want to convert datatype from varchar to datetime. The format of date data is: 6/2/2018 3:…
Syntax Error when using multiple parameter substitutions in a MYSQL Query
I need to Update MYSQL data using JS after I receive an AJAX Post request I made a variable for the MYSQL Update Query and I’m passing in the field to be updated, new value, row to be updated as an …
MySQL INSERT inserting BIT to 1 instead of 0
I have the following query which should insert a 0 but instead inserts 1. It is executed with prepared statements in PHP: All inserts are performed fine, except a 1 is inserted in column6 instead of a 0. Column 6 has the datatype BIT. The query works fine, however, when executing directly in the phpmyadmin ‘MySQL’ tab. What am I
Data truncation: ” While Inserting or Updating Value
I am trying to Insert/update a string (JSON). While inserting/Updating the value I get data truncation error. I have tried making a JSON type column and passing an JSON_OBJECT() type but that fails …
MySQL Workbench “OLD” syntax changed?
I had a trigger set in MySQL workbench that used to accomplish what I wanted. Recently, my workplace updated MySQL workbench to version 6.3.10 across all computers and the triggers were dropped during …
SELECT COUNT with JOIN optimization for tables with > 100M rows
I have following query SELECT SUBSTRING(a0_.created_date FROM 1 FOR 10) AS sclr_0, COUNT(1) AS sclr_1 FROM applications a0_ INNER JOIN package_codes p1_ ON a0_.id = p1_.application_id …
WordPress: How to update data in database using PHP with MySQL?
I am using core PHP to make WordPress custom page template for my website, but when I am updating user id, it was not updating the user id. Suppose I have a user in database and when the user pay amount using PayPal, make that user id 1. By default its id is 0. I want to make that user
Split a string into two fields based on multiple words
I have the following column in my Table name:STAT It would be helpful if someone can assist in writing a mysql script I would like to split the strings in to 2 columns. However the split should happen only based on the last word, i.e the Region as can be seen in the example below. There is an exception that
SQL refer Primary Key in same Table and Query
I am maintaining a table of Items (Id is Primary Key). Some Items have Parent Item ( Which is also an Item, the colum Parent holds the Id of the parent Item, which is also in the same table. Some …