While working with MySQL database there might be situation when you wish to update certain column only if the value being updated is greater than existing value or current value stored in that column. MySQL GREATEST and LEAST functions are quite handy in such situations. Let’s see how can we use first function to update a column if value is greater than current value.
In MySQL we create tables and build relationship among them. In the process if we need to truncate foreign key constrained table then we can’t do it simply and MySQL will throw an error even we have already removed table having foreign key constraint:
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint…
Sometimes we create MySQL tables which don’t contain auto increment primary key column instead they may have unique key or combination of unique indexes. In such tables we wish to insert a new row if primary/unique key or keys combination doesn’t exists and update record if exists.
As responsive design has come into trends, I decided to write this post to display Data in Responsive Columns from Database using PHP. Like the image below, a product catalog would be good example of this where columns adjust them automatically to maintain responsiveness with different screen sizes.
In MySQL we might need to auto increment a non primary key field or column in a database table. This might require particularly in cases where we have already set a combination of columns as primary key. So here are steps with images to let you understand how can we make a column as auto increment non primary key column in phpMyAdmin.
Even wonder how to set mix of two or more MySQL database columns/fields as primary key in phpMyAdmin as there is no direct link during creation of table. This articles with tell you the way to setting composite primary key in phpMyAdmin.
I have the old database in which time was stored as string with AM/PM. One day I required to do some time calculation on that field. I have googled out for convert time string into time with AM/PM in MySQL and found some solution.
Sometime we face the problems for concatenating of group rows column value in a single string. Group_concat() is a function which help us to achieve this.
If you already have database tables and you just want to apply foreign keys for record on those table then you can apply foreign key in phpMyAdmin using database query also.
I have already written a tutorial ‘Setting up foreign key in phpMyAdmin‘ with example images and I prefer to write database query to set up foreign key in existing database tables as queries allow me to accomplish all required steps in once go.
Saving comma separated values or delimited list in database looks convenient at first but never use them because most and most of the time that’s only a bad idea. I have provided many reasons to avoid them and only one when you can take favour of.