Mysql – Query distinct then select option then choose and query choosed as html result on page

MySQLPHP

Structure of database "diary":

id------user_name-----day-----month------post
1--------erik-------- 3--------dec-------something
1--------erik---------3--------dec-------12345
1--------erik---------2--------dec-------kkkkk
1--------erik---------6--------jan-------ok
2--------john---------1---------dec------testing

Now I need to get unique month for user $login_session and show him select option. I managed to get distinct select option query from the database.
So here it is, and it's working:

$sql = "SELECT DISTINCT  month FROM `diary` 
    where user_name = '$login_session' order by month"; 
$result = mysql_query($sql);

while($row = mysql_fetch_assoc($result)){
    echo '<option value="'.$row['month'].'">'.$row['month'].'</option>';
}

When user erik login he got:

dec
jan

…as option. The problem is when user login and see top option value on select to list his data. Example:

Let's say he choose dec, I need to get his output in html form:

 id----day ------- month ------- posts ------------
  1----3 ------- dec -------  something  ------- 
  1----3 ------- dec -------  12345 ------- 
  1----2 ------- dec -------  kkkkk  -------

Basically, I am trying to list logged user all posts by month select.

Best Answer

I manage to solve the problem, here is the code for others who need help.

$sid=$_GET['month'];
$sql = "SELECT * FROM diary where user_name = '$login_session' 
    and month= '$sid' ORDER BY day";
$result = mysql_query($sql);
while ($rs=mysql_fetch_object($result)){

and then just wrap in tables:

echo $rs->day;
echo $rs->post;
}

Related Solutions

Mysql – Slow performance of MySQL Join Query

Please provide SHOW CREATE TABLE; the explain is useless without it.

OR is a performance killer in many contexts.

( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR ( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ...

Turn that into

JOIN ( SELECT id FROM p WHERE 
( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR 
( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ... ) x ON x.id = foo.id

Also needed (on p):

INDEX(pricelist, iln, id)

(With the CREATEs, I could be more specific.)

The idea behind this "trick" is to move the costly work of the OR into a subquery that returns the necessary ids. Plus the INDEX makes it so that it can do all that work in the INDEX.

Mysql – Connecting to MYSQL on an EC2 instance

Since Zapier is coming in from outside your instance, a possible cause of your problem is the security group for your instance. Most ports on EC2 instances (except the SSH port) are normally blocked by default unless you explicitly open them.

So you would need to open the mysql port 3306 on your instance up to the ip address Zapier is coming in from. (preferably you would not open it up to everyone in the world.)

See Using Amazon RDS/EC2 on the Zapier site, starting from the section where it says Can't Connect to My Database. They continue on to provide specific instructions for the security group settings.

You will also probably need to grant permission on the database using the mysql GRANT command to the user coming in from the Zapier ip address.

Something like this should work :

GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'54.86.9.50'
IDENTIFIED BY 'newpassword';

Or you can allow that user to come in from any ip adress like this:

GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'%'
IDENTIFIED BY 'newpassword';

(Incoming requests would still be limited to Zapier ip addresses due to the security group settings.)

Related Question