php - mysql admin screen +++ Help Please

[mdcloud]

hey everyone,

thanks for all the help this board is. i am very new to php, but i think it is what i am going to need to use to accomplish what i need to. i hope someone out there can help. i have searched all over with no result.

ok here is what i need. i have a registration page

http://www.beinhealth.com/test/fmlftlsched.html

that links to a form i was able to get linked up to my mysql database. that is all working. now what i need to be able to do is make a screen that a novice computer user like a secretary of BE IN HEALTH could go to and download all that have registered for a certain date. i can log into mysql and export it, but i dont want a secretary in there mucking around ya know. is there a way to use the date drop down in the form to export all people registered for that week?

thanks for any help you can give

brent

:)

[Titan85]

What i would do is make a page that has a place to enter the date in and then queries the database for user registered on that date. I will write up the code real quick and post it in a few minutes.

[Titan85]

ok, here is the code as promised:
<?php

// If search is active
if ($_POST['search']) {
$date = $_POST['date'];

// Get results from database
$users = mysql_query("SELECT * FROM `users` WHERE date = '$date' ORDER BY id DESC") or die ('Error Getting Users! \n<br />\n' .mysql_error());
$chk = mysql_num_rows($users);

// If no users found
if ($chk < 1) {
echo 'There are no users registered on the date: <b>'.$date.'</b>';
}

// If there are users
else {

// Start table to display users
echo '
<h3>Users Registerd On '.$date.'</h3>

<table width="400">
<tr>
<td><b>ID</b></td>
<td><b>Username</b></td>
<td><b>Email</b></td>
</tr>';

// For each user found
while($u = mysql_fetch_array($users)) {

// Show data for each user
echo '
<tr>
<td>'.$u['id'].'</td>
<td>'.$u['username'].'</td>
<td><a href="mailto:'.$u['email'].'">'.$u['email'].'</a></td>
</tr>';
}

// End table
echo '
</table>';
}
}


// If form was not submitted
if (!$_POST['search']) {
echo '
<form method="post" action="">
<b>Date:</b> <input type="text" name="date" />
<input type="submit" name="search" value="Search" />
</form>';
}

?>It will require changing the query to have the table name you are using and it will need your sql connection variables. This script just shows the users registered on a specific date. It is untested, but it should work. Hope it helps :)

EDIT:
You can view a slightly modified version of the script here (http://www.echo-designes.com/testing/downloader/index.php). Just enter 3/22/07 in the date box.

[mdcloud]

hey thanks for the reply. i have been out of town so i just checked on it. i like your example. i am about to play with the code and see what i can get. in addition to a display, is there a way to export a whole table in csv format? might work well for mail merge and such.

thanks

[mdcloud]

please forgive my ignorance, but i do not see where i put in mysql and table information. is there supposed to be a call at the top? when i view souce on your example, all i can see is the form part.

[Titan85]

Do you mean how do you connect to the sql database? If so, what I did was include a file that had the connection data in it. Here is how you connect to a mysql database:
$connect = mysql_connect(database_host, database_user, database_pass);
mysql_select_db(database_name, $connect) or die ("Error selecting database! <br />" .mysql_error());I did some updating to the code, here is the new code:
<?php
require('config.php');

// If search is active
if ($_POST['search']) {
$date = $_POST['date'];

// Get results from database
$users = mysql_query("SELECT * FROM `users` WHERE date = '$date' ORDER BY id DESC") or die ('Error Getting Users! \n<br />\n' .mysql_error());
$chk = mysql_num_rows($users);

// If no users found
if ($chk < 1) {
echo 'There are no users registered on the date: <b>'.$date.'</b>
<br />
<a href="index.php">&lt;&lt;Back</a>';
}

// If there are users
else {

// Start table to display users
echo '
<h3>Users Registerd On '.$date.'</h3>

<table width="500">
<tr>
<td><b>ID</b></td>
<td><b>Username</b></td>
<td><b>Email</b></td>
</tr>';

// For each user found
while($u = mysql_fetch_array($users)) {

// Show data for each user
echo '
<tr>
<td>'.$u['id'].'</td>
<td>'.$u['username'].'</td>
<td>'.$u['email'].'</td>
</tr>';
}

// End table
echo '
</table>';
echo "<a href=\"?act=download&d=$date\">Text File</a>";
}
}


// If form was not submitted
if (!$_POST['search']) {
echo '
<form method="post" action="">
<b>Date:</b> <input type="text" name="date" />
<input type="submit" name="search" value="Search" />
</form>';
}

if ($_GET['act'] == "download") {
require('make_file.php');
}

?>And make_file.php (makes the text that appears in the frame that can be copied):
<?php

$date = $_GET['d'];
$users = mysql_query("SELECT * FROM `users` WHERE date = '$date'") or die ('Error Getting Users! \n<br />\n' .mysql_error());

$add .= 'Users Registered On '.$date.'

';

while($u = mysql_fetch_array($users)) {
$add .= '
User ID: '.$u['id'].'
Username: '.$u['username'].'
Email: '.$u['email'].'

';
}

$file = "registry.txt";
$fp = fopen($file, "w");
fwrite($fp, $add);
fclose($fp);

echo'<meta http-equiv="refresh" content="0;registry.txt" />';

?> In order for the copy friendly text to be made, you must put a file named registry.txt in the same directory as the rest of the script and chmod it to 777 (can be done using an FTP client, just right click and it should have something like "properties").
Hope this helps

[mdcloud]

awsome.... i got it working. thanks so much for your help and patience with a novice like me. i even got it downloading into a delemeted text file by modifying your code. learning is great with helpful teachers. thanks

[Titan85]

Glad to hear you got it working :)

