[SOLVED] Query not working.... why?

[stuffradio]

$testing = mysql_query("UPDATE `usersitems2` SET `trading`='no', `owner`='$user_id', `trade_id`='' WHERE `trade_id`='$trade_id' AND `owner`='$userid'") or die(mysql_error());
[/Code]

 
[Code]
$testing1 = mysql_query("UPDATE `usersitems2` SET `trading`='no', `owner`='$userid', `trade_id`='' WHERE `trade_id`='$trade_id' AND `owner`='$user_id'")or die(mysql_error());

 

$trade_id = id of the trade

$user_id = id of the person who offered on the trade

$userid = the id of the person who is accepting the trade

 

I tried echoing all the variables... they're correct. Only the one setting the owner to $user_id works. Why doesn't the other one?

[btherl]

What is the error?

[stuffradio]

There is no error.

 

 

The query where it sets the owner to $user_id works, but it doesn't set trading=no

The query where it sets owner to $userid doesn't set owner to $userid, it just leaves it at the $user_id id.

[btherl]

Then it's not matching the right row.  First check that your query is what you expect it to be by printing it out.  Then check your program logic.  If you are still having trouble, post the entire program, or a large enough portion that we can look through it.

[cooldude832]

is trading a varchar or field that can accept a string??

if it is odds are it shouldn't and should be enum or int or bool

[stuffradio]

I will change that soon enough to enum if you think I should... here is the whole section of that.

 

} elseif ($_GET['action'] == "accept") {
$user_id = $_POST['user_id'];
$trade_id = $_POST['trade_id'];


$testing = mysql_query("UPDATE `usersitems2` SET `trading`='no', `owner`='$user_id', `trade_id`='' WHERE `trade_id`='$trade_id' AND `owner`='$userid'") or die(mysql_error());
$testing1 = mysql_query("UPDATE `usersitems2` SET `trading`='no', `owner`='$userid', `trade_id`='' WHERE `trade_id`='$trade_id' AND `owner`='$user_id'")or die(mysql_error());

echo "Trade ID: $trade_id Owner: $userid Offerer: $user_id";


mysql_query("UPDATE `trades` SET `done`='yes' WHERE `id`='$trade_id'");
mysql_query("DELETE FROM `trade_items` WHERE `trade_id`='$trade_id'");
mysql_query("DELETE FROM `trade_offers` WHERE `trade_id`='$trade_id'");
//echo '<META HTTP-EQUIV="Refresh" CONTENT="0; URL=mytrades.php">';
}

 

The echo was just to test what the results were, and they came out fine.

[cooldude832]

The only reason I suggest this is it looks like you have a game or large market place script and any place you can save bytes is big.  Storing integers is smarter than string.  I really rarely use enum uneless I have greater than 5 options.  FOr the most part I will use bool or a int and just use php to do psedo enum fields

[jcd]

The first query sets trade_id to blank yes?

 

So when the second query is executed it doesn't find a "WHERE trade_id = '$trade_id'", so no update is made.

[btherl]

Wait a minute .. are you trying to swap two entries with only two updates?  To swap items you need three updates (or one very well constructed one).  For example

 

$a = $b;
$b = $a;

 

After that, both values will be set to $b.  But

 

$t = $a;
$a = $b;
$b = $t;

 

This will swap two values successfully.

 

While I was typing, jcd pointed out another problem with the logic .. you'll need to fix both of those.  THe item should only go out of "Trade mode" when the trade is complete, not part-way through.

[stuffradio]

I tried what you said.

 

} elseif ($_GET['action'] == "accept") {
$user_id = $_POST['user_id'];
$trade_id = $_POST['trade_id'];
$t = $userid;
$a =  $user_id;
$b = $t;

$testing = mysql_query("UPDATE `usersitems2` SET `trading`='no', `owner`='$a' WHERE `trade_id`='$trade_id' AND `owner`='$userid'") or die(mysql_error());
$testing1 = mysql_query("UPDATE `usersitems2` SET `trading`='no', `owner`='$b' WHERE `trade_id`='$trade_id' AND `owner`='$user_id'")or die(mysql_error());

echo "Trade ID: $trade_id Owner: $userid Offerer: $user_id";


mysql_query("UPDATE `trades` SET `done`='yes' WHERE `id`='$trade_id'");
mysql_query("DELETE FROM `trade_items` WHERE `trade_id`='$trade_id'");
mysql_query("DELETE FROM `trade_offers` WHERE `trade_id`='$trade_id'");
//echo '<META HTTP-EQUIV="Refresh" CONTENT="0; URL=mytrades.php">';
}

 

Now it just sets both to the id of $userid. Any ideas?

[jcd]

Put an echo statement after each of your testing queries. This will help you see where you're going wrong. Paste the results of the echos here.

[stuffradio]

It just says 1 for both of them.

[btherl]

Hmm, actually I think there's a much easier way to do this.  First, fetch a unique identifier for each item separately (also called "primary key".  If you don't have one, it's time to make one).  Then do the 2 update statements, but use the unique identifier as the condition, rather than userid and tradeid.

 

That will do the swap correctly.

[jcd]

I've just noticed that your echo doesn't query the database. You are at the moment echoing normal $variables which you set at the beginning of the script, but did not change..so of course the echo will not change.

 

You need something like

 

$query = "SELECT owner, trade_id FROM usersitems2 WHERE ...(unique id)";

$rh = mysql_query($query);

$results = mysql_fetch_rows($rh);

echo 'Owner: ', $results[0], ' Trade id: ', $results[1];

 

Do that after every testing query to see if the values updated as you expected.

[mmarif4u]

Can u show us from $userid is coming.

[stuffradio]

$userid is a global variable that is the id of the username you're logged in to.

[stuffradio]

After playing around for a while... I found out they are overwriting each other. (The queries)

 

How can I fix this? I'm using the Id idea.

 

$query = mysql_query("SELECT id, owner, trade_id FROM `usersitems2` WHERE `trade_id`='$trade_id'");

while ($results = mysql_fetch_array($query)) {
mysql_query("UPDATE `usersitems2` SET `owner`='$user_id' WHERE `id`='$results[id]' AND `trade_id`='$trade_id' AND `owner`='$userid'") or die(mysql_error());
echo "$results[id]<br />";
//mysql_query("UPDATE `usersitems2` SET `owner`='$userid' WHERE `id`='$results[id]' AND `trade_id`='$trade_id' AND `owner`='$user_id'")or die(mysql_error());


}

 

any ideas how to solve this?

[btherl]

It's tough to do it like that, because you need to reference the other owner as well.  I would do it explicitly like this:

 

$userid_id_query = mysql_query("SELECT id, trade_id FROM `usersitems2` WHERE `trade_id`='$trade_id' AND owner = '$userid'");
$user_id_id_query = mysql_query("SELECT id, trade_id FROM `usersitems2` WHERE `trade_id`='$trade_id' AND owner = '$user_id'");
$userid_id = mysql_fetch_array($userid_id_query);
$user_id_id = mysql_fetch_array($user_id_id_query);
print "User $userid will trade item {$userid_id['id']}, user $user_id will trade item {$user_id_id['id']}";

 

I think this is a good time to point out that $userid and $user_id are not great for variable names .. they look too similar.  If $userid is what you already have, something like "$other_user_id" would be clearer.

 

Anyway, once you have those two ids, you can do two updates using them.

[stuffradio]

omg,

 

After hours of doing this.. I made it work! I accomplished this by adding a temp_id field, insert the temp_id in to the table... and set the owner to that temp_id.

 

 

Thanks guys, I really appreciate all the advice I was given!