MYSQL - PHP Record Update problem
I have a MYSQL table called 'news' that has the following fields:
With the following code, I was hoping to be able to insert a new record and have the 'order' field automatically increment on any conflicting records and all other ascending records. Basically allowing a reorder of the records during the insertion of a new record.
By this, I mean, if the order already exists in the table, it and all other ascending records would be incremented by one before the new record is inserted.
The records are being inserted into the table but the reorder is not happening, what am I doing wrong?
The first function is addnews and is called from a submit.
id - int(10) - Primary Key
message - varchar(500)
order - in(2)
active - tinyint(1)
With the following code, I was hoping to be able to insert a new record and have the 'order' field automatically increment on any conflicting records and all other ascending records. Basically allowing a reorder of the records during the insertion of a new record.
By this, I mean, if the order already exists in the table, it and all other ascending records would be incremented by one before the new record is inserted.
The records are being inserted into the table but the reorder is not happening, what am I doing wrong?
The first function is addnews and is called from a submit.
<?php
function addnews($message, $order, $active) {
$message = mysql_real_escape_string($message);
$order = mysql_real_escape_string($order);
if (orderexists($order, $active) === true) {
$pass = orderrenum($order, $active);
}
$sql = "INSERT INTO news VALUES ('','$message', '$order', '1')";
$result = mysql_query($sql);
mysql_close();
if (!empty($result)) {
return true;
} else {
return $result;
}
}
function orderexists($order, $active) {
$sql = "SELECT order FROM news WHERE order= " . $order . " AND active = 1";
$result = mysql_query($sql);
$num = mysql_numrows($result);
if ($num > 0) {
return true;
} else {
return false;
}
}
function orderrenum($order, $active) {
$sql = "SELECT id, order FROM news WHERE order >= " . $order . " AND active = 1";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
$sql = "UPDATE news SET order = " . $row['order']+1 . " WHERE id = " . $row['id'];
$result = mysql_query($sql);
}
}
?>
Hi,
Just taking a quick look:
$pass will always be nothing because orderrenum() is not returning anything. (maybe you should return something so $pass will be happy)
Does that help? :)
Chris
Just taking a quick look:
$pass = orderrenum($order, $active);
$pass will always be nothing because orderrenum() is not returning anything. (maybe you should return something so $pass will be happy)
Does that help? :)
Chris
Ah you are right.
What do you see if you try to print $row['order'] before using it in the query? Also make the second $result, $result2 perhaps to avoid overwriting your first $result.
What do you see if you try to print $row['order'] before using it in the query? Also make the second $result, $result2 perhaps to avoid overwriting your first $result.
Try this. it will be more effective and fast
function orderrenum($order, $active) {
$sql = "UPDATE news SET order = order + 1 WHERE order >= '" . $order . "' AND active = 1";
$result = mysql_query($sql);
}
@Ultrus, I am not sure? How do you print or echo from a set of functions being called by submit? I have tried echo, print, etc. Nothing seems to display on the parent page.
@liveaspankai, I agree this code is more efficient but the results are still the same, the order is not incremented.
@liveaspankai, I agree this code is more efficient but the results are still the same, the order is not incremented.
function orderrenum($order, $active) {
$sql = "UPDATE news SET order = order + 1 WHERE order >= '" . $order . "' AND active = 1";
$result = mysql_query($sql);
}
try this here:
if it doesn't work
please echo the query and post it here. it works and i have been using it, so there might be some problem
function orderrenum($order, $active) {
$sql = "UPDATE news SET order = order + 1 WHERE order >= '" . $order . "' AND active = 1 order by order desc";
$result = mysql_query($sql);
}
if it doesn't work
please echo the query and post it here. it works and i have been using it, so there might be some problem
Agreed. If this does not print anything, your $order variable may not be getting assigned and will need to take a step back:
function orderrenum($order, $active) {
print $order;
$sql = "SELECT id, order FROM news WHERE order >= " . $order . " AND active = 1";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
$sql = "UPDATE news SET order = " . $row['order']+1 . " WHERE id = " . $row['id'];
$result = mysql_query($sql);
}
}
I am not getting any print's or echo's from any of the functions. I know the functions are executing since the records are being added into the table. Here is what I have now (notice the print statements in all of the functions), nothing is being displayed.
Calling code from PHP form after field validation: SQLADD.PHP
Function Code: I know this executes since the record is added to the table: NEWS.FUNCTIONS.PHP
sqladd.php
news.functions.php
Calling code from PHP form after field validation: SQLADD.PHP
...
$add = addnews($message, $order, $active);
Function Code: I know this executes since the record is added to the table: NEWS.FUNCTIONS.PHP
function addnews($message, $order, $active) {
print ("addnews: ".$order);
$message = mysql_real_escape_string($message);
$order = mysql_real_escape_string($order);
if (orderexists($order, $active) === true) {
$pass = orderrenum($order, $active);
}
$sql = "INSERT INTO news VALUES ('','$message', '$order', '1')";
$result = mysql_query($sql);
mysql_close();
if (!empty($result)) {
return true;
} else {
return $result;
}
}
function orderexists($order, $active) {
print ("orderexists: ".$order);
$sql = "SELECT order FROM news WHERE order= " . $order . " AND active = 1";
$result = mysql_query($sql);
$num = mysql_numrows($result);
if ($num > 0) {
return true;
} else {
return false;
}
}
function orderrenum($order, $active) {
print ("orderrenume: ".$order);
$sql = "UPDATE news SET order = order + 1 WHERE order >= '" . $order . "' AND active = 1 order by order desc";
$result = mysql_query($sql);
}
sqladd.php
news.functions.php
you have a redirect at line 102 on sqladd.php
that is causing the prints to be not shown. it will display the print and immediately redirect
i would request you to print $sql also so that you can validate the query. you can even try that query in phpmyadmin and see if anything changes
that is causing the prints to be not shown. it will display the print and immediately redirect
i would request you to print $sql also so that you can validate the query. you can even try that query in phpmyadmin and see if anything changes
Found it! Had a brain fart and forgot debugging 101. I removed the redirect and found that I had the order field quote delimited on the update statement.
Was:
Should be:
Was:
$sql = "UPDATE news SET order = order + 1 WHERE order >='" . $order . "' AND active = 1 order by order desc";
Should be:
$sql = "UPDATE news SET order = order + 1 WHERE order >=" . $order . " AND active = 1 order by order desc";
Premium Content
You need an Expert Office subscription to comment.Start Free Trial