[mdcloud]

hey titan,

thanks for all your help. you are a life saver. i am learning alot in the last few days. i am trying to make one of your other codes work with this one. it is on this thread

http://www.dynamicdrive.com/forums/showthread.php?t=19115

my problem is that i can not get the edit link part to work. here is what i got.

you can see a working example to see errors i am getting for yourself at www.beinhealth.com/form/bih.html and follow the links to search.

i used edit code from above linked thread with this name search code.


<?php
require('link.php');

// If search is active
if ($_POST['search']) {
$date = $_POST['LName'];

// Get results from database
$users = mysql_query("SELECT * FROM `form` WHERE LName = '$date' ORDER BY RegID DESC") or die ('Error Getting Users! \n<br />\n' .mysql_error());
$chk = mysql_num_rows($users);

// If no users found
if ($chk < 1) {
echo 'There are no users registered on the date: <b>'.$date.'</b>
<br />
<a href="bih.html">&lt;&lt;Back</a>';
}

// If there are users
// If there are users
else {

// Start table to display users
echo '
<h3>Users Registerd With Last Name of '.$date.'</h3>

<table border="1">
<tr>
<td></td><td></td>
<td><b>First Name</b></td>
<td><b>Last Name</b></td>
<td><b>Date Attending</b></td>
<td><b>Email</b></td>
<td><b>Phone Number</b></td>
</tr>';

// For each user found
while($u = mysql_fetch_array($users)) {

// Show data for each user
echo '
<tr>
<td><a href="?act=edit&id='/echo $qry['id'];/'>Edit</a></td>
<td>'.$u['RegID'].'</td>
<td>'.$u['FName'].'</td>
<td>'.$u['LName'].'</td>
<td>'.$u['Date'].'</td>
<td>'.$u['EMail'].'</td>
<td>'.$u['Phone'].'</td>
</tr>';
}

// End table
echo '
</table><br><br><a href="bih.html">Back</a>';

}
}


// If form was not submitted
if (!$_POST['search']) {
echo '
<form method="post" action="">
<b>Last Name:</b> <input type="text" name="LName" />
<input type="submit" name="search" value="Search" />
</form><br><br><a href="bih.html">Back</a>';
}
if ($_GET['act'] == 'edit') {
require('edit.php');
}

?>


it is the edit link i can not get to work. i have tried everything that comes to mind with no result. you also mentioned that i could delete records on that other thread using a similar method, can you explain that a bit more? would i use the edit.php coding and change the update line to delete instead? would that work?

