Making the list to be in alphabetical order

Chrisj · July 12, 2009

This php page called member_credits.php allows the admin to edit the credits on account of the member. It automatically adds the registered member's user name to the list, but it adds the member in order of who registers next. When this list gets long it will be hard to search for members to edit their credits, if needed. Is there a way to add some code so that members are listed (or sorted) alphabetically? Thanks.

<?php

/// - Database Information
$dbhost = 'localhost';
$dbuser = 'dbase_user';
$dbpass = 'xxxxx';
$dbname = 'dbase_name';

/// - Do Not Edit Below This Line

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

mysql_select_db($dbname);

/////////////////////////////////////////////////////////////////////////////////////////

$sql = "SELECT * FROM credits ORDER BY user_id";
$query = mysql_query($sql, $conn) or DIE(mysql_error());


////////////////////////////////////////////////////////////////////////////////////////
//   FUNCTIONS
////////////////////////////////////////////////////////////////////////////////////////

function getUsername($id) {

$sql1 = "SELECT * FROM member_profile WHERE user_id = $id";
$query1 = mysql_query($sql1) or DIE(mysql_error());
$result = mysql_fetch_array($query1);

return $result['user_name'];
}

////////////////////////////////////////////////////////////////////////////////////////
// Edit Members Credits
////////////////////////////////////////////////////////////////////////////////////////

// edit members credits
$uid = $_GET['id'];
if (isset($uid)) {

$sql = "SELECT total_credits FROM credits WHERE user_id = $uid";
$return = mysql_fetch_array(mysql_query($sql));

echo "<center><h1>Update ".getUsername($uid)."'s Credits</h1>";
echo "<form action=\"member_credits.php\" method=\"post\">";
echo "<input type=\"hidden\" name=\"user_id\" value=\"{$uid}\">";
echo "<input type=\"text\" name=\"new_credits\" value=\"{$return['total_credits']}\">";
echo "<input type=\"submit\" name=\"submitted\" value=\"Update\"></form></center>";
die();

}


// Member Updated
$update = $_POST['new_credits'];
if (isset($update)) {

$user = $_POST['user_id'];
$sql = "UPDATE credits SET total_credits=$update WHERE user_id = $user";
$query = mysql_query($sql) or DIE(mysql_error());

if ($query) {

	echo "<center>Success! ".getUsername($user)." has been updated.";
	echo "Click <a href=\"member_credits.php\">HERE</a> to return to list</center>";
	die();
}else{
	echo "There was a problem.";
	die();
}
}


?>
<html>
<head>
<title>Members Credits</title>
</head>
<body>
<center>
<H1>Members Credits</h1>
<table border="1" bordercolor="#000000">
<tr>
   <td><b>user</b></td>
   <td><b>credits</b></td>
   <td><b>edit</b></td>
</tr>
<?
while($row = mysql_fetch_array($query)) {
echo "<tr><td>".getUsername($row['user_id'])."</td>";
echo "	<td>".$row['total_credits']."</td>";
echo "	<td><a href=member_credits.php?id=".$row['user_id'].">Edit Credits</a></td>";
echo "</tr>";
}
?>
</table>
</center>
</body>
</html>
<?
mysql_close($conn);
?>

wildteen88 · July 12, 2009

You can assign the order in your SQL query

$sql = "SELECT * FROM credits ORDER BY user_name ASC";

Chrisj · July 13, 2009

Thanks for your reply.

Do I now go to phpmyadmin > My database > go to members_credit table and choose SQL tab and enter:

$sql = "SELECT * FROM credits ORDER BY user_name ASC";

And then select "Go"?

Or is there more to it?

Thanks.

wildteen88 · July 13, 2009

What! I meant you need to edit your code. phpMyAdmin has nothing to do with it.

You need to edit this line in your code:

$sql = "SELECT * FROM credits ORDER BY user_id";

Change that line to

$sql = "SELECT * FROM credits ORDER BY user_name";

Change user_name to the actual field name that holds the username's in your credits table.

Chrisj · July 14, 2009

Thanks for that clarification. I will change the line of code, as per your instructions, thanks.

However, there is no "actual field name that holds the username's in your credits table". In member_profile there is "user_name". Being I'm not so versed in phpadmin, can you tell me how/where to add user_name to the credits table, please?

Thanks again.

Psycho · July 14, 2009

Looks like we have a failure to communicate. Wildteen gave you the answer. There is nothing for you to do in phpmyadmin. Just change the query in your script as he suggested and the records will be ordered accordingly. The query currently orders by the ID which - surprise, surprise - is the order in which they were added. You just need to change that to order the results by the name field.

But, there is another issue as well which is absolute terrrible programming. The script first does a query of the user table to get all the data for all users. Then when displaying the results another query is run for each and every record to get the user name from the same table that the original records were retrieved from (which, by the way, queries all the data, but only uses one field). You already have all the data from the first query, why on earth would you want to run another query for each and every record?! Run ONE query and only request the data you will actually use.

Psycho · July 14, 2009

Ok, my mistake, the data is coming from two different tables. Still, only one query is needed. I will post some code in a moment.

Psycho · July 14, 2009

Give the following a try. This should display the records in order of the user name, let you update multiple records at a time, and does not use DIE to create invalid pages.

Not tested, so there may be some syntax errors.

<?php

/// - Database Information
$dbhost = 'localhost';
$dbuser = 'dbase_user';
$dbpass = 'xxxxx';
$dbname = 'dbase_name';

// Make database connection
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname);

