Change data in 2 tables at the same time.

[rvdveen27]

Hello all, 

 

I've recently made an application form, based on that I created another page where application forms can be reviewed and then accepted/denied. Now I'm working on the code where an application should be accepted. 

 

What I want to achieve, is that if on the page where applications can be reviewed the action "accept" is chosen, it sets the application status to "2" (which is accepted) and at the same time sets the user's (creator of the application) verifypend to "0" and verified to "1". 

 

For that I use the code below. However, it doesn't run through the code fully. I have concluded that it runs it fine, then stops at line 58. I understand that's it's unable to get the username, but I do not know how to solve that issue. 

<?php 
	ob_start();

	require('extra/header.php');
     
    if(empty($_SESSION['user'])) 
    { 
        header("Location: login.php"); 
        exit;
	} 
	else if($_SESSION['adminlevel'] == 0)
	{
		header("Location: dashboard.php"); 
        exit;
	} 
	else {
		if(empty($_GET['id'])) 
		{ 
			header("Location: index.php"); 
			exit;
		} 
		$id = $_GET['id'];
		$id = intval($id);

		if(!is_numeric($id))
		{
			header("Location: index.php");
			exit;
		}
		
		$query = " 
            UPDATE applications
			SET
				status = 2
        "; 
		$query_params = array( 
            ':id' => $id, 
        ); 
        $query .= " 
            WHERE 
                id = :id 
        "; 
         
        try 
        { 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        } 
        catch(PDOException $ex) 
        { 
            die("Failed to run query: " . $ex->getMessage()); 
        }
		
	// Verify the user
	if(empty($_GET['username'])) 
		{ 
			header("Location: inforules.php"); 
			exit;
		} 
		$id = $_GET['username'];
		$id = intval($id);

		if(!is_numeric($id))
		{
			header("Location: index.php");
			exit;
		}
	
		$query = " 
            UPDATE users
			SET
				verifypend = 0,
				verified = 1
        "; 
		$query_params = array( 
            ':id' => $id, 
        ); 
        $query .= " 
            WHERE 
                id = :id 
        "; 
         
        try 
        { 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        } 
        catch(PDOException $ex) 
        { 
            die("Failed to run query: " . $ex->getMessage()); 
        } 
        
        header("Location: editprofile.php?id=". $id .""); 

        exit;
	} 
    

	
?> 

[maxxd]

The username variable is not being passed by the sending page. Basically, however you get to this script is the issue. If the link on the sending page is "http://mysite.com/mypage.php", then it needs to be "http://mysite.com/mypage.php?username=myusername". If you're sending from a form, you'll need to make sure the form method is 'get' and there's a field in the form named 'username'.

[rvdveen27]

The page for reviewing looks like this: 
 

 
Because there are multiple applications being shown at the same time it seems impossible to get the URL to be something like: "http://mysite.com/mypage.php?username=myusername"?
 
The code for the reviewing page looks like this: 

<?php 
	ob_start();

	require('extra/header.php');
    
    if(empty($_SESSION['user'])) 
    { 
        header("Location: login.php"); 
         
        exit;
	} 
	if($_SESSION['adminlevel'] == 0) 
    { 
        header("Location: dashboard.php"); 
         
        exit;
	} 
	
	ini_set('display_errors', 1);
	error_reporting(E_ALL); 
	
    $query = " 
        SELECT 
			id
			,username
			,steamprofile
			,age
			,timezone
			,description 
			,playtime
			,CASE othervtc
				WHEN 1 THEN 'Yes'
				WHEN 2 THEN 'No'
			END as othervtc
			,vtcreason
			,CASE teamspeak
				WHEN 11 THEN 'Yes'
				WHEN 22 THEN 'Yes, with headset'
				WHEN 33 THEN 'No'
			END as teamspeak
			,tsreason
			,CASE aboutedim
				WHEN 1 THEN 'ETS2MP Company forums'
				WHEN 2 THEN 'ETS2C Convoy'
				WHEN 3 THEN 'Google or another search engine'
				WHEN 4 THEN 'EDIM website'
				WHEN 5 THEN 'EDIM Teamspeak' 
			END as aboutedim
        FROM applications
		"; 
		$query_params = array( 
            ':id' => "0", 
        ); 
        $query .= " 
            WHERE 
                status = :id 
        "; 
        try 
        { 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        } 
        catch(PDOException $ex) 
        { 
            die("Failed to run query: " . $ex->getMessage()); 
        }
         
    $rows = $stmt->fetchAll(); 
	$count = $stmt->rowcount();