thanks

[mdcloud]

ow forgot to say you can search for mcleod and get 3 entries. if i have anything in the edit field though, it either only shows first entry or gives error.

[mdcloud]

i got it!!! well the link part

i used <a href="edit.php?RegID='.$u['RegID'].'">Edit</a> as the link and it seems to work.

still when i run the edit it does not update. here is the code. can you tell me what i have wrong?


<?php
require('link.php');

// Get the user id
$id = $_GET['RegID'];

// Get data from user with the specified id
$info = mysql_query("SELECT * FROM `form` WHERE RegID = '$id'") or die ('Error Getting User Data! <br />' .mysql_error());
$chk = mysql_num_rows($info);
$u = mysql_fetch_array($info);

// If edit not hit
if (!$_POST['edit']) {

// If user id returns no results
if ($chk < 1) {
echo 'The user with the id <b>'.$id.'</b> does not exist!';
}
else {
// Edit Form
?>
<!-- Edit Form -->
<form method="post" action="">
<table width="250">
<tr>
<td>First Name:</td>
<td><input type="text" name="firstname" value="<?php echo $u['FName'] ?>" /></td>
</tr>
<tr>
<td>Last Name:</td>
<td><input type="text" name="lastname" value="<?php echo $u['LName'] ?>" /></td>
</tr>
<tr>
<td>Username:</td>
<td><input type="text" name="EMail" value="<?php echo $u['EMail'] ?>" /></td>
</tr>
<tr>
<td colspan="2"><input type="submit" name="edit" value="Edit" /></td>
</tr>
</table>
</form>
<!-- /Edit Form -->
<?
}

// If edit was hit
if ($_POST['edit']) {
$firstname = $_POST['FName'];
$lastname = $_POST['LName'];
$username = $_POST['EMail'];

// Update data
$update = mysql_query("UPDATE `form` SET FName = '$FName', LName = '$LName', EMail = '$EMail' WHERE RegID = '$id'") or die ('Error Updating Data! <br />' .mysql_error());

echo 'Update successfull';
}
}

?>

[Titan85]

Ok, here is what you would do to add a delete function. The first part is the delete link.
<?php
echo '<a href="index.php?act=chk_del&id='.$qry['id'].'">[X]</a>';

