Jump to content
Larry Ullman's Book Forums

Ex1 - How Create On Update Since The Add_Page.php


Recommended Posts

Hello Larry,

 

Yes, with the ex1 we could add page.

But how create an update page ?

it's CRUD system?

Where i could find it in one of your book ?

 

So first personaly, i create  the list_page  where i find all the list of pages from an user.

 

//path to page Update

 <td><a href="author_list_page_update.php?page_id=<?php echo $row['page_id'];?>">Mise à jours</a></td>

i create the page UPDATE  for this user.

 

first on this page UPDATE  i select all attributes of this page

$q = "SELECT * FROM pages 
	WHERE (status='$st', title='$t', summary='$s', ingredient='$i', recipe='$i', tips='$t',filename='$f', category ='$cat_id', tag='$tag_id') )"; 

and after 

with the form  "update"

 


$q="UPDATE pages SET status='$st',title='$t', summary='$s', ingredient='$i', recipe='$i', tips='$t',filename='$f', category ='$cat_id', tag='$tag_id' ";
	$r = @mysqli_query($dbc, $q);
	

 My first problem that any value appear in the input of my form.

<div class="form-group">
<label for="title">titre</label>
<input type="text" name="title" value="<?php  if(isset($_POST['title']) ) echo $_POST['title']; ?>" />
</div>

a little advice will be cordially accept.

 

Thanks

Link to comment
Share on other sites

Hello Hartley,

I think, for instance i use the variable of my prepare statement but now i think that is perhaps not the right thing.

 

i 'm trying to use with another method.

// from page update_list_addpage.php
$q = "SELECT * FROM pages 
	WHERE (status='$st', title='$t', summary='$s', ingredient='$i', recipe='$i', tips='$t',filename='$f', category ='$cat_id', tag='$tag_id') )"; 
	
	
	$r = @mysqli_query($dbc, $r);
	$num =@mysqli_num_rows($r); 
// from page add_page.php
if(empty($add_page_errors)){
		$q = "INSERT INTO pages(cat_p_id,tag_p_id,user_id,title,summary,ingredient,recipe,tips, status) VALUES (?,?,?,?,?,?,?,?,?)";
		$stmt = mysqli_prepare($dbc, $q);
		
		mysqli_stmt_bind_param($stmt, 'iiissssss',$_POST['category'],$_POST['tag'],$_POST['user_id'],$st,$t,$s,$i,$r,$ti );
		$allowed='<div><p><span><br><a><img><h1><h2><h3><h4><ul><ol><li><blockquote>';
		$st= strip_tags($_POST['status']);
		$t= strip_tags($_POST['title']);
		$s= strip_tags($_POST['summary'], $allowed);
		$i= strip_tags($_POST['ingredient'], $allowed);
		$r= strip_tags($_POST['recipe'], $allowed);
		$ti= strip_tags($_POST['tips'], $allowed);
		
		mysqli_stmt_execute($stmt);

thanks for the reflexion

 

i work about that.

 

cordialy

Link to comment
Share on other sites

Laurent, maybe I can help with this as I've recently created a massive update form based on a registration form that HartleySan wrote for the same site.

 

First of all, when the user accesses the page, the form needs the previous values already stored in the database.  The way HartleySan and I go about this is like this:

if ($_SERVER['REQUEST_METHOD'] === 'POST') {

    Assign $_POST values to variables and validate them.

    ex.) $email = $_POST['email'];

    if (is_empty($errors)) {

        update the database

    }

else {

    Get the original values from the database and assign them to variables.

    ex.) mysqli_stmt_bind_results($stmt, 's', $e);

         $email = $e;

}

Create the form and pass in the variables to each input.

ex.) <input id="email" name = "email" type="email" value="<?php echo $email>">

The point is that you can't do things exactly the same way that Larry does with a usual insert form.  You have to change the "sticky form" logic from within the form functions and replace them with simple echos of a value that you pass to the functions as a parameter.

 

