How to code submit button to update database...URGENT :(

PHP
1

I am not experience wiht PHP, have just started using it last week. I’m trying to find out how to call a function for submit button. Using html and php I’ve created a form that has a table which displays some data from MySql. When new information is selected using drop down menu, the field in table has changed accordingly, but I need a code for the “Submit button” to update that information in database. I have an update query that it should work, and I have created the image of the button using html.

I don’t know how to code function/trigger for submit button in php. Any feedback is welcome. I need this urgently :frowning:

2

Welcome to SitePoint Forums :slight_smile:

PHP doesnt trigger. Javascript can trigger; PHP works on pre-page loading. (Hence the name: Hypertext Preprocessor. Dont ask me how they manage to get PHP out of that.)

What you do is point your form to a processing page (which COULD be the same page). In the processing page, use something like if(isset($_POST[‘afieldname’])) { to make sure the form data is there, and then handle it (remember to sanitize, or use prepared statements)

3

Thanks for your reply, please be patient with me as I am total novice to PHP…I don’t know what do you mean by “sanitize or use prepared statements”?

The field name in db I wanting to update is called “approval_status”, and the table name name is “webform_submitted_data”, the dropdown box is called “apprrovalstat” …and my update query it looks like this…not sure whether is right…it looks like it should work:

mysql_query(“UPDATE webform_submitted_data SET approval_status = $dropdown”);

Would you mind please put this information into syntax…I am very visual person…much easier to understand after seeing it :slight_smile:

4

You’re on the right track for basics (quick and dirty). I’d suggest when you’re not on a time crunch to take a look at the [FPHP]PDO[/FPHP] library to understand prepared statements.

But lets work with what you’ve got now.


mysql_query("UPDATE webform_submitted_data SET approval_status = $dropdown");

There are two things that spring to mind when I look at this:

#1: “What row is this meant to update?”
If there’s only one row in your table ever, then the above is fine. If it’s meant to affect only one of several rows in the table, then we need a WHERE clause in there somewhere.

#2: “Sanitize Sanitize Sanitize”

#2a: personally i dislike throwing variables into a string unescaped, but it’s perfectly valid syntax.

Now, when I say sanitize, i mean… make sure someone didnt slip something nasty into your form variables. For example. If i submitted to your form the value of $dropdown as

""; DROP TABLE webform_submitted_data;

I’d destroy your table.

PDO and prepared statements can prevent this type of attack; but as you’re on an urgency, we’ll stick to sanitizing (or more accurately in this case, validating) the data.


$allowed = array('option1','option2','option3');
if(in_array($dropdown,$allowed)) {
  //Do Your form processing
} else {
  //Didnt recognize the value of DropDown. DO NOT PROCESS. SECURITY VIOLATION.
}

What this says is: Define an array of valid values. If the value in $dropdown is in my array of valid values, process the form. Otherwise, do not.

Do note that in_array is a case-sensitive function.

5

Hi Lilly,

One of the things PHP can do is to handle data submitted inside a form.
When a submit button is pressed inside a form it collects all the data in the form and submits it to the path in the the forms action attribute.

<form action="handle_submit.php" method="POST">
  <select id="apprrovalstat" name="apprrovalstat">
    <option value="approved">Approved</option>
    <option value="declined">Declined</option>
  </select>
  <input type="submit">
</form>

So if you selected a value from the drop down it would send apprrovalstat=approved to handle_submit.php
I you had a file called handle_submit.php with the following code you should see the value of the drop down echoed to the page.

<?php echo $_POST['apprrovalstat']; ?>

You can then use this value as a variable in your PHP code, e.g.

mysql_query("UPDATE webform_submitted_data SET approval_status = '". $_POST['apprrovalstat'] ."'");

Hope it helps,

6

Thanks StarLion and markbrown4 for your advise…I will try it and will let you know how did I go…in a day or two. @StarLion: I will definitely had a closer look into PHP once I’m done with this project :slight_smile: Cheers guys

7

Hi,

StarLion gives you good advice here. Just in case you want/can/have time to wrap your head around PDO escaping then here is a basic example:

The form:


<form id="ContactForm" action="process_user.php" method="post" >
   <fieldset>
       <label for="users_name"><span>Name:</span>
            <input name='users_name' type="text" value="" />
       </label>
       <label for="favourite_tag"><span>Favourite Tag:</span>
            <input name='favourite_tag' type="text" value="" />
       </label>
   </fieldset>
   <fieldset>
        <ul class='buttons_right'>
            <li><input type="submit"  value="Add"></li>
        </ul>
  </fieldset>
</form>

The process_user.php page (including PDO connection to database):

See the comments in the script



if( $_POST['users_name'] && $_POST['favourite_tag']){
         $users_name = $_POST['users_name'];
         $favourite_tag = $_POST['favourite_tag'];
         try { 
            $Db = new Db();
            $pdo = $Db->getPdoObject();
            $sql="
                INSERT INTO favourite_tags
                    (users_name, favourite_tag )
                VALUES
                    (:users_name, :favourite_tag) // pdo placeholders
            ";
            // preparing the sql to receive the escaped data
            $stmt = $pdo->prepare($sql);

            // executing the SQL
            $results = $stmt->execute(
               // mapping data to placeholders, escaping the data
               array( ':users_name' => $users_name , ':favourite_tag' => $favourite_tag)
            );
         } catch (PDOException $e) {
             echo 'Query failed: ' . $e->getMessage();
         }
        if($results){
            foreach($results as $result){
                foreach($result as $key => $value){
                  // do something with $key and $value
               }
        } else {
             // let the users know no data was found
       }
} else {
        echo "No values submitted to process";
        exit;
}

Class Db {
    protected $db;


    public function __construct(){
                // change the db_user to your database user and db_password to the your database user's password
        $this->db = new PDO("mysql:host=localhost;dbname=users;charset=UTF-8", "db_user", "db_password");
    }
    public function getLastId(){
      return $this->db->lastInsertId();
    }
    public function getPdoObject(){
          // return the PDO database object
      return $this->db;
    }
}

Steve

8

Thanks Steve. I never used the forum before so I am overwhelm that you are guys are so kind and taking time to set me on the right path. I am beginner with PHP and little out of depth with your code…but hopefully will get there :slight_smile: I am working on other aspect of my project for couple of days and after that will let you know how well that went :slight_smile:

9

Hi again, I have copied the code in for my next question to give a little more context to the situation

I am having trouble with my query to update the database( under the if statement near the bottom of the code below), at the moment it is updating all the rows to the same status eg “Approved” so I am assuming that my where clause is incorrect when trying to single out 1 particular row…??? Any help would much appreciated with this issue.

Thanks Again.

<html>
<body>

<?php
	//Connect to database
	include("C:\\wamp\\www\\wintec\\sites\\all\\connect.php");
	
	//Retrieve data from a table
	$result = mysql_query("SELECT nid, sid, data, approval_status FROM webform_submitted_data WHERE cid='7' AND nid='6' ")
	or die(mysql_error());
	
	//Table starts
	echo "<table border='1'>";
	echo "<tr> <th>Project ID </th> <th>Project Name</th> <th> Approval Status</th> <th>Approve/Unapprove</th</tr>";
	
	// Keeps getting the next row until there are no more to get
	while($row = mysql_fetch_array( $result)) {
		//Print out the contents of each row into a table
		echo "<tr><td>";
		echo $row['nid'];
		echo $row['sid'];
		$projid = $row['nid'].$row['sid'];
		echo "</td><td>";
		echo $row['data'];
		echo "</td><td>";
		echo $row['approval_status'];
		echo "</td><td>";
		// Dropdown menu options and save button query
		echo "<form id=form1 method='POST'>
		<select id='apprrovalstat' name='apprrovalstat'>
		<option value='-select-'>-Select-</option>
		<option value='Approved'>Approved</option>
		<option value='Declined'>Declined</option>
		<option value='Pending'>Pending</option>
		</select>
		<input id='sub' type='submit' value='Save'>";
			if (isset($_POST['apprrovalstat'])) {
				mysql_query("UPDATE webform_submitted_data SET approval_status = '". $_POST['apprrovalstat'] ."'
				WHERE CONCAT(nid, sid) = '".$projid."' AND '". $_POST['apprrovalstat'] ."' != '-select-';" );
				echo "Rows updated";
			}
			else {
				echo "Update error";
			}
		echo "</form>";
		echo "</td></tr>";
	}
	echo "</table>";
?>

</body>
</html>

10

Hi Lilly2012,



[COLOR=#0000FF]WHERE CONCAT(nid, sid)[/COLOR]

Should be:



[COLOR=#0000FF]WHERE CONCAT([/COLOR][COLOR=#000088]$row[/COLOR][COLOR=#009900][[/COLOR][COLOR=#0000FF]'nid'[/COLOR][COLOR=#009900]][/COLOR][COLOR=#0000FF], [/COLOR][COLOR=#000088]$row[/COLOR][COLOR=#009900][[/COLOR][COLOR=#0000FF]'sid'[/COLOR][COLOR=#009900]][/COLOR][COLOR=#0000FF])

[/COLOR]

11

Okay hang on. This is going to go badly very quickly.

You’re cycling a form without passing an identifier, and updating without validating or sanitizing…

So lets crack at this a bit.


	while($row = mysql_fetch_array( $result)) {
		//Print out the contents of each row into a table
		echo "<tr><td>";
		echo $row['nid'];
		echo $row['sid'];
		$projid = $row['nid'].$row['sid'];
		echo "</td><td>";
		echo $row['data'];
		echo "</td><td>";
		echo $row['approval_status'];
		echo "</td><td>";
		// Dropdown menu options and save button query
		echo "<form id=form1 method='POST'>
		<select id='apprrovalstat' name='apprrovalstat'>
		<option value='-select-'>-Select-</option>
		<option value='Approved'>Approved</option>
		<option value='Declined'>Declined</option>
		<option value='Pending'>Pending</option>
		</select>
		<input id='sub' type='submit' value='Save'>";
			if (isset($_POST['apprrovalstat'])) {
				mysql_query("UPDATE webform_submitted_data SET approval_status = '". $_POST['apprrovalstat'] ."'
				WHERE CONCAT(nid, sid) = '".$projid."' AND '". $_POST['apprrovalstat'] ."' != '-select-';" );
				echo "Rows updated";
			}
			else { 
				echo "Update error"; 
			}
		echo "</form>";
		echo "</td></tr>";
	}
	echo "</table>";

I’m gonna slice out a few lines and see what i can make sense of…


		$projid = $row['nid'].$row['sid'];

I shudder every time i see this. What happens when you have a project with nid 11, and sid 1, and another with nid 1, and sid 11? Both project "id"s would be 111.

If NID and SID are a unique identifier (duple), they should be the key. So lets assume they are.

Your form has no way of identifying which project you’re trying to modify. The only field you’re passing atm is “approvalstat”. Approval stat for… what?
Let’s add some hidden values.


		echo "<form id=form1 method='POST'>
		<select id='apprrovalstat' name='apprrovalstat'>
		<option value='-select-'>-Select-</option>
		<option value='Approved'>Approved</option>
		<option value='Declined'>Declined</option>
		<option value='Pending'>Pending</option>
		</select>
                <input type='hidden' value='".$row['nid']." name='nid'>
                <input type='hidden' value='".$row['sid']." name='sid'>
		<input id='sub' type='submit' value='Save'>";

So now the form handler knows that this approval state is for nid X and sid Y.

Now, the update query: because we’re not passing a combined key anymore, the query doesnt need to do concatenation.


			if (isset($_POST['apprrovalstat']) && in_array($_POST['apprrovalstat'],$allowed)) {
				mysql_query("UPDATE webform_submitted_data SET approval_status = '". mysql_real_escape_string($_POST['apprrovalstat']) ."'
				WHERE nid = ".intval($_POST['nid'])." AND sid = ".intval($_POST['sid']));
				echo "Rows updated";

Notes here:
1: i’ve shifted the “is not -select-” check into the IF. Saves a bit of time by not querying the database server unnecessarily.
2: Are nid and sid integers? or strings? I’ve assumed integers above.
3: Somewhere before your loop, define $allowed


$allowed = array("Approved","Declined","Pending");

4: I would remove the “else” from the IF entirely. the else will show up every time you dont submit data (IE: When you first view the page…)

12

Additional: Take the update query out of the loop. The way you have this constructed, you will only ever have 1 status adjustment to make per page load.

13

You guys are brilliant! Its working…but please stay a little bit longer with me…I need to create another form on another page to display all approved items and then give the option for those items to be applied for. I am hopeful to use the first form to model my answer for the second. If I’m stuck…I am hopeful that you will help me again. Thank you so much :slight_smile:

14

Hi guys, I am working on another form for the same project and I am having trouble with the SELECT query in my while loop.
Because Drupal stores some of the records in rows instead of columns, I need that query to call “Project Description” which is referenced as Cid=‘1’ and is stored under the “data” column in my mysql database. For example data is stored as shown in the attached image:

Can anyone help me with the 2nd query in my code which is under the while statement, at the moment the “project description” (which is referenced as Cid=‘1’ and is stored under the “data” column in my mysql database) is not showing for each project???

Any help is much appreciated :slight_smile:

<html>
<body>
<?php
	//Connect to database
	include("C:\\wamp\\www\\drupal\\sites\\all\\connect.php"); //Connect to database
	
	//Retrieve data from a table
	$result = mysql_query("SELECT nid, sid, data, approval_status FROM webform_submitted_data WHERE approval_status='Approved' AND cid='7' AND nid='6' ")
	or die(mysql_error());
	
	//Table Starts
	echo "<table border='1'>";
	
	// Keeps getting the next row until there are no more to get
	while($row = mysql_fetch_array($result)) {
		//Print out the contents of each row into a table
		echo "<tr><th>Project Name: ";
		echo $row['data'];
		echo "</th></tr>";
		echo "<tr><td>";
		echo "<b>Project ID:</b> ";
		echo $row['nid'];
		echo $row['sid'];
		echo "<br><br> ";
		echo "<b>Project Description: </b>";
		// Query to display project description for the current row
		echo mysql_query("SELECT data FROM webform_submitted_data WHERE cid='1' AND nid='6' AND sid= '".$row['sid']."' ") or die (mysql_error());
		
		echo "<br><br> <br><b>Approval Status: </b>";	
		echo $row['approval_status'];
		echo "<br> <br>Apply Button <br>";
	}	
	echo "</td></tr>";
	echo "</table>";
?>
</body>
</html>
15

[SIZE=3][FONT=trebuchet ms]Hi Lily2012,

I would recommend that you place your sql string in a variable like:
[/FONT][/SIZE]

$sql = "
SELECT 
    data 
FROM 
    webform_submitted_data 
WHERE 
    cid = 1 
AND 
    nid = 6 
AND 
    sid = " . $row['sid'];

[SIZE=3][FONT=trebuchet ms] People have their own styles, but clarity when it comes to troubleshooting SQL is important.

So executing the query becomes

echo mysql_query($sql) or die (mysql_error());

[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]You can also

echo $sql;

and it should output something like:

SELECT data FROM webform_submitted_data WHERE cid = 1 AND nid = 6 AND sid = 1

It appears that you data types for cid, nid, and sid are each integers; you don’t need to quote them in your sql as you only need to quote strings. However the quoted integers in your example won’t cause a problem; mysql is smart enough and will still interpret them as integers.

By echoing your sql, you can see if $row[‘sid’] has a proper value.

You should run your SQL in PHPMyAdmin or MySQL Workbench. This enables you to eliminate a PHP and Apache and focus on getting your query to return a result.

Let us know what you get after doing this.

Steve [/FONT]

[/SIZE]

16

Hi Steve, your code gives me a same result as mine, which is "project description " just keep saying “1” whereas it should say “bla bla bla”. If i make it echo what the query would be for each of the individual projects instead of showing the result its showing me the correct query so not sure why the project description just keeps saying “1” for each of the projects on the screen. Does this make sense? All suggestion are welcome :frowning:

17

Hi please echo out the $sql string and then post that here; it should look like the same sql you have in your script only the real values that you will be submitting to the database will be there.

Regards,
Steve

18

Hi,

When I echo the $sql string to see what the query looks like i get the following queries for my projects on my web page:

SELECT data FROM webform_submitted_data WHERE cid = 1 AND nid = 6 AND sid = 5;
SELECT data FROM webform_submitted_data WHERE cid = 1 AND nid = 6 AND sid = 6;
SELECT data FROM webform_submitted_data WHERE cid = 1 AND nid = 6 AND sid = 7;

And when i try these queries in mysql myphpadmin they work so im not sure why when i echo the following im seeing “1” on the screen for each project instead of their project descriptions:

$sql = "SELECT data FROM webform_submitted_data WHERE cid = 1 AND nid = 6 AND sid = ".$row['sid'].";";
echo mysql_query($sql) or die (mysql_error());

Anyone got any thoughts as to what the problem maybe?

Heres the full code for the page to give more context:

	<html>
	<body>
	<?php
		//Connect to database
		include("C:\\wamp\\www\\drupal\\sites\\all\\connect.php"); //Connect to database
		
		//Retrieve data from a table
		$result = mysql_query("SELECT nid, sid, data, approval_status FROM webform_submitted_data WHERE approval_status='Approved' AND cid='7' AND nid='6' ")
		or die(mysql_error());
		
		//Table Starts
		echo "<table border='1'>";
		// Keeps getting the next row until there are no more to get3
		while($row = mysql_fetch_array($result)) {
			//Print out the contents of each row into a table
			echo "<tr><th>Project Name: ";
			echo $row['data'];
			echo "</th></tr>";
			echo "<tr><td>";
			echo "<b>Project ID:</b> ";
			echo $row['nid'];
			echo $row['sid'];
			echo "<br><br> ";
			echo "<b>Project Description: </b>";
			
			// Query to display project description for the current row
			$sql = "SELECT data FROM webform_submitted_data WHERE cid = 1 AND nid = 6 AND sid = ".$row['sid'].";";
			echo mysql_query($sql) or die (mysql_error());
			echo $sql;
			echo "<br><br> <br><b>Approval Status: </b>";	
			echo $row['approval_status'];
			echo "<br> <br>Apply Button <br>";
		}	
		echo "</td></tr>";
		echo "</table>";
	?>
	</body>
	</html>
19

ah.

See when you pasted your query how the hilighter made ‘data’ brown? It’s keying it up as a reserved word in SQL.

put backticks around it, just in case.

SELECT data …

(I’d also suggest not using data as a field name in the future :wink: A list of mysql’s reserved words can be found here

You also dont need to run a query inside the loop. you should alraedy have the data object from your first query.

Echoing a mysql_query will return a number not the data from the query. Thats what the fetch series of commands is for.

20

You mean PHP: Hypertext Pre-precessor. It’s a recursive acronym.