[SOLVED] Code not working.

[Dragoon]

Sorry to make my first post here a request for help, but I've been having trouble with a bit of code and this was one of the first sites that came up in a Google search.

Anyway, I've been having trouble getting this bit of code to work:

(Edit: The advertisement is getting in the way, so I'm going to force the code-box down a bit...)
.
.
.
.
.
.
.
.
.
.

[code]<?php
$result = mysql_query("SELECT * FROM `links` WHERE `linkid` = $mid LIMIT 1");
while ($result2 = mysql_fetch_array($result))
{
$oldcp = $result2[colpos];
$newcp = $oldcp - 1;
$currentLinkTitle = $result2[urltext];
$currentCategory = $result2[cat];

$result3 = mysql_query("SELECT * FROM `links` WHERE `colpos` = $newcp AND `cat` = '$currentCategory' LIMIT 1");
while ($result4 = mysql_fetch_array($result))
{
$linkToReplaceID = $result4[linkid];
$linkToReplaceTitle = $result4[urltext];
$linkToReplaceCP = $result4[colpos];
}

mysql_query("UPDATE `links` SET `colpos` = $oldcp WHERE `colpos` = $newcp AND `cat` = '$currentCategory' LIMIT 1");
mysql_query("UPDATE `links` SET `colpos` = $newcp WHERE `linkid` = $mid AND `cat` = '$currentCategory' LIMIT 1");
echo "<meta http-equiv='refresh' content='0;URL=index.php?action=admin&ad=links'>";
}
?>[/code]

What it should do is move the link in question down (reduce its colpos by one) and then move the link it's replacing up one (set its colpos to the colpos previously owned by the link it's moving). However, it doesn't seem to be finding the link it should be replacing, so what I end up with is two links with the same colpos.

I've been tinkering with it for a while, and I haven't figured out what my problem is. I'm guessing it's something painfully simple, but my meager self-learned knowledge of PHP is erratic.

Any help would be greatly appreciated, as well as other comments/suggestions on my sloppy coding.

[Psycho]

Since you think it is not finding the 2nd link, have you echoed your 2nd query to the page to verify it is properly formatted? You should also and an error handler to your queries to see if any errors are happening:
[code]<?php
$sql = "SELECT * FROM `links` WHERE `colpos` = $newcp AND `cat` = '$currentCategory' LIMIT 1";
$result3 = mysql_query($sql) or die ("The query:<br>".$sql."<br>Caused the error:<br>".mysql_error());
?>[/code]

[Dragoon]