As an example:

  function makeTextInput($id, $label, $default = '', $errors, $type = 'text') {
    
    $error = false;
    
    foreach ($errors as $k => $v) {
      
      if ($k === $id) {
        
        $error = true;
        
        break;
        
      }
      
    }
    
    $return_str = '<label for="' . $id . '" class="input-label-top';
    
    if ($error) {
      
      $return_str .= ' error_input_label';
      
    }
    
    $return_str .= '">' . $label . '</label>
    
    <input type="' . $type . '" id="' . $id . '" name="' . $id . '"';
    
    $return_str .= ' value="' . htmlspecialchars($default) . '"';
    
    $return_str .= ' class="text_input';
    
    if ($error) {
      
      $return_str .= ' error_input';
      
    }
    
    $return_str .= '">';
	
	if (isset($errors['form_errors'])) {
    
	  if (array_key_exists($id, $errors)) {
	  
		$return_str .= '<p class="form_error_message">' . $errors[$id] . '</p>';
		
	  }
	
	}

    return $return_str;

}

echo makeTextInput('email', 'Email Address', $email, $errors);

I don't know if this is what you are looking for, but hopefully it gets you on the right path!

 

Matt

Link to comment
Share on other sites

Also, just in case you are not aware of this, when you perform the update and none of the values have changed from those previously stored in the database, the query will return 0 for the number of rows affected!  The best way to check that the update was successful is to test if the execute statement returned 'true'!

 

if (!mysqli_stmt_execute($stmt)) {

 

$errors['system_error'] = 'There was an error updating the database. Please try again.';

 

}

Link to comment
Share on other sites

Thanks Hartley and Matt

 

for all your advice.

 

i am work about this. After reading your tips, i think that values recuperation is bad

for exemple

title=' $title'  and no '$t' as i do.

$q = "SELECT * FROM pages 
	WHERE (status='$st', title='$t', summary='$s', ingredient='$i', recipe='$i', tips='$t',filename='$f', category ='$cat_id', tag='$tag_id') )";  

Really fine Matt, the function "makeTextInput()"

 

cordially

Link to comment
Share on other sites

Hello Hartley,

 

i progress but i have one problem with update 

 

For SELECT it's well, all data are fine.

ERROR ->//An error occurred in script '/Applications/MAMP/htdocs/originalr/html/author_list_page_update.php' on line 81:
mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given
[q] => UPDATE pages SET cat_p_id, tag_p_id, user_id, status, title, filename, summary, ingredient, recipe, tips ) VALUES (?,?,?,?,?,?,?,?,?,?) WHERE page_id=58

                            [stmt] => 

                            [category] => 

                            [user] => 

                            [st] => 

                            [f] => 

                            [s] => 

                            [i] => 

                            [r] => 

                            [ti] => 

                        )
update page code
//ligne 81 // 
$q = 'UPDATE pages SET cat_p_id, tag_p_id, user_id, status, title, filename, summary, ingredient, recipe, tips ) VALUES (?,?,?,?,?,?,?,?,?,?) WHERE page_id='.$page_id;
				
				$stmt = mysqli_prepare($dbc, $q);
				mysqli_stmt_bind_param($stmt,'iiisssssss',$category,$tag,$user,$st,$t,$f,$s,$i,$r,$ti);
				$allowed='<div><p><span><br><a><img><h1><h2><h3><h4><ul><ol><li><blockquote>';
				//$page_id = strip_tags($_GET['page_id']);
				$category = strip_tags($_POST['category']);
				$tag = strip_tags($_POST['tag']);
				$user = strip_tags($_POST['user_id']);
				$st= strip_tags($_POST['status']);
				$t= strip_tags($_POST['title']);
				$s= strip_tags($_POST['summary'], $allowed);
				$i= strip_tags($_POST['ingredient'], $allowed);
				$r= strip_tags($_POST['recipe'], $allowed);
				$ti= strip_tags($_POST['tips'], $allowed);
				$f= strip_tags($_POST['filename']);
				
				/*ce qui est referencé dans la BD
				UPDATE `pages` SET `page_id`=[value-1],`cat_p_id`=[value-2],`status`=[value-3],`tag_p_id`=[value-4],`user_id`=[value-5],`filename`=[value-6],`title`=[value-7],`summary`=[value-8],`ingredient`=[value-9],`recipe`=[value-10],`tips`=[value-11],`date_created`=[value-12],`date_updated`=[value-13] WHERE 1 
				*/
					mysqli_stmt_execute($stmt);
					if(mysqli_stmt_affected_rows($stmt) === 1){
						echo'<div class="alert alert-success"><h3>Page ajoutée </h3></div>';
						mysqli_stmt_close($stmt);
					}else{
						echo 'erreur Update';
					}
			}

