Hi,
I am trying to make a function in my db driven site where I can move articles up & down on a page. Here’s my article table…
sort_id | page_id | title | text
This way I can auto increment the sort_id (not a primary key) so every new article gets a new number. I did look at ways that would +1 or -1 from the sort_id, but since the prev or next article may be any number higher or lower, the only way I can be sure they will swap place is if they physically swap sort_id.
So I have a foreach loop going something like this…
So now if I click move up, how can I capture the value of the previous iteration so that I can add it to the query string to make the swap happen. Or am I just barking up the wrong tree here?
Hmm ok I haven’t used a for loop to output content before (still learning).
The articles sort_id column will be sequential yes, but numbers will almost certainly jump due to articles being deleted. Each new entry will be incremented, but if I delete article #1 & #3 for example, my artilces table will be 0,2,4,5,6,7…
However, when I output them, they will be displayed using WHERE page_id =(whichever page). They will still display in numerical order, but the numbers could by now be completely random which is why I need to capture the previous & next iteration’s value so that I can tell mysql to update the sort_id for both articles.
Thanks a lot for taking a look
Hope I’m making sense, here’s what I have so far. I tried to output using prev & next, but it’s not working. I don’t really understand these functions properly I have to admit. I just looked them up on php.net & tried this out.
// Turn a result into an assoc array
function result_to_assoc($result)
{
$result_array = array();
for ($i=0; $row = mysql_fetch_assoc($result) ; $i++)
{
$result_array[$i] = $row;
}
return $result_array;
}
// output the articles
function show_articles()
{
$connection = db_connect();
$query = ("select * from article_test");
$result = mysql_query($query);
$number_of_posts = mysql_num_rows($result);
if ($number_of_posts == 0)
{
return false;
}
$row = result_to_assoc($result);
return $row;
}
$articles = show_articles();
//print_r($articles);
foreach($articles as $article){
$prev = prev($article);
$next = next($article);
echo '<p>'.$article['title'].' <a href=_test-show-articles.php?sort_id='.$article['sort_id'].'&direction=up>Move up</a> |<a href=_test-show-articles.php?sort_id='.$article['sort_id'].'&direction=down>Move down</a></p><p>'.$article['text'].'</p><h1>'.$prev['sort_id'].' - '.$next['sort_id'].'</h1><hr />';
}
//'&prev='.prev($article['sort_id']).
//'&next='.next($article['sort_id']).
because $article[$key-1] doesnt make any sense. wonshikee meant to just use $articles[$key-1] and $articles[$key+1].And then i assume you want the title element, so $articles[$key-1][‘title’] …
Oops yeah that “s” is required lol, was typing that during load screen while playing League of Legends =D
Just to go a little deeper, the code will throw a notice because $key-1 and $key+1 will go out of bounds, not a real issue for PHP but it is a run-time error for strictly typed languages, just something to keep in mind.
Ah you mean $prev on the 1st move up and $next on the last move down? I’m thinking I can just test to see if they have a value and if not, no move up/down link.
Thanks a million for your help here, happily coding away at my pages now
Actually it’s not throwing a notice, its just that the query string on the first iteration has no value for prev and the last iteration has no value for next. I’ll still try to clean it up tho, but the important thing is I can successfully swap them now.
If you got the WHOLE table back for the DB you could simply set aside a variables for prev/next and retrieve TWO records at a time… something like
$prev=$next=false;
while ($thisRow = mysql_fetch_assoc($result)){
$next=mysql_fetch_assoc($result);
if ($prev){ //Call your output foo: 'prev' link code //}
//Call your output 'current link' link code goes here//
if ($next){ //Call your output 'next' link code goes here //}
$prev=thisRow;
}
if ($next){
//Call your output foo: 'prev' link code, but this time send $thisRow as an argument //
//Call your output 'current link', but this time send $next;
}
voila… this will scale anyway way you want it and you can arrange the order you get your results via your mySQL statement!
Of course, I don’t understand why one would retrieve a whole table for a specific article. You could use a couple of mySQL queries ( possibly one , but I didn’t want to start explain JOINS etc, when I myself rarely use them) .
for example here am sorting by alphabetically ( around a chosen article ID=19 )
SELECT * FROM `article_test` WHERE `title`<=(SELECT `title` FROM `article_test` WHERE `articleID`=19) ORDER BY `title` DESC limit 2
This gives your previous and current article…
SELECT * FROM `article_test` WHERE `title`>(SELECT `title` FROM `article_test` WHERE `articleID`=19) ORDER BY `title` ASC limit 2
and this gives you your next. The beauty of this method is it conserves resources, and its scalable. Wanna confuse users by presenting links to 5 previous articles and 10 following articles … change the limit on the corresponding mySQL query. You can order change the ordering method by changing the SORT BY and the column which is selected inside the parenthesis (these two must be the same) .
It also has the added advantage of giving you convenient falses. That is the LAST entry will return ‘false’ on the NEXT query, and you will only get ONE entry ( the article itself) if your target article is the first article. These could be used to skip echoing back/forth button when they dont apply.
Anyway, I hope that helps.
Absolutely it helps! That’s my homework for this week then, I’m going to try all that out
Just having some trouble with my query now tho. I thought I had it sussed, but no. I want to try updating both rows in one query. I found this syntax, but it doesn’t play ball when I use variables as clauses & values. I tried using quotes around them but to no avail. I wonder if this just doesn’t whork when the case is the same as the row I am updating?
// Run the query & update sql according to the url.
$direction = $_GET['direction'];
$id_active = $_GET['sort_id'];
// See if we are moving up or down
switch ($_GET['direction']){
case 'down':
$id_swap = $_GET['next'];
break;
case 'up':
$id_swap = $_GET['prev'];
break;
}
// Now let's swap!
function swap_articles(){
db_connect();
$query = "UPDATE article_test
SET sort_id = CASE sort_id
WHEN $id_active THEN $id_swap
WHEN $id_swap THEN $id_active
WHERE sort_id IN ($id_swap,$id_active)";
$result = mysql_query($query);
if (!$result)
{
return false;
}
else
{
return true;
}
}
swap_articles();
?>
If someone could tell me if this syntax is wrong, I’d greatly appreciate it.
Been looking around for other solutions, but they usually come back to this method. I can’t seem to make this do anything.
Am I understanding this correctly?
$id_active = 1;
$id_swap = 2;
UPDATE article_test
// the column to update is 'sort_id' and since it is also the column I need to test, then I use the same value for CASE right?
SET sort_id = CASE sort_id
WHEN $id_active THEN $id_swap // sort_id 1 to become sort_id 2
WHEN $id_swap THEN $id_active // sort_id 2 to become sort_id 1
WHERE sort_id IN ($id_swap,$id_active) // test only rows with sort_id 1 & sort_id 2
UPDATE article_test
SET sort_id =
CASE sort_id
WHEN $id_active THEN $id_swap
WHEN $id_swap THEN $id_active
ELSE sort_id
END
WHERE sort_id IN ($id_swap, $id_active)
Someone assured me that should work, but it doesn’t, so I wonder if my function is flawed somehow, but I can’t for the life of me figure out how
This is the script.
<?php
// Run the query & update sql according to the url.
$direction = $_GET['direction'];
$id_active = $_GET['sort_id'];
// See if we are moving up or down
switch ($_GET['direction']){
case 'down':
$id_swap = $_GET['next'];
break;
case 'up':
$id_swap = $_GET['prev'];
break;
}
// Now let's swap!
function swap_articles(){
db_connect();
if (!db_connect()){
echo '<P>Connection failed</P>';
}
else { echo '<p>Connection successful!</p>';
}
$query = "UPDATE article_test
SET sort_id =
CASE sort_id
WHEN $id_active THEN $id_swap
WHEN $id_swap THEN $id_active
ELSE sort_id
END
WHERE sort_id IN ($id_swap, $id_active)
";
$result = mysql_query($query);
if (!$result)
{
return false;
}
else
{
return true;
}
}
echo $id_active.'<br />';
echo $id_swap;
swap_articles();
?>
Well the immediate answer is… you havent passed the variables to the function.
But lets sort this out a little bit more.
You have a table, with sort_order field.
sort_order is an INT (of some variation), UNIQUE field.
sort_order has no holes (this is important)
sort_order ‘top’ is 1 (‘up’ moves towards 1, ‘down’ moves towards max)
Your idea is to move an article in the sort order.
2 Variables are needed:
The current article (defined as $_GET[‘sort_id’])
Which way to move it. (defined as $_GET[‘direction’])
Here’s how i’d plot the logic.
Get variables from the $_GET array into local variables.
If id_active is not set, die.
If direction is not set, assume and set as down.
If direction is down; id_swap is id_active + 1
Else id_swap is id_active - 1
If id_swap = 0; id_swap = 1.
If id_swap Exists in Table:
Swap(id_active,id_swap)
