Storing sets of data with a timestamp is common practice, especially if you want to keep a history of the values that have been stored previously. Lets say you wanted to store a piece of information about two variables, each of which can have a history. Here is an example dataset.
To find duplicate values you need to use the MySQL COUNT() function and then pick out all of the counts that are greater than one.
SELECT value,COUNT(value) AS Count FROM test GROUP BY value HAVING (COUNT(value) > 1) ORDER BY Count DESC;
Conversely you can also select the rows that only have a single entry.
SELECT value,COUNT(value) AS Count FROM test GROUP BY value HAVING (COUNT(value) = 1) ORDER BY Count DESC;
However, it is very nice to pick out the duplicate entries in a table, but you might still need to do something with them. Here is a query to delete any duplicate rows from a table. It does a simple self join and deletes the row value with the lowest ID.
Using MySQL as a database engine in PHP is very powerful, but one thing that can be a pain is trying to debug code. Spotting the difference between a PHP error and a MySQL error can be hard with larger systems.
A good way of debugging MySQL code is by using the mysql_errno() and mysql_error() functions. These functions print off the last error that yuour MySQL server encountered so it can tell you exactly what is wrong with your SQL statements.
The following example code tries to get data from a non-existent table. The $result variable is set to false as the call failed so the error functions print off the SQL error.
If you have command line access to your MySQL database server you will need to use certain parameters to log in. Most web hosts will not allow you to do this, so you might want to install MySQL into a local computer and give it a go.
To log into mysql you must run the program called mysql with certain parameters. Here is an example.
./mysql -u username
One thing you must realise is that all usernames are associated with a host so if the user you specified can't access the server from this host then you won't get far. To specify the host location enter the -h flag.
./mysql -h hostaddress -u username
If your user is able to access this server then you will be asked for a password. You can set the password in the string using the -p flag. If this doesn't work then leave out the space between the -p and the password.
MySQL uses the datatype TINYINT to store boolean values. MySQL stores the value as TINYINT(1) which is the same as a bit so the value is either 0 (false) or 1 (true). Using boolean fields can be very useful, but it can be costly in processing as to change the value you have to query the database, find out the value of the field and then act accordingly.
Here is a simple MySQL query that can be used to toggle the value already present in the TINYINT field without having to do any pre-querying.
UPDATE table SET field = 1 - field
Getting a random row from a MySQL table requires the use of the RAND() function in the ORDER BY clause of the SELECT statement. This will generate a new random number for each row and order them by that new number. In order to get a single row this is combined with the LIMIT clause to limit the result to a single row.
SELECT * FROM theTable ORDER BY RAND() LIMIT 1;