I try to find since few hours where is the problem but if you see a mistake, thanks for advice

 

cordialy

Link to comment
Share on other sites

Hello Hartley,

 

i don't find the soluce

perhaps the problem is to my value $filename which is Null on DB ?

For Select i use COALESCE(filename,NULL) but for Update that don't match

 

i try a lot of thing 

for testing the value of $stmt i do

//test
					if(($stmt = mysqli_prepare($dbc, $query)) === false){
						trigger_error(mysqli_error($dbc), E_USER_ERROR);
					 }

but nothing happen

 

i show you my code. certainly you will see something "bizarre"

tomorow i test with PDO system

Why Larry don't use mysqli object ?

 

Thanks very much for suggestion and advice  :)


<?php
require('./is/config.inc.php');

redirect_invalid_user('user_member');
require(MYSQL);
$update_page_errors = array();

$page_title = 'Modifier une recette';


include('./is/header.inc.html');

// page_id testé
if( filter_var($_GET['page_id'], FILTER_VALIDATE_INT, array('min_range'=>1)) ){
	$page_id = $_GET['page_id'];

		//si post
		if ($_SERVER['REQUEST_METHOD'] === 'POST') {
		
			//cat
			if(filter_var($_POST['category'], FILTER_VALIDATE_INT, array('min_range'=>1))){
				$category = $_POST['category'];
				}else{
				$update_page_errors['category'] = 'SVP, une categorie';
				}
			//tag	
			if(filter_var($_POST['tag'], FILTER_VALIDATE_INT, array('min_range'=>1))){
				$tag = $_POST['tag'];
				}else{
				$update_page_errors['tag'] = 'SVP, un tag';
				}
				
			if(!empty($_POST['user_id'])){
				$user_id = escape_data(strip_tags($_POST['user_id']), $dbc);
				}else{
				$update_page_errors['user_id'] = 'SVP, Qui êtes vous?';
				}
				
			if(!empty($_POST['status'])){
				$status = escape_data(strip_tags($_POST['status']), $dbc);
				}else{
				$update_page_errors['status'] = 'SVP, brouillon?';
				}
			
			//test champ 
			 if(!empty($_POST['title']) ){
					$title = escape_data(strip_tags($_POST['title']), $dbc);
					}else{
					$update_page_errors['title'] = 'SVP, un titre';
					}
								
				if(!empty($_POST['summary'])){
				$allowed = '<div><p><span><br>';
					$summary = escape_data(strip_tags($_POST['summary'], $allowed), $dbc);
					}else{
					$update_page_errors['summary'] = 'SVP, un résumé.';
					}
				
				
				if(!empty($_POST['ingredient'])){
				$allowed = '<div><p><span><br>';
					$ingredient = escape_data(strip_tags($_POST['ingredient'], $allowed), $dbc);
					}else{
					$update_page_errors['ingredient'] = 'SVP, ingredient';
					}
					
				if(!empty($_POST['filename'])){
					$filename = escape_data(strip_tags($_POST['filename']), $dbc);
					}else{
					$update_page_errors['filename'] = 'SVP,image';
					}
				
				
				if(!empty($_POST['recipe'])){
				$allowed = '<div><p><span><br>';
					$recipe = escape_data(strip_tags($_POST['recipe'], $allowed), $dbc);
					}else{
					$update_page_errors['recipe'] = 'SVP, recipe';
					}
				
				if(!empty($_POST['tips'])){
				$allowed = '<div><p><span><br>';
					$tips = escape_data(strip_tags($_POST['tips'], $allowed), $dbc);
					}else{
					$update_page_errors['tips'] = 'SVP, tips';
					}
				
			//si pas de probleme 
			if (empty($update_page_errors)){
			
					$query = 'UPDATE pages SET cat_p_id=?,tag_p_id=?,user_id=?,status=?,title=?,filename=?,summary=?,ingredient=?,recipe=?,tips=?  WHERE page_id=?';
					
					//test
					if(($stmt = mysqli_prepare($dbc, $query)) === false){
						trigger_error(mysqli_error($dbc), E_USER_ERROR);
					 }
					mysqli_stmt_bind_param($stmt,'iiisssssssi',$category,$tag,$user_id,$status,$title,$filename,$summary,$ingredient,$recipe,$tips,$page_id);
						$page_id = strip_tags($_GET['page_id']);
						$allowed='<div><p><span><br><a><img><h1><h2><h3><h4><ul><ol><li><blockquote>';
						
						$category = strip_tags($_POST['category']);
						$tag = strip_tags($_POST['tag']);
						$user_id = strip_tags($_POST['user_id']);
						
						$status= strip_tags($_POST['status']);
						$title= strip_tags($_POST['title']);
						$summary= strip_tags($_POST['summary'], $allowed);
						$ingredient= strip_tags($_POST['ingredient'], $allowed);
						$recipe= strip_tags($_POST['recipe'], $allowed);
						$tips= strip_tags($_POST['tips'], $allowed);
						$filename= strip_tags($_POST['filename']);
						
				//test	
				if (mysqli_stmt_execute($stmt) === false) {   
				trigger_error(mysqli_stmt_error($stmt), E_USER_ERROR);
				}
						
					/*****
					 attention dans la base filename est a NULL donc ----> COALESCE(filename,NULL) ok pour SELECT mais pas pour pour UPDATE
					ce qui est dans la BD
					UPDATE `pages` SET `page_id`=[value-1],`cat_p_id`=[value-2],`status`=[value-3],`tag_p_id`=[value-4],`user_id`=[value-5],`filename`=[value-6],`title`=[value-7],`summary`=[value-8],`ingredient`=[value-9],`recipe`=[value-10],`tips`=[value-11],`date_created`=[value-12],`date_updated`=[value-13] WHERE 1 
					
					******/	
					$count = mysqli_stmt_affected_rows($stmt);
					
						mysqli_stmt_execute($stmt);
						
							if(mysqli_stmt_affected_rows($stmt) === 1){
								echo'<div class="alert alert-success"><h3>Page mise a jour </h3></div>';
								/*printf("%d Row inserted.\n", mysqli_stmt_affected_rows($stmt));*/
								mysqli_stmt_close($stmt);
							}else{
							
								echo 'ET non il y a une erreur Update';
								printf("%d Row inserted.\n", mysqli_stmt_affected_rows($stmt));
							}
						}
					
					
				
			
			//retrouver les données de la base
			}else{
					
					$q ='SELECT page_id,cat_p_id,status,tag_p_id,user_id,COALESCE(filename,NULL),title,summary,ingredient,recipe,tips FROM pages WHERE page_id= ? ';
						$stmt = mysqli_prepare($dbc, $q);
						
						//mysqli_stmt_bind_param($stmt,'iisiissssss',$pid,$cat,$st,$tag,$user,$f,$t,$s,$i,$r,$ti);
						mysqli_stmt_bind_param($stmt,'i',$page_id);
						
						mysqli_stmt_execute($stmt);
						mysqli_stmt_store_result($stmt);
							if(mysqli_stmt_affected_rows($stmt) === 1){
							// $pid,$cat,$tag,$user,$st,$t,$s,$i,$r,$ti,$f
							mysqli_stmt_bind_result($stmt,$page_id,$category,$status,$tag,$user_id,$filename,$title,$summary,$ingredient,$recipe,$tips);
							mysqli_stmt_fetch($stmt);
							
							
						echo 'hello, toto le SELECT marche';
						mysqli_stmt_close($stmt);
					}//fin  de labase	
			}//errIf
		
	
//
}else{
trigger_error('pas de reponse car pas de numero pour page_id');
}

