Jump to content

php/sql


Recommended Posts

I have a script here, I am working on an approval/decline system now.The idea is, whenever someone enters something new into the database, it does a bit, it gives the file name, a name, it does a url to the file, whatever. I have a whole system set up for that, now I have an admin page to decline approve new entries, I have a place in the db under the proper table called approval it's default is 0. I am having trouble doing 2 things, I have queried it, but I am having trouble making it work, I also need to know, how do I make new entries be tied into some new system to delete the data when it is declined. Because there will be new ones appearing here each time, I don't know how it will discern which entries are which, all I know is that it will pull up all entries with 0, I want if he accepts 1 it changes to 1, if he declines, it deletes the information, and deletes the file location as well. What I need to figure out though is how to get the new entries, to where they are tied in with whatever I use for decline/approve, I also need to know how to get this query to work properly.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /><title>Approval</title></head><body><h3>Welcome Bobby</h3><p>These are recently pending database entries, please accept/decline.  Please note declined entries are automatically permanently deleted from the database.</p><?php $connect = mysql_connect("localhost", "######", "######");$select = mysql_select_db("funnyemailforwards");$errorhandler = "";$management = true;if (!$connect || !$select) {	$errorhandler = "The database could not connect, or was not selected<br />";	$management = false;}		if ($management == true) {	$query = mysql_query("SELECT * FROM `fileinfo` WHERE approval = '0'";);	if (mysql_num_rows($query)) {	while($row = mysql_fetch_assoc($query)) {            extract($row);	}			}	}		if ($errorhandler != "") {	echo "$errorhandler";	$management = false;}?></body></html>

The first thing is building the general query, the second, is I know I am going to make it output a form for each entry, but I don't know how to make it work only with the entrys it supposed to, seeing that every entry will be different I Don't understand the general theory behidn this part. And about the query, am I atleast on the right track?

Link to comment
Share on other sites

You will probably want to get the list of all entries that are not approved, and loop through them. For each one, you will want to show approve/reject radio buttons, maybe the name or something or a description, and a link to the file so that he can see it before deciding. I think I've mentioned before that you will need a unique ID number for each entry, or some other primary key, but a numeric ID works well. So the loop would look something like this:

$result = mysql_query("SELECT * FROM fileinfo WHERE approval=0 ORDER BY date_added ASC");while ($row = mysql_fetch_assoc($result)){  echo "<tr>";  echo "<td><input type=\"radio\" name=\"approve_{$row['id']}\" value=\"0\">Decline</td>";  echo "<td><input type=\"radio\" name=\"approve_{$row['id']}\" value=\"1\">Approve</td>";  echo "<td>" . format_date . "</td>";  echo "<td>" . link_to_file . "</td>";  echo "</tr>";  }

When they press submit, you will do another query to the database for all entries with approval=0, and loop through the result again. For each entry, you will use the ID number to get the approval from the form, check it, and if it is approved you update the database. If it is not, you will want to use the unlink function to delete the file, and then delete the record from the database.

Link to comment
Share on other sites

Ok I made this

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /><title>Approval</title></head><body><h3>Welcome Bobby</h3><p>These are recently pending database entries, please accept/decline.  Please note declined entries are automatically permanently deleted from the database.</p><?php  // Connects to database$connect = mysql_connect("####", "####3", "######");$select = mysql_select_db("funnyemailforwards");$errorhandler = "";$management = true;if (!$connect || !$select) {	$errorhandler = "The database could not connect, or was not selected<br />";	$management = false;}?>		<?php // script to display information$result = mysql_query("SELECT * FROM fileinfo WHERE approval=0 ORDER BY entrydate ASC");echo "<form name=\"dataupdating\" action=\"approval.php\" method=\"post\">";while ($row = mysql_fetch_assoc($result)){extract($row);$date = date("m/d/y");$site = "http://www.funnyemailforwards.com/apex/";$funnyurl = $site . $funnyurl; echo "<ul>"; echo "<li><a href=" . '"' . $funnyurl . '"' . 'target="_blank">' . $nameoffunny . "</a></li>"; echo "<li>" . $date . "</li>"; echo "<li><input type=\"radio\" name=\"approve_{$row['id']}\" value=\"1\">Approve</li>"; echo "<li><input type=\"radio\" name=\"decline_{$row['id']}\" value=\"0\">Decline</li>"; echo "</ul>";  }echo "<input name=\"update\" type=\"submit\" value=\"Update Database\">";echo "</form>";			if ($errorhandler != "") {	echo "$errorhandler";	$management = false;}?><?phpif (isset($_POST['update'])) {}?></body></html>

This is what I have for my update query but it's not working.Also the date isn't posting right either, I pulled the date from a database as a unix time stamp so I could format it later.the lines of how it would look