// If delete was hit, ask for confirm
if ($_GET[['act'] == 'chk_del') {
$id = $_GET['id'];
echo 'Are you sure you want to delete this user?
<br />
<a href="index.php?act=delete&id='.$id.'">Yes, delete it</a> | <a href="javascript:history.back();">Cancel</a>';
}

// If delete confirmed
elseif ($_GET['act'] == 'delete') {
$id = $_GET['id'];
$del = mysql_query("DELETE FROM `form` WHERE id ='$id'") or die ('Error Deleting User! <br />' .mysql_error());
echo 'The user has been deleted.
<br />
<a href="index.php">Back To Main</a>';
}

?> It has a confirm delete before it actually deletes.
As for your edit problem, I will take a look when I get home, got to be heading out at the moment.
Hope this helps

[mdcloud]

i have just tried your code above. it runs through fine, but doesnt seem to do anything. i still have the record i was deleting. see for yourself at www.beinhealth.com/form/bih.html and then click on search by last name. enter mcleod in there. you can click on edit or delete there. neither give errors, just done do the function. i am stumped

[mdcloud]

here is the code i am using for all of three pages incase it is any one of them.

first the search one

<?php
require('link.php');

// If search is active
if ($_POST['search']) {
$date = $_POST['LName'];

// Get results from database
$users = mysql_query("SELECT * FROM `form` WHERE LName = '$date' ORDER BY RegID DESC") or die ('Error Getting Users! \n<br />\n' .mysql_error());
$chk = mysql_num_rows($users);

// If no users found
if ($chk < 1) {
echo 'There are no users registered on the date: <b>'.$date.'</b>
<br />
<a href="bih.html">&lt;&lt;Back</a>';
}

// If there are users
// If there are users
else {

// Start table to display users
echo '
<h3>Users Registerd With Last Name of '.$date.'</h3>

<table border="1">
<tr>
<td></td><td></td>
<td><b>First Name</b></td>
<td><b>Last Name</b></td>
<td><b>Date Attending</b></td>
<td><b>Email</b></td>
<td><b>Phone Number</b></td>
</tr>';

// For each user found
while($u = mysql_fetch_array($users)) {

// Show data for each user
echo '
<tr>
<td><a href="edit.php?RegID='.$u['RegID'].'">Edit</a></td>
<td><a href="delete.php?act=chk_del&id='.$qry['RegID'].'">Delete</a></td>
<td>'.$u['RegID'].'</td>
<td>'.$u['FName'].'</td>
<td>'.$u['LName'].'</td>
<td>'.$u['Date'].'</td>
<td>'.$u['EMail'].'</td>
<td>'.$u['Phone'].'</td>
</tr>';
}

// End table
echo '
</table><br><br><a href="bih.html">Back</a>';

}
}


// If form was not submitted
if (!$_POST['search']) {
echo '
<form method="post" action="">
<b>Last Name:</b> <input type="text" name="LName" />
<input type="submit" name="search" value="Search" />
</form><br><br><a href="bih.html">Back</a>';
}
if ($_GET['act'] == 'edit') {
require('edit.php');
}

?>


next the edit one:

<?php
require('link.php');

// Get the user id
$RegID = $_GET['RegID'];

// Get data from user with the specified id
$info = mysql_query("SELECT * FROM `form` WHERE RegID = '$RegID'") or die ('Error Getting User Data! <br />' .mysql_error());
$chk = mysql_num_rows($info);
$u = mysql_fetch_array($info);

// If edit not hit
if (!$_POST['edit']) {

// If user id returns no results
if ($chk < 1) {
echo 'The user with the id <b>'.$RegID.'</b> does not exist!';
}
else {
// Edit Form
?>
<!-- Edit Form -->
<form method="post" action="">
<table width="250">
<tr>
<td>First Name:</td>
<td><input type="text" name="firstname" value="<?php echo $u['FName'] ?>" /></td>
</tr>
<tr>
<td>Last Name:</td>
<td><input type="text" name="lastname" value="<?php echo $u['LName'] ?>" /></td>
</tr>
<tr>
<td>Username:</td>
<td><input type="text" name="EMail" value="<?php echo $u['EMail'] ?>" /></td>
</tr>
<tr>
<td colspan="2"><input type="submit" name="edit" value="Edit" /></td>
</tr>
</table>
</form>
<!-- /Edit Form -->
<?
}

// If edit was hit
if ($_POST['edit']) {
$firstname = $_POST['FName'];
$lastname = $_POST['LName'];
$username = $_POST['EMail'];

// Update data
$update = mysql_query("UPDATE `form` SET FName = '$FName', LName = '$LName', EMail = '$EMail' WHERE RegID = '$RegID'") or die ('Error Updating Data! <br />' .mysql_error());

echo 'Update successfull
<br />
<a href="bih.html">Back To Main</a>';
}
}

?>

last the delete one:


<?php
require('link.php');

// If delete was hit, ask for confirm
if ($_GET['act'] == 'chk_del') {
$RegID = $_GET['RegID'];
echo 'Are you sure you want to delete this user?
<br />
<a href="delete.php?act=delete&RegID='.$RegID.'">Yes, delete it</a> | <a href="javascript:history.back();">Cancel</a>';
}

// If delete confirmed
elseif ($_GET['act'] == 'delete') {
$RegID = $_GET['RegID'];
$del = mysql_query("DELETE FROM `form` WHERE RegID ='$RegID'") or die ('Error Deleting User! <br />' .mysql_error());
echo 'The user has been deleted.
<br />
<a href="bih.html">Back To Main</a>';
}

?>

man sorry for all the trouble i am causing you. i really appreciate the help though.

[mdcloud]

ok, figured out the edit part. had a ] at the end instead of at the end of the first if (edit) statment. now working in delete.

[Titan85]