require('./is/form_functions.inc.php');
?><form accept-charset="utf-8" action="author_list_page_update.php?page_id=<?php echo $_GET['page_id'];?>" method="post" enctype="multipart/form-data" class="form-signin">
<h2 class="form-signin-heading">Modifier votre recette</h2>

<!--retr-->

<input type="hidden" name="page_id" id="page_id" value="<?php echo $_GET['page_id'];?>" />
<input type="hidden" name="user_id" id="user_id" value="<?php echo $_SESSION['user_id'];?>" />

<div class="form-group">
	<label for="status" class="control-label">Statut de la page</label>
		<select name="status" class="form-control">
		<option value="<?php  if(isset($status) ) echo !empty($status)?$status:''; ?>"><?php  if(isset($status) ) echo !empty($status)?$status:''; ?></option>
			<option value="draft">Brouillon</option>
			<option value="live">Publier</option>
		</select>
</div>

<?php
//cat
echo'<div class="form-group';
	if(array_key_exists('category', $update_page_errors)) echo 'has-error';
	echo '"><label for="category" class="control-label">La catégorie</label>
	<select name="category" class="form-control"><option>Sélectionnez</option>';
		$q="SELECT cat_id, category FROM categorie ORDER BY cat_id ASC";
		$r= mysqli_query($dbc,$q);
		while ($row=mysqli_fetch_array($r, MYSQLI_NUM)) {
			echo "<option value =\"$row[0]\"";
			if(isset($category) && ($category == $row[0])) echo 'selected = "selected"';
			echo ">$row[1]</option>\n";
			}
	echo '</select>';
	if(array_key_exists('category', $update_page_errors)) echo '<span class="help-bloc">' .$update_page_errors['category'].'</span>';
	echo'</div>';

//tag
echo'<div class="form-group';
	if(array_key_exists('tag', $update_page_errors)) echo 'has-error';
	echo '"><label for="tag" class="control-label">Le tag</label>
	<select name="tag" class="form-control" >
		<option>Sélectionnez</option>';
		$q="SELECT tag_id, tag FROM tag ORDER BY tag ASC";
		$r=mysqli_query($dbc,$q);
	while ($row=mysqli_fetch_array($r, MYSQLI_NUM)) {
			echo "<option value=\"$row[0]\"";
			if(isset($tag) && ($tag == $row[0])) echo 'selected = "selected"';
			echo ">$row[1]</option>\n";
			}
	echo '</select>';
	if(array_key_exists('tag', $update_page_errors)) echo'<span class="help-bloc">'.$update_page_errors['tag'].'</span>';
	echo'</div>';
?>

<div class="form-group">
<label for="title">titre</label>
<input type="text" name="title" id="title" value="<?php  if(isset($title )) echo !empty($title)?$title:''; ?>" class="form-control"/>
</div>


<div class="form-group">
<label for="summary">summary</label>
<textarea name="summary" id="summary"class="form-control"><?php  if(isset($summary) ) echo !empty($summary)?$summary:''; ?></textarea>
</div>

<div class="form-group">
<label for="ingredient">ingredient</label>
<textarea name="ingredient" id="ingredient"class="form-control"><?php  if(isset($ingredient) ) echo !empty($ingredient)?$ingredient:''; ?></textarea>
</div>

<div class="form-group">
<label for="recipe">recipe</label>
<textarea name="recipe" id="recipe"class="form-control"><?php  if(isset($recipe) ) echo !empty($recipe)?$recipe:''; ?></textarea>
</div>

<div class="form-group">
<label for="tips">tips</label>
<textarea name="tips" id="tips"class="form-control"><?php  if(isset($tips) ) echo !empty($tips)?$tips:''; ?></textarea>
</div>

