In brief, to show the tables in a MySQL database, run the command:Continue reading
I have a
date field in my mysql database table. I want to replace year 2017 in this field to
2019 Following command would do this.
UPDATE races SET `date` =REPLACE( `date` , ‘2018’, ‘2019’ ) WHERE INSTR( `date` , ‘2018’ ) >0
Just saving it for reference here.
Sometimes you wanted to update columns of one MySQL table by quering another MySQL table and getting a column value from the later table. For example you have a ‘students’ table and you wanted to create a new field ‘totalMarks’ in it and wanted to fill or update by taking sum of the ‘marks’ field from another table named ‘marks’. Here comes the use of subquery to update column. Continue reading
You can print the table structure of MySQL table in PHP using simple php script. All you need to do is to make a ‘describe table’ call and process it using php script, similar to one you use to print or get records from a mysql table. This can prove to be a very useful piece of code in, Continue reading
For some reason i had duplicate entries in my WP database. The ID field in wp_users table was tempered with and was changed to Non Auto-Increment to insert records on the will. I deleted all ID=null records manually and then tried to change it to AUTO INCREMENT but it came up with Duplicate Entry error and then i found that it had duplicated/multiple entries with same IDs as well. Here’s the remedy i had to apply in order to delete those duplicate entries and change ID field to AUTO INCREMENT once again. Continue reading
Here’s the scenario.
I have two fields in my “listings” table with help of which i wanted to sort (order by) results. First field is “activation_date”. It is the activation date of a listing. Second one is “featured” which stores featured tag for a listing in a value of 1 or 0. Continue reading
While working on a web links directory i wanted to update categories table’s num_listings column with the count of listings under each category. The category id was stored something like this in listings table: Continue reading
Just a few notes on joining a MySQL table to itself to get unique combination of data stored in its fields.
Suppose we have a MySQL table named `keywords` having fields `word1`, `word2` and `word3`. Once we have got a few records inserted into this table we would want to show all possible combination of keywords stored in those fields. For example, a select query returns a following set of data: Continue reading
Having WAMP installed when i try to type mysql in my windows command prompt it would give me the following error: Continue reading