INSERT query acting kinda fun-ny

A

Anonymous

Guest
This code:

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.");
        }

modifies the following example MySQL database table:


------ref_num ------month

---------- 1 ----------- 6

---------- 2 ----------- 6
Click to expand...

ref_num is an auto-incrementing column that counts up from 1 in increments of 1......The code 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 (instead of inserting a new row)....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:

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;
 
what happends to the auto_increment column? does the value go like 1,2,3 or will it become 1,3 ? if it becomes 1, 3 -- the second row is being deleted and therefore your script somehow gets into the delete part..

the thing is that you dont have any UPDATE statements and if you delete a row and insert a new one after that: it will not have the same value in the auto_increment table as the former one: it will be +1
 
what happends to the auto_increment column? does the value go like 1,2,3 or will it become 1,3 ?
Click to expand...


First Time: 1 ---- 6 ---- adds new row

2nd Time: 2 ---- 6 ---- adds new row

3rd Time: 2 ---- 6 ---- replaces last row? Or doesn't add row at all?

4th Time: 2 ---- 6 ---- replaces last row? Or doesn't add row at all?

5th Time: 2 ---- 6 ---- replaces last row? Or doesn't add row at all?

6th Time: 2 ---- 6 ---- replaces last row? Or doesn't add row at all?

And on and on like this.....


The auto-incrementing column never gets past '2'......and the table itself never gets larger than 2 rows deep...
 
Alexei Kubarev said:
the thing is that you dont have any UPDATE statements and if you delete a row and insert a new one after that: it will not have the same value in the auto_increment table as the former one: it will be +1
Click to expand...

I understand that this is how it works....but I'm never actually querying the database and telling it to delete any single row.....
 
Alexei Kubarev said:
from that it doesnt do anything :) so the query runs only 2 times
Click to expand...

I can't begin to imagine why, though..... :(

do you see why?

Thanks...
 
seems to me that it will run only twice in your code :S
maximum... else it will run only once..

what are yuo trying to do by creating, dropping tables and so on? You know that you can simply use TRUNCATE TABLE name and the table will be empty and auto_increment reset..
 
Yeah, it appears to run twice and then stop....

Anyway, thanks for the tip about TRUNCATE TABLE...
 
You must log in or register to reply here.
Back
Top