Update details in database

[jd307]

Hi all,

 

This might be something really simple, but I am not yet very good with PHP.  I have a website that I am developing in XHTML which uses a SMF Forum.  I have created a new field in the SMF Forum database for 'points'.  It is website for a game and basically everytime someone comes to an event I want to give them 1 point (they must be signed up on the forum).  I have a page that displays a list of users and their associated points, however I want to create an 'admin' page that can add / deduct and reset all points per user accordingly. 

 

Preferably, I would like it to work as so:

 

Member1  0 Points  +  -

Member2  2 Points  +  -

RESET

 

This would allow me to click the + to add a point to someones name.  The - to remove a point from that person or RESET would put all points to 0.  I could not figure this out whatsoever.  So I used a method with a form.  I now have a form that shows all points and members in a form so I could type in the correct number of points next to the persons name and this then has a submit button to make all the changes.  This isnt working either.  Here is my code:

 

<?php
$host = "xxxxxxxx";
$db = "smf_members";
$pass = "xxxx";

$con = mysql_connect($host, $db, $pass);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db($con);

$result = mysql_query("SELECT * FROM smf_members");



echo "<form action=\"pointsadmin.php\" method=\"post\">
<table>
<tr>
<th width=\"100\">Member</th>
<th width=\"20\">Points</th>
</tr>";while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td> <input type=\"text\" name=\"memberName\" value=\"" . $row['memberName'] . "\"</td>";
  echo "<td> <input type=\"text\" name=\"points\" value=\"" . $row['points'] . "\"</td>";
  echo "</tr>";
  }
echo "</table>";
echo "<input type=\"submit\" name=\"submit\">";
echo "</form>";

if (!isset($_POST['submit'])) {
// form not submitted
}
else {
// form submitted

// open connection
$connection = mysql_connect($host, $db, $pass) or die ("Unable to connect!");

// get form input
// check to make sure it's all there
// escape input values for greater safety
$memberName = mysql_escape_string($_POST['memberName']);
$points = mysql_escape_string($_POST['points']);

mysql_select_db($db) or die ("Unable to select database!");

$query = "UPDATE INTO smf_members (memberName, points) VALUES ('$memberName', '$points')";

$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

mysql_close($con);
?>

 

If possible, I would prefer my first example, but have no idea how to start (well I can make the list but dont know how to programme the + and - buttons or the RESET button).  If it isn't that simple then the form version would be fine.

 

An example of the points list is at http://www.wildbox.co.uk/soulfire/points.php

 

Any help you can offer on this matter, or pointers in how to do this would be appreciated.  I think I might have to use an array, but I don't really understand how to do this from tutorials.. or at least how to apply them to do what i need it to.

 

Thanks!

[phpSensei]

MemberId is the Id of the member's points you want to change

Point is the number of points you want to reduce, or add

m fines wether you ADD or SUBSTRACT the amount of points

 

 

You have to make a CHECKBOX called "m" and have two options, one would be a "ADD", and the other "SUB"

 

try my code

 

<?php
$host = "xxxxxxxx";
$db = "smf_members";
$pass = "xxxx";

$con = mysql_connect($host, $db, $pass);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db($con);

$result = mysql_query("SELECT * FROM smf_members");

$points = ($_POST['points'] == "")  ? 1 : $_POST['point'];
$mode = ($_POST['m'] == "ADD" || $_POST['m'] == "SUB") ? $_POST['m'] : "ADD";
$member_id = mysql_real_escape_string($_POST['memberName']);

  if($mode == "ADD"){ $mode = "+"; } else { $mode = "-"; }


echo "<form action=\"pointsadmin.php\" method=\"post\">
<table>
<tr>
<th width=\"100\">Member</th>
<th width=\"20\">Points</th>
</tr>";while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td> <input type=\"text\" name=\"memberName\" value=\"" . $row['memberName'] . "\"</td>";
  echo "<td> <input type=\"text\" name=\"points\" value=\"" . $row['points'] . "\"</td>";
  echo "</tr>";
  }