if (isset($body_POST['update])) {DELETE * FROM fileinfo WHERE decline_$row['id'];and then my file delete likeunlink($fileurl);  // my file url is the full url http://www.funnyemailforwards.com/apex/uploads/filename.extand then to update I could have$update = "UPDATE approval FROM fileinfo WHERE accept_$row['id] VALUES (1);";mysql_query($update)}

Is this somethign along the lines of how it would go.

Link to comment
Share on other sites

$date = date("m/d/y");You need to include the timestamp in that function, you are just giving it a format, not a value. So it defaults to today's date.That code is sort of on the right track, but the syntax isn't correct. This is what a delete statement looks like:DELETE FROM table WHERE field=valuelike:DELETE FROM table WHERE id=7 or whatever.Update goes like this:UPDATE table SET column=value WHERE column=valueUPDATE table SET approval=1 WHERE id=7If you have questions with SQL syntax, you can search the MySQL documentation:http://dev.mysql.com/doc/refman/4.1/en/index.html

Link to comment
Share on other sites

The thing I don't understand about the date is, I have the unix time stampt set into the database as entrydateBut when I try to pull the entry date out and format it, it doesn't let me, it automatically sets the date to the current date.Another thing I needed to know, is how do I fix these problems

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /><title>Approval</title></head><body><h3>Welcome Bobby</h3><p>These are recently pending database entries, please accept/decline.  Please note declined entries are automatically permanently deleted from the database. These are all updated together, upon submit.  The ones that are approved are set to approved status, the ones that are declined, are immediately deleted from the database, and the file is removed from it's location on the server.  If you check some of the choice, and leave some of the choice alone, they will still be here later, for instance if there are 3 items listed.  If you approve 1, decline 1, and leave the other alone for now, the approved one is updated, the declined one is deleted, and the one left alone, is not touched, and left there, for you to update later.</p><?php  // Connects to database$connect = mysql_connect("localhost", "######", "#####");$select = mysql_select_db("funnyemailforwards");$errorhandler = "";$management = true;if (!$connect || !$select) {	$errorhandler = "The database could not connect, or was not selected<br />";	$management = false;}?>		<?phpif (isset($_POST['update'])) {       foreach ($_POST['approval'] as $key => $value) {    if ($value) {        mysql_query('DELETE FROM fileinfo WHERE id='.$key);  unlink($funnyurl); // url is set to exactly like this  // http://www.funnyemailforwards.com/apex/uploads/filename.ext  // Follows a path exactly to the file.    } else {        mysql_query('UPDATE fileinfo SET approval=1 WHERE id='.$key);   	 }	}// closes foreach}// closes isset control structure?><?php // script to display information$result = mysql_query("SELECT * FROM fileinfo WHERE approval=0 ORDER BY entrydate ASC");echo "<form name=\"dataupdating\" action=\"approval.php\" method=\"post\">";while ($row = mysql_fetch_assoc($result)){extract($row);$date = date("m/d/y");$site = "http://www.funnyemailforwards.com/apex/";$funnyurl = $site . $funnyurl; echo "<ul>"; echo "<li><a href=" . '"' . $funnyurl . '"' . 'target="_blank">' . $nameoffunny . "</a></li>"; echo "<li>" . $date . "</li>"; echo "<li><input type=\"radio\" name=\"approval[$id]\" value=\"1\">Approve</li>"; echo "<li><input type=\"radio\" name=\"approval[$id]\" value=\"0\">Decline</li>"; echo "</ul>";   $management = true;}if ($management === true) {echo "<input name=\"update\" type=\"submit\" value=\"Update Database\">";echo "</form>";}			if ($errorhandler != "") {	echo "$errorhandler";	$management = false;}?></body></html>

The thing that happens is now if there are no entrys it saysupdateanywayI don't want it to show that update field unless there are entries, but if I put it inside, it keeps repeating the update, I don't want it to do that either. Also it tells me hte file does not exist in the section where it's suppose to delete the file, I don't understand, I am feeding it the full file path, I add 2 variables, I have/uploads/filname.extensionsaved int he dband $site = "http://www.funnyemailforwards.com/apex/ saved in the variableso it looks like thishttp://www.funnyemailforwards.com/apex/uploads/file.extTHat is what is fed to unlink but it's telling me the file does not exist.

Link to comment
Share on other sites

This is where you are building the date:$date = date("m/d/y");There's nothing there that tells it to use your own timestamp. You only give it a format string, and since there's no timestamp, it uses the current date. That's what I said before. Here is the reference for date:http://www.php.net/manual/en/function.date.phpIf you don't want to show the form when there are no new entries, then check if there are any new entries before you display the form, and put it in an if/else statement to either display the form or display a message saying that there are no new entries.The unlink function requires a fully-qualified absolute file path, not a relative path and not a URL. You can either use $_SERVER['PATH_TRANSLATED'] and strip off the file name to dynamically get the path, or you can just print out the constant __FILE__ and see what the path is, and hard-code it in the script. It's probably better to hard-code it.

Link to comment
Share on other sites

  • 4 weeks later...

justsomeguys approve decline wont work because the page wont be able to tell what to delete, considering you dont send it an id. heres a way you can do it.

$result = mysql_query("SELECT * FROM fileinfo WHERE approval=0 ORDER BY date_added ASC");while ($row = mysql_fetch_assoc($result)){ echo "<tr>"; echo "<td><input type=\"radio\" name=\"decline\" value=\"{$row['id']}\">Decline</td>"; echo "<td><input type=\"radio\" name=\"accept\" value=\"{$row['id']}\">Approve</td>"; echo "<td>" . format_date . "</td>"; echo "<td>" . link_to_file . "</td>"; echo "</tr>";  }

Then on the page that accepts the form do this:

if(isset($_POST['accept'])){$query = "UPDATE `fileinfo` SET approval='1' WHERE id='{$_POST['accept']}'";mysql_query($query) or die("Error:" . mysql_error());}else{$query = "DELETE FROM `fileinfo` WHERE id = '{$_POST['decline']}'";mysql_query($query) or die("Error:" . mysql_error());}

Oh yeah, you messed up your mysql connection page lol. Its giving a bunch of errors now..

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...