Update database script

[jessnoonyes]

I'm just starting out with learning mySQL and I'm struggling with understanding what is probably a basic concept, which is passing information from PHP or Javascript into the database. I was hoping you guys could help me with one thing I'm attempting, and maybe explain a couple of things I'm unclear of.

I'm able to query the database and display a list of one of the tables. Now I'm following an example here http://www.wil-linssen.com/extending-the-jquery-sortable-with-ajax-mysql/ to make the list sortable. The problem I'm having is with updating the database with the new order. Here's my database query (if that's what it's called)

<?php include("config/config.php");
$query = "SELECT * FROM clientList ORDER BY id";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){

echo "<li id='listItem_".$row['id']."'>
<img src='images/arrow.png' alt='move' width='16' height='16' class='handle' />
<h2>".$row['Name']."</h2>

....etc

That works fine. The javascript that makes it sortable is:

$(document).ready(function() {
$("#test-list").sortable({
handle : '.handle',
update : function () {
var order = $('#test-list').sortable('serialize');
$("#info").load("config/sort.php?"+order);
}
});
});


It's supposed to look at sort.php and send the new order to the database I believe. Now here is where I think I'm stuck, which is probably the way I have sort.php set up:

<?php include("config.php");
$con = mysql_connect("localhost","$username","$password");
if (!$con) {
die('Could not connect: ' .mysql_error());
}

mysql_select_db("micro_todolist", $con);

foreach ($_GET['listItem'] as $position => $item) :
$sql[] = "UPDATE clientList SET 'position' = $position WHERE 'id' = $item";
endforeach;
print_r ($sql);
?>

I assume this is where I'm going wrong because the list will sort on my page, just not update on the server. The "foreach" part down is an example I got off of the other site and I'm not totally sure what it's doing. For example, where is the ['listItem'] coming from? And the "$item"? Is that saying something like "Update the clientList table and set 'position', where 'position' is the ID of item?

I'm just trying to understand what this part is actually trying to do, so I know how to write it myself in the future. And figure out how to pass the sorted ID's of my list to be changed and updated in the database...I'd really appreciate any guidance! Sorry for being such a noob.

[Old Pedant]

Well, one basic problem you have is not seeing the difference between apostrophes ('...') and back ticks (`...`).

In MySQL (and SQL in general), any value between apostrophes is a *string literal*, same as it is in PHP or JavaScript.

Any value between back ticks is a table name or column name!!

So your code:

$sql[] = "UPDATE clientList SET 'position' = $position WHERE 'id' = $item";

should be using *all* back ticks:

$sql[] = "UPDATE clientList SET `position` = $position WHERE `id` = $item";

Note that back ticks are *NOT REQUIRED* unless the table or column name (a) conflicts with a MySQL keyword or (b) is not a valid MySQL identifier (basically, a name that starts with a letter and has only letters, digits, and underlines in it).

position *might* be a MySQL keyword. But id certainly isn't. You could certainly *try* doing

$sql[] = "UPDATE clientList SET position = $position WHERE id = $item";

Chances are, it will work.

Now, mind you, I think this is pretty crappy code, requiring as it does a separate UPDATE for every item in the sortable list. Not terrible if there are only a handful of items, but really ugly if you had, say 50 to 100 items.

But never mind...get it working first and then think on how to make it much better.

Here's a hint: You can reorder as many items as needed with a max of *three* SQL queries, no matter how many there are in the list.

[jessnoonyes]

Ohhhhhh....that explains the backticks. Their example came with those but I thought it was just a copy/paste error and I rewrote them. Thank you for explaining all of that- it's really helpful.

Ok so I understand, this is updating certain columns in the table row. Well then that's going to get annoying because you're right, there are several columns for each row.

What I'm trying to do is this- each row has an ID number right? Like 1,2,3, etc. And I call them up in order like this:

$query = "SELECT * FROM clientList ORDER BY id";

All I'm trying to do by rearranging them is change their ID number. So if I move #2 up to the first position, it's ID would change to #1 in the database. And when I created the table I gave that ID column the name of "id". Is there a way to write sort.php so that it will do that? Or will I have to write it in such a way that it changes all of the rows that need updating?

[Old Pedant]

It depends.

What is the datatype of the ID field??

If it's an autonumber field--and ID fields usually *ARE* atuonumber--then no, you can't change the value. All autonumber fields are READ-ONLY.

But...

But there's an easy fix.

(1) Add a new field ("column", but I think "field" is a much better name..."column" implies a spreadsheet and DBs only superficially look like spreadsheets) name location (as in the example code!).
(2) To get started, set all occurrences of that field to the same value as the ID for that record.
(3) Presto! You are ready to use the code you got from that site!

How do you do that? Simple:

ALTER TABLE clientLIST ADD position INT;
UPDATE clientList SET position = id;

Done.

(Execute those two statement via whatever DB admin tool you are using.)

Oh...and of course you use ORDER BY position to get the records in the new/changed positions.

[jessnoonyes]

You're such a star. A million thank yous for all the help you've given me in the past too.

Ok your solution makes total sense and I did just what you said. Thanks for pointing out that the autonumber field can't be updated. That would have taken me awhile to figure out. However it still wasn't updating so I set it up so that I could submit the change directly to sort.php to see what happens and I get this error:

Invalid argument supplied for foreach() in /sort.php on line 10

Line 10 is:
foreach ($_GET['listItem'] as $position => $item) :

Is it just mad because I tried submitting directly to it, or is something wrong there?

[Old Pedant]

Sorry...now you are out of my area.

I don't use PHP.

I understand the essence of what that code wants to do, but I don't know the cause of the error.

If you don't get an answer soon, here, try asking in the PHP forum, as it really has nothing directly to do with the DB code and is simply PHP trying to get information from the <FORM> posting.

[jessnoonyes]

Ok, thank you again!!!