[PHP]Approval.

[andrew951]

ok so i was working on the approval script from a while ago. and then i realized that it only shows the ones that are by name.
this script i wrote just arrays everyone that is in the db from approved=0.
but i want it to match with the service that they did.
so for example it finds the id number, gets the name, then puts the name with the service they did with the amount of hours.

<?php
session_start();
if(isset($_SESSION['verify'])) {
 echo "<h1>Approval Section</h1><br>";
 mysql_connect("localhost", "ashin951_keydb", "___") or die(mysql_error());
 mysql_select_db("ashin951_keydb") or die(mysql_error());
 
 //User Approval Script
 $result2 = mysql_query("SELECT * FROM service WHERE approved='0'") or die(mysql_error());
 $result3 = mysql_query("SELECT * FROM community") or die(mysql_error());
 echo "<table border='1'>";
 echo "<tr> <th>Name</th> <th>Action</th> <th>Hours</th> <th>Approve</th> </tr>";
 while($row = mysql_fetch_array( $result2 ) and $row2 = mysql_fetch_array( $result3 )) {
  // Print out the contents of each row into a table
  echo "<tr><td>"; 
  echo $row2['name'];
  echo "</td><td>"; 
  echo $row['action'];
  echo "</td>"; 
  echo "<td>"; 
  echo $row['hours'];
  echo "</td><td>";
  $id = $row['id'];
  echo "<form action=\"approve.php\" method=\"post\"><input name=\"approve[]\" value=\"$id\" type=\"checkbox\">";
  echo "</td></tr>";
 }
 echo "</table>";
echo "<input type=\"submit\" name=\"Approve\" value=\"Approve\"></form>";
} else {
echo "Access Denied.";
}
?>
<title>Approval Section</title>

 

[Wh005h]

Hmm, you might can solve your problem by just querying another time with the info you retrieved with the first query.
I don't really understand your problem thought I'm just kind of shooting in the dark lol

 

[andrew951]

so i have 1 table that has users and their informations.
in another table i placed service and hours that have the id from the first table.
i want to get the name from the first table and show it with the service and hours done from a column that has a approve = 0

 

[Wh005h]

so i have 1 table that has users and their informations.
in another table i placed service and hours that have the id from the first table.
i want to get the name from the first table and show it with the service and hours done from a column that has a approve = 0

Yah then I think I was right before. You'll pull the user initially then use the pulled information from one table to pull the appropriate information from the other table.

For example (likely your situation):
Get the user's name according to the approval field
Use the name or corresponding id to get their service and amount of hours from the other table.
It will look something like this I would guess:

$result2 = mysql_query("SELECT * FROM service WHERE approved='0'") or die(mysql_error());
$row2 = mysql_fetch_array($result2);
$result3 = mysql_query("SELECT * FROM community WHERE name = '$row2['name']'") or die(mysql_error());

 

[Jackel]

This can be accomplished quite easily using one query.

Example

SELECT service.id, service.action, service.hours, community.name FROM service, community WHERE service.approved = 0 AND community.id = service.id

I'm not entirely sure of your table structure, so you may have to adjust the query.

 

[andrew951]

the approved is on the other table.
so what wh00sh said, i have to do the oposite of what he said.
but im trying to make a number of them come out. doing what wh00sh said would only have 1 come out at a time. unless i use a loop. haha.

 

[Jackel]

<?php

 
 session_start();
 if(isset($_SESSION['verify'])) {
 	echo "<h1>Approval Section</h1><br>";

 	mysql_connect("localhost", "ashin951_keydb", "___") or die(mysql_error());
 	mysql_select_db("ashin951_keydb") or die(mysql_error());
 
 	//User Approval Script
 	$query = mysql_query("SELECT service.id, service.action, service.hours, community.name FROM service, community WHERE community.approved = 0 AND community.id = service.id")
 	//$result2 = mysql_query("SELECT * FROM service WHERE approved='0'") or die(mysql_error());
 	//$result3 = mysql_query("SELECT * FROM community") or die(mysql_error());
	echo "<table border='1'>";
 	echo "<tr> <th>Name</th> <th>Action</th> <th>Hours</th> <th>Approve</th> </tr>";
 	while($row = mysql_fetch_array($query) {
		// Print out the contents of each row into a table
  		echo "<tr><td>"; 
  		echo $row['name'];
  		echo "</td><td>"; 
  		echo $row['action'];
  		echo "</td>"; 
  		echo "<td>"; 
  		echo $row['hours'];
  		echo "</td><td>";
  		$id = $row['id'];
  		echo "<form action=\"approve.php\" method=\"post\"><input name=\"approve[]\" value=\"$id\" type=\"checkbox\">";
  		echo "</td></tr>";
 	}
 	echo "</table>";
 	echo "<input type=\"submit\" name=\"Approve\" value=\"Approve\"></form>";
 } else {
 echo "Access Denied.";
 }

?>

 

[Wh005h]

This can be accomplished quite easily using one query.

Example

SELECT service.id, service.action, service.hours, community.name FROM service, community WHERE service.approved = 0 AND community.id = service.id

I'm not entirely sure of your table structure, so you may have to adjust the query.

Hmm well I learned something now then ^_^ lol
I've never really seen a query like that but that will come in handy later.

Can this type of query be used to use the info from table1 in table2 parameters? Like

table1.name, table2.infoaboutname FROM table1, table2 WHERE table2.infoaboutname = 'table1.name'

Kind of thing? Using info inside the query to retrieve specific data inside the query still (if that makes sense).

 

[Jackel]

Yes, should work fine.

 

[andrew951]

yeah! jackal. your code had a few errors but i fixed them. it works now. thanks a lot. =]

