small query problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

small query problem

Post by John Cartwright »

I'm trying to create a small links section where I can modify the order of links through a series of MOVE UP and MOVE DOWN links.

for example,

Code: Select all

<?php
	$sql = "SELECT * FROM `links` ORDER BY `position`";
	$result = $db->sql($sql);	
	
	if (mysql_num_rows($result) > 0)
	{
		while ($row = mysql_fetch_assoc($result))
		{
			if ($_SESSION['loggedin'] == 1 && isset($_GET['admin']))
			{
				$template->svar(array('LINKS_ADMIN' => '<a href="?goto=links&admin=1&move=up&position='.$row['position'].'">Move Up</a> <a href="?goto=links&admin=1&move=down&position='.$row['position'].'">Move Down</a>'));
			}
			
			$template->loadfile('links');
		}
	}
?>
now, when one of those links are clicked it will run

Code: Select all

<?php
	if ($_SESSION['loggedin'] == 1 && !empty($_GET['admin']) && !empty($_GET['move']) && !empty($_GET['position']))
	{
		switch ($_GET['move'])
		{
			case 'up' :
				$sql = "UPDATE `links` SET `position` = (`position` + 1) WHERE `position` = '".$_GET['position']."', SET `position` = (`position` - 1) WHERE `position` = '".($_GET['position'] + 1)."'";
			break;
			case 'down' :
				$sql = "UPDATE `links` SET `position` = (`position` - 1) WHERE `position` = '".$_GET['position']."', SET `position` = (`position` + 1) WHERE `position` = '".($_GET['position'] - 1)."'";
			break;
		}

		$db->sql($sql);
	}
?>
Now heres where my problems come in, I'm the farthest thing from a mysql guru and have been unable to create the update query to set 2 different rows with different conditions. Any help would be appreciated there.

My question is, how can I succesfuly accomplish this move position business if one of the rows is deleted by an admin. It will attempt to update a row that does not exist, because the position has been deleted.

my table looks like this

Code: Select all

ID (auto-incremented)
POSITION
TITLE
etc.
THanks in advance :)
Last edited by John Cartwright on Thu Dec 23, 2004 5:48 pm, edited 1 time in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

actually, you only update the rows where the condition is true... so if there is no such row, nothing will be updated :)

each time you delete a row, you should update all the rows under/above the deleted row, fe:

Code: Select all

DELETE FROM table WHERE position=$position;
UPDATE table SET position=position+1 WHERE position <= $position
because you don't want that anything can happen in between these 2 queries (logically they are one query) you should use a transaction (or mysql's row locking)
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

thanks alot tim you have shed tons of light on my situation.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

i don't really understand what you are doing to do :(

with transactions (or faking it with row lock etc) it will appear that the following 2 queries were only 1 query.

fe with innodb (mysql)

Code: Select all

start transaction;
update table set foo=newfoo where bar=bar1;
update table set foo=otherfoo where bar <> bar1;
commit;
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Im afraid I didn't get much out of your most recent post.
What I'm asking is how to run 2 seperate queries at once...

what I have now is

Code: Select all

$sql = "
UPDATE `links` SET `position`=`position`+1 WHERE `position`='".$_GET&#1111;'position']."'; 
UPDATE `links` SET `position`=`position`-1 WHERE `position`='".$_GET&#1111;'position']."+1';
and get the error
use correct syntax near '; UPDATE `links` SET `position`+1 WHERE `po
please excuse my noobishness in mysql..
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

you would need [php_man]mysqli_multi_query[/php_man] for that to be possible...
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

oh ok I understand now, thanks
npeelman
Forum Commoner
Posts: 32
Joined: Tue Jul 27, 2004 5:13 am
Location: Oviedo,FL.

Post by npeelman »

timvw wrote:you would need [php_man]mysqli_multi_query[/php_man] for that to be possible...
Understand that that function is executing one query at a time. It really only lets you 'join' them together into one string. It is a neat function though.

Norm
Post Reply