echo "</table>";
echo "<input type=\"submit\" name=\"submit\">";
echo "</form>";

if (!isset($_POST['submit'])) {
// form not submitted
}
else {
// form submitted

// open connection
$connection = mysql_connect($host, $db, $pass) or die ("Unable to connect!");

// get form input
// check to make sure it's all there
// escape input values for greater safety

mysql_select_db($db) or die ("Unable to select database!");

$query = "UPDATE smf_members SET points = points $mode $points WHERE memberName = '$memberID'";

$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

mysql_close($con);
?>

[jd307]

Thanks for the quick response.  I have looked through your code and to be honest I dont understand it.  I know nothing about complex PHP (only how to get info from a DB and just about how to add, not really much on updating.

 

So, I tried just copy & paste the entire code into a file and nothing worked (as I thought I am doing something wrong).  I have created the checkbox as well as per instructions.... I just get a PHP error.  COuld you sort of give me a pointer as to what to do with your code please?

 

SOrry Im not trying to be rude... just a noob! 

[phpSensei]

Hehe, its okay.

 

Just post your current code, and your new form please, also the ERROR you are getting.

[jd307]

Are you really in for the long haul? Haha.

 

Current code:

<?php
$host = "xxxx";
$db = "wil0801309563928";
$pass = "xxxx";

$con = mysql_connect($host, $db, $pass);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db($con);

$result = mysql_query("SELECT * FROM smf_members");

$points = ($_POST['points'] == "")  ? 1 : $_POST['point'];
$mode = ($_POST['m'] == "ADD" || $_POST['m'] == "SUB") ? $_POST['m'] : "ADD";
$member_id = mysql_real_escape_string($_POST['memberName']);

  if($mode == "ADD"){ $mode = "+"; } else { $mode = "-"; }


echo "<form action=\"pointsadmin.php\" method=\"post\">
<table>
<tr>
<th width=\"100\">Member</th>
<th width=\"20\">Points</th>
</tr>";while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td> <input type=\"text\" name=\"memberName\" value=\"" . $row['memberName'] . "\"</td>";
  echo "<td> <input type=\"text\" name=\"points\" value=\"" . $row['points'] . "\"</td>";
  echo "<td> <INPUT TYPE=CHECKBOX NAME=\"m\" value=\"ADD\"></td>";
  echo "<td> <INPUT TYPE=CHECKBOX NAME=\"m\" value=\"SUB\"></td>";
  echo "</tr>";
  }
echo "</table>";
echo "<input type=\"submit\" name=\"submit\">";
echo "</form>";

if (!isset($_POST['submit'])) {
// form not submitted
}
else {
// form submitted

// open connection
$connection = mysql_connect($host, $db, $pass) or die ("Unable to connect!");

// get form input
// check to make sure it's all there
// escape input values for greater safety

mysql_select_db($db) or die ("Unable to select database!");

$query = "UPDATE smf_members SET points = points $mode $points WHERE memberName = '$memberID'";

$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

mysql_close($con);
?>

 

My error:

Parse error: parse error, unexpected $ in /home/content/j/d/3/jd307/html/soulfire/pointsadmin.php on line 59

 

Please treat me as if I was someone who has just about heard what PHP is and have never written a line in my life... it might be something I'll understand.  I appreciate your help so much

[phpSensei]

I forgot a bracker "}" at the end of the script.

 

try

 

 

<?php
$host = "h50mysql47.secureserver.net:3306";
$db = "wil0801309563928";
$pass = "Bran3fy1";

$con = mysql_connect($host, $db, $pass);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db($con);

$result = mysql_query("SELECT * FROM smf_members");

$points = ($_POST['points'] == "")  ? 1 : $_POST['point'];
$mode = ($_POST['m'] == "ADD" || $_POST['m'] == "SUB") ? $_POST['m'] : "ADD";
$member_id = mysql_real_escape_string($_POST['memberName']);

  if($mode == "ADD"){ $mode = "+"; } else { $mode = "-"; }


echo "<form action=\"pointsadmin.php\" method=\"post\">
<table>
<tr>
<th width=\"100\">Member</th>
<th width=\"20\">Points</th>
</tr>";while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td> <input type=\"text\" name=\"memberName\" value=\"" . $row['memberName'] . "\"</td>";
  echo "<td> <input type=\"text\" name=\"points\" value=\"" . $row['points'] . "\"</td>";
  echo "<td> <INPUT TYPE=CHECKBOX NAME=\"m\" value=\"ADD\"></td>";
  echo "<td> <INPUT TYPE=CHECKBOX NAME=\"m\" value=\"SUB\"></td>";
  echo "</tr>";
  }
echo "</table>";
echo "<input type=\"submit\" name=\"submit\">";
echo "</form>";

if (!isset($_POST['submit'])) {
// form not submitted
}
else {
// form submitted

// open connection
$connection = mysql_connect($host, $db, $pass) or die ("Unable to connect!");

// get form input
// check to make sure it's all there
// escape input values for greater safety

mysql_select_db($db) or die ("Unable to select database!");

$query = "UPDATE smf_members SET points = points $mode $points WHERE memberName = '$memberID'";

$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

mysql_close($con);

}
?>

[jd307]

Oooh!  That's a bit better. I can now get my HTML table headers to appear... but above that I get an error saying:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/content/j/d/3/jd307/html/soulfire/pointsadmin2.php on line 30

 

On that line is the line of code stating:

while($row = mysql_fetch_array($result))

 

Due to the error I assume it is something to do with my SQL line which is $result:

$result = mysql_query("SELECT * FROM smf_members");

 

(The code is still the same as in your previous post).  I can't see anything wrong with this line personally...

[phpSensei]

change the

$result = mysql_query("SELECT * FROM smf_members");

to

$result = mysql_query("SELECT * FROM smf_members") or die(mysql_error());

[jd307]

YAY!!

 

It looks exactly how it is meant to now.  Showing some nice stuff and nice little check boxes.  There were still some problems with it, which I think I have fixed (it was saying no database selected) so I added some lines in for that.  Now, however, when I click on a check box and click submit I recieve an error:

Error in query: UPDATE smf_members SET points = points + WHERE memberName = 'memberID'. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE memberName = 'memberID'' at line 1

 

I believe it is to do with this line:

$query = "UPDATE smf_members SET points = points $mode $points WHERE memberName = 'memberID'";

 

Now I am about 90% sure that is shouldn't say WHERE memberName ='memberID' because memberID i not a variable.  I have changed that to $member_id as that is in the code, but that just selects Taurix (which is the last member name that was pulled from the database).

 

You can see this at http://www.wildbox.co.uk/soulfire/pointsadmin2.php if you want to see what I mean.

 

The full code is now:

<?php
$host = "xxxxx";
$db = "wil0801309563928";
$pass = "xxxxx";

$con = mysql_connect($host, $db, $pass);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db($con);

mysql_select_db($db) or die ("Unable to select database!");

$result = mysql_query("SELECT * FROM smf_members") or die(mysql_error());

$points = ($_POST['points'] == "")  ? 1 : $_POST['point'];
$mode = ($_POST['m'] == "ADD" || $_POST['m'] == "SUB") ? $_POST['m'] : "ADD";
$member_id = mysql_real_escape_string($_POST['memberName']);

  if($mode == "ADD"){ $mode = "+"; } else { $mode = "-"; }


echo "<form action=\"pointsadmin2.php\" method=\"post\">
<table>
<tr>
<th width=\"100\">Member</th>
<th width=\"20\">Points</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td> <input type=\"text\" name=\"memberName\" value=\"" . $row['memberName'] . "\"</td>";
  echo "<td> <input type=\"text\" name=\"points\" value=\"" . $row['points'] . "\"</td>";
  echo "<td> <INPUT TYPE=CHECKBOX NAME=\"m\" value=\"ADD\"></td>";
  echo "<td> <INPUT TYPE=CHECKBOX NAME=\"m\" value=\"SUB\"></td>";
  echo "</tr>";
  }
