Using 'NOT IN' in MySQL

Suppose we have a table called USERS that has a primary key 'id' and another table SUBSCRIPTIONS with foreign key 'uid'.

If we want to remove all subscriptions (from the SUBSCRIPTIONS table) that does not correspond to any user, we can run this MySQL query:

DELETE FROM SUBSCRIPTIONS WHERE uid NOT IN (SELECT id from USERS)

What we are doing here is getting a list of all the ids from the USERS table and then deleting all rows from the SUBSCRIPTIONS table where the user id (uid) does not correspond to any id in the USERS table. Very simple.

Note: Throughout this blog, you will find many books that are for sale on Amazon. I recommend you get these books while learning web programming as they will help strengthening your skills.

Replacing MySQL characters

The following MySQL query will replace all occurences of Ã§ in the column 'name' of the 'customers' table with ç

UPDATE customers SET name = replace(name,'ç','ç');

I found it useful especially after copying databases. Copying databases with different collations tend to screw up the characters (especially when working with French accents). So applying this query will fix things up.

This is also useful in many other situations where we will want to replace a character in the database. I leave it to you to think it out.

Update mySQL table with values from another table

Suppose we have two tables myCustomers and allCustomers. We can update the value of table myCustomers based on the value that is common to both tables.

Example

UPDATE myCustomers , allCustomers

SET myCustomers.name = allCustomers.name

WHERE myCustomers.email = allCustomers.email;

What we did here is update all the names of table 'myCustomers' with the names of table 'allCustomers' where their email matches.

Table 'myCustomers' can be a smaller table that stores a small amount of info and table 'allCustomers' can be a reference master table.

French unreadable characters

To fix the problem of unreadable french characters when pulling info from a MySQL database, do this:

$query = mysql_query("SET NAMES UTF8",$conn) or die(mysql_error());

That is, we change the collation of the names column to utf8.

Display number of items in category / subcategory

In many cases, one might want to display the number of items found in a category / subcategory. This is specially the case for classifieds websites.

For example,

Vehicles (30)

• Cars  (15)
• Bikes (13)
• Other (2)

This can be done by querying the database. In this case, I am using MySQL.

Let’s assume that we are passing the category id in the URL. So,

1. Get the category id from the URL

$cat = mysql_real_escape_string($_GET['category']);

2. Query the database with that id

$query = 'SELECT COUNT(*) as count FROM vehicles WHERE catId = '$cat'";

3. Build the query

$result = mysql_query($query);

4. Get the resulting row

$row = mysql_fetch_array($result);

5.  Finally, get the count field from the resulting row (this is the data we want)

$num_items_in_category = $row['count'];

Your comments are welcomed.