[quote author=mjdamato link=topic=118727.msg485438#msg485438 date=1166194372]
Since you think it is not finding the 2nd link, have you echoed your 2nd query to the page to verify it is properly formatted? You should also and an error handler to your queries to see if any errors are happening:
[code]<?php
$sql = "SELECT * FROM `links` WHERE `colpos` = $newcp AND `cat` = '$currentCategory' LIMIT 1";
$result3 = mysql_query($sql) or die ("The query:<br>".$sql."<br>Caused the error:<br>".mysql_error());
?>[/code]

[/quote]

I did try echoing all of the variables grabbed from that query, and they were empty. There were no errors, either.

I also tried putting the query directly into phpMyAdmin, replacing the variables with the correct values (which those variables DO contain; I double checked), and it worked fine there...

[complex05]

Your problem is you are not adding +1 to the old link, but you are -1 from the link you are trying to move.

Try this

$linkToReplaceCP = $result4[colpos];
$linkToReplaceCP = $linkToReplaceCP + 1;

then in your query

mysql_query("UPDATE `links` SET `colpos` = $linkToReplaceCP WHERE `colpos` = $newcp AND `cat` = '$currentCategory' LIMIT 1");

[Dragoon]

[quote author=complex05 link=topic=118727.msg485466#msg485466 date=1166199975]
Your problem is you are not adding +1 to the old link, but you are -1 from the link you are trying to move.

Try this

$linkToReplaceCP = $result4[colpos];
$linkToReplaceCP = $linkToReplaceCP + 1;

then in your query

mysql_query("UPDATE `links` SET `colpos` = $linkToReplaceCP WHERE `colpos` = $newcp AND `cat` = '$currentCategory' LIMIT 1");


[/quote]

I think you're looking at the wrong variable. $linkToReplaceCP was one of the variables I was setting to output later on for debugging. The variables I'm using are $oldcp and $newcp. $oldcp is set to the colpos of the link I'm moving, and $newcp is set to $oldcp - 1. The colpos of the link I'm moving down is set to $newcp ($oldcp - 1), and the colpos of the link it's replacing is set to $oldcp (which is essentially $newcp + 1)

[Psycho]

So, $newcp and $currentCategory were both empty???

$currentCategory would be empty if you are not pulling it by the correct field name, but I don't see how $newcp would be empty.

@complex: He wants the link to move UP in the sort order - so it would have a lower number if you sort the results ascending: 1 is on top, 2 is below that, etc.

[complex05]

I built the exact same script recently, here is my code, maybe it will help

[code]if ($action == "move_project")
{
$query = mysql_query("SELECT `order` FROM whatson WHERE id=$id");
$data = mysql_fetch_array($query);
$project_order = $data["order"];
switch($direction)
{
case 'up': $new_order = $project_order - 1; break;
case 'down': $new_order = $project_order + 1; break;
}
mysql_query("UPDATE whatson SET `order`=$project_order WHERE `order`=$new_order");
mysql_query("UPDATE whatson SET `order`=$new_order WHERE id=$id");
header("location: whatsonadmin.php?action=view_projects");
}
[/code]

[Dragoon]

[quote author=mjdamato link=topic=118727.msg485472#msg485472 date=1166200286]
So, $newcp and $currentCategory were both empty???

$currentCategory would be empty if you are not pulling it by the correct field name, but I don't see how $newcp would be empty.

@complex: He wants the link to move UP in the sort order - so it would have a lower number if you sort the results ascending: 1 is on top, 2 is below that, etc.
[/quote]

No, the first query ($result and $result2) works fine, and so does the one to update the colpos of the link it's moving down. The trouble is with second query ($result3 and 4) and query that should be moving the other link up, and out of the way of the link moving down ([b]mysql_query("UPDATE `links` SET `colpos` = $oldcp WHERE `colpos` = $newcp AND `cat` = '$currentCategory' LIMIT 1");[/b]).

So, what I end up with is two links that have the same colpos.

Edit: If it makes any difference, linkid is the primary key for the table.

Edit 2: Thanks, complex05.

[Psycho]

Put an echo statement inside the while loop to see if any records are being returned. However, you don't need a while loop since you used "LIMIT 1".

Also, if you do get a result, I would suggest grabbing the linkID and using it in the update query as well.

[complex05]

[code]mysql_query("UPDATE `links` SET `colpos` = $oldcp WHERE `colpos` = $newcp AND `cat` = '$currentCategory' LIMIT 1");[/code]

The problem might be with your initial data. Are all your rows in perfect order right now? (1,2,3,4)

what might be happening is it's looking for $newcp and it's not there, so this query fails.

[Dragoon]

[quote author=mjdamato link=topic=118727.msg485491#msg485491 date=1166201615]
Put an echo statement inside the while loop to see if any records are being returned. However, you don't need a while loop since you used "LIMIT 1".

Also, if you do get a result, I would suggest grabbing the linkID and using it in the update query as well.
[/quote]

I have tried echoing the results, and the variables were all empty. Here's the latest (still not quite working) version of the code in question:

[code]<?php
$result = mysql_query("SELECT * FROM `links` WHERE `linkid` = $mid LIMIT 1") or die(mysql_error());
$result2 = mysql_fetch_array($result);
$oldcp = $result2[colpos];
$newcp = $oldcp - 1;
$currentLinkTitle = $result2[urltext];
$currentCategory = $result2[cat];

$result3 = mysql_query("SELECT * FROM `links` WHERE `colpos` = $newcp AND `cat` = '$currentCategory' LIMIT 1") or die(mysql_error());
$result4 = mysql_fetch_array($result);
$linkToReplaceID = $result4[linkid];
$linkToReplaceTitle = $result4[urltext];
$linkToReplaceCP = $result4[colpos];

mysql_query("UPDATE `links` SET `colpos` = $oldcp WHERE `linkid` = $linkToReplaceID AND `cat` = '$currentCategory' LIMIT 1");
mysql_query("UPDATE `links` SET `colpos` = $newcp WHERE `linkid` = $mid AND `cat` = '$currentCategory' LIMIT 1");
echo "<meta http-equiv='refresh' content='0;URL=index.php?action=admin&ad=links'>";
?>[/code]

[quote author=complex05 link=topic=118727.msg485493#msg485493 date=1166201761]
[code]mysql_query("UPDATE `links` SET `colpos` = $oldcp WHERE `colpos` = $newcp AND `cat` = '$currentCategory' LIMIT 1");[/code]

The problem might be with your initial data. Are all your rows in perfect order right now? (1,2,3,4)

what might be happening is it's looking for $newcp and it's not there, so this query fails.
[/quote]

The next thing on my list of things to do is adding a check to make sure that the link has a place to move up or down to.

[Psycho]

Change these two lines
[code]$result3 = mysql_query("SELECT * FROM `links` WHERE `colpos` = $newcp AND `cat` = '$currentCategory' LIMIT 1") or die(mysql_error());
$result4 = mysql_fetch_array($result);[/code]

To this for debugging:
[code]$sql = "SELECT * FROM `links` WHERE `colpos` = $newcp AND `cat` = '$currentCategory' LIMIT 1";
$result3 = mysql_query($sql  or die($sql."<br><br>".mysql_error());
$result4 = mysql_fetch_array($result);
echo $sql."<br><pre>";
print_r($result4);
echo "</pre>";[/code]

What gets output to the page.

[Dragoon]

[quote author=mjdamato link=topic=118727.msg485548#msg485548 date=1166205078]
Change these two lines
[code]$result3 = mysql_query("SELECT * FROM `links` WHERE `colpos` = $newcp AND `cat` = '$currentCategory' LIMIT 1") or die(mysql_error());
$result4 = mysql_fetch_array($result);[/code]

To this for debugging:
[code]$sql = "SELECT * FROM `links` WHERE `colpos` = $newcp AND `cat` = '$currentCategory' LIMIT 1";
$result3 = mysql_query($sql  or die($sql."<br><br>".mysql_error());
$result4 = mysql_fetch_array($result);
echo $sql."<br><pre>";
print_r($result4);
echo "</pre>";[/code]

What gets output to the page.
[/quote]

It outputs a parse error.
[quote]Parse error: syntax error, unexpected ';' in D:\Webserver\xampp\htdocs\HomePHP3\inc\admin.php on line 52[/quote]

(Line 51 being the first line of your code)

Edit: Instead of using your code, I added the print_f($result4); to mine, and it didn't output anything. When I changed it to $result2 (the link being moved), it output this:

[code]Array
(
    [0] => http://www.fark.com/
    [url] => http://www.fark.com/
    [1] => Fark
    [urltext] => Fark
    [2] => Misc
    [cat] => Misc
    [3] => 9
    [linkid] => 9
    [4] => 0
    [colpos] => 0
)[/code]

which is all accurate.

[Psycho]

When you echo the query for $result3 are the values of the variables valid?

[Dragoon]

[quote author=mjdamato link=topic=118727.msg485598#msg485598 date=1166207662]
When you echo the query for $result3 are the values of the variables valid?
[/quote]

Yeah, the query is just fine--it works perfectly if I put it into phpMyAdmin, but it still won't work in the code.

[Psycho]

I don't know what to tell you. You say the query is correct yet returns no resutls when run in the PHP page. But, there's obviously a problem somewhere.

I can only suggest that you echo your variables and queries to the page before running any query and to echo the details of the results (i.e. num_rows, and print_r() the array). Also make sure you have error handling on every db call as well.

[Dragoon]

[quote author=mjdamato link=topic=118727.msg485648#msg485648 date=1166212403]
I don't know what to tell you. You say the query is correct yet returns no resutls when run in the PHP page. But, there's obviously a problem somewhere.

I can only suggest that you echo your variables and queries to the page before running any query and to echo the details of the results (i.e. num_rows, and print_r() the array). Also make sure you have error handling on every db call as well.
[/quote]

Yeah, this is really confusing me... I'll try a few more things. The next thing I think I may try is uploading the entire script to the webspace I pay for (it's running on a webserver I've set up on my own computer right now) to see if maybe it's not a problem with the script.

[Dragoon]

[quote author=Dragoon link=topic=118727.msg485760#msg485760 date=1166221115]
[quote author=mjdamato link=topic=118727.msg485648#msg485648 date=1166212403]
I don't know what to tell you. You say the query is correct yet returns no resutls when run in the PHP page. But, there's obviously a problem somewhere.

I can only suggest that you echo your variables and queries to the page before running any query and to echo the details of the results (i.e. num_rows, and print_r() the array). Also make sure you have error handling on every db call as well.
[/quote]

Yeah, this is really confusing me... I'll try a few more things. The next thing I think I may try is uploading the entire script to the webspace I pay for (it's running on a webserver I've set up on my own computer right now) to see if maybe it's not a problem with the script.
[/quote]

Bah! I told you all it was something incredibly simple!

[code] $result3 = mysql_query($sqltmp) or die(mysql_error());
$result4 = mysql_fetch_array($result);[/code]

Notice anything wrong with that?