// Member Updated
if (isset($_POST))
{
    $success = 0;
    $fail = 0;

    foreach ($_POST['credits'] as $user_id => $new_credits)
    {
        if ($new_credits!='')
        {
            $user_id = mysql_real_escape_string(trim($_POST['user_id']));
            $new_credits = mysql_real_escape_string(trim($_POST['new_credits']));
            $query = "UPDATE credits SET total_credits={$new_credits} WHERE user_id = {$user_id}";
            $result = mysql_query($query);
            if (mysql_query($query)) {
                $success++;
            } else {
                $fail++;
            }
        }
    }
    $status = "A total of " . ($success+$fail) . " records were processed with {$fail} failures.";
}


//Query for list of members
$query = "SELECT c.user_id, c.total_credits, mp.user_name
          FROM credits c
          JOIN member_profile mp ON c.user_id = mp.user_id
          ORDER BY mp.user_name";
$result = mysql_query($query, $conn) or DIE(mysql_error());

?>
<html>
<head>
<title>Members Credits</title>
</head>
<body>
<center>
<div><?php echo $status; ?></div>
<br />
<H1>Members Credits</h1>
<form name="updateCredits" action="" method="POST">
<table border="1" bordercolor="#000000">
<tr>
   <th><b>user</b></th>
   <th><b>credits</b></th>
   <th><b>new credits</b></th>
</tr>
<?php
    while($row = mysql_fetch_array($result)) {
        echo "<tr>\n";
        echo "	<td>{$row['user_name']}</td>\n";
        echo "	<td>{$row['total_credits']}</td>\n";
        echo "	<td><input type=\"text\' name=\"credits['{$row['user_id']}']\" value=\"\" /></td>\n";
        echo "</tr>\n";
    }
?>
</table>
<button type="submit">Update Credits</button>
</form>
</center>
</body>
</html>
<?
mysql_close($conn);
?>

Chrisj · July 14, 2009

Thanks for all of the replies.

I can't replace my original page of code with your suggested page of code. I wouldn't know where the syntax is incorrect, or how to fix it.

Regarding this line of code:

 $sql = "SELECT * FROM credits ORDER BY user_name ASC";

I replaced it where instructed, refreshed the page and the users were not in alphabetical order.

Any other help will be appreciated.

Psycho · July 14, 2009

I can't replace my original page of code with your suggested page of code. I wouldn't know where the syntax is incorrect, or how to fix it.

Did you at least try it? There might not be any errors and it might work fine. Or, if there are errors we can help resolve them.

Regarding this line of code:
 $sql = "SELECT * FROM credits ORDER BY user_name ASC";
I replaced it where instructed, refreshed the page and the users were not in alphabetical order.

Right, wildteen88 and I both thought that the name was in that same table. You would need to do a JOIN of that query. But, if you do a JOIN you would also want to "fix" the code which displays the results since it does a query for each record to get the name which wouldn't be needed any more.

Give the code I posted a try and respond back with the results.

Psycho · July 14, 2009

OK, I went ahead and created a couple of test tables and debugged the code. The following should work for you.

<?php

/// - Database Information
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'cdcol';

// Make database connection
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname);

$status = ' ';

// Member Updated
if (isset($_POST['credits']) && is_array($_POST['credits']) && count($_POST['credits'])>0)
{
    $success = 0;
    $fail = 0;

    foreach ($_POST['credits'] as $user_id => $new_credits)
    {

        if ($new_credits!='')
        {

            $user_id = mysql_real_escape_string(trim($user_id));
            $new_credits = mysql_real_escape_string(trim($new_credits));
            $query = "UPDATE credits SET total_credits={$new_credits} WHERE user_id = {$user_id}";
            //$result = mysql_query($query);
            if (mysql_query($query)) {
                $success++;
            } else {
                $fail++;
            }
        }
        if (($success+$fail)>0) {
            $status = "A total of " . ($success+$fail) . " records were processed with {$fail} failures.";
        }
}
}

//Query for list of members
$query = "SELECT c.user_id, c.total_credits, mp.user_name
          FROM credits c
          JOIN member_profile mp ON c.user_id = mp.user_id
          ORDER BY mp.user_name";
$result = mysql_query($query, $conn) or DIE(mysql_error());

$records = '';
while($row = mysql_fetch_array($result)) {
    $records .= "<tr>\n";
    $records .= "	<td>{$row['user_name']}</td>\n";
    $records .= "	<td align=\"center\">{$row['total_credits']}</td>\n";
    $records .= "	<td align=\"center\"><input type=\"text\" name=\"credits[{$row['user_id']}]\" style=\"width:50px;\" /></td>\n";
    $records .= "</tr>\n";
}

?>
<html>
<head>
<title>Members Credits</title>
</head>
<body>
<center>
<div><?php echo $status; ?></div>
<br />
<H1>Members Credits</h1>
<form name="updateCredits" action="" method="POST">
<table border="1" bordercolor="#000000">
<tr>
   <th><b>user</b></th>
   <th align="center"><b>credits</b></th>
   <th align="center"><b>new credits</b></th>
</tr>
<?php echo $records; ?>
</table>
<button type="submit">Update Credits</button>
</form>
</center>
</body>
</html>
<?
mysql_close($conn);
?>

Sign In

Making the list to be in alphabetical order

Recommended Posts

Chrisj

Link to comment

Share on other sites

wildteen88

Link to comment

Share on other sites

Chrisj

Link to comment

Share on other sites

wildteen88

Link to comment

Share on other sites

Chrisj

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Chrisj

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information