The mysql quiz has changed! We have replaced mysql questions with answers! And from those answers you'll find further links to even more information - forums where you can ask questions, training courses, longer articles, and more. [link to quiz index] ... If you really want the old quiz questions, you can find them here, here, here and here ... and with onward links to the possible answers to each question too.

Removing duplicates from a MySQL table

Surely there's got to be an easy way to remove duplicate records from a MySQL table? With a well designed table that has a unique primary key, you can simply get rid of a few duplicates, but if there ...

Optimising and caching your MySQL enquiries

If you want to get a list of all the different values in a column, but don't care how many times each occurs, you can apply the DISTINCT keyword on your SELECT. See [here]. The DISTINCTROW is a ...

Counting rows in joined MySQL tables

Yesterday, I showed you how to join two database tables and get out all matching records, and records which are "orphans" too - records in the left hand table that don't match anything in the right ...

Relating tables with joins in MySQL

Relational Databases are stores of data in which that data can be matched using common characteristics. For example, I have a table of data about programming languages (which includes a columns for ...

Extra MySQL course dates (2 day course, UK)

We've added a MySQL course into our calendar - on Saturday and Sunday 20th and 21st February, at Well House Manor. The course includes a general introduction to SQL, with a slant towards the MySQL ...

The future of MySQL

MySQL has grown from a very neat little open source database 10 years ago into a very powerful and widespread database that's not always fully open source any longer today. The MySQL company was ...

Moving the product forward - ours, and MySQL, Perl, PHP and Python too

I have just posted a "steady as she goes" message, welcoming the new decade but reassuring our customers that we're carrying on offering very much the same high quality courses, and the same high ...

Cant connect to local MySQL server through socket /tmp/mysql.sock

Q Have you ever had the error message Can't connect to local MySQL server through socket '/tmp/mysql.sock' from your PHP program. It's one that I see quite often, but it doesn't seem all that well ...

MySQL - efficiency and other topics

Following on from the last two days of MySQL training, you'll see a number of blog entries covering stored procedures, securing a mysql server, and exploring database requests in PHP. Further ...

MySQL stored procedures / their use on the web from PHP

Are there times when your PHP will pull a large result set back from MySQL, or have to make a large number of queries, just to further process the data into a relatively small dynamic section of a ...

Learn about MySQL stored procedures

Current releases of MySQL include "stored procedures". What are they? You may start by considering stored procedures to be "Macros" - a series of commands bunched together as one, or a complex ...

Securing MySQL on a production server

There's a conumdrum for the authors / distributors of any open source server software that's likely to be used in a productions environment - should they send it out so that it's quick and easy to try ...

Potted MySQL installation

Putting MySQL onto a machine that may previously have had an install ... here's a summary of the commands we used yesterday. It's a useful checklist (reminders to get rid of old config files that ...

Using print_r in PHP to explore mysql database requests

The print_r function in PHP lets you "print raw" the contents of a variable - great for debugging and far better that print which - if you pass it an array helpfully says Array Embed your print_r ...

Which version of MySQL am I running?

Oh be careful - that is a VERY dangerous question. Do you mean "what version is my MySQL server" or "what version of the mysql client program am I running?" I'll show you how to find answers to both ...

Mysqldump fails as a cron job - a work around

Here's a curiosity ... running a mysqldump as a crontab job, nothing happens on one of our servers. Now I HAVE included the full path to the executable file in my crontab line, and I have ...

Grouping rows for a summary report - MySQL and PHP

It's quite quick and easy to write a loop that goes through a mysql result set and displays the content on an HTML page (but remember security of data, size of resulting HTML page, and the need to ...

How do I query a database (MySQL)?

From my mailbox ... "What are the different ways of queries in databases? Could u pls explain each way in more detail? thanks" The term "query" means something a little bit different in the term ...

MySQL server replication

Following on from yesterday's article about MySQL tuning ... there may come a time that a single server can't cope. What should you do? Consider replicating your server. This works well if you've ...

Monitoring and Tuning your MySQL installation

How much memory does MySQL occupy? Is it efficient / does it need more memory? Can I tune it? All good questions ... and to make the most of any answers, there's something you'll need to know first ...

Images in a database? How big is a database? (MySQL)

The discussion came up yesterday as to whether images should be stored in plain files or in a database, with one of my delegates saying that he had been strongly advised against a database. That ...

Oracle take over Sun who had taken over MySQL

The newspaper tells me that Oracle is buying Sun Microsystems. Since Sun Microsystems bought MySQL about a year ago, this means that Oracle now owns MySQL. I wonder what they will do with it. I am ...

MySQL - looking for records in one table that do NOT correspond to records in another table

Q: Can you give an example where I can retrieve all the records of a table "users" where there are no corresponding records in other table "user_role" A: No (sorry - I'm too busy to set up tables of ...

MySQL - licensing issues, even with using the name

MySQL is a really good database and it's been available for a very long time under an open source license - indeed this piece of text will be stored in a MySQL database when I've added it to our web ...

Setting up a MySQL database from PHP

Yes ... training on a Sunday ;-) ... I've been setting up a MySQL database from a PHP script and testing it. A couple of bits of source to share. 1. The setup of the database: <?php ...

What a difference a MySQL Index made

If you add an index to a column in a MySQL table, you provide a very quick way of looking up a row or set of rows based on the value in a certain column, but at the expense of additional disc space ...

Summary of MySQL installation on a Linux system

The critical steps of deleting an old copy of MySQL, and installing and starting up a nice new clean one! (the numbers are my "history number" - I did a lot of ls-ing and vi-ing in between!) 1001 ...

Ruby, Perl, Linux, MySQL - some training notes

We have just come to the end of a solid 12 days of training ... and we are just getting ready for the next week, with delegates arriving this evening. There's a lot going on behind the scenes, even ...

MySQL database from Ruby - an example

Both MySQL and Ruby are huge subjects - the former being an excellent database engine and the latter a superb programming language. Yet although each individually is a huge subject, linking the two ...

Hiding a MySQL database behind a web page

"Keep the browser simple - with a single route in (http) to a server." That's the philosophy behind the web and one of the things that's made it a huge success. It means that a straightforward ...

More HowTo diagrams - MySQL, Tomcat and Java

Here are some more diagrams "off the wall" from last week - showing "howto" and "whatis" information for Tomcat, Java and MySQL Some Tomcat and Java Fundamentals On the right of the board ... the ...

Finding words and work boundaries (MySQL, Perl, PHP)

If you're searching for the word "mile", you probably don't want the page that tells you that Sally Smiled at Harry. But you may want to find a Milestone, even if it is within quotes. Regular ...

Apache httpd, MySQL, PHP - installation procedure

If you're installing LAMP (Linux, Apache, MySQL, PHP), what do you do and in which order? Here's a summary of the procedure we followed on todays Apache httpd deployment course - in this case ...

addslashes v mysql_real_escape_string in PHP

One of the most popular pages on our website is the one that shows you how to upload an image and store it in a database. And the associated demonstration showing you how to view images via a PHP ...

Making PHP and MySQL training relevant to the course delegates

If I'm training on MySQL or PHP, I'll often come up with an example in front of the class - it's much more effective training to see HOW an answer is reached than to simply be presented with a ...

Database design for a shopping application (MySQL)

Here's a "proof of concept" demo from yesterday's MySQL session - a plan for the various tables that might be involved, and a few sample columns to see how "the whole" would work together. On one ...

Joining MySQL tables revisited - finding nonmatching records, etc

A join lets me connect two tables one to the right of the other. Here's an example. First table - estate agents: mysql> select agent,town,aid from agents; ...

Uploading to a MySQL database through PHP - examples and common questions

One of the most popular pages on the technical part of our web site relates to the uploading of images to a server from a local disc file via a browser, then storing those images on a database, and ...

Downloading data for use in Excel (from PHP / MySQL)

Requirement: To allow a visitor to your web site to click on a link that downloads data from the server into a Microsoft Excel spreadhseet. Solutions - I'm going to offer you two: a) To send the ...

Ongoing Image Copyright Issues, PHP and MySQL solutions

I actually want to encourage people to use my images ... but to do so with permission, which usually means something as simple as a link back you our page which works out for everyone's benefit. A ...