Member Avatar for turt2live

Hello,

I have run into a problem where I need to update multiple rows in an SQL table at once. I have found and tried to use the tutorials on setting an ID and then looping through the data that way (http://www.daniweb.com/forums/thread35096.html) but my setup is a little different:

The table would look something like this:

ID | ItemName | Owner
0 | test | me
1 | test2 | you
2 | tester | me

Note how the ID number does NOT go in a sequence with the owner.

I need to ONLY edit data based on the owner (the owner being who is logged in).

I haven't been able to make that happen, if someone could help me out it would be appreciated.

~Turt2Live

  • 2 Contributors
  • 1 Reply
  • 137 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by diafol
Member Avatar for diafol

> Note how the ID number does NOT go in a sequence with the owner.

OK, as a rule your owner field would be an integer linked to a 'users' table and the itemname would also be an integer linked to an 'items' table. So your resulting table would be what's commonly called a 'link table'.

users
user_id | name | (other fields pertaining to the user, e.g. password, email etc)

items
item_id | itemname | (other fields pertaining to the item)

useritems (the link table)
item_id | user_id

Your logged in user (usually identified by a session variable, e.g. user_id: $_SESSION) can then view their items thus:

$user_id =  $_SESSION['user_id'];
$rs = mysql_query("SELECT i.item_id, i.itemname FROM items AS i ON useritems AS ui INNER JOIN i.item_id = ui.item_id WHERE ui.user_id = $user_id");

I've used aliases (AS) above - they cut down on having to name the full table as a prefix every time.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.