Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Mar 2009
    Posts
    55
    Thanks
    9
    Thanked 0 Times in 0 Posts

    mysql_query in a mysql_query

    Hey everyone,

    I am curious, would this code work? So far it isn't working for me but would appreciate some input on whether there would be a better way to handle it.

    PHP Code:
        $sql="SELECT id, post, username FROM pickles WHERE date < DATE_SUB(NOW(), INTERVAL 2 MONTH) && ageNote = '0' ";
        
    $r=mysql_query($sql);
        
        while(
    $row=mysql_fetch_assoc($r)){ 
            
    $id=$row['id'];
            
    $post=$row['post'];
            
    $username=$row['username'];    
            
            
    $sql2="SELECT email FROM potatoes WHERE username='$username' ";
                    
    $result2=mysql_query($sql2);
            while(
    $row2=mysql_fetch_assoc($result2)){ $email=$row['email']; }
            
            
    $sql3="UPDATE pickles SET ageNote='1' WHERE id='$id' ";
            
    mysql_query($sql3);
        } 
    Thanks!

    Serihon
    Last edited by Serihon; 02-24-2012 at 10:23 PM. Reason: Solved

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Yeah, you don't need to select a thing. Since you are not doing anything with any of this data, simply execute an update:
    Code:
    UPDATE pickles SET ageNote=1 WHERE date < DATE_SUB(NOW(), INTERVAL 2 MONTH) AND ageNote = 0
    No need to select at all.

    As for "not working", you need to be more specific. The nested loop would work just fine, but if you don't do anything with $email you'd never know. Although its a waste to query within a loop, for that you should use an inner join on your queries to link two tables together. The only thing that may not work is that if these number (which I assumed they are) are actual integers, mysql can reject them when provided as strings. By default I don't believe that mysql operates strict.

  • Users who have thanked Fou-Lu for this post:

    Serihon (02-24-2012)

  • #3
    New Coder
    Join Date
    Mar 2009
    Posts
    55
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Well I am actually using the code. I use it to send an email.

    ageNote is actually a boolean in the database so not sure if it needs to be in parenthesis?

    I was using the first query to find entries that are older than two months. The second query is to pull the users email address and email them to warn them about this. The third and final query is to update the current entry's ageNote to 1. Should I pull the third query out of the first query and just run it as a separate query after the first?

    Removing the parenthesis around the integers actually fixed the problem and it is working now. Thanks Fou-Lu!
    PHP Code:
        $sql="SELECT id, post, username FROM pickles WHERE date < DATE_SUB(NOW(), INTERVAL 2 MONTH) && ageNote = 0 ";
        
    $r=mysql_query($sql);
        
        while(
    $row=mysql_fetch_assoc($r)){ 
            
    $id=$row['id'];
            
    $post=$row['post'];
            
    $username=$row['username'];    
            
            
    $sql2="SELECT email FROM potatoes WHERE username='$username' ";
            
    $result2=mysql_query($sql2);
            while(
    $row=mysql_fetch_assoc($result2)){ $email=$row['email']; }
        
            
    //Email info to user
            
    $from    'noreply@mywebsite.org';
            
    $to      $email;
            
    $subject 'Post Pending Deletion Warning: '.$post;
            
    $message "Dear $username, \n Your post $post has reached the age of two months old. Once the post reaches the age of three months old it will be deleted from our system forever. If you like you can extend the life of your post by following one of the links listed below. If you do not want to keep the post active then please disregard this message. This will be your only warning for the deletion of this post. \n ";
            
            
    mail($to$subject$message$from);
            
            
    $sql3="UPDATE pickles SET ageNote=1 WHERE id='$id' ";
            
    mysql_query($sql3);
        } 
    Last edited by Serihon; 02-24-2012 at 10:26 PM. Reason: Solved

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Nope, there are no actual booleans in mysql, they are tinyint values, so you shouldn't quote them since they are numbers.
    The context is much better. Query with a join on your select and capture the results of the known id's into an array. This can be used within an IN clause:
    PHP Code:
    $sQry 'SELECT p.id, p.post, po.username, po.email
            FROM pickles p
            INNER JOIN potatoes po ON po.username = p.username
            WHERE p.date < DATE_SUB(NOW(), INTERVAL 2 MONTH) AND p.ageNote = 0'
    ;
    $qry mysql_query($sQry) or die(mysql_error());
    $aIDs = array();
    while (
    $row mysql_fetch_assoc($qry))
    {
        
    $aIDs[] = $row['id'];
        
    // send an email
    }
    $sUpdate 'UPDATE pickles SET ageNote = 1 WHERE id IN (' implode(', '$aIDs) . ')';
    mysql_query($sUpdate) or die(mysql_error()); 

  • #5
    New Coder
    Join Date
    Mar 2009
    Posts
    55
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I will try that.

    I have a separate but different question. I want to fill a field in my table with a date that is increased by a month. I think I am close to figuring it out but it isn't working. Do you think this would work? The date in the TIMESTAMPADD would be a field of the entry of the table that is being updated.

    PHP Code:

    include('dbCon.php');

    //Function to sanitize values received from the form. Prevents SQL injection
    function clean($str) {
        
    $str = @trim($str);
        if(
    get_magic_quotes_gpc()) {
            
    $str stripslashes($str);
        }
        return 
    mysql_real_escape_string($str);
    }

    $id=clean($_GET['id']);
    $term=clean($_GET['term']);

    $sql="UPDATE posts SET ageNote=0 keep=TIMESTAMPADD(MONTH, '$term', date) WHERE id='$id' ";
    mysql_query($sql); 

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    In sql you can use the + overload to use datefield + INTERVAL 1 MONTH for example, or you can use the DATEADD. Timestampadd should also work. Don't forget to add the comma between the fields you are updating.

  • Users who have thanked Fou-Lu for this post:

    Serihon (02-24-2012)

  • #7
    New Coder
    Join Date
    Mar 2009
    Posts
    55
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    In sql you can use the + overload to use datefield + INTERVAL 1 MONTH for example, or you can use the DATEADD. Timestampadd should also work. Don't forget to add the comma between the fields you are updating.
    That worked beautifully! Thanks so much for your expert help!

  • #8
    New Coder
    Join Date
    Mar 2009
    Posts
    55
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Is this a valid statement?

    PHP Code:
    SELECT p.id p.post p.username u.email 
    FROM comments c 
    INNER JOIN posts p ON c
    .postId p.id 
    INNER JOIN users u ON c
    .username u.username 
    WHERE c
    .dateDATE_SUB(NOW(), INTERVAL 1 DAY
    ORDER BY c.postId ASC 
    I get the following error from it when running it in a SQL Query window.

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.post p.username u.email FROM comments c INNER JOIN posts p ON c.postId = p.id I' at line 1

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    No, you need to add commas between which fields you are selecting.

  • Users who have thanked Fou-Lu for this post:

    Serihon (02-27-2012)

  • #10
    New Coder
    Join Date
    Mar 2009
    Posts
    55
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    No, you need to add commas between which fields you are selecting.
    Thanks. *sigh* Forgot my coffee this morning it seems...


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •