Using Select And Update In A Form

[Wagtail]

Hi Larry and forum members,

 

I could please use some help as how to organize my code. What I'd like to do is retrieve some info using a SELECT query and then follow up with an UPDATE query.

 

The problem I'm experiencing is the combining of the two.

 

I have created a form with empty text inputs whereby I can update records in a database. Above each empty text input the current record must be displayed, which is retrieved using the SELECT query.

 

So my form might look like this:

 

Please use the form to update your details

 

Current name: Homer Simpson

// empty text input for new name

 

Current telephone number: 4853090142

// empty text input for new telephone number

 

current location: Springfield

// empty text input for new location

 

etc....

 

Please note that my form and queries are of course not complete. I'm just wondering how the code should be structured.

 

Thank you in advance!

<?php



$q = "SELECT name, location FROM table1 WHERE name = 'Homer Simpson' ";
    $r = @mysqli_query($dbc, $q);   
  
    $num = mysqli_num_rows($r);
    if ($num > 0) { 
        $row = mysqli_fetch_array($r, MYSQLI_ASSOC); 
print'<p class="name">'.$row['name'] . '</p>
<p class="location">'.$row['location'] . '</p>';
    }
    
if ($_SERVER['REQUEST_METHOD'] == 'POST') {    
    
    
 $newname = mysqli_real_escape_string($dbc, trim($_POST['name']));   
$q = "UPDATE table1 SET name='$newname' WHERE name='Homer Simpson' ";		
			$r = @mysqli_query($dbc, $q);
			
			if (mysqli_affected_rows($dbc) == 1) {} // If it ran OK.

				// Print a message.




}

?>

<h1>Please use the form to update your details</h1>
<form action="" method="post">
	<p>Current name: <input type="text" name="name" size="20" maxlength="60" value="<?php if (isset($_POST['name'])) echo $_POST['name']; ?>"  /> </p>

	<p><input type="submit" name="submit" value="Change Password" /></p>
</form>

[Larry]

How about you use the existing values as the pre-filled values for the form elements? Then when the form is submitted you can do an UPDATE using all the form values, whether they changed or not.

[Wagtail]

Hi Larry,

 

 

do you mean like this?

<p>change name: <input type="text" name="name" size="20" maxlength="60" value="'.$row['name'].'" /> </p>

I've been trying for a few hours but haven't managed to get it right. If I use the above method then what happens to checking if the new value (name etc) has been set, validation etc.?

 

 

I could also echo out the current name and then have an empty text input next to it, as in the following code. Can I print out html which includes a conditional as I'm trying to do?

<?php



$q = "SELECT name, location FROM table1 WHERE name = 'Homer Simpson' ";
    $r = @mysqli_query($dbc, $q);   
  
    $num = mysqli_num_rows($r);
    if ($num > 0) { 
        $row = mysqli_fetch_array($r, MYSQLI_ASSOC); 
print'<h1>Change your details</h1>
<form action="" method="post">
	<p>'.$row['name'] . '<input type="text" name="name" size="20" maxlength="60" value="';
if (isset($_POST['name'])) echo $_POST['name'];
print'"  /> </p>
	
	<p><input type="submit" name="submit" value="Change Password" /></p>
</form>';





    }
    
if ($_SERVER['REQUEST_METHOD'] == 'POST') {    
    
  
 $newname = mysqli_real_escape_string($dbc, trim($_POST['name']));   
$q = "UPDATE table1 SET name='$newname' WHERE name='Homer Simpson' ";		
			$r = @mysqli_query($dbc, $q);
			
			if (mysqli_affected_rows($dbc) == 1) {} // If it ran OK.

				// Print a message.




}

?>

Thank you for your help!

[Larry]

There are examples of this in the book, by the way. I think in the PHP & MySQL chapter, there should be an example on UPDATE that directly applies here.

 

As for your questions, the validation would be exactly the same, it's just that the value is being pre-set using a database-stored value. 

 

As for your conditional, that's kind of right, but you're referring to $_POST there when the value is actually coming from the database the first time.

[Wagtail]

Hi Larry,

 

thank you for the quick reply.

 

I've gone through the book multiple times but cannot recall having seen such an example (returning information and then updating it with new values).

 

In my example I've used $_POST but the value isn't coming from the database. I used <p>'.$row['name'] . ' to return the value from the database. The $_POST is for the new value.

 

Must I use something like if (isset($_POST[$row['prop_name']])) as my conditional?

 

 

Thank you.

[Larry]

If you're editing an existing record, the first time the page is loaded, the data will be in $row. Upon form submission, the form's data will be in $_POST. $_POST[$row['whatever'] will never exist. I would write the conditional so that it sets the form's value to $_POST['whatever'], if that value exists, and to $row['whatever'] otherwise.

[Wagtail]

Thanks Larry,

 

I have tried this but am receiving:

 

Parse error: syntax error, unexpected '{'

 

The { is the first curly brace after

if (isset($_POST['name'])

<?php



$q = "SELECT name FROM table1 WHERE name = 'Homer Simpson' ";
    $r = @mysqli_query($dbc, $q);   
  
    $num = mysqli_num_rows($r);
    if ($num > 0) { 
        $row = mysqli_fetch_array($r, MYSQLI_ASSOC); 
print'<h1>Change your details</h1>
<form action="" method="post">
	<p>change name: <input type="text" name="name" size="20" maxlength="60" value="';
	
if (isset($_POST['name']) {
    
    echo $_POST['name'];
}
    
    else {
    
    echo $row['name'];
    
    }
    
    
    print'"  /> </p>
	
	<p><input type="submit" name="submit" value="Change Password" /></p>
</form>';



    }

Thank you for your assistance!

[Larry]

What's the full error message?

[Wagtail]

I just get the following error:

 

 Parse error: syntax error, unexpected '{' in C:\wamp\www\testincludes\testupdates.php on line 75

 

Line 75 corresponds to

if (isset($_POST['name']) {

[Larry]

You're missing a closing parentheses that would come before the {

[Wagtail]

I missed that! I guess 'doh!' is appropriate.

 

It's finally working. Thank you very much for the great service.

 

Hope you enjoy the weekend.

[Larry]

You're quite welcome. Glad it's working. Let me know if you have any further questions or problems.

[Wagtail]

Hi Larry,

 

sorry to do this, but I hope that you could please help me out with another issue that I'm experiencing. I'm using a while loop to print out the current records of a user, which the user can then update themselves. For this example I'm just looping out a user's skills and an empty text input for each new skill. The problem is that the text inputs all have the same structure – name='skill'. When I do an update, all of that user's skills will have the same skill (from the last text input).

 

What must I do so that each text input updates only its associated row? This probably entails setting the name to an array – name='skill[$i]'?

 

 

Thank you!

if ($num > 0) { 
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) { 
print'<p>'.$row['skill'].'</p>
<input type="text" name="skill" size="" maxlength="" value="';
if (isset($_POST['skill'])) echo $_POST['skill'];
print'"  /> </p>';


}
}


if ($_SERVER['REQUEST_METHOD'] == 'POST') {    
      
$newskills = mysqli_real_escape_string($dbc, trim($_POST[skill'])); 
$q = "UPDATE  table1 INNER JOIN table2 USING (some_id) SET  skill='$newskills' WHERE user_id = {$_SESSION['user_id']}  ";		

[Larry]

Yeah, you just set the name of the input to skill[] to create an array. If the ID values are meaningful, you could use $skill[X] as you suggested.

 

You'll also need to rethink your logic on the UPDATE query as the skills may or may not change with the form submission.

[Wagtail]

Hi Larry,

 

please excuse the late reply. I will try that, thank you.