Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Unanswered: Minor Table Insert help

    i have the following code, it all works how i want it to bar the first time it runs, when i run the program and insert the data the first time, it inserts the data twice, all other times only once or the update.

    $dbh=mysql_connect ("localhost", "twqwwsoy_user", "iiyama") or die ('I cannot connect to the database because: ' . mysql_error());
    mysql_select_db ("twqwwsoy_resources");

    $count="SELECT COUNT(message) FROM Diary";
    $result = mysql_query($count);
    $co = mysql_result($result,$x);

    if ($co == 0) {
    $SQL= "INSERT INTO Diary (username, day_id, message) VALUES('$username', '$day', '$message')";
    $result = @mysql_query ($SQL) or die('query error ' . mysql_error());
    }
    else {

    $count="SELECT COUNT(username) FROM Diary WHERE username = '$username' AND day_id = '$day'";
    $result = mysql_query($count);
    $co1 = mysql_result($result,$x);
    echo "$co1";
    }

    if ($co1 == 1) {
    $SQL= "UPDATE Diary SET message = '$message' WHERE username = '$username' AND day_id= '$day'";
    $result = @mysql_query ($SQL) or die('query error ' . mysql_error());
    }
    else {
    $SQL= "INSERT INTO Diary (username, day_id, message) VALUES('$username', '$day', '$message')";
    $result = @mysql_query ($SQL) or die('query error ' . mysql_error());
    }

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't speak MySQL, but it seems like this: when the table 'diary' is empty, you are running the script for the first time:
    Code:
    co = 0 and co1 = 0
    
    IF co = 0 (yes, it is) THEN
       INSERT INTO diary ...    -> this is executed
    ELSE
       SELECT COUNT ...         -> this is not executed 
    END IF   
    
    IF co1 = 1 (no, it isn't) THEN
       UPDATE diary ...         -> this is not executed
    ELSE
       INSERT INTO diary ...    -> this statement performs second insert
    Running the script second (and every other) time:
    Code:
    IF co = 0 (no, it isn't) THEN
       INSERT INTO diary ...
    ELSE
       SELECT COUNT ...         -> it is executed and co1 = 2
    END IF
    
    IF co1 = 1 (no, it isn't) THEN
       UPDATE diary ...         -> this is not executed
    ELSE
       INSERT INTO diary ...    -> this is executed
    END IF
    If I'm not wrong, this is what happens. But, you didn't say what you wanted to happen ... Anyway, I guess you'll have to adjust logic a little bit.

Posting Permissions

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