?> 	

<div class="container2">
	<h1>Pending Applications</h1> 
	<div class="table-responsive">
	<table class="table table-striped">
	  <thead>
		<tr>
		  <th>#</th>
		  <th>Username</th>
		  <th>Steamprofile</th>
		  <th>Age</th>		 
  		  <th>Timezone</th>
		  <th>Description</th>
		  <th>Playtime</th>
		  <th>Other VTC's?</th>
		  <th>VTC reasons</th>
		  <th>Teamspeak?</th>
		  <th>Teamspeak reason</th>
		  <th>About EDIM</th>
		  <th>Action</th>
		</tr>
	  </thead>	  
	  <?php if($count == 0)
	{?>
		<tr>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
			<td>There</td> 
			<td>are</td>
			<td>currently</td>
			<td>no pending</td>
			<td>applications.</td>
			<td></td>
			<td></td>
			<td></td>
			<td></td>
		</tr>
		
	
	<?php }	else foreach($rows as $row): 
	    
		//Get the username
		$query = " 
        SELECT 
            *
			FROM users
			WHERE id = ". $row['username'] ."
		"; 
        try 
        { 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute(); 
        } 
        catch(PDOException $ex) 
        { 
            die("Failed to run query: " . $ex->getMessage()); 
        }
        $driverid = $stmt->fetch();
	?>
	  <tbody>
		<tr>
		  <td><?php echo $row['id']; ?></td>
		  <td><?php echo $driverid['username']; ?></td>
		  <td><a href="<?php echo htmlentities($row['steamprofile'], ENT_QUOTES, 'UTF-8'); ?>" target="_blank">steamprofile</a></td>
		  <td><?php echo $row['age']; ?></td>
		  <td><?php echo $row['timezone']; ?></td>
		  <td><?php echo $row['description']; ?></td>
		  <td><?php echo $row['playtime']; ?></td>
		  <td><?php echo $row['othervtc']; ?></td>
		  <td><?php echo $row['vtcreason']; ?></td>
		  <td><?php echo $row['teamspeak']; ?></td>
		  <td><?php echo $row['tsreason']; ?></td>
		  <td><?php echo $row['aboutedim']; ?></td>
		  <td><a href="acceptuser.php?id=<?php echo $row['id']; ?>">ACCEPT</a> - <a href="denyjob.php?id=<?php echo $row['id']; ?>">DENY</a></td>
		</tr>
	  </tbody>
	<?php endforeach; ?> 
	</table>
  </div>
</div>

<?php require('extra/footer.php'); ?>	

It echoes the ID to the acceptuser.php page, which is the ID for the application. Except it should elso send the "username" to the acceptuser.php page. I understand I need to change something in the code on the pendverify.php page at line 146. The difficulty is in the fact that two values need to be send to the next page I guess.

EDIT: I changed line 146 to:

<td><a href="acceptuser.php?id=<?php echo $row['id']; ?>?username=<?php echo $row['username']; ?>">ACCEPT</a> - <a href="denyjob.php?id=<?php echo $row['id']; ?>">DENY</a></td>

Now on the acceptuser.php it runs through the whole code (I can tell this because it sends me to the profile of the user with ID 3, which is at the bottom of the code), but the user doesn't get verified = 1 and verifypend = 0 in the database. 

Edited May 28, 2015 by rvdveen27

[Barand]

Doing it this way would require you pass just the application id (I'm guessing at the column name for the user_id in the application table)

UPDATE applications a
INNER JOIN users u 
    ON a.user_id = u.id
SET
    a.status = 2,
    u.verifypend = 0,
    u.verified = 1
WHERE a.id = :appid ;

Edited May 28, 2015 by Barand

[rvdveen27]

@Barand - You're golden.