Changing Data In MySQL Through PHP

[tomyknoker]

Thanks I didin't even see your post until now!

[tomyknoker]

Heya... Hmm it through out an error on this line

<td>Edit</td> 

[thetestingsite]

The only thing that I could see that would cause an error would be this line:

<div id=\"edit_".$column['id']."\" style="display: none;">

this should be like so:

<div id=\"edit_".$column['id']."\" style=\"display: none;\">

Hope this helps

[tomyknoker]

Strange it still spits out the same error on line 149...

[thetestingsite]

Well, the code I posted only goes has 122 lines. Please post the code that you have so that we can see what line is (and what is around line) 149 that could be causing this error.

[tomyknoker]

Oh no really? I must have done something wrong then... OK here it it

<?php
$cat = $_GET['cat'];

/* connect to the mysql database and use a query to get the members info */

include 'library/config.php';
include 'library/opendb.php';

//navigation
include("nav.php");

//approved
$a = mysql_query("SELECT * FROM `tblmembers` WHERE `MemberApproved`='A'");
$aCount = mysql_num_rows($a);

//deleted
$d = mysql_query("SELECT * FROM `tblmembers` WHERE `MemberApproved`='D'");
$dCount = mysql_num_rows($d);

//on hold
$h = mysql_query("SELECT * FROM `tblmembers` WHERE `MemberApproved`='H'");
$hCount = mysql_num_rows($h);

//pending
$p = mysql_query("SELECT * FROM `tblmembers` WHERE `MemberApproved`='P'");
$pCount = mysql_num_rows($p);

//not sure
$n = mysql_query("SELECT * FROM `tblmembers` WHERE `MemberApproved`='N'");
$nCount = mysql_num_rows($n);

//rejected
$r = mysql_query("SELECT * FROM `tblmembers` WHERE `MemberApproved`='R'");
$rCount = mysql_num_rows($r);

if ($cat == "a") {
$field = "Approved";
$theCount = $aCount;
}

elseif ($cat == "d") {
$field = "Deleted";
$theCount = $dCount;
}

elseif ($cat == "h") {
$field = "On Hold";
$theCount = $hCount;
}

elseif ($cat == "p") {
$field = "Pending";
$theCount = $pCount;
}

elseif ($cat == "n") {
$field = "Not Sure";
$theCount = $nCount;
}

elseif ($cat == "r") {
$field = "Rejected";
$theCount = $rCount;
}

echo 'There are '.$theCount.' members that are '.$field;

/* set the allowed order by columns */
$default_sort = 'LastName';
$allowed_order = array ('JoinDate', 'FirstName','LastName', 'loginDateTime');

$allowed_display = array('FirstName', 'LastName', 'PhoneNumber', 'MobileNumber', 'Email', 'City', 'State', 'JoinDate', 'MemberApproved', 'loginDateTime');

/* if order is not set, or it is not in the allowed
 * list, then set it to a default value. Otherwise, 
 * set it to what was passed in. */
if (!isset ($_GET['order']) || 
    !in_array ($_GET['order'], $allowed_order)) {
    $order = $default_sort;
} else {
    $order = $_GET['order'];
}


if ($_REQUEST['act'] == "del") {

$del = $_REQUEST['del'];
 
mysql_query("UPDATE `tblmembers` SET `status`='D' WHERE `userID`='$del'");


header('Location: '.$_SERVER["PHP_SELF"]);
}

else {



/* construct and run our query */
$query = "SELECT * FROM tblmembers WHERE `MemberApproved`='$cat' ORDER BY $order";

$result = mysql_query ($query);

/* make sure data was retrieved */
$numrows = mysql_num_rows($result);
if ($numrows == 0) {
    echo "No data to display!";
    exit;
}

/* now grab the first row and start the table */
$row = mysql_fetch_assoc ($result);

echo '<script type="text/javascript">
function showForm(id) {

obj = document.getElementById(\'edit_\'+id);

 if (obj.style.display == "none") {
     obj.style.display = "";
 }

 else {
     obj.style.display = "none";
 }
}
</script>
';

echo "<TABLE border=1>\n";
echo "<TR>\n";

foreach ($row as $heading=>$column) {
    /* check if the heading is in our allowed_order
     * array. If it is, hyperlink it so that we can
     * order by this column */
    echo "<TD><b>";

 if (in_array($heading, $allowed_display)) {
    if (in_array ($heading, $allowed_order)) {
        echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading&cat=$cat\">$heading</a>";
    } else {
        echo $heading;
    }                
    echo "</b></TD>\n";
 }
}

<td>Edit</td>
<td>Delete</td>

echo "</TR>\n";

/* reset the $result set back to the first row and 
 * display the data */

mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
    echo "<TR>\n";
    foreach ($row as $column) {
        echo "<TD>$column</TD>
<td><a href=\"#\" onclick=\"showForm('".$column['id']."'); return false;\">Edit</a>

<div id=\"edit_".$column['id']."\" style=\"display: none;\">
<!--status form-->

<form method=\"POST\" action=\"{$_SERVER['PHP_SELF']}\">
<input type=\"hidden\" name=\"act\" value=\"edit\">
<input type=\"hidden\" name=\"id\" value=\"".$column['id']."\">

<select name=\"status\">
<option value=\"A\">Approved</option>
<!--enter options for status menu-->
</select>

</div>

</td>

<td><a href=\"{$_SERVER['PHP_SELF']}?del=".$column['id']."&act=del\">Delete User</a></td>\n";
    }
    echo "</TR>\n";
}
echo "</TABLE>\n";

}
?>

[thetestingsite]

Try changing:

<td>Edit</td>
<td>Delete</td>

to:

