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 5 of 5
  1. #1
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,796
    Thanks
    19
    Thanked 156 Times in 147 Posts

    INSERT query acting kinda fun-ny

    This seems strange to me: I've got the following db query:

    PHP Code:
            $queryZY "INSERT INTO reference_no (ref_num, month) VALUES ('" $reference_no "', '" $month "')";
            
    $resultZY mysql_query($queryZY);
            if (
    mysql_errno())
            {
                die(
    "<br>" mysql_errno() . ": " mysql_error() . "<br>");
            }
            if (
    mysql_affected_rows() != 1)
            {
                die(
    "<br>Failed to add user information.");
            } 
    It does appear to insert the row (without errors), but instead of inserting a new row, it replaces the last row in the table....

    I thought INSERT created a new row? What gives? Help please....

    Thanks.
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!

  • #2
    Regular Coder
    Join Date
    May 2005
    Posts
    563
    Thanks
    0
    Thanked 3 Times in 3 Posts
    your are telling it what reference number to insert, unless this reference number is the next in the table it will overwrite.

    Make ref_num an auto incrementing column and then use this query

    PHP Code:
    $queryZY "INSERT INTO reference_no (ref_num, month) VALUES ('','" $month "')"

  • #3
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,265
    Thanks
    6
    Thanked 48 Times in 48 Posts
    If it's auto incrementing, surely he wouldn't need to mention it in the query?

    PHP Code:
     $queryZY "INSERT INTO reference_no (month) VALUES ('" $month "')"
    Not used mysql for a while, so can't really remember

  • #4
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,796
    Thanks
    19
    Thanked 156 Times in 147 Posts
    Well I changed ref_num to an auto incrementing column, and i tried the same query both of your ways, and now I believe the same query is giving me a MySQL error, albeit a strange one:

    0:
    That's it...I'll have to look up my MySQL error codes, but that seems like a funny looking error message to me....
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!

  • #5
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,796
    Thanks
    19
    Thanked 156 Times in 147 Posts
    OK, nevermind the odd MySQL error code...I fixed that....

    This code:
    PHP Code:
            $queryZY "INSERT INTO reference_no (month) VALUES ('" $month "')";
            
    $resultZY mysql_query($queryZY);
            if (
    mysql_errno())
            {
                die(
    "<br>" mysql_errno() . ": " mysql_error() . "<br>");
            }
            if (
    mysql_affected_rows() != 1)
            {
                die(
    "<br>Failed to add user information.");
            } 
    works fine until it is accessed on the THIRD time around...it seems like the auto increment is ignored, and the THIRD insert query replaces the previous insert query in the table....This happens the fourth time around, the fifth time, and on and on....So the table never grows beyond 2 rows.....

    This code is part of a group of files that run a shopping cart....The code surrounding and including this code is meant to generate a timestamp and a unique reference number for an e-mail containing order information that will be sent to the vendor....The reference number will start at one at the beginning of every month and progress in increments of '1' with each successive order....The next month, the reference # will reset back to one and start the process over....

    Anyway, this should explain how I'm trying to use this code...if there are more than 2 orders in a month, the table containing the reference numbers needs to have more than 2 rows, because ultimately I'm counting the rows in the table to obtain the reference number for each successive order....

    Here is the surrounding code, for the above snippet of code, that generates the reference number....Maybe you can identify the problem:

    PHP Code:
    // Create reference number for subject line of e-mail.


    $queryX "SELECT month FROM reference_no WHERE ref_num=1";
    $resultX mysql_query($queryX);

    if (!
    mysql_error())
    {
        while (
    $rowX mysql_fetch_assoc($resultX))
        {
            
    $month $rowX['month'];
        }


        
    // If today's month is different than the last entries month.

        
    if (date("n") != $month);
        {
            
    $query2 "DROP TABLE reference_no";
            
    $result2 mysql_query($query2);
            if (
    mysql_errno())
            {
                die(
    "<br>" mysql_errno() . ": " mysql_error() . "<br>");
            }

            
    $query2 "CREATE TABLE reference_no (month VARCHAR(20))";
            
    $result2 mysql_query($query2);
            if (
    mysql_errno())
            {
                die(
    "<br>" mysql_errno() . ": " mysql_error() . "<br>");
            }

            
    $queryQ "ALTER TABLE reference_no ADD ref_num INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST";
            
    $resultQ mysql_query($queryQ);
            if (
    mysql_errno())
            {
                die(
    "<br>" mysql_errno() . ": " mysql_error() . "<br>");
            }


            
    $month date("n");


            
    $queryZ "INSERT INTO reference_no (month) VALUES ('" $month "')";
            
    $resultZ mysql_query($queryZ);
            if (
    mysql_errno())
            {
                die(
    "<br>" mysql_errno() . ": " mysql_error() . "<br>");
            }
            if (
    mysql_affected_rows() != 1)
            {
                die(
    "<br>Failed to add user information.");
            }
        }


        
    // If today's month is the same as last entries month.

        
    if (date("n") === $month)
        {
            
    $queryZY "INSERT INTO reference_no (month) VALUES ('" $month "')";
            
    $resultZY mysql_query($queryZY);
            if (
    mysql_errno())
            {
                die(
    "<br>" mysql_errno() . ": " mysql_error() . "<br>");
            }
            if (
    mysql_affected_rows() != 1)
            {
                die(
    "<br>Failed to add user information.");
            }
        }

    }
    else
    {
        
    $query2 "CREATE TABLE reference_no (month VARCHAR(20))";
        
    $result2 mysql_query($query2);
        if (
    mysql_errno())
        {
            die(
    "<br>" mysql_errno() . ": " mysql_error() . "<br>");
        }

        
    $queryK "ALTER TABLE reference_no ADD ref_num INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST";
        
    $resultK mysql_query($queryK);
        if (
    mysql_errno())
        {
            die(
    "<br>" mysql_errno() . ": " mysql_error() . "<br>");
        }


        
    $month date("n");


        
    $queryZ "INSERT INTO reference_no (month) VALUES ('" $month "')";
        
    $resultZ mysql_query($queryZ);
        if (
    mysql_errno())
        {
            die(
    "<br>" mysql_errno() . ": " mysql_error() . "<br>");
        }
        if (
    mysql_affected_rows() != 1)
        {
            die(
    "<br>Failed to add user information.");
        }
    }



    $queryH "SELECT * FROM reference_no";
    $resultH mysql_query($queryH);
    if (
    mysql_errno())
    {
        die(
    "<br>" mysql_errno() . ": " mysql_error() . "<br>");
    }

    $reference_no mysql_num_rows($resultH);



    $subject .= " - " date("mdy") . "-" $reference_no
    Thanks.
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!


  •  

    Posting Permissions

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