SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Member
    Join Date
    Jul 2012
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    Welcome to SitePoint Forums

    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)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Member
    Join Date
    Jul 2012
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    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 PDO library to understand prepared statements.

    But lets work with what you've got now.
    PHP Code:
    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
    Code:
    ""; 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.

    PHP Code:
    $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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  5. #5
    padawan silver trophybronze trophy markbrown4's Avatar
    Join Date
    Jul 2006
    Location
    Victoria, Australia
    Posts
    4,107
    Mentioned
    28 Post(s)
    Tagged
    2 Thread(s)
    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.
    HTML Code:
    <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.
    Code php:
    <?php echo $_POST['apprrovalstat']; ?>

    You can then use this value as a variable in your PHP code, e.g.
    Code php:
    mysql_query("UPDATE webform_submitted_data SET approval_status = '". $_POST['apprrovalstat'] ."'");

    Hope it helps,

  6. #6
    SitePoint Member
    Join Date
    Jul 2012
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 Cheers guys

  7. #7
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    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:
    PHP Code:
    <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
    PHP Code:

    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
    ictus==""

  8. #8
    SitePoint Member
    Join Date
    Jul 2012
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 I am working on other aspect of my project for couple of days and after that will let you know how well that went

  9. #9
    SitePoint Member
    Join Date
    Jul 2012
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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>
    Code PHP:
    <?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. #10
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi Lilly2012,

    Code:
    WHERE CONCAT(nid, sid)
    Should be:
    Code:
    WHERE CONCAT($row['nid'], $row['sid'])
    ictus==""

  11. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
        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...
    PHP Code:
            $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.

    Code:
    		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.

    Code:
    			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
    PHP Code:
    $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....)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  12. #12
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  13. #13
    SitePoint Member
    Join Date
    Jul 2012
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  14. #14
    SitePoint Member
    Join Date
    Jul 2012
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    table weform data.jpg

    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

    Code PHP:
    <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. #15
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi Lily2012,

    I would recommend that you place your sql string in a variable like:
    PHP Code:
    $sql "
    SELECT 
        data 
    FROM 
        webform_submitted_data 
    WHERE 
        cid = 1 
    AND 
        nid = 6 
    AND 
        sid = " 
    $row['sid']; 
    People have their own styles, but clarity when it comes to troubleshooting SQL is important.

    So executing the query becomes
    PHP Code:
    echo mysql_query($sql) or die (mysql_error()); 

    You can also
    PHP Code:
    echo $sql
    and it should output something like:
    Code:
    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



    ictus==""

  16. #16
    SitePoint Member
    Join Date
    Jul 2012
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  17. #17
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    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
    ictus==""

  18. #18
    SitePoint Member
    Join Date
    Jul 2012
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code SQL:
    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:

    Code SQL:
    $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:


    Code PHP:
    	<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. #19
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    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 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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  20. #20
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    Welcome to SitePoint Forums

    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.)
    You mean PHP: Hypertext Pre-precessor. It's a recursive acronym.

  21. #21
    SitePoint Member
    Join Date
    Jul 2012
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi guys you have been a great help, thanks so much. I got the button and query working I am faced now with a different issue....working on a same thing but a different form I am trying to find out "How to update details of the current logged in user?" All suggestions are welcome. I need this urgently my deadline is in a few days time


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •