We help IT Professionals succeed at work.

MYSQL - PHP Record Update problem

tnowacoski
tnowacoski asked
on
I have a MYSQL table called 'news' that has the following fields:

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);
	}
}
?>

Open in new window

Comment
Watch Question

Commented:
Hi,
Just taking a quick look:

$pass = orderrenum($order, $active);

Open in new window


$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

Author

Commented:
But I am not evaluating $pass for anything so it should not matter if it is empty.

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

Open in new window

Author

Commented:
@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.

function orderrenum($order, $active) {
	$sql = "UPDATE news SET order = order + 1 WHERE order >= '" . $order . "' AND active = 1";
	$result = mysql_query($sql);
}

Open in new window


try  this here:

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

Open in new window



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

Commented:
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);
        }
}

Open in new window

Author

Commented:
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
...
$add = addnews($message, $order, $active);

Open in new window


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

Open in new window



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

Author

Commented:
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:
$sql = "UPDATE news SET order = order + 1 WHERE order >='" . $order . "' AND active = 1 order by order desc";

Open in new window


Should be:
$sql = "UPDATE news SET order = order + 1 WHERE order >=" . $order . " AND active = 1 order by order desc";

Open in new window

i dont see why that sould be a problem... unless $order is not a value
any way congrats

Author

Commented:
it is an int not string in the table
ok

Explore More ContentExplore courses, solutions, and other research materials related to this topic.