What you need to do is change this:
<a href="delete.php?act=delete&id='.$RegID.'">To this:
<a href="delete.php?act=delete&RegID='.$RegID.'">In your main page that displays the delete link and it should work.

Hope this helps out.

Also, if you don't mind, could you tell me how you got it to force the download of the text file rather than just display it? I would really like to learn that. Thanks

[mdcloud]

i will give that a try. here is what i used to export. it is your code that i just tweeked a bit. mostly changing the document type to attachment.


<?php
require('link.php');

// If search is active
if ($_POST['search']) {
$date = $_POST['date'];

// Get results from database
$users = mysql_query("SELECT * FROM `form` WHERE Date = '$date' ORDER BY RegID DESC") or die ('Error Getting Users! \n<br />\n' .mysql_error());
$chk = mysql_num_rows($users);

// If no users found
if ($chk < 1) {
echo 'There are no users registered on the date: <b>'.$date.'</b>
<br />
<a href="bih.html">&lt;&lt;Back</a>';
}

// If there are users
else {

// Set header to download users
header("Content-type: text/plain");
header("Content-disposition: attachment; filename=Registered.TXT");

// For each user found
while($u = mysql_fetch_array($users)) {

// Show data for each user
echo $u['FName'].'|'.$u['LName'].'|'.$u['EMail'].'|'.$u['Phone'].'|'.$u['Address'].'|'.$u['City'].'|'.$u['State'].'|'.$u['Zip'].'|'.$u['OState'].'|'.$u['Country'].'|'.$u['DOB'].'|'.$u['Occ'].'|'.$u['Meds'].'|'.$u['Married'].'|'.$u['Accom'].'|'.$u['ReadAMEW'].'|'.$u['ListenTapes'].'|'.$u['AttendSeminar'].'|'.$u['AttendWhere'].'|'.$u['Knowledge'].'|'.$u['KnowledgeHow'].'|'.$u['Issues1'].'|'.$u['Issues2'].'|'.$u['Issues3'].'|'.$u['Issues4'].'|'.$u['Comments']."\n";
}

// End table

}
}


// download on click
if (!$_POST['search']) {
echo ' <center><h2>Download Program Signups.</h2></center><br><br>
<form method="post" action="">
<b>Date:</b> <input type="text" name="Date" value="">
<input type="submit" name="search" value="Search" />
</form><br><br><br><a href="bih.html">&lt;&lt;Back</a>';
}


?>

hope that helps you. you have been a huge help. much appreciation.

[mdcloud]

still not deleting though. i am going over the code hoping to find it. if you have any other ideas..... would be much appreciated.

[mdcloud]

Never mind... i got it. bah it is always the little things.

thanks for your help. you have saved me a ton of head scratching

brent

[Titan85]

Thanks for showing me how to make it download. Glad to hear you got it working. Was it just the link like I said or something else?

[mdcloud]

yea i had to change it to <a href="edit.php?RegID='.$u['RegID'].'">Edit</a>

guess it didnt like .qry

[Titan85]

ok, glad its working correctly now :)

[chubcity]

Hi mdcloud,

those edit page and delete page u showed on page 2 are not working.

Would you mind posting the working edit page and delete page codes here?

Thanks alot.

[thetestingsite]

What isn't working on it? Do you get an error message or anything like that? The code that was posted should work (syntax wise).

[chubcity]

It wont update and delete..

here is the main page:

<title>Supplier Projects</title>
<?php

require('../Connections/config.php');
require('../Connections/opendb.php');

$result = mysql_query( "SELECT * FROM supplier_projects ORDER BY Supplier ASC " )
or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
print "There are $num_rows records.<P>";

if ($num_rows < 1) {
echo 'There are no products in the database
<br />
<a href="option.php">&lt;&lt;Back</a>';
}

