Page 1 of 1

identifing a row

Posted: Wed Jul 02, 2003 9:41 am
by Drachlen
My table has a few fields, and one that auto increments. Some times there will be multiple rows with the same information, just a different number in the auto_increment field. What would be the solution to finding one of them? Also, the number wont always be the same because it is added depending on what a user does.

Posted: Wed Jul 02, 2003 10:15 am
by SBukoski
Is the auto increment field the Primary index? What criteria are you using to find the row(s). I need a little more info and detail.

Posted: Wed Jul 02, 2003 10:26 am
by Drachlen
Yes, it is the primary... criteria? uhm, to add the row im using:

Code: Select all

<?php
$res = mysql_query("SELECT maxhp FROM buildings WHERE bid='$bid'") or die("res".mysql_error());
$row = mysql_fetch_array($res) or die("row".mysql_error());
$maxxhp = $row[0];
mysql_query("INSERT INTO buildings_instance (iid, number, owner, currenthp) VALUES ('', '$bid', '$username', '$maxxhp')", $link) or die("qu".mysql_error());
?>
But i need to modify specific rows, and as i stated some times the rows may have the same values except for the the auto_increment field.



This is what im trying to use to update the row, but i need a WHERE statement to identify it, but im not sure what to search for

Code: Select all

<?php
$res = mysql_query("SELECT maxhp FROM buildings WHERE bid='$bid'") or die("res".mysql_error());
$row = mysql_fetch_array($res) or die("row".mysql_error());
$maxxhp = $row[0];
mysql_query("UPDATE buildings_instance SET number='$bid', owner='$username', currenthp='$maxxhp' WHERE ...", $link) or die("qu".mysql_error());

?>

Posted: Wed Jul 02, 2003 10:38 am
by SBukoski
The WHERE clause should be the ID field which is your primary key. Somewhere in your HTML, either via checkboxex, pulldowns, radio or hidden fields you have to tell PHP which row it is you are wanting to update.

For example, there is a listbox with possible buildings to update. The Value for each option would be the Unique ID stored in your table. That way, when you process the form, you have the ID which is passed that you can use in your WHERE clause.

Where is your HTML code to have you pick which building you are going to upgrade? If I see that I can probably help you with some more code.

Posted: Wed Jul 02, 2003 10:44 am
by Drachlen
Sbukoski, i understand what you are saying, but you arent understanding me. This variable could be anything from 1 to 994743, i cant just write the variable in. This is going to be for multiple users, and the id is added to each time someone creates a new building. some buildings will be upgrade-able, and thats the problem. I have no way of identifying between a building if a person builds multiple of them.

Posted: Wed Jul 02, 2003 11:14 am
by SBukoski
You do have a way of identifying, it's your ID field which you stated is your Primary key that is setup as auto_increment. No two rows will have the same ID value. The only way you're going to get your UPDATE statement to work properly is to somehow keep track of this ID field when the user selects which building they want to upgrade.

And I'm willing to bet the way you setup your HTML and the whole process needs to be tweaked to allow for this ID field to be propogated from step to step.

Re: identifing a row

Posted: Wed Jul 02, 2003 11:37 am
by Drachlen
Drachlen wrote:Some times there will be multiple rows with the same information, just a different number in the auto_increment field.
As you can see, i am already aware of this. Thank you for your attempt to help me, but i actually came out with something, all i did was tell it to delete the row where a few of the fields matched, and limit it to 1, then replace it with the new values :D.

Posted: Wed Jul 02, 2003 11:53 am
by SBukoski
How are you associating the different rows with the appropriate users? Or are you not even worrying about that? If you have User A who has a building called "bar" and User B who doesn't have a "bar", how do you differentiate whether User B can upgrade the "bar" or not.

Sorry, I'm simply trying to understand the whole logic here and the association between users and buildings. There currently doesn't appear to be any association between the two.

Posted: Wed Jul 02, 2003 11:59 am
by Drachlen

Code: Select all

<?php
mysql_query("DELETE FROM buildings_instance WHERE number='$b' AND owner='$username' LIMIT 1") or die("delete error: " .mysql_error());
?>
First it checks the building number($b) and then it checks the session username. This way, if they have 5 of these buildings, it only deletes one and adds another. So if we have these rows:

Code: Select all

iid  number    owner    currenthp 
1      1        bob       500  
2      1        bob       500  
3      1        bob       500  
4      2        bob       500
its just going to pick one of the ones with number 1 and delete it, then create a new one with the ID 5. It may be a problem because people will upgrade alot, so ill probably use something to find the iid of what they deleted, and then have it inserted with that one.

Posted: Wed Jul 02, 2003 12:04 pm
by Drachlen
SBukoski wrote:How are you associating the different rows with the appropriate users? Or are you not even worrying about that? If you have User A who has a building called "bar" and User B who doesn't have a "bar", how do you differentiate whether User B can upgrade the "bar" or not.

Sorry, I'm simply trying to understand the whole logic here and the association between users and buildings. There currently doesn't appear to be any association between the two.
Ohh.. Havent got there yet, but probably load a page with if statements checking if they have atleast one of the building, then enabling it.

Posted: Wed Jul 02, 2003 12:47 pm
by Drachlen
Now it replaces the old one with the same iid, that way it doesnt clutter the space, just showin the code incase anyone else has a problem like this...

Code: Select all

<?php
if($upg == "yes") {
    $que = MYSQL_QUERY("SELECT * FROM buildings_instance WHERE number='$b' AND owner='$username' LIMIT 1") or die(mysql_error()); 
    while ($fetch=mysql_fetch_array($que)) {


mysql_query("DELETE FROM buildings_instance WHERE number='$b' AND owner='$username' LIMIT 1") or die("delete error: " .mysql_error());
$res = mysql_query("SELECT maxhp FROM buildings WHERE bid='$bid'") or die("res".mysql_error());
$row = mysql_fetch_array($res) or die("row".mysql_error());
$maxxhp = $row[0];
mysql_query("INSERT INTO buildings_instance (iid, number, owner, currenthp) VALUES ('$fetch[iid]', '$bid', '$username', '$maxxhp')", $link) or die("qu".mysql_error());
}
}

?>