Page 1 of 1

what i've miss?

Posted: Mon Jul 27, 2009 3:03 am
by cronika

Code: Select all

 
 
<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
 
mysql_select_db("joc", $con);
 
$xp = $_POST['xp'];
$sql = mysql_query("UPDATE users SET xp=(xp +'$xp')")or die(mysql_error()); 
 
?>
<form action="insert_xp.php" method="post">
XP : <input type="text" name="xp" />
<input type="submit" value="submit">
</form>
 
 
The script should insert xp just to one user(the one loged in) but he is inserting xp to everyone. What should i do?

Re: what i've miss?

Posted: Mon Jul 27, 2009 3:24 am
by Eran
You need to add a WHERE clause to your query. It should indicate which user should be updated

Re: what i've miss?

Posted: Mon Jul 27, 2009 3:27 am
by cronika
you mean WHERE username = username ?

Re: what i've miss?

Posted: Mon Jul 27, 2009 3:38 am
by Eran
something like that. make sure it's a unique identifier (if the username isn't unique, use something like 'user_id' or simply 'id')

Re: what i've miss?

Posted: Mon Jul 27, 2009 3:42 am
by cronika

Code: Select all

 
 
-- Table structure for table `users`
--
 
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(60) NOT NULL,
  `password` varchar(60) NOT NULL,
  `email` varchar(120) NOT NULL,
  `xp` mediumint(15) NOT NULL,
  `bank` bigint(10) NOT NULL,
  PRIMARY KEY (`user_id`,`username`,`xp`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
 
 
 
This is my db.

Im rookie to PHP please be gentle.

Re: what i've miss?

Posted: Mon Jul 27, 2009 4:05 am
by Eran
best to use the user_id field. You would need to have that attribute value for the user you want to update

Re: what i've miss?

Posted: Mon Jul 27, 2009 4:14 am
by cronika
Can you show me a small example?
Because in my db every new user has user_id=0 .

Re: what i've miss?

Posted: Mon Jul 27, 2009 4:22 am
by Eran
That can't be true, since according to the schema you posted of your table, that field is auto-incremented. That means that every new user should have a user_id that is larger by 1 than the previous one.

Re: what i've miss?

Posted: Mon Jul 27, 2009 4:25 am
by turbolemon
You're using a composite primary key there. Auto increment might not work on the id field? (Never tried auto-incrementing a portion of a composite key before, the composition of the key should make it unique without auto-increment). You ought to use id as the unique identifier. If you want to have unique usernames also, add a UNIQUE INDEX AS BTREE ( `username`) to the end of your SQL, maybe something like this:

Code: Select all

 
 CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `username` varchar(60) NOT NULL,
  `password` varchar(60) NOT NULL,
  `email` varchar(120) NOT NULL,
  `xp` mediumint(15) NOT NULL,
  `bank` bigint(10) NOT NULL,
  UNIQUE INDEX USING BTREE ( `username` )
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
 
You would probably also want a unique index on the email field (which would require a second unique index line), so two users can't use the same e-mail address.

Re: what i've miss?

Posted: Mon Jul 27, 2009 4:27 am
by cronika
it worked!

But still is not inserting the data to just one user, here is th new db :

Code: Select all

 
 
--
-- Table structure for table `users`
--
 
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` bigint(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(60) NOT NULL,
  `password` varchar(60) NOT NULL,
  `email` varchar(120) NOT NULL,
  `xp` mediumint(15) NOT NULL,
  `bank` bigint(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;
 
--
-- Dumping data for table `users`
--
 
INSERT INTO `users` (`user_id`, `username`, `password`, `email`, `xp`, `bank`) VALUES
(14, 'admin', '47bce5c74f589f4867dbd57e9ca9f808', 'aaa@aaa.aaa', 0, 0),
(15, 'admina', '47bce5c74f589f4867dbd57e9ca9f808', 'aaa@aaa.aaaa', 0, 0);
 
 
 
And i've let the script simple, i dont know where exactly to put the WHERE condition, fi ill put it here

Code: Select all

$sql = mysql_query("UPDATE users SET xp=(xp +'$xp') WHERE ....!?")or die(mysql_error());
what i should write after WHERE?

Re: what i've miss?

Posted: Mon Jul 27, 2009 4:33 am
by turbolemon
where user_id = {$id} should do it, where $id is populated from wherever you are storing the id value (session, cookie or request variable?).

Re: what i've miss?

Posted: Mon Jul 27, 2009 4:40 am
by cronika
Here is the full script

Code: Select all

 
 
<?php
// Connects to your Database
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("joc") or die(mysql_error());
 
//checks cookies to make sure they are logged in
if(isset($_COOKIE['ID_my_site']))
{
$username = $_COOKIE['ID_my_site'];
$pass = $_COOKIE['Key_my_site'];
$check = mysql_query("SELECT * FROM users WHERE username = '$username'")or die(mysql_error());
while($info = mysql_fetch_array( $check ))
{
 
//if the cookie has the wrong password, they are taken to the login page
if ($pass != $info['password'])
{ header("Location: login.php");
}
 
//otherwise they are shown the admin area
else
{
echo "";
}
}
}
else
//if the cookie does not exist, they are taken to the login screen
{
header("Location: login.php");
}
?> 
<table align="center" height="227" width="750">
<tr> <th height="85" colspan="3">Banner</th>
  </tr>
  <td width="131" height="134"><?php echo "Wellcome $username."; ?>
  <p><br />
      <strong>Profil :</strong><br />
      <?php 
       $con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("joc", $con);
 
$result = mysql_query("SELECT * FROM users");
 
while($row = mysql_fetch_array($result))
  {
  echo "XP :" .$row['xp'];
  echo "<br />";
  echo "Level :" .$row['level'];
  echo "<br />";
  }
 
       
       
       
      ?>
      
      <br />
      <a href=logout.php> Logout</a> </p>
  </td>
  <td width="638"><a href="insert_xp.php">Status </a></td>
 
  <td width="206">
  <?php
  
$xp = $_POST['xp'];
$sql = mysql_query("UPDATE users  SET xp=(xp +'$xp') WHERE user_id='$username' ")or die(mysql_error()); 
 
?>
<form action="insert_xp.php" method="post">
XP : <input type="text" name="xp" />
<input type="submit" value="submit">
</form>
  
  
  
  </td>
</table>
 
 
 

Re: what i've miss?

Posted: Mon Jul 27, 2009 4:52 am
by cronika
Thank you all for the support i've succeed.