...

View Full Version : INSERT query acting kinda fun-ny



chump2877
06-29-2005, 02:26 AM
This seems strange to me: I've got the following db query:


$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.

SeeIT Solutions
06-29-2005, 03:09 AM
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


$queryZY = "INSERT INTO reference_no (ref_num, month) VALUES ('','" . $month . "')";

Nightfire
06-29-2005, 03:47 AM
If it's auto incrementing, surely he wouldn't need to mention it in the query?



$queryZY = "INSERT INTO reference_no (month) VALUES ('" . $month . "')";


Not used mysql for a while, so can't really remember

chump2877
06-29-2005, 07:27 AM
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....

chump2877
06-29-2005, 11:45 AM
OK, nevermind the odd MySQL error code...I fixed that....

This 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:


// 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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum