Archive for category mysql
Generate Date from a range in Mysql and pad the results where no date is present
If you want to generate dates, MYSQL doesn’t have a range function, so I wrote a work around for the same. So how it works is I have reused a code to generate numbers from my previous post. So I added date function to that query and reduce the generated numbers from the date. Hence effectively its Currrent Date – x number of days. Thus we generate the dates from the date we provide to the x number we generate. So here is the code:
Generate 1 to 1000 in mysql
Sometimes its necessary to have thousand rows to join or generate new values, we need to get the thousand rows first. For this I used union of numbers 0-9. I have joined four of these to get to four digit values. So here is the code:
Continue reading “Generate 1 to 1000 in mysql” »
Check the port in which the Mysql is running on
Mysql can be configured to run other ports other than the default port (of 3306). So what will you do when you need to find the port on which a mysql server runs on. If you don’t have access to the server files and can access the server only using the mysql client, the only way to know this is by checking the Global Variable set. We can see all variables by issuing the command:
Continue reading “Check the port in which the Mysql is running on” »
Group by Date when Stored in Mysql DataBase as Date Time
I have a table called date_count with 2 columns one is a value and another is a date time, say called date_time in mysql. Date Time in mysql is stored as dd-mm-yy hh:mm:ss. The issue comes when I need to get the number of rows per day. As its stored as date time format in mysql , you need to specify both date and time when you specify a group by. So grouping by day is not possible in that case. So we need to use the in-build mysql function DATE(). The function accepts date time as parameter and returns only date back. So we just need to use the DATE() function where we need to group by date and all the date time values are converted to date and can be grouped by date. So here is the implementation of it.
Continue reading “Group by Date when Stored in Mysql DataBase as Date Time” »
Check if a table exists in MySQL from PHP
Sometimes its necessary to execute a query or execute some code if a table exists or not. So a bit of searching got me to the conclusion that mysql doesn’t have a direct query or method for it from php. So there are two methods to do it. Continue reading “Check if a table exists in MySQL from PHP” »
Display row number in Mysql Select Query
How many times did want to have the row number or the position of the row in the selected query? I had many cases where I needed this feature. I needed the feature for printing the rank of a person where each row is the mark of the person. So lets see how to implement this in mysql.
There is no straight forward function or query parameter to do it (At least none I know of). So I am writing the query to declare a variable and increment that variable on printing that variable along with printing each row. So it just displays the variable on each row. So here is sql query for it. Try it out and tell me.
Continue reading “Display row number in Mysql Select Query” »
Create a table like another table or from a select query
There are some cases where we need to create a table similar or exactly same as another table. The easiest and best way is use create table along with additional parameters. We can create a table similar to another table without data and with data. Continue reading “Create a table like another table or from a select query” »
Customizing Mysql Prompt For multiple Servers
If you are using mysql regularly on multiple servers its really useful to have the promt in mysql to show something more than mysql>. It would be useful if it showed the username,hostname, current database and things like that.
So how to do this.
Continue reading “Customizing Mysql Prompt For multiple Servers” »
Recent Comments