echo "</table>";
echo "<input type=\"submit\" name=\"submit\">";
echo "</form>";

if (!isset($_POST['submit'])) {
// form not submitted
}
else {
// form submitted

// open connection
$connection = mysql_connect($host, $db, $pass) or die ("Unable to connect!");

// get form input
// check to make sure it's all there
// escape input values for greater safety

mysql_select_db($db) or die ("Unable to select database!");

$query = "UPDATE smf_members SET points = points $mode $points WHERE memberName = 'memberID'";

$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

mysql_close($con);

}
?>

 

Any further thoughts.... thank you so much for your help so far!

[phpSensei]

Ah yes, sorry for that mistake, and thankyou for telling me

 

Np

[jd307]

Does anyone else have any other ideas on how to get this working? 

[jd307]

Thanks for all assistance on this issue.  I was having some problems with the method suggested by phpSensei so I had another thought on how to do this.  I have now come up with a different method which I have now managed to get working.  This involves two additional PHP pages (though probably could have done it by using just the one, but this works for me).

 

I am now using the $_GET method.  On creating the list of users and their associated points, it also generates two text hyperlinks (one to ADD and one to SUBTRACT).  These then pass the members' name and how many points they currently have from the admin page to either the ADD page or the SUB page.  Using the $_GET method, these are used to update the database by adding or subtracting 1 point accordingly.  My final code is as follows.

 

