Mysql / Email / PHP

Discussion in 'Web Design and Development' started by SChaput, Aug 4, 2009.

  1. SChaput macrumors regular

    Joined:
    Jul 2, 2008
    Location:
    United States
    #1
    I am running a script where i use php to get a users email address that they entered in the previous page.

    I then query my database for any mentions of that email address and try to update them to "Removed"
    -----
    Code:
    
    $query  = "SELECT * from hmr";
    $result = mysql_query($query);
       $result=mysql_query($query);
       while (list($id, $posterEmail) = 
        mysql_fetch_row($result)) {
      
      $result = mysql_query("UPDATE hmr SET posterEmail='Removed' WHERE posterEmail= $email ") ;
    
    However, when i run the code, the database doesn't get updated at all, and i get this error.

    Any ideas? Thanks

    Code:
    
    Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home1/ihatemyr/public_html/notifications/remove.php on line 1
    
     
  2. Cerebrus' Maw macrumors 6502

    Joined:
    Mar 9, 2008
    Location:
    Brisbane, Australia
    #2
    2nd and 3rd lines, why do you call mysql_query twice on the same variable? Just interested.....

    Are you sure you are connecting to your database correctly, and that the table name is correct (case sensitive)?
    If you are calling your mysql_db_connect from an include file, that is in your form html file, and your form actions to another php script, that script must also include your databse connection script.
     
  3. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #3
    Yea, it could be a connection issue. You can try adding an if statement to check,

    PHP:
    if (!$result) { echo 'Error occured! 'mysql_error(); }
    else {
     while ...
    }
    And place that right after you do the query call.
     
  4. Joined:
    Jun 30, 2009
    #4
    Let's look at your code.

    Code:
    $query  = "SELECT * from hmr";
    $result = mysql_query($query);
    $result = mysql_query($query);
    while (list($id, $posterEmail) = mysql_fetch_row($result)) {
      
      $result = mysql_query("UPDATE hmr SET posterEmail='Removed' WHERE posterEmail= $email ") ;
    
    You've got the $result variable initialized twice. Also, when you're querying, the syntax is (DATABASE, QUERY), so you have to specify a database connection before the querying statement. So, supposing you've got the connection details in a $dbc variable, this is the code: $result = mysqli_query($dbc, $query);

    Also, I'd suggest installing MAMP and using mysqli instead of mysql.

    The code does not look readable at all. Try writing SELECT * FROM hmr, uppercase-ing the keywords. Not to mention that a less generic name than hmr would do better...

    You've also forgot to close the WHILE loop, as well as the database connection, if there is any.

    Why don't you post the code from <?php to ?> ? It would help us a lot. Replace the database settings.

    Also, I'm a beginner, but correct me if I'm wrong (and based on the error, I might not be): does the mysql_fetch_row exist? I think it would be easier to call mysqli_fetch_array. It does for me, at least...

    @angelwatt an or die("Message") would be easier. For example

    Code:
    mysqli_query($dbc, $query)
    or die("Error performing query");
    
     
  5. italiano40 macrumors 65816

    italiano40

    Joined:
    Oct 7, 2007
    Location:
    NY
    #5
    Code:
    mysql_fetch_row($result, MYSQL_BOTH)
    needs another thing like mysql_both google their are a lot of different things you can add
     
  6. Cerebrus' Maw macrumors 6502

    Joined:
    Mar 9, 2008
    Location:
    Brisbane, Australia
    #6
    Yes, mysql_fetch_row does exists. Where mysql_fetch_array returns both a numerical and associative array, mysql_fetch_row only returns numerical one.

    His problem is not PHP syntaxical, as the SQL queries are running. Else he would be getting "unexpected }, or FOUND unterminated literal etc...

    EDIT:
    Ok, think I got it.

    In your conditional while loop, you call list($var1, $var2) = mysql_fetch_row($result))

    BUT when your IN your while loop, you overwrite the $result variable, with an malformed mysql query. So when the while loop runs again, and calls the fetch_row with the shafter $result variable, its buggered.

    I've also realized your are running two queries (the second one tries to make a field have two values at the same time (which is impossible)

    Try this (Im assuming that your connection to your DB is ok, and that from your form, you have named the input field cust_email. Your DB needs to have AT LEAST 2 fields ( cust_email, email_status)

    Code:
    <?php
    //First up, get the customer email from your form
    $email = $_POST['cust_email'];
    
    //Next, look for this customers email, and update the status field with Removed
    $sel = "update hmr set email_status='Removed' where cust_email='$email' ";
    mysql_query($sel)
    or die ("Could not execute : mysql_error() );
    
    //END
    ?>
    
    An ID field would also be handy for uniqueness, but I'm assuming there can't be duplicate email address in the table.
     
  7. SChaput thread starter macrumors regular

    Joined:
    Jul 2, 2008
    Location:
    United States
    #7
    Thank you all for the quick responses heres some more code that i hope answers some questions, ill also try to make it more readable. Still getting the same error listed above however.
    Code:
        <?php
    		  mysql_connect("*****","*****","*******");
                      mysql_select_db("****");
    
    
                     $email = strtolower($_POST['email']);
    
       $query ="SELECT * FROM hmr WHERE posterEmail = $email";
           $result=mysql_query($query);
               while (list($entrytext,$posterEmail) =mysql_fetch_row($result)) 
               {
    	
    	           echo $posterEmail;
    	    }
    ?>
    

    EDIT:

    Code:
    $query ="SELECT posterEmail FROM hmr WHERE posterEmail = '$email' ORDER by id DESC";
       $result=mysql_query($query);
       while (list($entrytext,$posterEmail) = 
        mysql_fetch_row($result)) {
    	
    	echo $entrytext;
    	}
    	?>
    
    Changing the code to this i am able to get an output, the correct NUMBER of outputs, IE it prints entrytext twice, which is what i want, however. its not printing the entrytext column, its printing posterEmail column.

    .....Any ideas?
     
  8. Cerebrus' Maw macrumors 6502

    Joined:
    Mar 9, 2008
    Location:
    Brisbane, Australia
    #8
    Firstly, the reason your first code above didnt work is because you $email variable wasn't in single quotes: posterEmail='$email' ";

    Second code, in your while loop type
    Code:
    echo "$entrytext $posterEmail<br />";
    
    I think you have your fields back to front, so your $entrytext is recieveing your posterEmail column.
     
  9. Joined:
    Jun 30, 2009
    #9
    If he types that, isn't he going to get $entrytext $posterEmail as long as the loop is going? What I mean is, shouldn't the variable's name be outside the quotation mark? So, shouldn't he use echo $entrytext . $posterEmail . '<br />'; instead?
     
  10. SChaput thread starter macrumors regular

    Joined:
    Jul 2, 2008
    Location:
    United States
    #10
    i agree my columns must be mixed up, out of curiosity how does that happen / how do i fix it? for organizational sake.

    I now have the code here:
    Code:
      $query ="SELECT entrytext FROM hmr WHERE entrytext = $email ORDER by id DESC";
       $result=mysql_query($query);
       while (list($entrytext) = 
        mysql_fetch_row($result)) {
    	
    	echo "$entrytext <br />";
    	}
    	?>
    
    This code is saying (with the column mix up)
    Select the email from hmr where the email column is equal to the email variable. then output the email, however many rows it found above.
    However, im still getting the error ive listed previously.

    I really appreciate the guidance!
     
  11. Cerebrus' Maw macrumors 6502

    Joined:
    Mar 9, 2008
    Location:
    Brisbane, Australia
    #11
    Again, your $email in your query MUST be in single quotes:

    "SELECT entrytext FROM hmr WHERE entrytext = '$email' ORDER by id DESC";


    And shouldnt entrytext not be posterEmail?

    "SELECT entrytext FROM hmr WHERE posterEmail = '$email' ORDER by id DESC";
     
  12. Cerebrus' Maw macrumors 6502

    Joined:
    Mar 9, 2008
    Location:
    Brisbane, Australia
    #12
    As long as the while loop is running, yes this will output each row value. He said he only has two entries, so its pretty trivial.

    The way you echo'd, is the exact same was as I did. PHP detects $ (unless you use \ to escape it) within strings, so you dont have to use . to concactenate every variable.
     
  13. SChaput thread starter macrumors regular

    Joined:
    Jul 2, 2008
    Location:
    United States
    #13
    Alright everyone, it seems that because of all your help its working now.

    Code:
    $removed = "Removed";
    
      $query ="SELECT id, entrytext FROM hmr WHERE posterEmail = '$email' ORDER by id DESC";;
       $result=mysql_query($query);
       while (list($id,$entrytext) = 
        mysql_fetch_row($result)) {
    		
    	 $query1 ="UPDATE hmr SET posterEmail='$removed' WHERE posterEmail='$email'";
    	 
        $result1=mysql_query($query1);
    	}
    	
    	?>
    	
    
    I can't thank you all enough. I really appreciate it!

    Thanks again.
     
  14. Cerebrus' Maw macrumors 6502

    Joined:
    Mar 9, 2008
    Location:
    Brisbane, Australia
    #14

    I'm glad its working how you expect it, but....

    ..Man, you have one awkward way of doing it... ;)

    Good luck in the future. :cool:
     
  15. SChaput thread starter macrumors regular

    Joined:
    Jul 2, 2008
    Location:
    United States
    #15
    Hahaha I'm sure i do. Maybe one day i'll think things out, thoroughly before i embark on them.
    Have a good night.
     

Share This Page