<div class="form-group">
<label for="filename">Image</label>
<input type="text" name="filename" id="filename" value="<?php  if(isset($filename) ) echo !empty($filename)?$filename:''; ?>" class="form-control"/>
</div>

<!--
//dans add_page le systeme pour associer une image
//Pour l'instant je l'ai oté pour simplifier la update
<div class="form-group">
	<label for="add_image">Vous pouvez ajouter une photo à votre recette : (cochez la case)</label>
			<p><label for="add_image">
						<input type="radio" id="add_image_yes" name="add_image" value="yes"> ma photo.</label>
							<?php 
								if(array_key_exists('image',  $update_page_errors)){
								echo '<span class="help-block">'. $update_page_errors['image'].'</span>
								<input type="file" name="image" class="error" />';
								}else{
									echo '<input type="file" name="image" />';
									if( isset($_SESSION['image']))
										{ 
										echo "<br />Currently '{$_SESSION['image']['file_name']}'";
										}
									}
									?>
			</p>
			<p><label for="add_image">
			<input type="radio" id="add_image_no" name="add_image" value="no"> Je n'ai pas de photo.</label>
			</p>
	</div>
-->
	
	<input type="submit" name="update" value="update" id="submit_button" class="btn btn-default" />
 
 </form>

  
 <?php include('./is/footer.inc.html');

Link to comment
Share on other sites

Hello Hartley & Matt

 

I find the soluce. The problem was in the order of the bind_param values on UPDATE.

 

But now i research the system for retrieve the filename value because in the BD is equal to NULL. i use this function  COALESCE(filename,NULL) for the SELECT and it's fine but for the UPDATE  that don't match.

 

"une piste" perhaps ? otherwise i do without this value.

 

In any event, so thanks for advice.

 

Cordialy

Link to comment
Share on other sites

  • 2 weeks later...
  • 2 weeks later...

Hello Larry,

 

thanks for interest of my problem.

 

I work about an another question about your "placing content in Multiple Categorie".

 

Your book is very interesting but for this "plus" the explain is not exhaustive.

 

I look about the  COALESCE(filename,NULL) problem tonight(live in France, Paris)

 

thanks

Link to comment
Share on other sites

Hello Hartley,

 

sorry but i post a query about multiple categorie( chap.12 /p.406-407)

http://www.larryullman.com/forums/index.php?/topic/3106-ex1-html-content-multiple-cat/

 

for my problem of Update a NULL value. I don't find the right soluce for 

$query = 'UPDATE pages SET cat_p_id=?,tag_p_id=?,user_id=?,status=?,title=?,summary=?,ingredient=?,recipe=?,tips=?,filename=?, date_updated=NOW() WHERE page_id='.$page_id;

if i try this query, if the filename are the value ' NULL' before the query $query, after the value filename in my BD is empty.


 


But  if i use the system COALESCE(filename,NULL) , i have this report error
[errno] => 1064
[sqlstate] => 42000
[error] => 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 '(filename=?,NULL) , date_updated=NOW() WHERE page_id=81' at line 1 

So, how write the UPDATE for a value as NULL  in BD

and sometimes is not NULL (because a value is associated)

but who must be  a value in the DB?

 

 

thanks for soluce

Link to comment
Share on other sites

There are two ways to add null via a prepared statement:

  1. Dynamically generate the query, and add NULL as necessary; or
  2. Assign null to a variable, and then bind the variable.

 

For example, your query could be:

$query = "UPDATE pages SET cat_p_id = ?, tag_p_id = ?, user_id = ?, status = ?, title = ?, summary = ?, ingredient = ?, recipe = ?, tips = ?, filename = NULL, date_updated = NOW() WHERE page_id = $page_id;";

Or, you could set up the $file_name variable as follows:

$file_name = $file_name ? $file_name : null;

That make sense?

 

As a side note, you may want to use a question mark for page_id as well, if $page_id is user-provided.

Link to comment
Share on other sites

 Share

×
×
  • Create New...