Q:

Unable to select Database Foo using Ec2 and RDS

I have set up an RDS Database Instance with a security group where I use my EC2 Elastic IP as my CIDR/IP.  I have also associated the security group with my EC2. 

My security group on the EC2 Instance looks like this.  I associated one of the 3306 ports with my Elastic IP.  

I have created a database and a table in phpMyAdmin and am trying to test it out by printing out all the values by using the code below:
<? php

    // set database server access variables:
    $host = "XXXXXXX.XXXXXXXXXX.eu-west-1.rds.amazonaws.com";
    $user = "XXXXXXX";
    $pass = "XXXXXXXX";
    $db = "XXXXXXX";    
    $con = mysql_connect($host, $user, $pass, $db);

// Check connection
if (mysql_connect_error())
  {
  echo "Failed to connect to MySQL: " .  mysql_connect_error();
  }else { echo "You have connected successfully!"; 
}

 $result = mysql_query($con, "SELECT * FROM `XXXXX` LIMIT 0, 30 ");

echo "<p>starting again..</p>";

while($row = mysql_fetch_assoc($result)){
    //iterate over all the fields
    foreach($row as $key => $val){
        //generate output
        echo $key . ": " .  $val . " <BR />";
    }
}
    mysql_close($connection);

? >

The error that I am getting is Unknown database 'XXXX'.  Any ideas? 
EDIT 1
I have just changed all the mysqli statements to mysql.  But the connection is still not successful i.e. the database cannot be found.   
EDIT 2
Here is a screenshot of my mysql privileges. 

A:

Unknown database 'XXXX' means the database could not be accessed.  This could happen because :

Server is connecting successfully, but database does not exist.  Try connecting without giving the database name as a parameter.  Then run query show databases;.  It will show all the databases there. 
You are connecting to wrong server (you added database somewhere else database exists on other server).  Checking the databases present can help you identify which server is it. 

It seems that you are able to connect to the Amazon RDS server.  But database is not there.  Similar error would show if we try to connect to fresh installation of mysql.  It has zero databases (other than information_schema and mysql). 

A:

First off, you should not (never) use the mysql_* extension and its function anymore.  They are outdated, deprecated and will be removed from php in a near version.  Use mysqli_, or PDO.  See the huge red warning at the top of the mysql_connect page in the php docs for more info. 

Check that you connect to the right server

Connect to your webserver in ssh, and use the local mysql client to connect to the database server as root (you will be asked the password for the root user):
$ mysql -uroot -p -hXXXXXXX.XXXXXXXXXX.eu-west-1.rds.amazonaws.com
Enter password:
Welcome to the MySQL monitor.   Commands end with ; or \g. 
Your MySQL connection id is 100530
Server version: 5.1.66-0+squeeze1 (Debian)

[...]

Type 'help;' or '\h' for help.  Type '\c' to clear the current input statement. 

mysql>

If this work, you should end up at a mysql prompt.  If this didn't work, your issue lies in your network configuration, your webserver cannot connect to the mysql server (check ports openings). 

Make sure the database in question exists on the server
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| [... db names ...] |
+--------------------+
X rows in set (0.00 sec)

mysql>

If you cannot see your database name in the list, this means the server your see in phpmyadmin is not the correct one; check your phpmyadmin settings. 

Check if you can connect with this user using the cli mysql client.  Note that I do not specify a database on the connection line. 
// close the root connection
mysql> quit
Bye
// connect using your specific user, replace USERNAME with your db user (and type its password when asked)
$ mysql -uUSERNAME -p
Enter password:

[...]

mysql> 

If this fails and you do not get to a prompt, this can be either because the permission does not exists, or because this user cannot connect from the host you specified.  Look at the error message mysql will display; something like! 
ERROR 1045 (28000): Access denied for user 'USERNAME'@'HOST' (using password: NO)

What is in the HOST is important and you have to make sure it is allowed in your privileges.  If everything seems ok and it is still not working, try editing the privileges for that user and set the HOST to '%' (means anything is allowed).  If it works with that, it means the HOST is wrongly configured in mysql's privileges. 

Check if the user can see the database in question.  When you connect with a specific user, "show databases" will only list databases this user has privileges to see. 
mysql > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| [... db names ...] |
+--------------------+
X rows in set (0.00 sec)

mysql>

If you cannot see your database in the list, it means the privileges for the user are wrong / not set for this database. 
If every steps here works fine, it probably means you have an error in your php code, double check the host, password, user name, ...

If you really can't figure out what parts of the privileges are wrong, try creating a dummy user will all permissions from any host:
$ mysql -uroot -p -hXXXXXXX.XXXXXXXXXX.eu-west-1.rds.amazonaws.com
Enter password:
mysql > GRANT ALL ON yourdbname.*  to 'testuser'@'%' IDENTIFIED BY 'dummypassword';
mysql > quit
Bye
// note that I give the password in the prompt directly, to make sure this isn't an input error just in case
$ mysql -utestuser -pdummypassword
mysql >

Once you made this user and can connect to it, try to connect to it using your php code.  Once this work, delete the user (using DROP USER 'testuser'), create it again with a more limited HOST privilege and try again, until you replicated the permissions you want.  
Remember to delete that user and you're done.