else {

// Start table to display users
echo '


<table border="1" width"800">
<tr>
<td></td><td></td>
<td><b>Supplier</b></td>
<td><b>Project</b></td>
<td><b>Project Name</b></td>
<td><b>Project Code</b></td>
<td><b>sp_pri</b></td>
</tr>';

// For each user found
while($u = mysql_fetch_array($result)) {

// Show data for each user
echo '
<tr>
<td><a href="updateproject2.php?sp_pri='.$u['sp_pri'].'">Edit</a></td>
<td><a href="deleteproject1.php?act=chk_del&id='.$u['sp_pri'].'">Delete</a></td>
<td>'.$u['Supplier'].'</td>
<td>'.$u['Project'].'</td>
<td>'.$u['Project_Name'].'</td>
<td>'.$u['Project_Code'].'</td>
<td>'.$u['sp_pri'].'</td>

</tr>';
}

// End table
echo '
<form method="POST" action="insertproject1.php">
<input type="submit" value="Insert New">
</form>
</table><br><br><a href="bih.html">Back</a>';

}





?>

here is the edit page:

<?php
include('../Connections/config.php');
include('../Connections/opendb.php');


// Get the user id
$getid = $_GET['sp_pri'];
echo $getid;

// Get data from user with the specified id
$query="SELECT * FROM supplier_projects WHERE sp_pri ='$getid'";
$result=mysql_query($query) or die ('Error Getting User Data! <br />' .mysql_error());
$chk = mysql_num_rows($result);
$u = mysql_fetch_array($result);

// If edit not hit
if (!$_POST['edit']) {

// If user id returns no results
if ($chk < 1) {
echo 'The supplier project does not exist!';
}
else {
// Edit Form
?>
<!-- Edit Form --><title>Update Project</title>
<form method="post" action="">
<table width="250">
<tr>
<td>Supplier:</td>
<td><input type="text" name="supplier" value="<?php echo $u['Supplier'] ?>" /></td>
</tr>
<tr>
<td>Project:</td>
<td><input type="text" name="project" value="<?php echo $u['Project'] ?>" size="50"/></td>
</tr>
<tr>
<td>Project Name:</td>
<td><input type="text" name="projectname" value="<?php echo $u['Project_Name'] ?>" size="100"/></td>
<tr>
<td>Project Code:</td>
<td><input type="text" name="projectcode" value="<?php echo $u['Project_Code'] ?>" /></td>
</tr>

<tr>
<td colspan="2"><input type="submit" name="edit" value="Update" /></td>
</tr>
</table>
</form>
<!-- /Edit Form -->
<?php
}

// If edit was hit
if ($_POST['edit']) {
$supp = $_POST['Supplier'];
$proj = $_POST['Project'];
$projname = $_POST['Project_Name'];
$projcode = $_POST['Project_Code'];

// Update data
$update = mysql_query("UPDATE supplier_projects SET sp_pri = '$supp$proj', Supplier='$supp', Project = 'proj', Project_Name = 'projname',Project_Code = 'projcode' WHERE sp_pri = '$getid'") or die ('Error Updating Data! <br />' .mysql_error());

echo 'Update successfull';

}
}

?>

here is the delete page:

<title>Delete Project</title>
<?php
include('../Connections/config.php');
include('../Connections/opendb.php');

// If delete was hit, ask for confirm
if ($_GET['act'] == 'chk_del') {
$getid = $_GET['sp_pri'];
echo 'Are you sure you want to delete this record?
<br />
<a href="deleteproject1.php?act=delete&sp_pri='.$getid.'">Yes, delete it</a> | <a href="javascript:history.back();">Cancel</a>';
}

// If delete confirmed
elseif ($_GET['act'] == 'delete') {
$getid = $_GET['sp_pri'];
$del = mysql_query("DELETE FROM supplier_projects WHERE sp_pri='$getid'") or die ('Error Deleting User! <br />' .mysql_error());
echo 'The record has been deleted.
<br />
<form method="POST" action="editproject2.php">

<input type="submit" value="Back to Project Forms">
</form>';
}

?>

Please help me. Thanks a lot !

[chubcity]

the delete is working, now working on edit :)

[chubcity]

finally fixed the edit problem too..
dam small mistake..

thanks everyone :)

[mdcloud]

sorry chubcity,

just looked back at this thread. would have posted earlier if i saw it.