echo "<td>Edit</td>";
echo "<td>Delete</td>";

That should fix the error. Hope this helps.

[tomyknoker]

Cool thatworked, but it's gone a little crazy. It still displays every single column in the 'tblmembers' table... And also next to every single column it adds the 'Edit' and 'Delete' link... When I clicked on the 'Edit' link it only gave me the option of 'Approved'.... I PM'ed you a little snapshot, I thought it might give you a better understanding of what is happening...

[thetestingsite]

After looking at the screenshot, I figured it was time to rewrite the eintire code. Probably not as effective as some would write it, but still does what you want it to do. Below is the code:

<?php
$cat = $_GET['cat'];

/* connect to the mysql database and use a query to get the members info */

include 'library/config.php';
include 'library/opendb.php';

//navigation
include("nav.php");

//approved
$aCount = mysql_query("SELECT COUNT(*) FROM `tblmembers` WHERE `MemberApproved`='A'");

//deleted
$dCount = mysql_query("SELECT COUNT(*) FROM `tblmembers` WHERE `MemberApproved`='D'");

//on hold
$hCount = mysql_query("SELECT COUNT(*) FROM `tblmembers` WHERE `MemberApproved`='H'");

//pending
$pCount = mysql_query("SELECT COUNT(*) FROM `tblmembers` WHERE `MemberApproved`='P'");

//not sure
$nCount = mysql_query("SELECT COUNT(*) FROM `tblmembers` WHERE `MemberApproved`='N'");

//rejected
$rCount = mysql_query("SELECT COUNT(*) FROM `tblmembers` WHERE `MemberApproved`='R'");

if ($cat == "a") {
$field = "Approved";
$theCount = $aCount;
}

elseif ($cat == "d") {
$field = "Deleted";
$theCount = $dCount;
}

elseif ($cat == "h") {
$field = "On Hold";
$theCount = $hCount;
}

elseif ($cat == "p") {
$field = "Pending";
$theCount = $pCount;
}

elseif ($cat == "n") {
$field = "Not Sure";
$theCount = $nCount;
}

elseif ($cat == "r") {
$field = "Rejected";
$theCount = $rCount;
}

echo 'There are '.$theCount.' members that are '.$field;

/* if order is not set, or it is not in the allowed
 * list, then set it to a default value. Otherwise, 
 * set it to what was passed in. */

if (empty($_GET['order'])) {
$order = 'LastName';
}
else {
$order = $_GET['order'];
}

  if ($_REQUEST['act'] == "del") {

$del = $_REQUEST['del'];
 
mysql_query("UPDATE `tblmembers` SET `status`='D' WHERE `userID`='$del'");


header('Location: '.$_SERVER["PHP_SELF"]);
  }

  else {



/* construct and run our query */
$query = "SELECT * FROM tblmembers WHERE `MemberApproved`='$cat' ORDER BY `$order`";

$result = mysql_query ($query);

/* make sure data was retrieved */
$numrows = mysql_num_rows($result);
if ($numrows == 0) {
    echo "No data to display!";
    exit;
}


echo '<script type="text/javascript">
function showForm(id) {

obj = document.getElementById(\'edit_\'+id);

 if (obj.style.display == "none") {
     obj.style.display = "";
 }

 else {
     obj.style.display = "none";
 }
}
</script>


<table border="1">
 <tr>
  <td> &nbsp;</td>
  <td> &nbsp;</td>
  <td><a href="?order=FirstName">FirstName</a></td>
  <td><a href="?order=LastName">LastName</a></td>
  <td><a href="?order=PhoneNumber">PhoneNumber</a></td>
  <td><a href="?order=MobileNumber">MobileNumber</a></td>
  <td><a href="?order=Email">Email</a></td>
  <td><a href="?order=City">City</a></td>
  <td><a href="?order=State">State</a></td>
  <td><a href="?order=JoinDate">JoinDate</a></td>
  <td><a href="?order=MemberApproved">MemberApproved</a></td>
  <td><a href="?order=loginDateTime">loginDateTime</a></td>
 </tr>';

/* Now display the data */
 while ($q = mysql_fetch_array($result)) {
?>
 <tr>
   <td><a href="#" onclick="showForm(' <?php echo $q['id'];?>'); return false;">Edit</a>

<div id="edit_<?php echo $q['id'];?>" style="display: none;">
<!--status form-->

<form method="POST" action="<?php echo $_SERVER['PHP_SELF'];?>">
<input type="hidden" name="act" value="edit">
<input type="hidden" name="id" value="<?php echo $q['id'];?>">

<select name="status">
<option value="A">Approved</option>
<!--enter options for status menu-->
</select>

</div>
   </td>

   <td><a href="<?php echo $_SERVER['PHP_SELF'];?>?del=<?php echo $q['id'];?>&act=del">Delete User</a></td>
  <td><?=$q['FirstName'];?></td>
  <td><?=$q['LastName'];?></td>
  <td><?=$q['PhoneNumber'];?></td>
  <td><?=$q['MobileNumber'];?></td>
  <td><?=$q['Email'];?></td>
  <td><?=$q['City'];?></td>
  <td><?=$q['State'];?></td>
  <td><?php echo date('d-m-Y', strtotime($q["JoinDate"]));?></td>
  <td><?=$q['MemberApproved'];?></td>
  <td><?php echo date('d-m-Y', $q['loginDateTime']);?></td>
 </tr>
<?php
 }
 
echo '</table>';
}
?>

As for the other thing (the edit only showing "Approved"), you have to add the other itemds to the select box (where I put <!--enter options for status menu-->).

Hope this helps,

[tomyknoker]

Thanks again, doesn't like this line although I can't understand why

  <td><?=$q['Email';?></td>