pointsadmin.php

<?php
$con = mysql_connect("h50mysql47.secureserver.net:3306","wil0801309563928","xxxx");
if (!$con)
{
	die('Could not connect: ' . mysql_error());
}

mysql_select_db("wil0801309563928", $con);

$result = mysql_query("SELECT * FROM smf_members");

echo "<table border='1'>
<tr>
<th width=\"200px\">Member</th>
<th width=\"75px\" align=\"center\">Points</th>
<th width=\"30px\"></th>
<th width=\"30px\"></th>
</tr>";

while($row = mysql_fetch_array($result))
{
	echo "<tr>";
	echo "<td>" . $row['memberName'] . "</td>";
	echo "<td align=\"center\">" . $row['points'] . "</td>";
	echo "<td align=\"center\"><a href=\"pointsadd.php?member=" . $row['memberName'] . "&pts=" . $row['points'] . "\"><b>+</b></a>";
	echo "<td align=\"center\"><a href=\"pointssub.php?member=" . $row['memberName'] . "&pts=" . $row['points'] . "\"><b>-</b></a>";
	echo "</tr>";
}
echo "</table>";



mysql_close($con);
?>

 

pointsadd.php:

<?php
$con = mysql_connect("h50mysql47.secureserver.net:3306","wil0801309563928","xxx");
if (!$con)
{
	die('Could not connect: ' . mysql_error());
}

mysql_select_db("wil0801309563928", $con);

$member = $_GET['member'];
$pts = $_GET['pts'];

$newpoints = $pts + 1;

mysql_query("UPDATE smf_members SET points='$newpoints' WHERE memberName='$member'") or die("Failed Query");

echo "<p>ADDING one point for <b>" . $member . "</b>.</p>";
echo $pts ;
echo $newpoints;
echo "<a href=\"pointsadmin.php\">Points Admin</a>";

mysql_close($con);
?>

 

pointssub.php:

<?php
$con = mysql_connect("h50mysql47.secureserver.net:3306","wil0801309563928","xxx");
if (!$con)
{
	die('Could not connect: ' . mysql_error());
}

mysql_select_db("wil0801309563928", $con);

$member = $_GET['member'];
$pts = $_GET['pts'];

$newpoints = $pts - 1;

mysql_query("UPDATE smf_members SET points='$newpoints' WHERE memberName='$member'") or die("Failed Query");

echo "<p>REMOVING one point from <b>" . $member . "</b>.</p>";
echo $pts ;
echo $newpoints;
echo "<a href=\"pointsadmin.php\">Points Admin</a>";

mysql_close($con);
?>

 

I hope this is useful to anyone else in the future that needs to do something like this.