asked on

MYSQL - PHP Record Update problem

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

Ultrus

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

tnowacoski

ASKER

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

Ultrus

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.

liveaspankaj

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

tnowacoski

ASKER

@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

liveaspankaj

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

Ultrus

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

tnowacoski

ASKER

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

ASKER CERTIFIED SOLUTION

liveaspankaj

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

tnowacoski

ASKER

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

liveaspankaj

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

tnowacoski

ASKER

it is an int not string in the table

liveaspankaj