edit:
while i fixed all the errors, my approval itself does not work anymore. any one know why this is?
here is the code:

<?php
if (isset($_POST['approve'])) {
$id = $_POST['approve'];

mysql_connect("localhost", "ashin951_keydb", "keydb") or die(mysql_error());
mysql_select_db("ashin951_keydb") or die(mysql_error());
$ticked = $_POST['approve'];

foreach($ticked as &$id) {
     mysql_query("UPDATE `service` SET `approved` = '1' WHERE `ID` = '{$id}'");
}
unset($id);  
echo "Successfully Approved.";
} else {
echo "Access Denied.";
}
?>
<title>Approval</title>

it worked before too.

 

[Jackel]

What exactly isn't working? More details of the error please.

 

[andrew951]

the approved = 0 is not changing to 1. thats the whole point of the script. to make them approve with the checkbox on the other script.

 

[s-p-n]

This can be accomplished quite easily using one query.

Example

SELECT service.id, service.action, service.hours, community.name FROM service, community WHERE service.approved = 0 AND community.id = service.id

I'm not entirely sure of your table structure, so you may have to adjust the query.

Wow.. I never knew that..
Learn something new everyday, right? lol :construct

 

[s-p-n]

the approved = 0 is not changing to 1. thats the whole point of the script. to make them approve with the checkbox on the other script.

replace:

mysql_query("UPDATE `service` SET `approved` = '1' WHERE `ID` = '$id'");

with :

mysql_query("ALTER TABLE `service` SET `approved` = '1' WHERE `ID` = '$id'");

and it should work.. I've made this problem in the past a few times over

 

[andrew951]

update doesnt work?
edit: nah yours didnt work either.

 

[s-p-n]

now try it, I had an error in the code

 

[andrew951]

umm you didnt post anything.
i used a mysql_error and this is what showed with alter table:

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 'SET `approved` = '1' WHERE `ID` = '100000'' at line 1

i use mysql5.
so i changed it back to update and there was no error. but it still didnt change.

 

[s-p-n]

Good good, very useful information. and I was wrong about Alter Table.. I use that for a totally different thing. I see the problem is that $id, ($_POST['approved']), is set to 100000. I'll take a wild guess you have 6 IDs, and your script is trying to get the 100,000'th ID, while there is only 6. Somewhere in your submitting code that has the field 'approved', needs to be changed. post that code?

 

[andrew951]

<?php 
if (isset($_POST['approve'])) { 
$id = $_POST['approve']; 

mysql_connect("localhost", "ashin951_keydb", "keydb") or die(mysql_error()); 
mysql_select_db("ashin951_keydb") or die(mysql_error()); 
$ticked = $_POST['approve']; 

foreach($ticked as &$id) { 
     mysql_query("UPDATE `service` SET `approved` = '1' WHERE `ID` = '{$id}'"); 
} 
unset($id);   
echo "Successfully Approved."; 
} else { 
echo "Access Denied."; 
} 
?> 
<title>Approval</title>

you think the $id is 100000 right?
ill echo it.
i added

echo $id;
die();

underneath $id and it just says array. so i believe that the script before this is not sending the correct information.

 

[s-p-n]

it's supposed to be echo("$id"); and you don't need a die message for an echo..

And what's with the {} brackets around {$id} inside the sql statement?

And believe me, $id is coming out as 100000 and that's a fact if the code you give me is true. I'm getting the picture to what the problem is... but I need more access for testing and working out the problem until I know it works. The most I can do is look for little mistakes that might be the problem.. And that's not going to help in this case. I need to see the big picture to really help you.

EDIT: I need to see the page the sends $_POST['approve']