Bazaar – php example code – part 17 – ordering tables by category

Article focus on way how to display content of list inserted in table order by activating visual elements in header of appropriate table. Our focus is enable reorder listening ascending or descending way along selected column.

Expectations from ordering system

Content of the lists display usefull information for page users. Quick way how to obtain relevant data is ordering them ascending or descending by interesting category. For implementation of ordering ability in our tables we must:

  • add links (best way is to associate them with nice graphics that is intuitive for the user) for ascending or descending ordering in header of our table
  • generate GET links for submitting necessary data for ordering functionality
  • prepare appropriate scripts handling calculations for number of displayed data, use LIMIT functionality in SQL queries
  • generate pagination links at the left bottom part of our lists for browsing functionality through our results

How ordering works, brief introduction – our code must take in mind number of items for display and number of list items for display. After calculations number of browsable page, function for generating pagination links must generate appropriate links. User select appropriate page or next/ previous page. Data from user are send to themeself as GET links. From obtained data outr code prepare SQL request containing LIMIT keyword fro slecting only rows from appropriate calculated position and with expected number of selected items. In table output ist next displayed selected data and links on page must be displayed with respect of actually selected page and ordering.

Next pictures visualy display our expectations/ implementations of this functionality.

Links for ordering output data along selected category
Pagination on the bottom of the page

Code implementing pagination on page

First discused code is responsible for generation of links at the bottom part of a page.

Our code page named functions.php contains function invoked in other pages handling pagination functionality. Contentn of this function is:

<?php
/**
 * Changelog 
 * v 1.0 – first working version, update 6.11.2020
 * v 1.1 – for ordering option was updated header of function by adding $sort –> $sort_by and new $order 
 *             $odrder variable can by 1 for ASC order and -1 for DESC order, update 28.11.2020
 */
function generate_page_links($user_search, $sort_by, $order, $cur_page, $num_pages) { //($user_search, $sort_by, $order, $cur_page, $num_pages);
    $page_links = „“;
    echo „<br>“;
   
    // if this is not first in row, we need generate the „previous“ link
    if  ($cur_page > 1) {
        $page_links .= ‚<a id=“pagination“ href=“‚ . $_SERVER[‚PHP_SELF‘] . ‚?usersearch=‘
        .$user_search . ‚&sort_by=‘ . $sort_by . ‚&order=‘ . $order .’&page=‘ . ($cur_page – 1) . ‚“><img src=“./images/previous_icon.png“ alt=“previous image“ width=“30″ height=“30″></a>‘;
    } else {
        $page_links .= ‚<span id=“pagination“><img src=“./images/previous_icon.png“ alt=“previous image“ width=“30″ height=“30″></span> ‚;
    }
    // Loop through the pages generating the page numbered links
    for($i = 1; $i <= $num_pages; $i++) {
        if  ($cur_page == $i) {  
        $page_links .= ‚<span id=“pagination“>‘ . $i. ‚</span>‘; // span inline element mark non a tag (unlinked number) as pagination for further formating by css
        } else {
        $page_links .= ‚<a id=“pagination“ href=“‚ . $_SERVER[‚PHP_SELF‘] . ‚?usersearch=‘
        .$user_search . ‚&sort_by=‘ . $sort_by . ‚&order=‘ . $order .’&page=‘ . $i . ‚“>‘ . $i . ‚</a>‘;
        }
    } 
    // If this page is not last in row, generate „next“ link
    if  ($cur_page < $num_pages) {
        $page_links .= ‚<a id=“pagination“ href=“‚ . $_SERVER[‚PHP_SELF‘] . ‚?usersearch=‘
        .$user_search . ‚&sort_by=‘ . $sort_by . ‚&order=‘ . $order .’&page=‘ . ($cur_page + 1) . ‚“><img src=“./images/next_icon.png“ alt=“next image“ width=“30″ height=“30″></a>‘;
    } else {
        $page_links .= ‚ <span id=“pagination“><img src=“./images/next_icon.png“ alt=“next image“ width=“30″ height=“30″></span>‘;
    }
  
    return $page_links;
}
?>

In our code are at current time not implemented all expeted functionality. User serarch is not passed but all other arguments are used for passing necessary data for genration of pagination links.

Oru code must handle situations when there are no more page next or previous, and must be able to generate all other numbered page links. All links are selectable on focus by user.

Real implementation on pages

Next part of index page is responsible for ordering data and iterating through all available pages with content. Our code relevant for this functions is highlighted by blue color.

— output  omited —
/***********************************************************/
/*             Output in paginated form                                                 */
/***********************************************************/
 /***
  *  Display pagination on the page – part included to listening in this area
  */
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user ‚root‘ with no password) */
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);
//GET data for pagination send to page herself
//calculate pagination information
$cur_page = isset($_GET[‚page‘]) ? $_GET[‚page‘] : 1;
// results per page default declater as 5 on top of page and changed in submitt part after reset button handling $results_per_page = 5;
$skip = (($cur_page -1) * $results_per_page);
// Check connection
if($dbc === false){
    die(„ERROR: Could not connect to database – stage of article listing. “ . mysqli_connect_error());
}
// first  question to database table for obtaining number of published items in a database – obtain value for $total
$sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY item_id DESC „;  // read in reverse order of score – highest score first              
$output_for_number_rows_count = mysqli_query($dbc, $sql); // query database
$total = mysqli_num_rows($output_for_number_rows_count);    //get number of rows in databse             
    
            
//older approach without SORT functionality read all rows (data) from guestbook table in „test“ database
// $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY item_id DESC LIMIT $skip, $results_per_page“;  // read in reverse order of score – highest score first
/**
 * SORTING – PART II. Here is into sql request implemented along which filed and how ascend or desc is output ordered
 */
if(isset($_GET[‚sort_by‘]) && isset($_GET[‚order‘]) ){
    // take a data from GET link generated by adminscript
    $sort_by = htmlspecialchars($_GET[‚sort_by‘]);
    $order = htmlspecialchars($_GET[‚order‘]);
    // debug echo „sort_by“.$sort_by;
    // debug echo „order“.$order;
    if(($sort_by == „name“) && ($order == „1“)) { // along name and ASC order
        $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY name_of_item ASC LIMIT $skip, $results_per_page“; 
    };
    if(($sort_by == „name“) && ($order == „-1“)) { // along name and DESC order
        $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY name_of_item DESC LIMIT $skip, $results_per_page“; 
    };
    if(($sort_by == „price“) && ($order == „1“)) { // along price and ASC order
        $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY price_eur ASC LIMIT $skip, $results_per_page“; 
    };
    if(($sort_by == „price“) && ($order == „-1“)) { // along price and DESC order
        $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY price_eur DESC LIMIT $skip, $results_per_page“; 
    };
    if(($sort_by == „category“) && ($order == „1“)) { // along category and ASC order
        $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY subcategory_id ASC LIMIT $skip, $results_per_page“; 
    };
    if(($sort_by == „category“) && ($order == „-1“)) { // along category and DESC order
        $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY subcategory_id DESC LIMIT $skip, $results_per_page“; 
    };
    if(($sort_by == „default“)) { // along category and DESC order
        $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY item_id DESC LIMIT $skip, $results_per_page“; 
    };
} else {  // first run without ordering – no get link generated
    $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY item_id DESC LIMIT $skip, $results_per_page“;  // read in reverse order of score – highest score first
}
/**********************************************************************/
/*        Output in Table – solution 1 – for debuging data from database         */
/**********************************************************************/
// if data properly selected from guestbook database tabele
echo „<br><br>“;
echo „<h4>Latest added items for you! </h4>“;
echo „<br>“;
/***
 *  Obtaining wished number of item per page – option for select
 */
?>
<form  method=“post“ action=“<?php echo $_SERVER[‚PHP_SELF‘]; ?>“>
      
<div class=“form-group“>
<label> Set expected number of items per page -5 is default:</label>
<input list=“number_per_page“ name=“number_per_page“ placeholder=“please select or write nr.“>
      <datalist id=“number_per_page“> <!– must be converted in subcategory_id in script – marked with (*) –>
          <option value=“5″>
          <option value=“10″>
          <option value=“15″>   
          <option value=“20″>   
          <option value=“50″>
          <option value=“100″>      
        </datalist>
     
    
      
      <button type=“submit“ name=“nr_of_pages“ class=“btn btn-warning“> Use selected number of pages! </button>
</div>
</form>
<?php
echo „<br>“; echo „<br>“;
if($output = mysqli_query($dbc, $sql)){
    if(mysqli_num_rows($output) > 0){  // if any record obtained from SELECT query
        // create table output
        echo „<table>“; //head of table
            echo „<tr>“;
                //echo „<th>id</th>“;
                // functionality for ordering result
                /**
                 * SORTING – PART I. Here are generated GET links for UP/DOWN ordering by appropriate category
                 */
                echo ‚<th>Name  <br /><a id=“SORT“ href=“index.php?sort_by=name&amp;order=1″> <img id=“arrow“ src=“./images/arrowup.png“> </a>
                                <a id=“SORT“ href=“index.php?sort_by=name&amp;order=-1″> <img id=“arrow“ src=“./images/arrowdown.png“> </a> </th>‘; //order 1 up -1 down
                echo ‚<th>Price <br /><a id=“SORT“ href=“index.php?sort_by=price&amp;order=1″> <img id=“arrow“ src=“./images/arrowup.png“> </a>
                                <a id=“SORT“ href=“index.php?sort_by=price&amp;order=-1″> <img id=“arrow“ src=“./images/arrowdown.png“> </a></th>‘;
                echo ‚<th>Category <br /><a id=“SORT“ href=“index.php?sort_by=category&amp;order=1″> <img id=“arrow“ src=“./images/arrowup.png“> </a>
                                <a id=“SORT“ href=“index.php?sort_by=category&amp;order=-1″> <img id=“arrow“ src=“./images/arrowdown.png“> </a> </th>‘;
                echo „<th>Screenshot1</th>“;
                echo „<th>More info</th>“;
                
                
            echo „</tr>“;
        while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop
            echo “ <div class=\“mailinglist\“> “ ;
            echo „<tr>“;
                //echo „<td>“ . $row[‚item_id‘] . „</td>“;
                echo „<td class=\“item_name\“>“ . $row[‚name_of_item‘] . „</td>“;
                echo „<td class=\“price\“>“ . $row[‚price_eur‘] . “ € </td>“;
                            /* convert category_id in to category and subcategory */
                            $subcategory_id = $row[‚subcategory_id‘];
                            $category_idsupl    = „“ ;
                            $subcategory_idsupl = „“ ;
                            // (*) — conversion of category and subcategory into category%id
                                
                                //create SELECT query for category and subcategory names from database
                                 $sql_supl = „SELECT category, subcategory FROM bazaar_category WHERE subcategory_id = „.“‚$subcategory_id'“ ;
                                /*$output_supl = mysqli_query($dbc, $sql_supl);
                                $row_supl = mysqli_fetch_array($output_supl);
                                $category_id    = $row_supl[‚category‘] ;
                                $subcategory_id = $row_supl[‚subcategory‘] ;
                                echo „<td>“ . $category_id.“/“.$subcategory_id.“</td>“;*/
                                // execute sql and populate data list with existing category in database
                                if($output_supl = mysqli_query($dbc, $sql_supl)){
                                    if(mysqli_num_rows($output_supl) > 0){  // if any record obtained from SELECT query
                                        while($row_supl = mysqli_fetch_array($output_supl)){ //next rows outputed in while loop
                                            
                                            $category_idsupl    = $row_supl[‚category‘] ;
                                            $subcategory_idsupl = $row_supl[‚subcategory‘] ;
                                            
                                                
                                        }
                                        
                                          // Free result set
                                        mysqli_free_result($output_supl);
                                    } else {
                                        echo „There is no souch category-subcategory in category table. Please correct your error.“; // if no records in table
                                    }
                                } else{
                                    echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
                                }
                echo „<td>“ . $category_idsupl.“/“.$subcategory_idsupl.“</td>“;
                
                    $image_location = IMAGE_PATH.$row[‚screenshot1‘];
                echo „<td id=\“gray_under_picture\“> <img  src=\“$image_location\“ alt=\“ screenshot of product primary \“  height=\“250\“> </td>“; 
                echo ‚<td colspan=“1″><a id=“DEL“ href=“item.php?item_id=‘.$row[‚item_id‘]. ‚“><img id=“next“ src=“./images/next.png“>   </a></td></tr>‘; //construction of GETable link
            echo „</tr>“;
            echo “ </div> “ ;
        }
        echo „</table>“;
        //count nuber of pages total
        $num_pages = ceil($total / $results_per_page);
        
        //generate navigational page links if we have more than one page
        
        if($num_pages > 1) {
            $user_search = „“; // not implemented yet, then set as clear values
            if(empty($sort_by)) { // if not obtained by get then default order is applied
                $sort_by=“default“;
            };
            if(empty($order)) { // if not obtained by get then default order is applied
                $order=“1″;
            };
            
            // included function for pagination generation function stored in functions.php page
            echo generate_page_links($user_search, $sort_by, $order, $cur_page, $num_pages);
            echo „<br><br>“;
        }
        // Free result set
        mysqli_free_result($output);
    } else{
        echo „There is no item for sell. Please add one.“; // if no records in table
    }
} else{
    echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
}
// Close connection
mysqli_close($dbc);

From our code excerpt can be visible these parts: 1) parts responsible for calculating number of necessary pages for display, 2) part handling ordering logic responsible for creation of SQL queries, 3) table part responsible for display of data and generating links and part 4) invoking function for generating pagination information at the bottom part of page.

Conclusion

Our code fulfil our expectation. Because we used functional approach, maintainability is hard. One big problem that we gained is visible if we work with two or more pagination/ ordering functionality. information about current page is passed by GET link and is one for all ordered tables. When we order along one table, also other tables are iterated by the same page. But this is acceptable solution. because focus of user is only for actually handled part of page.

For futher improvement is way how to maintaing ordering along two or more pagination links best part of focus. Also further abstraction this functionality with OOP implementation can lead to a more reusable part of code.

Full code for further study can be obtained from github here.




Bazaar – php example code – part 16 – administration of users

Article focus on bulding blocks for enabling user administration by page admin. How user list is introduced in page admin management page and way how to change user data in this view.

Introduction of user management

Administrator of the page must be able display info about user, change their role or delete them. User password is sensitive information and can not be save as plain text. Our database store only secure hashes of them.

Basic functionality for management of the user can be described this way. Page admin on their own admin page has user administration section with list of all users. These information are displayed in a table. At the end of appropriate row is link pointing to second context page with option for change category of the user, edit of user data or enable to remove a user.

Only editing of user data lead to another page very similiar to the users profile page, where through the form can be data reedit.

Whole process depict following sequence of screenshots.

Section for display user info on administrators page
Content on linked page for appropriate user management
Part of user data editing page available from invocation on user management page – change_user_data option

Code implementation part

Our solution consist from part generating user list in form a table in admin.php script:

/***********************************************************************/
/*     III. Showing registered user table with option for adminster them        */
/***********************************************************************/  
$results_per_page_3 = 8;  
//calculate pagination information
$cur_page = isset($_GET[‚page‘]) ? $_GET[‚page‘] : 1;
// results per page default declater as 5 on top of page and changed in submitt part after reset button handling $results_per_page = 5;
$skip = (($cur_page -1) * $results_per_page_3); 
// first  question to database table for obtaining number of published items in a database – obtain value for $total
$sql =“SELECT * FROM bazaar_user“;  // read in reverse order of score – highest score first
$output_for_number_rows_count_3 = mysqli_query($dbc, $sql); // query database
$total_3 = mysqli_num_rows($output_for_number_rows_count_3);    //get number of rows in databse  
// querying bazaar_category for listed category items            
//older approach without SORT functionality                     
// $sql = „SELECT * FROM bazaar_item ORDER BY item_add_date DESC LIMIT $skip, $results_per_page“;  // read in reverse order of score – highest score first
/**
 * SORTING – PART II. Here is into sql request implemented along which filed and how ascend or desc is output ordered
 */
if(isset($_GET[‚sort_by‘]) && isset($_GET[‚order‘]) ){
    // take a data from GET link generated by adminscript
    $sort_by = htmlspecialchars($_GET[‚sort_by‘]);
    $order = htmlspecialchars($_GET[‚order‘]);
    // debug echo „sort_by“.$sort_by;
    // debug echo „order“.$order;
    if(($sort_by == „users_id“) && ($order == „1“)) { // along name and ASC order
        $sql = „SELECT * FROM bazaar_user ORDER BY users_id ASC LIMIT $skip, $results_per_page_3“;
    };
    if(($sort_by == „users_id“) && ($order == „-1“)) { // along name and DESC order
        $sql = „SELECT * FROM bazaar_user ORDER BY users_id DESC LIMIT $skip, $results_per_page_3“;
    }; 
    if(($sort_by == „username“) && ($order == „1“)) { // along name and ASC order
        $sql = „SELECT * FROM bazaar_user ORDER BY username ASC LIMIT $skip, $results_per_page_3“;
    };
    if(($sort_by == „username“) && ($order == „-1“)) { // along name and DESC order
        $sql = „SELECT * FROM bazaar_user ORDER BY username DESC LIMIT $skip, $results_per_page_3“;
    };    
        
    if(($sort_by == „nickname“) && ($order == „1“)) { // along name and ASC order
        $sql = „SELECT * FROM bazaar_user ORDER BY nickname ASC LIMIT $skip, $results_per_page_3“;
    };
    if(($sort_by == „nickname“) && ($order == „-1“)) { // along name and DESC order
        $sql = „SELECT * FROM bazaar_user ORDER BY nickname DESC LIMIT $skip, $results_per_page_3“; 
    }; 
    
    if(($sort_by == „write_date“) && ($order == „1“)) { // along name and ASC order
        $sql = „SELECT * FROM bazaar_user ORDER BY write_date ASC LIMIT $skip, $results_per_page_3“;
    };
    if(($sort_by == „write_date“) && ($order == „-1“)) { // along name and DESC order
        $sql = „SELECT * FROM bazaar_user ORDER BY write_date DESC LIMIT $skip, $results_per_page_3“; 
    }; 
    if(($sort_by == „user_role“) && ($order == „1“)) { // along price and ASC order
        $sql = „SELECT * FROM bazaar_user ORDER BY user_role ASC LIMIT $skip, $results_per_page_3“;
    };
    if(($sort_by == „user_role“) && ($order == „-1“)) { // along price and DESC order
        $sql = „SELECT * FROM bazaar_user ORDER BY user_role DESC LIMIT $skip, $results_per_page_3“;
    };
    if(($sort_by == „default“)) { // along category and DESC order
        
        $sql = „SELECT * FROM bazaar_user ORDER BY users_id ASC LIMIT $skip, $results_per_page_3“; 
    };
} else {  // first run without ordering – no get link generated
    $sql = „SELECT * FROM bazaar_user ORDER BY users_id ASC LIMIT $skip, $results_per_page_3“;   // read in reverse order of score – highest score first
}
// processing table output form bazaar_category
echo „<h4>III. Manage List of active bazaar users and roles</h4>“;
//echo „<br>“;
//echo ‚ <button class=“btn btn-secondary btn-lg “ onclick=“location.href=\’managecategory.php\'“ type=“button“>  Create new category-subcategory -> </button>‘;
echo „<br>“; echo „<br>“;
    if($output = mysqli_query($dbc, $sql)){
        if(mysqli_num_rows($output) > 0){  // if any record obtained from SELECT query
            // create table output
            echo „<table>“; //head of table
                echo „<tr>“;
                    echo ‚<th>users_id <br /><a id=“SORT“ href=“admin.php?sort_by=users_id&amp;order=1″> <img id=“arrow“ src=“./images/arrowup.png“> </a>
                    <a id=“SORT“ href=“admin.php?sort_by=users_id&amp;order=-1″> <img id=“arrow“ src=“./images/arrowdown.png“> </a></th>‘;
                    echo ‚<th>username <br /><a id=“SORT“ href=“admin.php?sort_by=username&amp;order=1″> <img id=“arrow“ src=“./images/arrowup.png“> </a>
                    <a id=“SORT“ href=“admin.php?sort_by=username&amp;order=-1″> <img id=“arrow“ src=“./images/arrowdown.png“> </a></th>‘;
                    echo ‚<th>nickname <br /><a id=“SORT“ href=“admin.php?sort_by=nickname&amp;order=1″> <img id=“arrow“ src=“./images/arrowup.png“> </a>
                    <a id=“SORT“ href=“admin.php?sort_by=nickname&amp;order=-1″> <img id=“arrow“ src=“./images/arrowdown.png“> </a></th>‘;
                    echo ‚<th>write_date <br /><a id=“SORT“ href=“admin.php?sort_by=write_date&amp;order=1″> <img id=“arrow“ src=“./images/arrowup.png“> </a>
                    <a id=“SORT“ href=“admin.php?sort_by=write_date&amp;order=-1″> <img id=“arrow“ src=“./images/arrowdown.png“> </a></th>‘;
                    echo ‚<th>user_role <br /><a id=“SORT“ href=“admin.php?sort_by=user_role&amp;order=1″> <img id=“arrow“ src=“./images/arrowup.png“> </a>
                    <a id=“SORT“ href=“admin.php?sort_by=user_role&amp;order=-1″> <img id=“arrow“ src=“./images/arrowdown.png“> </a></th>‘;
                    echo „<th>manage</th>“;
                    
                    
                    
                    
                echo „</tr>“;
            while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop
                echo “ <div class=\“mailinglist\“> “ ;
                echo „<tr>“;
                    echo „<td>“ . $row[‚users_id‘] . „</td>“;
                    echo „<td>“ . $row[‚username‘] . „</td>“;
                    echo „<td>“ . $row[‚nickname‘] . „</td>“;
                    echo „<td>“ . $row[‚write_date‘] . „</td>“;
                    echo „<td>“ . $row[‚user_role‘] . „</td>“;
                     // removal line with removing link line
                
                     
                    // echo „<td  colspan=\“1\“> Manage entry: </td>“; // description on first line
                         echo ‚<td colspan=“1″><a id=“DEL“ href=“manageuser.php?users_id=‘.$row[‚users_id‘] . ‚&amp;username=‘
                         . $row[‚username‘] . ‚&amp;user_role=‘. $row[‚user_role‘] .'“><center><img id=“next“ src=“./images/manageuser.png“></center></a></td></tr>‘; //construction of GETable link
                         // for removecategory.php input
                    
                    
                echo „</tr>“;
                echo “ </div> “ ;
            }
            echo „</table>“;
             //count nuber of pages total
             $num_pages_3 = ceil($total_3 / $results_per_page_3);
                
             //generate navigational page links if we have more than one page
             
             if($num_pages_3 > 1) {
                 $user_search = „“; // not implemented yet, then set as clear values
                 if(empty($sort_by)) { // if not obtained by get then default order is applied
                     $sort_by=“default“;
                 };
                 if(empty($order)) { // if not obtained by get then default order is applied
                     $order=“1″;
                 };
                 
                 // included function for pagination generation function stored in functions.php page
                 echo generate_page_links($user_search, $sort_by, $order, $cur_page, $num_pages_3);
                 echo „<br><br>“;
             }
            echo „<br />“;
            // Free result set
            mysqli_free_result($output);
        } else{
            echo „There is no benchmark result in chart. Please wirite one.“; // if no records in table
        }
    } else{
        echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
    }    
// Close connection
mysqli_close($dbc);
?>

Example now contains ordering functionality, this part will be explained with details in later published articles.

All functionality for specific user management is located in script manageuser.php. This script is invoked through GET link from admin.php page.

Script obtain data from GET link after invocation, but second submitting mechanism with post enable selection from other specific operation.

Changing role from user to admin and vice versa, deletion of user are created with appropriate SQL statment executed at SQL database.

Interesting part of script is responsible for page redirecting:

switch ($operation) {
                case „make_admin„:
                    $sql = „UPDATE bazaar_user SET user_role = ‚admin‘ WHERE users_id = $users_id LIMIT 1“;
                    // execute SQL
                    mysqli_query($dbc, $sql);
                    // confirm executed command
                    echo ‚<p> User <strong>‘ . $username . ‚</strong> with id <strong>‘ . $users_id . ‚</strong> was succesfully promoted as page <b>admin<b>. </p>‘;
                    break;
                case „make_user„:
                  $sql = „UPDATE bazaar_user SET user_role = ‚user‘ WHERE users_id = $users_id LIMIT 1“;
                  // execute SQL
                  mysqli_query($dbc, $sql);
                  // confirm executed command
                  echo ‚<p> User <strong>‘ . $username . ‚</strong> with id <strong>‘ . $users_id . ‚</strong> was succesfully promoted as page <b>user<b>. </p>‘;
                  break;     
  /* to do */  case „change_user_data„: // –> make solution for update other users data – link profile page for another user
                    // next part of code redirect to manageuserdata.php page for administration of appropriate users data by admin
                    
                    ?>
                       <script type = „text/javascript“>
                          
                              function Redirect() {
                                window.location = „./manageuserdata.php?users_id=<?php echo $users_id ?>&username=<?php echo $username ?>“; // send users_id of user to edit must send to manageuserdata.php to be able edit appropriate user and not user extracted from session informatin
                              }            
                              document.write(„You will be redirected to manageuserdata.php page.“);
                              setTimeout(‚Redirect()‘, 0);
                          
                      </script>

After submitting request for change_user_data, javscript redirect currently logged admin on manageuserdata.php and generate GET link with ID and Username.

Content of manageuserdata.php or whole script of project can be obtaned from github here.

Conclusion

Previous text show example of way how to add user management functionality to our bazaarap. This approach demonstrate classical way for user management. Real world application need further security hardening with eliminating GET data exchange. POST method with SESSION verification provide further level of validation.




Bazaar – php example code – part 15 – visual improvements of page

Article focus on further improvements in visual. We look at way how to create main part of all pages more template based and separate header and footer into a scripts. Another visual enhancements introduce background image and other small visual eye candy’s as pictures of arrows, main menu icons and so on.

Expectation from visual improvements

After obtaining basic functionality on our app, we will focus now on further visual improvements. Our focus lead to:

  • generating page header and footer (these parts will be included from appropriate scripts)
  • adding background image to our page
  • add another eye candy as images for appropriate categories in menu

Header and footer scripts

Our goal is separate code generating content of the page in smaller parts responsible only for their own things.

Script headermenu.php include script hederlogo.php and display menu content diferently if user is loged in or not.

Content of script follows:

<?php
   // generate menu if user is loged in or not
         // old solution with cookies if(isset($_COOKIE[‚username‘])) { // loged in user
            require_once(‚headerlogo.php‚);
            
            if(isset($_SESSION[‚username‘])) { // loged in user
                 
                echo ‚<div id=“menu“>‘;
                echo ‚<a class=“navbar-brand“ href=“index.php“><img width=“150″ src=“./images/bazaarheader.png“> Bazaar – best items for a best prices!</a>‘;
                echo ‚<a class=“navbar-brand“ href=“editprofile.php“><img id=“menuimage“ src=“./images/menu_profile.png“> Edit profile </a>‘;
                                
                if(isset($_SESSION[‚user_role‘])==’admin‘) { // if loged user is admin role
                   echo ‚<a class=“navbar-brand“ href=“admin.php“><img id=“menuimage“ src=“./images/menu_admin.png“> Manage your page </a>‘;
               };
               echo ‚<a class=“navbar-brand“ href=“logout.php“><img id=“menuimage“ src=“./images/menu_logout.png“> Logout <b><span id=“username“>‘ .$_SESSION[‚username‘] .'</span></b></a>‘;
               echo ‚</div >‘;
               require_once(‚sell_icon.php‘); // graphic menu item for selling your items
               echo ‚<a class=“navbar-brand“ href=“rss.php“><img src=“./images/rss.png“ width=“45″></a>‘; //rss feed link
               require_once(‚cart_icon.php‘); // small cart icon in menu
               
              } else { // visitor without login
               echo ‚<div id=“menu“>‘;
               echo ‚<a class=“navbar-brand“ href=“login.php“><img id=“menuimage“ src=“./images/menu_login.png“> Log In </a>‘;
               echo ‚<a class=“navbar-brand“ href=“signup.php“><img id=“menuimage“ src=“./images/menu_signup.png“> Sign Up for better membership! </a>‘;
   
               echo ‚<a class=“navbar-brand“ href=“index.php“><img width=“150″ src=“./images/bazaarheader.png“> Bazaar – best items for a best prices!</a>‘;
               echo ‚</div >‘;
             };
             
?>

Headerlogo.php script is very simple

<?php
 echo ‚<img id=“headerlogo“ src=“./images/bazaarheader.png“ alt=“bazaar header image“ >‘;
 echo ‚<br />‘;
?>

Footer.php script can acomodate two widths becasuse our design is created with two fixed width 580px and 1060px.

Content of the script is:

<?php
 // for further rework of the code
 function generate_footer($width) {
  if($width==580) {
    echo ‚<div class=“footer“ >‘;     
    echo ‚<div class=“footer“ id=“footer_container_580„>‘; 
    echo ‚<a class=“navbar-brand“ href=“https://cdesigner.eu“> Visit us on CDesigner.eu </a>‘; 
    echo ‚<a class=“navbar-brand“ href=“rss.php“> Subscribe to newsfeed <img src=“./images/rss.png“ width=“25″> </a>‘; 
    echo ‚</div>‘; 
    echo ‚</div>‘;    
  }
  if($width==1060) {
    echo ‚<div class=“footer“ >‘;     
    echo ‚<div class=“footer“ id=“footer_container_1060„>‘; 
    echo ‚<a class=“navbar-brand“ href=“https://cdesigner.eu“> Visit us on CDesigner.eu </a>‘; 
    echo ‚<a class=“navbar-brand“ href=“rss.php“> Subscribe to newsfeed <img src=“./images/rss.png“ width=“25″> </a>‘; 
    echo ‚</div>‘; 
    echo ‚</div>‘;    
  }
      }
?>

Visual adjustments are created by style.css linked into all of our pages. Next lines display interesting part from style.css relevant to footer:

#footer_container_1060 { /* wider header container for admin page or where it is needed */
  width: 1060px;
  margin-left: 240px;
  border: dotted 1px gray;
  background-color: #363636;
  padding: 10px 10px 10px 10px;
 /* background-image: url(‚../images/bazaar.png‘);
  background-repeat: no-repeat;
   background-attachment: fixed;
  background-position: center; */
  padding-bottom: 240px;
    margin-bottom: -240px;
  
}
#footer_container_580 { /* wider header container for admin page or where it is needed */
  width: 580px;
  margin-left: 240px;
  border: dotted 1px gray;
  background-color: #363636;
  padding: 10px 10px 10px 10px;
 /* background-image: url(‚../images/bazaar.png‘);
  background-repeat: no-repeat;
   background-attachment: fixed;
  background-position: center; */
  padding-bottom: 240px;
    margin-bottom: -240px;
  
}
Display of header and footer – width 1060px
Display of header and footer – width 580px

Background image for page

background image is placed on main body of the HTML by statements in our css:

body  {
  background-image: url(‚../images/background.png‘);
  background-repeat: no-repeat;
  background-attachment: fixed;
  background-size: cover; // image is spread on the background
  
 
}
Example of background image

Conclusion

Previous lines depict simple way hw to vidual improve our app. Visual atraction is very important for all products in new internet era.

Project bazaar has been created from scratsch as educational project. Now we can see that, first separation page elements in their own blocks interconected through a page template will be better solution. Our approach partialy override all existing problems and introduced further maintainability.

Ful code of bazarapp can be obtained from github from here.




Bazaar – php example code – part 14 – handling sold item in database

Article focus on way how to mark sold item in the bazaar_item table for further deletion. Successfully sold item is marked with -1 in cart_number, that is displayed in listing on admin page of bazaar. Admin can see it and delete them. Separate article will focus on way how to order items in lists for better manipulation.

Requirements for item handling

During lifecycle of item we must to manage marking different states of item. When user publish item for sell, item is marked by cart_number field in a database table bazaar_item as 0 and published gain default value of 0 (waiting for admin to make item visible in listening’s).

After admin approval, visible field change its value from 0 to 1. Item now waiting for adding into a cart of buyer. After selecting and adding item into a cart cart_number is changed in to a number representing ID of user that make buy (added item into their own cart).

If usere remove item from cart, cart_number value returns to 0. Another user can add item into a cart and mark them with own ID number.

If user commit cart for final buy, all successfully sold items are marked by -1 in cart_number field.

On admin page, sold item marked with -1 are displayed and after admins decision can be finally removed from a database. Optional functionality for further implementation is option for archiving of soled item by site admin. Another way for automation of removing unused and sold item is creating automated script associated with button for removing any sold item from database table before a some date (as example one month after sold).

Full process of altering of cart_number field is shown on next picture.

Process of handling cart_number filed during lifetime of item

In further rows you can see how our page codes handle this changes during whole lifetime of item for sell.

1. sellitem.php – item added for listening

Because default values for fields published and cart_number set to 0. There is no need for any specific write values into a database table. As you can see in next sql statement:

$sql = „INSERT INTO bazaar_item (name_of_item, price_eur, subcategory_id, users_id, item_add_date, screenshot1, screenshot2, screenshot3, item_description
                            VALUES (‚$name_of_item‘, $price_eur , ‚$subcategory_id‘ , ‚$users_id‘ , now(), ‚$screenshot1‘, ‚$screenshot2‘, ‚$screenshot3‘, ‚$item_description‘ )“;
                            

2. cart.php – mark item as sold after successful commit to buy

Cart.php script is responsible for marking cart_number with -1 after successful buy. This is how it is implemented in our code

             /*************************************************************
             *   obtain data buyed items from this buyer with users_id defined by current SESSION
             */
            //get info about sold items – we must go through all buyed items and send emaily one by one for all diferent selers of item (first approach for all item one)
            $sql = „SELECT * FROM bazaar_item WHERE cart_number = „.“‚$users_id'“  ;
            if($output = mysqli_query($dbc, $sql)){
                if(mysqli_num_rows($output) > 0){  // if any record obtained from SELECT query
                    
                    while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop
                            $item_id = $row[‚item_id‘];
                            $name_of_item = $row[‚name_of_item‘];
                            $price_eur = $row[‚price_eur‘];
                            $users_id_of_seller = $row[‚users_id‘];
                             /********************************************************
                             *   if item with item_ide was bought, tgen set cart_number to -1 and published to -1 – mean sold
                             */
                             // update cart_number and published to -1 sold
                            $sql_update = „UPDATE bazaar_item SET cart_number = ‚-1‚, published = ‚-1‚ WHERE item_id = $item_id LIMIT 1″;
                            // execute SQL
                            mysqli_query($dbc, $sql_update);

3. buyitem.php – mark added item into a cart by users ID

When a user add item into a cart, buyitem.php script mark cart_number field with ID of that user. This script work as you now in a two ways. After first call from index.php obtaing get data about item of interest and show info about product of interest, user can chose if will add item into a cart and post submit result on themself. Only post handling part (not get handling part) of code will make operation that is shown in next rows:

             // conect to the database
              $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);
              // get user id from selected session
              $users_id = $_SESSION[‚users_id‘];
              //create sql query along selected operation
                    
                    $sql = „UPDATE bazaar_item SET cart_number =“.“‚$users_id‚“.“ WHERE item_id = $item_id LIMIT 1″;
                    // execute SQL
                    mysqli_query($dbc, $sql);
                    // confirm executed command
                    echo ‚<p> The item <strong>‘ . $name_of_item . ‚</strong> with id <strong>‘ . $item_id . ‚</strong> was succesfully added into a cart. </p>‘;
                
              // close database connection
              mysqli_close($dbc);

4. admin.php – show status of item and give option for deletion

Admin page show items in a database and allow ordering (it will be explained in one of the next articles, how we obtained these functionality).

Next picture show how it looks like without ordering functionality.

admin.php – sold item marked in published field

Next code snippet show, how are information depicted in published filed in our table:

 while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop
                echo “ <div class=\“mailinglist\“> “ ;
                echo „<tr>“;
                    echo „<td>“ . $row[‚item_id‘] . „</td>“;
                    echo „<td>“ . $row[‚name_of_item‘] . „</td>“;
                    if ($row[‚published‘]==1) { // show if published – set 1 or waiting set to 0
                        echo ‚<td><span class=“green“> ok-Published </spann></td>‘;
                    } else if ($row[‚published‘]== 0){ // 1 published, 0 unpublished, -1 sold ready for deletion
                        echo ‚<td><span class=“gray“> X-waiting </spann></td>‘;
                    } else if ($row[‚published‘]== -1){ // 1 published, 0 unpublished, -1 sold ready for deletion
                        echo ‚<td><span class=“red“> sold – can be deleted! </spann></td>‘;
                    } 
… next part is omited

Adin can now see all itms marked as sold and can manually delete them. For this operation is requested removeitem.php script by clicking link generating link in row manage from upper picture (on admin page). This way is provided call with GET data.

Our removeitem.php script work also as it was mentioned before for buyitem.php in two ways. GET data from calling ling and next submit user decision for removing item from database. For deletion user must change radiobutton from no to yes and submit deletion commit.

Main part enable publishing, unpublishing and deletion of our item. Next code snippet show main logic of this script.

… omitted part of removeitem.php …

 //create sql query along selected operation
              switch ($operation) {
                case „publish“:
                    $sql = „UPDATE bazaar_item SET published = ‚1‘ WHERE item_id = $item_id LIMIT 1“;
                    // execute SQL
                    mysqli_query($dbc, $sql);
                    // confirm executed command
                    echo ‚<p> The item <strong>‘ . $name_of_item . ‚</strong> with id <strong>‘ . $item_id . ‚</strong> was succesfully published. </p>‘;
                    break;
                case „unpublish“:
                    $sql = „UPDATE bazaar_item SET published = ‚0‘ WHERE item_id = $item_id LIMIT 1“;
                    // execute SQL
                    mysqli_query($dbc, $sql);
                    // confirm executed command
                    echo ‚<p> The item <strong>‘ . $name_of_item . ‚</strong> with id <strong>‘ . $item_id . ‚</strong> was succesfully unpublished. </p>‘;
                    break;
                case „delete“:
                    $sql = „DELETE FROM bazaar_item WHERE item_id = $item_id LIMIT 1“;
                    // execute SQL
                    mysqli_query($dbc, $sql);
                    // confirm executed command
                    echo ‚<p> The item <strong>‘ . $name_of_item . ‚</strong> with id <strong>‘ . $item_id . ‚</strong> was succesfully deleted from listening on bazaar. </p>‘;
                    @unlink(IMAGE_PATH . $screenshot1); //delete image file
                    @unlink(IMAGE_PATH . $screenshot2);
                    @unlink(IMAGE_PATH . $screenshot3);
                    break;
            }
… omitted part …

Conclusion

We have now a better view on to how is lifecycle of item managed from moment of publishing for sell up to time of removing item from a database.

Now we can see further ways for improvement mostly in last phases of item lifecycle. Our first approach rely on manual handling by site admin, we can add much more automation into a this process by adding code for batch deletion of all sold items or only items sold before a specific time. Other thoughts rely on way how to run script in regulary base without need for intervention. Or how to archive all or only a specific items.

Full application code can be obtained from github from there.




Bazaar – php example code – part 13 – RSS syndication script

Article focus on a way how to generate RSS XML code for syndication news about currently published articles. User can subscribe for obtaining news about currently posted articles into a RSS readers.

RSS short introduction

RSS (RDF Site Summary or Really Simple Syndication)[2] is a web feed[3] that allows users and applications to access updates to websites in a standardized, computer-readable format.  (wikipedia, 26.12.2020)

By creating RSS feed, we enable to other people gain info about all of our new post. Our goal is to populate newsfeed from the bazaar_item database table to inform about new items available for sell.

To view an RSS feed, users need a RSS newsreader. Some of them are integrated in most of today browsers.

Closer look at RSS

RSS uses as a HTML markup language that uses tags and attributes for content description. Base of RSS is then XML used to describe web content for syndication.

Next picture show output of generated RSS code for advertising new items on our Bazaar app.

Required tags for the channel

  • title. The title of the channel. Should contains the name.
  • link. URL of the website that provides this channel.
  • description. Summary of what the provider is.
  • one item tag at least, for the content.
1
2
3
4
5
6
7
8
9
10
<rss version="2.0">
<channel>
    <title>XUL</title>
    <link>https://www.link.domain</link>
    <description></description>
    <item>
    ...
    </item>
</channel>
</rss>

Script rss.php for RSS markup generation

Next code provide all functionality for obtaining data about items for sell and generate RSS XML syndication feed for subscribed users.

<?php header(‚Content-Type: text/xml‘); ?>
<?php echo ‚<?xml version=“1.0″ encoding=“utf-8″?>‚; ?>
<rss version=“2.0″>
<channel>
<title>Bazaar – items for sell feed </title>
<link>http://localhost/bazaar/ </link>
<description>Latest items for sell on example Bazaar app created for educational purposes. </description>
<language>en-gb</language>
<?php // main part read info about items for sell and generate feed posts
session_start(); // start the session – must be added on all pages for session variable accessing
// solution using SESSIONS with COOKIES for longer (30days) login persistency
if(!isset($_SESSION[‚users_id‘])) { // if session is no more active
    if(isset($_COOKIE[‚users_id‘]) && isset($_COOKIE[‚username‘])) { // but cookie is set then renew session variables along them
        $_SESSION[‚users_id‘] = $_COOKIE[‚users_id‘];
        $_SESSION[‚username‘] = $_COOKIE[‚username‘];
        $_SESSION[‚user_role‘] = $_COOKIE[‚user_role‘]; // added for role
    }
 }
 /**
  *  Main part of generator newsfeed
  */
  // connect to the database
  require_once(‚appvars.php‘); // including variables for database
  $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);
    // Check connection
        if($dbc === false){
            die(„ERROR: Could not connect to database. “ . mysqli_connect_error());
        }
  // Obtain all listed items for sell
  $sql = „SELECT * FROM bazaar_item“  ;
  //go through the array of items for sell and format it as RSS
  
            if($output = mysqli_query($dbc, $sql)){
                if(mysqli_num_rows($output) > 0){  // if any record obtained from SELECT query
                    
                    while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop
                            //$first_name_buyer = $row[‚first_name‘];
                        echo  ‚<item>‚; 
                        echo  ‚<title>‘.$row[‚name_of_item‘].‘</title>‚; 
                        echo  ‚<link> http://localhost/bazaar/item.php?item_id=‚.$row[‚item_id‚].‘</link>‚;
                        echo  ‚<pubDate>‚.$row[‚item_add_date‘].‘ ‚.date(‚T‘).‘</pubDate>‚; 
                        echo  ‚<description>‚.substr($row[‚item_description‘], 0, 64).‘</description>‚; 
                        
                        
                        echo  ‚</item>‚; 
                                                                            
                    }
                    
                    // Free result set
                    mysqli_free_result($output);
                } else {
                    echo „No info about buyer obtained.“; // if no records in table
                    $cart_was_submitted = false; // items cann not be bought by technical issue
                }
            } else {
                echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
                $cart_was_submitted = false; // items cann not be bought by technical issue
            };
?>
</channel>
</rss>

Integration RSS generator in to a page

Our RSS generator is available for syndication from bottom part (footer) of the pahe via link with syndication image as it depicts next screen.

RSS subscription available from page footer

Full code of footer.php scrict included in our pages is:

<!– ***************************************************************** –>
<!–                              PHP footer of bazaar  for including                                 –>
<!– ***************************************************************** –>
<!– Vrsion: 1.0        Date: 22. – 22.11.2020 by CDesigner.eu                              –>
<!– ***************************************************************** –>
<?php
 // for further rework of the code
 function generate_footer($width) {
  if($width==580) {
    echo ‚<div class=“footer“ >‘;     
    echo ‚<div class=“footer“ id=“footer_container_580″>‘; 
    echo ‚<a class=“navbar-brand“ href=“https://cdesigner.eu“> Visit us on CDesigner.eu </a>‘; 
    echo ‚<a class=“navbar-brand“ href=“rss.php„> Subscribe to newsfeed <img src=“./images/rss.png“ width=“25″> </a>‚; 
    echo ‚</div>‘; 
    echo ‚</div>‘;    
  }
  if($width==1060) {
    echo ‚<div class=“footer“ >‘;     
    echo ‚<div class=“footer“ id=“footer_container_1060″>‘; 
    echo ‚<a class=“navbar-brand“ href=“https://cdesigner.eu“> Visit us on CDesigner.eu </a>‘; 
    echo ‚<a class=“navbar-brand“ href=“rss.php“> Subscribe to newsfeed <img src=“./images/rss.png“ width=“25″> </a>‘; 
    echo ‚</div>‘; 
    echo ‚</div>‘;    
  }
      }
?>
<!– older solution without daptive width
    <div class=“footer“ >     
        <div class=“footer“ id=“footer_container_1060″> 
            <a class=“navbar-brand“ href=“https://cdesigner.eu“> Visit us on CDesigner.eu </a>
            <a class=“navbar-brand“ href=“rss.php“> Subscribe to newsfeed <img src=“./images/rss.png“ width=“25″> </a>
            </div>
    </div>
–>

In our pages is this footer script invoked way as can be visible on next code snippet. After including with requireonce() is invoked function for generating footer with expected with.

… code omitted from index.php …

   
        <?php  // footer include code
            require_once(‚footer.php‘); // including footer
            generate_footer(1060); // function from footer.php for seting width, you can use 580 and 1060px width
        ?>         
      
      
</body>
</html>

Conclusion

With RSS syndication is opened new way for our users to subscribe for getting all new info about products for sell. Short example provide shor introduction to RSS generation and how to implement RSS generator into a code of our page.

Full Bazaar app code for further reference can be obtained from github here.




Bazaar – php example code – part 12 – CAPTCHA login hardening

Article focus on implementation of CAPTCHA with tools available in PHP. Separate script generate randomly rotated and by scratches and lines distorted text consist from 6 alphanumeric characters stored in a picture. Also provide hash of generated pass_phrase stored in session variable. Login page read text from verification field, hash them and compare against value stored in session. If password and captcha pass_phrases are as expected. User is validated as logged in user.

Expectation from CAPTCHA hardened login

CAPTCHA (/kæp.tʃə/, a contrived acronym for „Completely Automated Public Turing test to tell Computers and Humans Apart“) is a type of challenge–response test used in computing to determine whether or not the user is human. ( as is is mentioned in wiki, 25.12.2020).

Most common way how to implement captcha verification, is generating human readable picture containing alphanumeric characters that are displayed on pages with some type of prompt for a data. Our login page expect login name and password, rouge system (robot) can attempt for brute force access gain. Inserting third field for retyping code from provided image is a way how to eliminate automatized mechanism for gaining page access.

Our expectation from way how it will be implemented are:

  • simple CAPTCHA image generation only with resource available in PHP code
  • generated CAPTCHas must be hardly readable by captcha OCR software (image rotation, scratches, ping or red color text, random lines)
  • main captcha code stored separately from login or other pages implementing them
  • way how to delete used captcha images
  • quick implementation on login page without large code retyping

Captcha image is implemented in frontpage of login as it display next image.

Captcha verification on login page

If user type incorrect text, error message is displayed along with new CAPTCHA image as it is shown on next picture.

captcha.php generating code

Our CAPTCHA images generating ode is stored in separate script. This approach enable further improvements and transfer into other applications.

Leading part of code enable define dimensions of generated image, next variety of alphanumeric characters included in to a code and number of them in one image.

<!– ***************************************************************** –>
<!– PHP  code generating verification captcha image                                      –>
<!– ***************************************************************** –>
<!– Vrsion: 1.0        Date: 8. – 9.11.2020 by CDesigner.eu                                  –>
<!– ***************************************************************** –>
<?php
    //require_once(‚appvars.php‘); // including variables for database
    
    // if included whole not necessary session_start(); // start the session – must be added on all pages for session variable accessing
    // solution using SESSIONS with COOKIES for longer (30days) login persistency
    
    /*if(!isset($_SESSION[‚users_id‘])) { // if session is no more active
        if(isset($_COOKIE[‚users_id‘]) && isset($_COOKIE[‚username‘])) { // but cookie is set then renew session variables along them
            $_SESSION[‚users_id‘] = $_COOKIE[‚users_id‘];
            $_SESSION[‚username‘] = $_COOKIE[‚username‘];
            $_SESSION[‚user_role‘] = $_COOKIE[‚user_role‘]; // added for role
        }
     } */
     
     // important captcha constants
     define(‚CAPTCHA_NUMCHARS‘, 6); // number of charakters in CAPTCHA
     define(‚CAPTCHA_WIDTH‘, 200); // width of image
     define(‚CAPTCHA_HEIGHT‘, 60); // height of image
     // Set Correct Path to Font File
     $fontPath=’C:\xampp_7_4_2020\htdocs\bazaar\images\courier_new_bold.ttf‘; 
     // generating passphrase by random numbers
     $pass_phrase = „“;
     for($i = 0; $i < CAPTCHA_NUMCHARS; $i++ ) {
        $pass_phrase .= chr(rand(97, 122));
     }
     // store the encryption pass-phrase in a session variable
     $_SESSION[‚pass_phrase‘] = sha1($pass_phrase);
     //create the image
     $img = imagecreatetruecolor(CAPTCHA_WIDTH, CAPTCHA_HEIGHT);
     //set a white background with black text and gray graphics
     $bg_color = imagecolorallocate($img, 255, 255, 255); //white
     $text_color = imagecolorallocate($img, 255, 146, 130); //pale red
     $graphic_color = imagecolorallocate($img, 64, 64, 64); //darkgray
     $graphic_color_noise_red = imagecolorallocate($img, 255, 128, 128); //red noise pattern
     $graphic_color_noise_green = imagecolorallocate($img, 128, 255, 128); //green noise pattern
     // fill the background
     imagefilledrectangle($img, 0, 0, CAPTCHA_WIDTH, CAPTCHA_HEIGHT, $bg_color);
     // image edges rectangle drawing 
     imagerectangle ( $img , 0 , 0, CAPTCHA_WIDTH -1  , CAPTCHA_HEIGHT -1 , $graphic_color  );
     //draw some random lines
     for($i = 0; $i < 5; $i++) {
         imageline($img,0, rand() % CAPTCHA_HEIGHT, CAPTCHA_WIDTH, rand() % CAPTCHA_HEIGHT, $graphic_color);
     }
     
     //sprinkle in some random green dots
     for($i = 0; $i < 1000; $i++) {
        imagesetpixel($img, rand() % CAPTCHA_WIDTH,  rand() % CAPTCHA_HEIGHT, $graphic_color_noise_green);
    } 
    // draw the pass-phrase string
    imagettftext($img, 36, rand(0,10), rand(0, 12) , CAPTCHA_HEIGHT – rand(-5, 5), $text_color, $fontPath, $pass_phrase);
    //sprinkle over in some random dots
    for($i = 0; $i < 1000; $i++) {
        imagesetpixel($img, rand() % CAPTCHA_WIDTH,  rand() % CAPTCHA_HEIGHT, $graphic_color_noise_red);
    } 
    
    // VERY IMPORTANT: Prevent any Browser Cache!! – older approach send by header
    // header(„Cache-Control: no-store, 
    //no-cache, must-revalidate“);  
    // output the image as PNG using a header;
    /* ob_clean(); 
    header(„Content-type: image/jpg“);
    imagejpg($img);*/
    // creating filename and sending them through session and variable
    $imageCaptchafilename = IMAGE_PATH . „captcha“.rand(1,1000).“.png“;
    // debug echo $imageCaptchafilename;
    $_SESSION[‚imageCaptchafilename‘] = $imageCaptchafilename;
    //writting image to png
    imagepng($img, $imageCaptchafilename, 5);
    //clean up
    imagedestroy($img);
 ?>    

Example of generated image for closer look follows

Improved login page with CAPTCHA

Link to generated CAPTCHA image and pass_phrase is available in session variables for login page scripts.

Existing form code is extended for verification field and is followed by CAPTCHA image. After unsuccessfully retyped code, error message is displayed formatted with bootstrap danger style.

Al parts implementing CAPTCH in login page are marked by orange for better understanding and distinguishing them from other text.

<!– ****************************************************************** –>
<!– PHP „self“ code handling login into the bazaar app                                   –>
<!– ****************************************************************** –>
<!– Vrsion: 1.0        Date: 11.10-24.10.2020 by CDesigner.eu                             –>
<!– ****************************************************************** –>
<?php
 require_once(‚appvars.php‘); // including variables for database
 require_once(‚captcha.php‘); // including generator of captcha image
 session_start(); // start the session
   
 // two variables for message and styling of the mesage with bootstrap
 $msg = “;
 $msgClass = “;
 $usr_username = “;
 $usr_passwd = “;
 $verified_human_by_CAPTCHA = -1; //
//get info that user is loged in, if not try it looking at cookies
//if(!isset($_COOKIE[‚s‘])) { old solution with cookies
  if(!isset($_SESSION[‚users_id‘])) { //new with session variables
    if(isset($_POST[‚submit‘])) {
        /* Attempt MySQL server connection.  */
             $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);
             
                // accessing user entered login data
             $usr_username = htmlspecialchars($_POST[‚u_name‘]);    
             $usr_passwd = htmlspecialchars($_POST[‚u_pass‘]);
             //implement CAPTCHA pass-phrase verification
    
            $user_pass_phrase = sha1(htmlspecialchars($_POST[‚verify‘]));
            $pass_phrase_now = htmlspecialchars($_POST[‚pass_phrase_now‘]);
            $imageCaptchafilename_now = htmlspecialchars($_POST[‚imageCaptchafilename_now‘]); // name of current captcha photo file for deletion after usage
             
            if($pass_phrase_now == $user_pass_phrase) {
              $verified_human_by_CAPTCHA = 1;
              @unlink($imageCaptchafilename_now); // delete captcha file
                //debug echo „captcha ok“;
        
            } else {
              $verified_human_by_CAPTCHA = 0;
              @unlink($imageCaptchafilename_now); // also delete captcha file because new one was created
              $msgClass = ‚alert-danger‘;
              $msgCAPTCHA = „Your CAPTCHA was written wrong, please correct it and resend.“;
            }; 
             if(!empty($usr_username) && !empty($usr_passwd) && $verified_human_by_CAPTCHA) {
              // try lookup user database
              $usr_passwd_SHA = sha1($usr_passwd);
              $sql = „SELECT users_id, username, user_role FROM bazaar_user WHERE username = „.“‚$usr_username'“. “ AND pass_word = „.“‚$usr_passwd_SHA'“ ;
              // debug output echo  $usr_username; 
              // echo  $usr_passwd;
              //echo $usr_passwd_SHA;
              $data = mysqli_query($dbc, $sql);   
              
              if(mysqli_num_rows($data) == 1) {
                  // login is ok, set user  ID and username cookies and redirect to the homepage
                  $row = mysqli_fetch_array($data);
                  //setcookie(‚users_id‘, $row[‚users_id‘]); old solution with cookies
                  //setcookie(‚username‘, $row[‚username‘]);
                  $_SESSION[‚users_id‘] = $row[‚users_id‘]; // sloution with sessions
                  $_SESSION[‚username‘] = $row[‚username‘];
                  $_SESSION[‚user_role‘] = $row[‚user_role‘]; // added user_role session variable
                  // new cookies for login persistency that expires after 30 days without logout combination SESSION with COOKIES is awailable
                  setcookie(‚users_id‘, $row[‚users_id‘], time()+(60+60*24*30));
                  setcookie(‚username‘, $row[‚username‘], time()+(60+60*24*30));
                  setcookie(‚user_role‘, $row[‚user_role‘], time()+(60+60*24*30)); // cookie for user_role of loged in user added
                  $home_url = ‚http://‘. $_SERVER[‚HTTP_HOST‘] . dirname($_SERVER[‚PHP_SELF‘]) . ‚/index.php‘;
                  header(‚Location:‘. $home_url);
                  // Free result set
                  mysqli_free_result($data);
                  // Close connection
                  mysqli_close($dbc);
              } else  {
                  // urename/ password are incorrect – error meesage is displayed
                  $msg = „Incorrect username or password. Login denied!  „;
                  $msgClass = ‚alert-danger‘;
   
            }     
              
            } else {
                // username/ password were not entered – display error message
                $msg = „Sorry, you must eneter username and password along with correct CAPTCHA phrase to log in. „;
                $msgClass = ‚alert-danger‘;
   
            }     
    }  
?>
<!– **************************************** –>
<!– HTML code containing Form for submitting –>
<!– **************************************** –>
<!DOCTYPE html>
<html>
<head>
  <title> Bazaar login page  </title>
  <link rel=“stylesheet“ href=“./css/bootstrap.min.css“> <!– bootstrap mini.css file –>
  <link rel=“stylesheet“ href=“./css/style.css“> <!– my local.css file –>
    <script src=“https://code.jquery.com/jquery-3.1.1.slim.min.js“ integrity=“sha384-A7FZj7v+d/sdmMqp/nOQwliLvUsJfDHW+k9Omg/a/EheAdgtzNs3hpfag6Ed950n“ crossorigin=“anonymous“></script>
        <script src=“https://cdnjs.cloudflare.com/ajax/libs/tether/1.4.0/js/tether.min.js“ integrity=“sha384-DztdAPBWPRXSA/3eYEEUWrWCy7G5KFbe8fFjk5JAIxUYHKkDx6Qin1DkWx51bBrb“ crossorigin=“anonymous“></script>
  
</head>
<body>
  <nav class=“navbar „>
      <div class=“container“ id=“header_container_580″>
        <div class=“navbar-header“>  
          <?php
             require_once(‚headerlogo.php‘);
          ?>  
          <a class=“navbar-brand“ href=“index.php“>Bazaar – Login page</a>
        </div>
      </div>
    </nav>
    <div class=“container“ id=“formcontainer“>  
    <?php if($msg != “): ?>
        <br> 
        <div class=“alert <?php echo $msgClass; ?>“><?php echo $msg; ?></div>
      <?php endif; ?> 
      
      <?php 
            //if(empty($_COOKIE[‚users_id‘])) { solution with cookies
              if(empty($_SESSION[‚users_id‘])) { // solution with sessions
                // only show for if session with name users_id does not exist
                //echo ‚ <br> ‚;
                //echo  ‚<p class=“alert alert-danger“>‘ . $msg . ‚</p>‘;
       ?> 
        
        <br> 
        <img id=“calcimage“ src=“./images/login.png“ alt=“bazaar image“ width=“150″ height=“150″>
        <br>
        <form  method=“post“ action=“<?php echo $_SERVER[‚PHP_SELF‘]; ?>“>
           <div id=“login“>
                <legend> Log In <legend>
                <label>Username:</label>
                    <input type=“text“ onfocus=“this.value='<?php echo isset($_POST[‚u_name‘]) ? “ : “; ?>'“ name=“u_name“ class=“form-control“ value=“<?php echo isset($_POST[‚u_name‘]) ? ‚Please reenter‘ : ‚Login name‘; ?>“>
                    <label>Password:</label>
                    <input type=“password“ onfocus=“this.value='<?php echo isset($_POST[‚u_pass‘]) ? “ : “; ?>'“ name=“u_pass“ class=“form-control“ value=“<?php echo isset($_POST[‚u_pass‘]) ? ‚Please reenter‘ : ‚Login name‘; ?>“>
                    <label for=“verify“>Verification – enter text from image below:</label>
                    <input type=“text“ onfocus=“this.value='<?php echo isset($_POST[‚verify‘]) ? “ : “; ?>'“ name=“verify“ class=“form-control“ value=“<?php echo isset($_POST[‚verify‘]) ? “ : ‚Enter the CAPTCHA verify code‘; ?>„>
                    <br>
                    <?php if(($verified_human_by_CAPTCHA == 0) ): //error messaging if wrong CAPTCHA?>
                    <br> 
                    <div class=“alert <?php echo $msgClass; ?>“><?php echo $msgCAPTCHA; ?></div>
                    <?php endif; ?> 
                    <center> <img src=“<?php echo $imageCaptchafilename ; ?>“ alt=“Verification pass-phrase“ > </center> 
                    <!– ass a hidden is sent sha actualy generated captcha pass-phrase only this way it is producet in same run –>
                    <input type=“hidden“ name=“pass_phrase_now“ value=“<?php echo sha1($pass_phrase); ?>“ />
                    <!– as a hidden is sentname of captcha file for deletion after use –>
                    <input type=“hidden“ name=“imageCaptchafilename_now“ value=“<?php echo $imageCaptchafilename; ?>“ />
            </div>
           <input id=“loginsubmitt“ type=“submit“ name=“submit“ class=“btn btn-info“ value=“Log In“> 
           <br>
        </form>
        <?php }  else { 
                 // successfull login
                  // cookie solution echo ‚<p class=“alert alert-success“> You are loged in as ‚ . $_COOKIE[‚username‘]. ‚</p>‘;
                  echo ‚<br>‘;
                  echo ‚<p class=“alert alert-success“> You are loged in as <em>‘ . $_SESSION[‚username‘]. ‚</em></p>‘; // session solution
                  echo ‚<p class=“alert alert-success“> If you will logout or login with anither credentials, please first <a href=“logout.php“>logout!. </a></p>‘;
              } 
        ?>  
      </div>
          
    
    
      <?php  // footer include code
          require_once(‚footer.php‘); // including footer
          generate_footer(580); // function from footer.php for seting width, you can use 580 and 1060px width
        ?>  
 
</body>
</html>

As it was mentioned before we must have way how to signal generated codes for verification, they are stored in session variable as pass_phrase, also image name. Second problem is, how to deleted unused images. Our implementation is simple, but if app run with many connected clients then in a short time can by generated many pictures. There is small posibility for generate one with same numbering part. Wider number is for consideration, also new problems will arisen in heavy loads that we must take in mind.

Conclusion

CAPTCHA verified login provide new level of security for gaining access to our application. Please keep in mind, that verification user and distinguishing them for automatized scripts is now a must have thing. It is a bare minimal for supporting basic level of security for today apps.

Full code of our bazaar education project can be obtained from github here.




Bazaar – php example code – part 11 – profile page for user

Our article focus on way how all logged in users can maintain accurate information about themselves. Profile page will contain data fully adjustable by logged in user and can display data fully accessible only in time of subscribing or next maintainable by site admin.

Expectation from edit profile page

Way how to maintain user profile information accurate is to enable to user maintain it by themselves. This function can be implemented by edutprofile.php form. From this form we expect these functionalities:

  • table display all currently provided information
  • some information was obtained during registration process but some must be provided later (finer info about current city of living, streeet, avatar photos etc.)
  • output display also un editable part of data as registration e-mail or users ID and name – these information will be editable in some manner only for administrator of page
  • from must be visually attractive and must be able display selected avatar photo
  • latest part must enable change user password

editprofile.php code

Next part of page display code of editprofile.php page. Interesting part of from input element is this:

 <div id=„frame_gray“>
              <label> Your registered with these credentials. They cannot be changed, only way how to obtain new is deleting account asking page admin and create new one:</label>
              <label>User ID:</label>
          <input type=“text“  name=“users_id“ class=“form-control“ value=“<?php echo $_SESSION[‚users_id‘]?>“ disabled>
              <br>
              <label>User name:</label>
          <input type=“text“  name=“username“ class=“form-control“ value=“<?php echo $_SESSION[‚username‘]?>“ disabled>
              <br>
              <label>E-mail:</label>
              <input type=“text“  name=“email“ class=“form-control“ value=“<?php echo $email?>“ disabled>
              <br>
            </div>  

DIV element with id=“frame_gray“ link external css style from style.css:

#frame_gray {  padding: 10px;  background-color: rgb(167, 167, 167) ;  border: 1px dotted #616161 ;
}

This approach enable color distinguishing some part of page as it show next picture:

Upper part of editprofile page from Bazaar app

Also in input element of our form we can find new term disabled, this option disable editing option for that element. data are only for read only.

Full code o editprofile.php scrip follows:

<!– ***************************************************************** –>
<!– PHP „self“ code handling user profile editing                                            –>
<!– ***************************************************************** –>
<!– Vrsion: 1.0        Date: 25.10-30.10.2020 by CDesigner.eu                            –>
<!– ***************************************************************** –>
<?php
    require_once(‚appvars.php‘); // including variables for database
  // two variables for message and styling of the mesage with bootstrap
  session_start(); // start the session – must be added on all pages for session variable accessing
  // solution using SESSIONS with COOKIES for longer (30days) login persistency
    
    if(!isset($_SESSION[‚users_id‘])) { // if session is no more active
    if(isset($_COOKIE[‚users_id‘]) && isset($_COOKIE[‚username‘])) { // but cookie is set then renew session variables along them
      $_SESSION[‚users_id‘] = $_COOKIE[‚users_id‘];
      $_SESSION[‚username‘] = $_COOKIE[‚username‘];
      $_SESSION[‚user_role‘] = $_COOKIE[‚user_role‘]; // added for role
    }
   }
  $msg = “;
  $msgClass = “;
  // default values of auxiliary variables
  $users_id = „“;
  $username = „“;
  $pass_word = „“;
  $nickname= „“;
  $first_name = „“;
  $lastname_name = „“;
  $address = false;
  $city = „“;
  $ZIPcode = „“;
  $email = „“;
    $GDPR_accept = false;
    $rules_accept = false;
    $avatar = „“; // photo location of avatar
    $profile_text = „“;
  $is_result = false; //before hitting submit button no result is available
  
  // Control if data was submitted
  if(filter_has_var(INPUT_POST, ‚submit‘)) {
        // Data obtained from $_postmessage are assigned to local variables
        
        $users_id = $_SESSION[‚users_id‘]; // obtained from login user
        $username = $_SESSION[‚username‘];
        
        $pass_word1 = sha1(htmlspecialchars($_POST[‚pass_word1‘]));
        $pass_word2 = sha1(htmlspecialchars($_POST[‚pass_word2‘]));
        $pass_word_old = sha1(htmlspecialchars($_POST[‚pass_word_old‘]));
      $nickname= htmlspecialchars($_POST[‚nickname‘]);
      $first_name = htmlspecialchars($_POST[‚first_name‘]);
      $lastname_name = htmlspecialchars($_POST[‚lastname_name‘]);
      $addresss = htmlspecialchars($_POST[‚addresss‘]);
      $city = htmlspecialchars($_POST[‚city‘]);
      $ZIPcode = htmlspecialchars($_POST[‚ZIPcode‘]);
      
       // $GDPR_accept = isset($_POST[‚GDPR_accept‘]); // checkbox doesnot send post data, they must be checked for its set state !!!
        isset($_POST[‚rules_accept‘]) ? $rules_accept =“1″: $rules_accept =“0″; // checkbox doesnot send post data, they must be checked for its set state !!!
        isset($_POST[‚GDPR_accept‘]) ? $GDPR_accept =“1″: $GDPR_accept =“0″;
    
        $avatar = htmlspecialchars($_FILES[‚avatar‘][‚name‘]);           // photo location of avatar
        $profile_text = htmlspecialchars($_POST[‚profile_text‘]);
    
        //echo ‚users_id‘; echo $users_id;
        //echo $rules_accept;
        //echo $GDPR_accept;
        //echo $nickname;
    
  
    
    // Controll if all required fields was written
    if( !empty($nickname) && $rules_accept && $GDPR_accept) { // these item identifiers are mandatory and can not be empty
      // If check passed – all needed fields are written
      // Check if E-mail is valid
      //echo $rules_accept;
      //  echo $GDPR_accept;
                
                // move image to /images final folder from temporary download location
        $avatar_target1 = IMAGE_PATH . $avatar;
        
        
           // insert into databse 
                      if (1) {
                         move_uploaded_file($_FILES[‚avatar‘][‚tmp_name‘], $avatar_target1);
              
              // make database connection
              $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);
              // Check connection
                if($dbc === false){
                  die(„ERROR: Could not connect to database. “ . mysqli_connect_error());
                }
              
              // INSERT new entry
              // need systematic debug!!!  – now it is ok, it can be used as further example  
          
              // example working and tested syntax for UPPDATE query $sql = „UPDATE bazaar_user SET nickname = ‚“.$nickname.“‚,  first_name = ‚“.$first_name.“‚
              //               WHERE   users_id = ‚“.$users_id. „‚ AND username = ‚“.$username.“‚“ ; 
              $sql = „UPDATE bazaar_user SET
                                            nickname = ‚“.$nickname.“‚,
                                            first_name = ‚“.$first_name.“‚,
                                            lastname_name = ‚“.$lastname_name.“‚,
                                            addresss = ‚“.$addresss.“‚,
                                            city = ‚“.$city.“‚,
                                            ZIPcode = ‚“.$ZIPcode.“‚,
                                            write_date = now(),
                                            
                                            GDPR_accept = ‚“.$GDPR_accept.“‚,
                                            rules_accept = ‚“.$rules_accept.“‚,
                                            avatar  = ‚“.$avatar.“‚,
                                            profile_text = ‚“.$profile_text.“‚
                                            
                                            WHERE   users_id = ‚“.$users_id. „‚ AND username = ‚“.$username.“‚“; 
              // . $_POST[‚userid‘] . „‚, first_name='“ . $_POST[‚first_name‘] . „‚, last_name='“ . $_POST[‚last_name‘] . „‚,
              // city_name='“ . $_POST[‚city_name‘] . „‚ ,email='“ . $_POST[‚email‘] . „‚ WHERE userid='“ . $_POST[‚userid‘] . „‚“);
                               
              //show updated user data true
              $is_result = true; 
              if(mysqli_query($dbc, $sql)){
                
                $msg = ‚Profile updated succesfuly. ‚;
                $msgClass = ‚alert-success‘;
              } else {
                
                $msg = „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc);
                $msgClass = ‚alert-danger‘;
                            }
                            // echo „DEBUG – idem k casti s heslom“;            
                            // update password only if both passwords are not emty and are equal and old password match 
                            //pass_word = $pass_word, and only add hash to filed not plane password
                           // $pass_word1 = htmlspecialchars($_POST[‚pass_word1‘]);
                           // $pass_word2 = htmlspecialchars($_POST[‚pass_word2‘]);
                           // $pass_word_old = htmlspecialchars($_POST[‚pass_word_old‘]);
                           //DEBUG – echo $pass_word1;
                           //DEBUG – echo $pass_word2;
                           //DEBUG – echo $pass_word_old;
                           if(isset($pass_word1) && isset($pass_word2) && isset($pass_word_old )){ // old and two input for new password are provided
                            if($pass_word1 == $pass_word2){ // new passwords is ok typed 2x the same
                                // echo „DEBUG – hesla sa rovnaju“; 
                                // obtain old password sha1 for reference
                                $_username = $_SESSION[‚username‘];
                                // echo „DEBUG -username $_username“;
                                $_users_id = $_SESSION[‚users_id‘];
                                //echo “ DEBUG -users_id $_users_id  „;
                                //$sql = „SELECT * FROM bazaar_user WHERE username = „.“‚$_username'“. “ AND users_id = „.“‚$_users_id'“ ;
                                $sql = „SELECT * FROM bazaar_user WHERE username = „.“‚$_username'“.“LIMIT 1″  ;
                                if($output = mysqli_query($dbc, $sql)){
                                    if(mysqli_num_rows($output) > 0){  // if any record obtained from SELECT query
                                        
                                        while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop
                                       
                                                $pass_word_old_stored = $row[‚pass_word‘];
                                                // echo „DEBUG – 0. vo while hash stareho hesla je $pass_word_old_stored  „;
                                                                                     
                                        }
                                        
                                        // Free result set
                                        mysqli_free_result($output);
                                    } else{
                                        echo „Old password can not be obtained.“; // if no records in table
                                    }
                                } else{
                                    echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
                                }
                                //echo „DEBUG – 1. hash stareho hesla je $pass_word_old_stored „;
                                //echo „DEBUG – 2. hash stareho zadaneho hesla uzivatelom $pass_word_old) „;
                                if($pass_word_old_stored == $pass_word_old){ // if old pasword provided by user is the same as in database, passwords can be changed
                                //  echo „DEBUG – 3. stare heslo bolo zadane spravne“; 
                                   
                                //  echo „DEBUG – pasword je zmienany na $pass_word1“;
                                    $sql = „UPDATE bazaar_user SET
                                        
                                            
                                            pass_word = ‚“.$pass_word1.“‚
                                   
                                            WHERE   users_id = ‚“.$users_id. „‚ AND username = ‚“.$username.“‚“ ;   
                                    if($output = mysqli_query($dbc, $sql)){
                                        if($output) {  // if any record obtained from SELECT query
                                          //echo „Heslo bolo úspešne zmenené“; 
                                          $msg .= ‚ PASSWORD changed succesfuly. ‚;
                                          $msgClass = ‚alert-success‘;
                                          
                                        } else{
                                            //echo „Password cannot be changed.“; // if no records in table
                                            $msg .= ‚ PASSWORD cannot be changed. ‚;
                                            $msgClass = ‚alert-danger‘;
                                        }
                                    } else{
                                        echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
                                    }        
                                }
                            } 
                           
                           }
              // end connection
                mysqli_close($dbc);
                
      
      
            } else {
              // Failed – if not all fields are fullfiled
              $msg = ‚Please fill in all * marked contactform fields – nickname, GDPR and portal rules are mandatory!‘;
              $msgClass = ‚alert-danger‘; // bootstrap format for allert message with red color
                        };
                        
                   
        } else {
          // Failed – if not all fields are fullfiled
          $msg = ‚Please fill in all * marked contactform fields – nickname, GDPR and portal rules are mandatory!‘;
          $msgClass = ‚alert-danger‘; // bootstrap format for allert message with red color
                    };
        
    
  };  
  
  
  
    
?>
<!– **************************************** –>
<!– HTML code containing Form for submitting –>
<!– **************************************** –>
<!DOCTYPE html>
<html>
<head>
  <title> Bazaar – item for sell  </title>
  <link rel=“stylesheet“ href=“./css/bootstrap.min.css“> <!– bootstrap mini.css file –>
  <link rel=“stylesheet“ href=“./css/style.css“> <!– my local.css file –>
    <script src=“https://code.jquery.com/jquery-3.1.1.slim.min.js“ integrity=“sha384-A7FZj7v+d/sdmMqp/nOQwliLvUsJfDHW+k9Omg/a/EheAdgtzNs3hpfag6Ed950n“ crossorigin=“anonymous“></script>
        <script src=“https://cdnjs.cloudflare.com/ajax/libs/tether/1.4.0/js/tether.min.js“ integrity=“sha384-DztdAPBWPRXSA/3eYEEUWrWCy7G5KFbe8fFjk5JAIxUYHKkDx6Qin1DkWx51bBrb“ crossorigin=“anonymous“></script>
  
</head>
<body>
  <nav class=“navbar „>
      <div id=“header_container_580″>
        <div class=“navbar-header“>  
        <?php  
            require_once(‚headermenu.php‘); // including menu items
        ?>    
         
          <?php /*– older solution only for this page menu if(isset($_SESSION[‚users_id‘])) {  // display different page header along way why is user loged in or not – users_id is set when user is loged in
                  echo  ‚<a class=“navbar-brand“ href=“editprofile.php“>Bazaar – editing personal profile</a>‘;
                } else { 
                  echo  ‚<a class=“navbar-brand“ href=“login.php“>Unauthorized – please Log In </a>‘; 
            }; */
            ?>
        </div>
      </div>
    </nav>
    <div class=“container“ id=“formcontainer“>  
<!– ***************************************** –>
<!– HTML par available after succesfull login –>
<!– ***************************************** –>    
<?php if(isset($_SESSION[‚users_id‘])) { //if user is loged with users_id then editprofile form is available?> 
    <?php if($msg != “): ?>
        <div class=“alert <?php echo $msgClass; ?>“><?php echo $msg; ?></div>
      <?php endif; ?> 
        
        <br> 
        <img id=“calcimage“ src=“./images/logout.png“ alt=“Edit profile main page icon“ width=“150″ height=“150″>
        <br>
      <?php   //part displaying user_role of loged user
         
          
            echo “ <br> <br>“;
            echo “ <table class=\“table table-success\“> „;
            $user_role = $_SESSION[‚user_role‘];
            $username = $_SESSION[‚username‘];
            echo “ <tr>
                 <td><h5>  User_role of succesfully loged user with name <strong> $username </strong> is <strong>$user_role</strong> . „;    
            
              
            echo “     <td>   </tr> „; 
            echo “ </table> „;
          
          //echo “ <input type=“text“ id=“result_field“ name=“result_field“ value=“$result“  >  <br>“ ;
        
         ?>  
      <form enctype=“multipart/form-data“ method=“post“ action=“<?php echo $_SERVER[‚PHP_SELF‘]; ?>“>
      <input type=“hidden“ name=“MAX_FILE_SIZE“ value=“5242880″>
        <div class=“form-group“>
          <?php // here read data from bazar_user table and prefill input fileds with previeously obtained data from user
            $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);
                // Check connection
               if($dbc === false){
                 die(„ERROR: Could not connect to database. “ . mysqli_connect_error());
               };
             
             
              
                            $_username = $_SESSION[‚username‘];
                            $_users_id = $_SESSION[‚users_id‘];
              // create SELECT query for category names from database
              $sql = „SELECT * FROM bazaar_user WHERE username = „.“‚$_username'“.“ AND users_id=“.“‚$_users_id'“ ;
              // execute sql and populate data list with existing category in database
              if($output = mysqli_query($dbc, $sql)){
                if(mysqli_num_rows($output) > 0){  // if any record obtained from SELECT query
                  
                  while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop
                  
                                            
                                            $pass_word = $row[‚pass_word‘];
                                            $nickname= $row[‚nickname‘];
                                            $first_name = $row[‚first_name‘];
                                            $lastname_name = $row[‚lastname_name‘];
                                            $addresss = $row[‚addresss‘];
                                            $city = $row[‚city‘];
                                            $ZIPcode = $row[‚ZIPcode‘];
                                            $email = $row[‚email‘];
                                            $gdpr = $row[‚GDPR_accept‘]; // checkbox doesnot send post data, they must be checked for its set state !!!
                                            $rules_accept = $row[‚rules_accept‘];
                                        
                                            $avatar = $row[‚avatar‘];           // photo location of avatar
                                            $profile_text = $row[‚profile_text‘];
                      
                      
                  
                  }
                  
                  // Free result set
                  mysqli_free_result($output);
                } else{
                  echo „There is no category in category table. Please wirite one.“; // if no records in table
                }
              } else{
                echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
              }
       
                      // Close connection
                            mysqli_close($dbc);
                    ?>
            <!– these data are only displayed but cannot be changed –>
            <div id=“frame_gray“>
              <label> Your registered with these credentials. They cannot be changed, only way how to obtain new is deleting account asking page admin and create new one:</label>
              <label>User ID:</label>
          <input type=“text“  name=“users_id“ class=“form-control“ value=“<?php echo $_SESSION[‚users_id‘]?>“ disabled>
              <br>
              <label>User name:</label>
          <input type=“text“  name=“username“ class=“form-control“ value=“<?php echo $_SESSION[‚username‘]?>“ disabled>
              <br>
              <label>E-mail:</label>
              <input type=“text“  name=“email“ class=“form-control“ value=“<?php echo $email?>“ disabled>
              <br>
            </div>  
              <br>
              <br>
              
            <div id=“frame_green“>
              <label>Further user data data:</label>
              <br>
              <label>*Nickname:</label>
          <input type=“text“ onfocus=“this.value='<?php echo isset($_POST[‚nickname‘]) ? $nickname : “; ?>'“ name=“nickname“ class=“form-control“ value=“<?php echo isset($_POST[‚nickname‘]) ? $nickname : $nickname; ?>“>
              <br>
              <label>First name:</label>
          <input type=“text“ onfocus=“this.value='<?php echo isset($_POST[‚first_name‘]) ? $first_name: “; ?>'“ name=“first_name“ class=“form-control“ value=“<?php echo isset($_POST[‚first_name‘]) ? $first_name : $first_name; ?>“>
              <br>
              <label>Last name:</label>
          <input type=“text“ onfocus=“this.value='<?php echo isset($_POST[‚lastname_name‘]) ? $lastname_name : “; ?>'“ name=“lastname_name“ class=“form-control“ value=“<?php echo isset($_POST[‚lastname_name‘]) ? $lastname_name : $lastname_name; ?>“>
              <br>
              <label>Adress in form – Street Nr.:</label>
          <input type=“text“ onfocus=“this.value='<?php echo isset($_POST[‚addresss‘]) ? $addresss : “; ?>'“ name=“addresss“ class=“form-control“ value=“<?php echo isset($_POST[‚addresss‘]) ? $addresss : $addresss; ?>“>
              <br>
              <label>City:</label>
          <input type=“text“ onfocus=“this.value='<?php echo isset($_POST[‚city‘]) ? $city : “; ?>'“ name=“city“ class=“form-control“ value=“<?php echo isset($_POST[‚city‘]) ? $city : $city; ?>“>
              <br>
              <label>ZIP code in form XXXXX:</label>
              <input type=“text“ onfocus=“this.value='<?php echo isset($_POST[‚ZIPcode‘]) ? $ZIPcode : “; ?>'“ name=“ZIPcode“ class=“form-control“ value=“<?php echo isset($_POST[‚ZIPcode‘]) ? $ZIPcode : $ZIPcode; ?>“>
              <br>
            </div> 
            <br> 
              <!– GDPR and rule of the portal acceptance –>
            <div id=“frame_red“>
              <div class=“form-group“>
              <label>Acceptation of portal rules and GDPR regulations – IMPORTANT PART:</label>
              <br>
              <br>
            <input type=“checkbox“ name=“GDPR_accept“ class=“form-control“ <?php if($gdpr) { echo „checked“; } ?> >
              <label>* I agree with GDPR regulations</label>
              <br>
              <input type=“checkbox“ name=“rules_accept“ class=“form-control“ <?php if($rules_accept) { echo „checked“; } ?> >
              <label>* I agree with rules of the portal</label>
              <br>
              </div>
            </div>  
        
        
        </div>
        <div id=“frame_green“>
                 <?php
                        // From database obtain avatar image file name and next recreate their location
                       
                        $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);
                        // Check connection
                        if($dbc === false){
                          die(„ERROR: Could not connect to database – stage of article listing. “ . mysqli_connect_error());
                        }
                
                    
                       // read $avatar value from databaze of user
                        $_username = $_SESSION[‚username‘]; // get info about currently loged user
                        $_users_id = $_SESSION[‚users_id‘];
                        $sql = „SELECT * FROM bazaar_user WHERE username = „.“‚$_username'“.“ AND users_id=“.“‚$_users_id'“; // query avatar
                        
                        if($output = mysqli_query($dbc, $sql)){
                          if(mysqli_num_rows($output) > 0){
                           $row = mysqli_fetch_array($output);
                           $write_date_obtained = $row[‚write_date‘]; // get latest profile update date for output located at the botoom part of page
                           if (!empty($row[‚avatar‘])) {
                            $image_location = IMAGE_PATH.$row[‚avatar‘];
                            echo „<center> <td id=\“gray_under_picture\“>  <br> <img  align=\“middle\“ src=\“$image_location\“ alt=\“ profile avatar picture \“  height=\“250\“> <br> <br> <br> </td> </center>“;
                           } else {
                            echo „<center> <td id=\“gray_under_picture\“> <br> <img align=\“middle\“ src=\“./images/default_avatar.png\“ alt=\“ profile avatar picture \“  height=\“250\“> <br> <br> </td> </center>“;
                           }
                           
                           mysqli_free_result($output);
                          }
                        }    
                        
                        // Close connection
                        mysqli_close($dbc);
                 ?>        
                <p> In this part you can select your profile avatar! </p>
                <label>* Please select location of your avatar from drive – max 5MB!</label>
                <div class=“custom-file“>
                <br>
                <input type=“file“ name=“avatar“ class=“custom-file-input“ id=“avatar“ lang=“en“ onchange=“getFilename(this)“>
                    <label class=“custom-file-label1 custom-file-label“  for=“customFile“>Screenshot1 – required:</label>
                <br>
                </div>
          </div>       
          
        <script type=“application/javascript“> // javascript handling chaging filename of selected file
               $(document).ready(function(){
        $(„#avatar“).change(function(){
          //alert(„A file 1 has been selected.“);
                    var thefile1 = document.getElementById(‚avatar‘);
                    
          var fileName1 = thefile1.value;
                    //var fileName1 = „A file 1 has been selected.“;
                    $(‚.custom-file-label1‘).html(fileName1);
            
        });
        $(„#screenshot2“).change(function(){
          //alert(„A file 2 has been selected.“);
          var thefile2 = document.getElementById(‚screenshot2‘);
                    
                    var fileName2 = thefile2.value;
          //var fileName2 = „A file 2 has been selected.“;
                    $(‚.custom-file-label2‘).html(fileName2);
        });
        $(„#screenshot3“).change(function(){
          //alert(„A file 3 has been selected.“);
          var thefile3 = document.getElementById(‚screenshot3‘);
                    
                    var fileName3 = thefile3.value;
          //var fileName3 = „A file 3 has been selected.“;
                    $(‚.custom-file-label3‘).html(fileName3);
        });
              });
            
        
         
             </script>
       
          <br><br>
     
          <div id=“frame_green“> 
            <div class=“form-group“>
                <label>Profile text – plese provide some description for your profile if will:</label>  <!– textera for input large text –>
                <textarea id=“profile_text“ onfocus=“this.value='<?php echo isset($_POST[‚profile_text‘]) ? $profile_text : ‚Please provide description for your profile if will …‘; ?>'“ name=“profile_text“ class=“form-control“ rows=“3″ cols=“50″><?php echo isset($_POST[‚profile_text‘]) ? $profile_text : $profile_text; ?></textarea>
                <br>
            </div>
         </div>
         <br>
         <div id=“frame_red“>
              <label> If you will change password. Write old password and then for verification two times new one:</label>
              <label>Old password:</label>
          <input type=“password“  name=“pass_word_old“ class=“form-control“ >
              <br>
              <label>New password:</label>
          <input type=“password“  name=“pass_word1″ class=“form-control“ >
              <br>
              <label>New password once again for verification:</label>
              <input type=“password“  name=“pass_word2″ class=“form-control“ >
              <br>
            </div>  
              <br>
    
      
         <br><br>
     
   
      <center> <button type=“submit“ name=“submit“ class=“btn btn-warning btn-lg“> Update profile information </button> </center>
      
    
     
          <br><br>
      
      <?php   //part displaying info after succesfull added subscriber into a mailinglist
         if ($is_result ) {
          
            echo “ <br> <br>“;
            echo “ <table class=\“table table-success\“> „;
            echo “ <tr>
                 <td><h5>  Personal info  for user  <strong> $username </strong> was last modified at $write_date_obtained. „;    
            
              
            echo “     <td>   </tr> „; 
            echo “ </table> „;
          
          //echo “ <input type=“text“ id=“result_field“ name=“result_field“ value=“$result“  >  <br>“ ;
        } ; 
         ?>
                 <br>
    
    </form>
     
<!– ***************************************** –>
<!– HTML part displayed for unloged user      –>
<!– ***************************************** –> 
    <?php } else { // else if user is not loged then form will noot be diplayed?>  
      <br> 
        <img id=“calcimage“ src=“./images/logininvit.png“ alt=“Log in invitation“ width=“150″ height=“150″>
        <br>
        <h4>For further profile editing please log in<a class=“navbar-brand“ href=“login.php“><h4><u>here.</u> </h4></a></h4>
        <br>
      <?php } ?>  
    
    
    </div>
          
    
    
    <?php  // footer include code
      require_once(‚footer.php‘); // including footer
      generate_footer(580); // function from footer.php for seting width, you can use 580 and 1060px width
    ?>  
    
      
</body>
</html>

Last part of form enable change existing user password. Provided passwords must contain currently valid password and two identical new password. On script handling part of site are controlled these new passwords and from database is obtained sha1 hash of currently used password. Only if current password provided by user is equal to that stored in database (hashes not plain passwords!!!) and both new passwords are the same, then old hash of password is overwritten that new.

Visual implementation is shown next (bottom part of editprofile form).

Conclusion

Our editprofile form enable further user data maintaining by logged in user. Only registered user are permitted to access own profile. For better maintainability user_ID, registration username and registration e-mail is not editable (gray style with disabled option in appropriate input element).

Full code for further study can be obtained from github here.




Bazaar – php example code – part 10 – pagination in list output

Article will focus on way how to create pagination functionality for larger list of displayed data (list of items for sell, list of registered users, cart items, …). Our implementation will be portable among a pages or other projects, visually attractive and ergonomic.

Expectation from pagination functionality

Larger list output of data can be for user a bit overwhelming. Best way how to break output into a separate chunks of data is paginate them with support of separate numbered page links.

For our implementation of pagination mechanism is expected:

  • user can define or in code is hardcoded number of items per page, this is held in $number_per_page variable
  • page links must be shown as numbered hyperlinks in bottom left part of data lists
  • arrows for next and previous page must be present with its graphical representation
  • separate function for generating pagination link will be created (its name is functions.php and appropriate function is named   function generate_page_links($user_search, $sort_by, $order, $cur_page, $num_pages) { //($user_search, $sort_by, $order, $cur_page, $num_pages);

Our expectation how pagination links will look like show next picture:

Pagination links in bottom part of a item list on main Bazaar page

Code for page links generation

Function for paging links creation is inserted into a functions.php script. Full content of mentioned function follows:

function generate_page_links($user_search, $sort_by, $order, $cur_page, $num_pages) { //($user_search, $sort_by, $order, $cur_page, $num_pages);
    $page_links = „“;
    echo „<br>“;
   
    // if this is not first in row, we need generate the „previous“ link
    if  ($cur_page > 1) {
        $page_links .= ‚<a id=“pagination“ href=“‚ . $_SERVER[‚PHP_SELF‘] . ‚?usersearch=‘
        .$user_search . ‚&sort_by=‘ . $sort_by . ‚&order=‘ . $order .’&page=‘ . ($cur_page – 1) . ‚“><img src=“./images/previous_icon.png“ alt=“previous image“ width=“30″ height=“30″></a>‘;
    } else {
        $page_links .= ‚<span id=“pagination“><img src=“./images/previous_icon.png“ alt=“previous image“ width=“30″ height=“30″></span> ‚;
    }
    // Loop through the pages generating the page numbered links
    for($i = 1; $i <= $num_pages; $i++) {
        if  ($cur_page == $i) {  
        $page_links .= ‚<span id=“pagination“>‘ . $i. ‚</span>‘; // span inline element mark non a tag (unlinked number) as pagination for further formating by css
        } else {
        $page_links .= ‚<a id=“pagination“ href=“‚ . $_SERVER[‚PHP_SELF‘] . ‚?usersearch=‘
        .$user_search . ‚&sort_by=‘ . $sort_by . ‚&order=‘ . $order .’&page=‘ . $i . ‚“>‘ . $i . ‚</a>‘;
        }
    } 
    // If this page is not last in row, generate „next“ link
    if  ($cur_page < $num_pages) {
        $page_links .= ‚<a id=“pagination“ href=“‚ . $_SERVER[‚PHP_SELF‘] . ‚?usersearch=‘
        .$user_search . ‚&sort_by=‘ . $sort_by . ‚&order=‘ . $order .’&page=‘ . ($cur_page + 1) . ‚“><img src=“./images/next_icon.png“ alt=“next image“ width=“30″ height=“30″></a>‘;
    } else {
        $page_links .= ‚ <span id=“pagination“><img src=“./images/next_icon.png“ alt=“next image“ width=“30″ height=“30″></span>‘;
    }
  
    return $page_links;
}

Our function can generate also user_search if needed for providing optional chunk of information trough GET able links.

In main implementation page we take closer look in a way how to display paginated outputs.

Way how to implement pagination in a main page code

For first approach we will show you code implementing pagination with calling generate_page_links($user_search, $sort_by, $order, $cur_page, $num_pages) function.

As a comments are displayed necessary leadings for understanding our code. Next code sniped is selected from main Bazaar page, from bottom list of items for sell (permanently displayed part, not part generated by user selected category – upper part is optionally displayed).

/**********************************************************************/
/*  Output in paginated form                                                                                  */
/**********************************************************************/
 /***
  *  Display pagination on the page – part included to listening in this area
  */
/* Attempt MySQL server connection. Assuming you are running MySQL
 */
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);
//GET data for pagination send to page herself
//calculate pagination information
$cur_page = isset($_GET[‚page‘]) ? $_GET[‚page‘] : 1;
// results per page default declater as 5 on top of page and changed in submitt part after reset button handling $results_per_page = 5;
$skip = (($cur_page -1) * $results_per_page);
// first  question to database table for obtaining number of published items in a database – obtain value for $total
$sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY item_id DESC „;  // read in reverse order of score – highest score first              
$output_for_number_rows_count = mysqli_query($dbc, $sql); // query database
$total = mysqli_num_rows($output_for_number_rows_count);    //get number of rows in databse 
 
… omitted part of code enabling ordering along selected category
if(($sort_by == „name“) && ($order == „1“)) { // along name and ASC order
        $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY name_of_item ASC LIMIT $skip$results_per_page“; 
    };
    if(($sort_by == „name“) && ($order == „-1“)) { // along name and DESC order
        $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY name_of_item DESC LIMIT $skip, $results_per_page“; 
    };
    if(($sort_by == „price“) && ($order == „1“)) { // along price and ASC order
        $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY price_eur ASC LIMIT $skip, $results_per_page“
    };
    if(($sort_by == „price“) && ($order == „-1“)) { // along price and DESC order
        $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY price_eur DESC LIMIT $skip, $results_per_page“; 
    };
    if(($sort_by == „category“) && ($order == „1“)) { // along category and ASC order
        $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY subcategory_id ASC LIMIT $skip, $results_per_page“; 
    };
    if(($sort_by == „category“) && ($order == „-1“)) { // along category and DESC order
        $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY subcategory_id DESC LIMIT $skip, $results_per_page“; 
    };
    if(($sort_by == „default“)) { // along category and DESC order
        $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY item_id DESC LIMIT $skip, $results_per_page“; 
    };
} else {  // first run without ordering – no get link generated
    $sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY item_id DESC LIMIT $skip, $results_per_page“;  // read in reverse order of score – highest score first
}
 
/***********************************************************************/
/*  Output in Table – solution 1 – for debuging data from database                 */
/***********************************************************************/
 
echo „<br><br>“;
echo „<h4>Latest added items for you! </h4>“;
echo „<br>“;
/***
 *  Obtaining wished number of item per page – option for select
 */
?>
<form  method=“post“ action=“<?php echo $_SERVER[‚PHP_SELF‘]; ?>“>
      
<div class=“form-group“>
<label> Set expected number of items per page -5 is default:</label>
<input list=“number_per_page“ name=“number_per_page“ placeholder=“please select or write nr.“>
      <datalist id=“number_per_page“> <!– must be converted in subcategory_id in script – marked with (*) –>
          <option value=“5″>
          <option value=“10″>
          <option value=“15″>   
          <option value=“20″>   
          <option value=“50″>
          <option value=“100″>      
        </datalist>
     
    
    
      <!– users_id from session obtaining – for debuging and testing is set as hidden –>
     
      <button type=“submit“ name=“nr_of_pages“ class=“btn btn-warning“> Use selected number of pages! </button>
</div>
</form>
<?php
echo „<br>“; echo „<br>“;
if($output = mysqli_query($dbc, $sql)){
    if(mysqli_num_rows($output) > 0){  // if any record obtained from SELECT query
        // create table output
        echo „<table>“; //head of table
            echo „<tr>“;
                //echo „<th>id</th>“;
                // functionality for ordering result
                /**
                 * SORTING – PART I. Here are generated GET links for UP/DOWN ordering by appropriate category – not pertinent to our explantation related to pagination functionality
                 */
                echo ‚<th>Name  <br /><a id=“SORT“ href=“index.php?sort_by=name&amp;order=1″> <img id=“arrow“ src=“./images/arrowup.png“> </a>
                                <a id=“SORT“ href=“index.php?sort_by=name&amp;order=-1″> <img id=“arrow“ src=“./images/arrowdown.png“> </a> </th>‘; //order 1 up -1 down
                echo ‚<th>Price <br /><a id=“SORT“ href=“index.php?sort_by=price&amp;order=1″> <img id=“arrow“ src=“./images/arrowup.png“> </a>
                                <a id=“SORT“ href=“index.php?sort_by=price&amp;order=-1″> <img id=“arrow“ src=“./images/arrowdown.png“> </a></th>‘;
                echo ‚<th>Category <br /><a id=“SORT“ href=“index.php?sort_by=category&amp;order=1″> <img id=“arrow“ src=“./images/arrowup.png“> </a>
                                <a id=“SORT“ href=“index.php?sort_by=category&amp;order=-1″> <img id=“arrow“ src=“./images/arrowdown.png“> </a> </th>‘;
                echo „<th>Screenshot1</th>“;
                echo „<th>More info</th>“;
                
                
            echo „</tr>“;
        while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop
            echo “ <div class=\“mailinglist\“> “ ;
            echo „<tr>“;
                //echo „<td>“ . $row[‚item_id‘] . „</td>“;
                echo „<td class=\“item_name\“>“ . $row[‚name_of_item‘] . „</td>“;
                echo „<td class=\“price\“>“ . $row[‚price_eur‘] . “ € </td>“;
                            /* convert category_id in to category and subcategory */
                            $subcategory_id = $row[‚subcategory_id‘];
                            $category_idsupl    = „“ ;
                            $subcategory_idsupl = „“ ;
                            // (*) — conversion of category and subcategory into category%id
                                
                                //create SELECT query for category and subcategory names from database
                                 $sql_supl = „SELECT category, subcategory FROM bazaar_category WHERE subcategory_id = „.“‚$subcategory_id'“ ;
                                /*$output_supl = mysqli_query($dbc, $sql_supl);
                                $row_supl = mysqli_fetch_array($output_supl);
                                $category_id    = $row_supl[‚category‘] ;
                                $subcategory_id = $row_supl[‚subcategory‘] ;
                                echo „<td>“ . $category_id.“/“.$subcategory_id.“</td>“;*/
                                // execute sql and populate data list with existing category in database
                                if($output_supl = mysqli_query($dbc, $sql_supl)){
                                    if(mysqli_num_rows($output_supl) > 0){  // if any record obtained from SELECT query
                                        while($row_supl = mysqli_fetch_array($output_supl)){ //next rows outputed in while loop
                                            
                                            $category_idsupl    = $row_supl[‚category‘] ;
                                            $subcategory_idsupl = $row_supl[‚subcategory‘] ;
                                            
                                                
                                        }
                                        
                                        
                                        // Free result set
                                        mysqli_free_result($output_supl);
                                    } else {
                                        echo „There is no souch category-subcategory in category table. Please correct your error.“; // if no records in table
                                    }
                                } else{
                                    echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
                                }
                echo „<td>“ . $category_idsupl.“/“.$subcategory_idsupl.“</td>“;
                
                    $image_location = IMAGE_PATH.$row[‚screenshot1‘];
                echo „<td id=\“gray_under_picture\“> <img  src=\“$image_location\“ alt=\“ screenshot of product primary \“  height=\“250\“> </td>“; 
                echo ‚<td colspan=“1″><a id=“DEL“ href=“item.php?item_id=‘.$row[‚item_id‘]. ‚“><img id=“next“ src=“./images/next.png“>   </a></td></tr>‘; //construction of GETable link
            echo „</tr>“;
            echo “ </div> “ ;
        }
        echo „</table>“;
        //count nuber of pages total
        $num_pages = ceil($total / $results_per_page);
        
        //generate navigational page links if we have more than one page
        
        if($num_pages > 1) {
            $user_search = „“; // not implemented yet, then set as clear values
            if(empty($sort_by)) { // if not obtained by get then default order is applied
                $sort_by=“default“;
            };
            if(empty($order)) { // if not obtained by get then default order is applied
                $order=“1″;
            };
            
            // included function for pagination generation function stored in functions.php page
            echo generate_page_links($user_search, $sort_by, $order, $cur_page, $num_pages);
            echo „<br><br>“;
        }
        // Free result set
        mysqli_free_result($output);
    } else{
        echo „There is no item for sell. Please add one.“; // if no records in table
    }
} else{
    echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
}
// Close connection
mysqli_close($dbc);
?>
 

By this way we can implement pagination also in admin page. If two or more ordering are present on page, only one selected number of page is mandatory for all lists. it means, if we selected page nr. 2 in one list, also other list are on page 2 after reloading page with appropriate GET links is generated for requesting that page for display.

This is not bad solution of most of our needs for simplifying information displayed in one time.

Conclusion

Pagination is a way how to limit number off information visible to a user in given time. For our solution we used selection with LIMIT functionality and further mechanism for reading total number of items for display. After necessary calculation, links for pagination are generated and with help of GET request, page obtain all information important for display only that part of selection, that is needed for that moment.

Full working code of bazaar training app can be downloaded from github here.

Main parts are implemented in as function in functions.php where is function for link generation. Index.php or admin.php are great examples how to implement two or more pagination per single page. This simple solution has its own limitation as it was mentioned before.




Bazaar – php example code – part 9 – cart and other notification icons

Article will focus on implementation user cart icon with display of number and total price for items in main menu page. For further visual improvement we use graphical icon with text. Also sell icon with link to sellitem page is added.

Expectation from cart icon display

Cart notification icon is small graphic remainder of number of items added into a cart and actual total price for pay.

Our implementation will offer these functionalities:

  • contain graphic depiction of cart
  • show total number of items in a cart
  • show total price that will be paid for all items in a cart
  • must be includable into a other bazaar pages
  • cart must be visible only for registered and loged in users

Visual implementations of cart icon

Next part provide further depiction of cart icon shown in upper part of logged in user pages.

Cart icon implemented in upper part of cart page (gray block)

Code of the cart icon and including into a page

In a next rows you can look at way how cart icon is implemented in script named cart_icon.php.

<!– *************************************************************** –>
<!– PHP included code for cart icon with number of items displaing      –>
<!– *************************************************************** –>
<!– Vrsion: 1.0        Date: 17. – 18.10.2020 by CDesigner.eu                          –>
<!– *************************************************************** –>
<?php
   $_user_id = $_SESSION[‚users_id‘];
   $_number_of_items_in_cart =“-„;
   $_total_price =“0″;
   /*********************************************************
    * Count mumber of items in cart and total item price
    */
    /* Attempt MySQL server connection. Assuming you are running MySQL
             */
            $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);
            // Check connection
            if($dbc === false){
                die(„ERROR: Could not connect to database – stage of article listing. “ . mysqli_connect_error());
            }
            
                        
            // read all rows (data) from guestbook table in „test“ database
                
            $sql = „SELECT * FROM bazaar_item WHERE cart_number=“.“‚$_user_id'“.“ ORDER BY item_id ASC „;  // read items marked in cart_number with appropriate users_id
            /****************************************************************/
            /*  Output in Table – solution 1 – for debuging data from database    */
            /****************************************************************/
            // if data properly selected 
                        
            if($output = mysqli_query($dbc, $sql)){
                   if(mysqli_num_rows($output) > 0){  // if any record obtained from SELECT query
                    // create table output
                    
                    $_total_price = 0; // initialize cariable calculating total price for items in cart
                    $_number_of_items_in_cart =0;
                    while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop
                        
                            $_total_price += $row[‚price_eur‘];
                            $_number_of_items_in_cart += 1;

              

                    }
                    
                    // Free result set
                    mysqli_free_result($output);
                } else {
                    echo „“; // if no records in table
                }
            } else {
                echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
            }
            // Close connection
            mysqli_close($dbc); 
   //debug
   echo ‚&nbsp;  &nbsp; &nbsp; &nbsp;  <span class=“cart“> <a class=“navbar-brand“ href=“cart.php“> <img id=“cart“ src=“./images/small_cart.png“ alt=“cart small icon“ width=“35″ height=“35″><strong>(‚ .$_number_of_items_in_cart .‘)  ‚ .$_total_price .‘ €</strong></a> </span>‘;
   // add some space with &nbsp;
?>

Our code obtain users_id of currently logged in user from session variable, then go through all rows in bazaar_item table that are marked by users_id (they are added into a cart). If current row id marked by users_id of currently logged user then increment number of items and accumulate price of items by price of that item.

In next part of code snippet is cart_icon.php included in headermenu.php and header menu is included (required_once) in all pages that in some way implement menu. In this way we created decomposition and make our code much more modular.

<!– **************************************************************** –>
<!– PHP header menu  of bazaar for including                                               –>
<!– **************************************************************** –>
<!– Vrsion: 1.0        Date: 22. – 22.11.2020 by CDesigner.eu                            –>
<!– **************************************************************** –>
<?php
   // generate menu if user is loged in or not
         // old solution with cookies if(isset($_COOKIE[‚username‘])) { // loged in user
            require_once(‚headerlogo.php‘);
            
            if(isset($_SESSION[‚username‘])) { // loged in user
                 
                echo ‚<div id=“menu“>‘;
                echo ‚<a class=“navbar-brand“ href=“index.php“><img width=“150″ src=“./images/bazaarheader.png“> Bazaar – best items for a best prices!</a>‘;
                echo ‚<a class=“navbar-brand“ href=“editprofile.php“><img id=“menuimage“ src=“./images/menu_profile.png“> Edit profile </a>‘;
                                
                if(isset($_SESSION[‚user_role‘])==’admin‘) { // if loged user is admin role
                   echo ‚<a class=“navbar-brand“ href=“admin.php“><img id=“menuimage“ src=“./images/menu_admin.png“> Manage your page </a>‘;
               };
               echo ‚<a class=“navbar-brand“ href=“logout.php“><img id=“menuimage“ src=“./images/menu_logout.png“> Logout <b><span id=“username“>‘ .$_SESSION[‚username‘] .'</span></b></a>‘;
               echo ‚</div >‘;
               require_once(‚sell_icon.php‘); // graphic menu item for selling your items
               echo ‚<a class=“navbar-brand“ href=“rss.php“><img src=“./images/rss.png“ width=“45″></a>‘; //rss feed link
               require_once(‚cart_icon.php‘); // small cart icon in menu
               
              } else { // visitor without login
               echo ‚<div id=“menu“>‘;
               echo ‚<a class=“navbar-brand“ href=“login.php“><img id=“menuimage“ src=“./images/menu_login.png“> Log In </a>‘;
               echo ‚<a class=“navbar-brand“ href=“signup.php“><img id=“menuimage“ src=“./images/menu_signup.png“> Sign Up for better membership! </a>‘;
   
               echo ‚<a class=“navbar-brand“ href=“index.php“><img width=“150″ src=“./images/bazaarheader.png“> Bazaar – best items for a best prices!</a>‘;
               echo ‚</div >‘;
             };
             
?>

And final way how headermenu.php is called in our bazaar pages

… code omitted …
<body>
    <nav class=“navbar „>
      <div id=“header_container_1060″>
        <div class=“navbar-header“>   
        <?php 
           require_once(‚headermenu.php‚); // including menu items
        ?>   
         
        </div>
      </div>
    </nav>
    <div class=“container“ id=“container_1060″> 
… code omitted …

Conclusion

Cart_icon.php fulfill our expectation for good way how to constantly inform our customer about all items added to buy. Problem decomposition by implementing them into a headermenu.php and text to all pages is solution for further code maintainability.

Full code of bazaar app can be obtained from github here.




Bazaar – php example code – part 8 – shopping cart of user

Article focus on way of implementation of user shopping cart. Our bazar_item table is altered for new field cart_number. If cart number is zero, item is not added into a buyer cart. After adding item into a cart, this number is changed to a number of buing user. Showing content of cart relays on display bazaar_itmes marked by users_id of appropriate user.

Way how to implement shopping cart

Shopping cart will display all items added by appropriate user for further revision and commitment to buy.

In our cart page we must be able to do:

  • display all items added to buy by a single user
  • enable delete items from cart
  • calc total price for items in cart
  • delivery adress can be updated by editinfo.php page, here is only displayed content
  • after definitive submitting, send info to seler by e-mail about succesfull buy

Cart is available only for loged in users, also loged in user can commit buy to a seler/ sellers of selected items.

For further improvement or consideration is way how to inform sellers about succesull buy. Our simle solution send one e-mail for one item. But there is not a small possibility, tahat cart can contain more items from one seler. For all of these items is send to seler one buy commitment request.

Visual look of shopping cart

Next pictures show content of our shopping cart and related scripts output.

Shopping cart frontend
Displayed info about not selected comitting YES for BUY
Succesfully submited buy with notification about contacting of sellers for that items

Implementation of shopping cart

Next code shows how shopping cart is implemented in our aplication.

<!– ***************************************************************** –>
<!– PHP „self“ code showing content of items added into a cart                   –>
<!– ***************************************************************** –>
<!– Vrsion: 1.0        Date: 1.11.2020 by CDesigner.eu                                        –>
<!– ***************************************************************** –>
<!– ***************** MEMO – base is from index.php – show all items with cart_number = session(users_id) + calc total summ, create remove from cart link with removefromcart.php show address for delivery and button submitt to buy ************************************ –>
<?php
    require_once(‚appvars.php‘); // including variables for database
    session_start(); // start the session – must be added on all pages for session variable accessing
    // solution using SESSIONS with COOKIES for longer (30days) login persistency
    
    if(!isset($_SESSION[‚users_id‘])) { // if session is no more active
        if(isset($_COOKIE[‚users_id‘]) && isset($_COOKIE[‚username‘])) { // but cookie is set then renew session variables along them
            $_SESSION[‚users_id‘] = $_COOKIE[‚users_id‘];
            $_SESSION[‚username‘] = $_COOKIE[‚username‘];
            $_SESSION[‚user_role‘] = $_COOKIE[‚user_role‘]; // added for role
        }
     }
    // two variables for message and styling of the mesage with bootstrap
    $msg = “;
    $msgClass = “;
    // default values of auxiliary variables
    $name_of_item = „“;
    $price_eur = „“;
    $subcategory_id = „“;
    $users_id = „“;
    $item_add_date = „“;
    $subcategory_id = „“;
    $published = false;
    $screenshot1 = „“;
    $screenshot2 = „“;
    $screenshot3 = „“;
    $item_description = “;
    $is_result = false; //before hitting submit button no result is available
    
    // Control if data was submitted
    if(filter_has_var(INPUT_POST, ‚submit‘)) {
        // Data obtained from $_postmessage are assigned to local variables
        if($_POST[‚confirm‘] == ‚Yes‚ ){ // if yuser selected YES and hit Buy button on below of the page
            //read all data from $_POST array
            $users_id = htmlspecialchars($_POST[‚users_id‘]);
            /***********************************************************
             *   obtain data about buyer
             */
            // read data about buying user with users id from database
            // make database connection
            $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);
            // Check connection
                if($dbc === false){
                    die(„ERROR: Could not connect to database. “ . mysqli_connect_error());
                }
            
            //– only one needed — geting users data for purchase e-mail
            $sql = „SELECT * FROM bazaar_user WHERE users_id = „.“‚$users_id'“.“LIMIT 1″  ;
            if($output = mysqli_query($dbc, $sql)){
                if(mysqli_num_rows($output) > 0){  // if any record obtained from SELECT query
                    
                    while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop
                            $first_name_buyer = $row[‚first_name‘];
                            $lastname_name_buyer = $row[‚lastname_name‘];
                            $addresss_buyer = $row[‚addresss‘];
                            $city_buyer = $row[‚city‘];
                            $ZIPcode_buyer = $row[‚ZIPcode‘];
                            $email_buyer = $row[‚email‘];
 
                                                    
                    }
                    
                    // Free result set
                    mysqli_free_result($output);
                } else{
                    echo „No info about buyer obtained.“; // if no records in table
                }
            } else{
                echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
            };
            
             /**************************************************************
             *   obtain data buyed items from this buyer with users_id defined by current SESSION
             */
            //get info about sold items – we must go through all buyed items and send emaily one by one for all diferent selers of item (first approach for all item one)
            $sql = „SELECT * FROM bazaar_item WHERE cart_number = „.“‚$users_id'“  ;
            if($output = mysqli_query($dbc, $sql)){
                if(mysqli_num_rows($output) > 0){  // if any record obtained from SELECT query
                    
                    while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop
                            $item_id = $row[‚item_id‘];
                            $name_of_item = $row[‚name_of_item‘];
                            $price_eur = $row[‚price_eur‘];
                            $users_id_of_seller = $row[‚users_id‘];
                            
                             /******************************************************
                             *   sent info to seler item by item in the buyer cart
                             */
                            // send appropriate e-mails about buy items by items
                             // validate e-mail
                            if(filter_var($email_buyer, FILTER_VALIDATE_EMAIL) === false){
                                // E-mail is not walid
                                $msg = ‚Wrong e-mail format of buyer, purchase can not be created. Please contact page admin.‘;
                                $msgClass = ‚alert-danger‘;
                            } else {
                                // E-mail is ok
                                $is_result = true;
                                /* request e-mail of seller */
                                 /****************************************************
                                 *   obtain e-mail of appropriate seller – this is done for all buying items one by one
                                 */
                                $sql2 = „SELECT email FROM bazaar_user WHERE users_id = „.“‚$users_id_of_seller'“  ;
                                if($output2 = mysqli_query($dbc, $sql2)){
                                    if(mysqli_num_rows($output2) > 0){  // if any record obtained from SELECT query
                                        
                                        while($row = mysqli_fetch_array($output2)){ //next rows outputed in while loop
                                            $email_of_seller = $row[‚email‘];
                                                
                     
                                                                        
                                        }
                                        
                                        // Free result set
                                        mysqli_free_result($output2);
                                    } else{
                                        echo „No email about seller can be obtained.“; // if no records in table
                                    }
                                } else{
                                    echo „ERROR: Could not able to execute $sql2. “ . mysqli_error($dbc); // if database query problem
                                };
                                 /******************************************              *   construct information e-mails about item buy one by one for all items in cart
                                 */
                                $toEmail = $email_of_seller; //!!! e-mail address to send to 
                                $subject = ‚Item ‚.$name_of_item.‘ purchased on Bazaar by ‚.$first_name_buyer.‘ ‚.$lastname_name_buyer;
                                $body = ‚<h2>Item ‚.$name_of_item.‘ was succesfully purchased by : ‚.$first_name_buyer.‘ ‚.$lastname_name_buyer.'</h2>
                                    <h4>Delivery adress for this purchase is: </h4><p>‘.$addresss_buyer.‘,</p><p> ‚.$city_buyer.‘, </p><p>‘.$ZIPcode_buyer.'</p>
                                    <h4>Email</h4><p>E-mail of buyer is‘.$email_buyer.‘ this e-mail can be used for further communication.</p>
                                    <h4>Selling price was:</h4><p‘.$price_eur.‘ €.</p>
                                    ‚;
                                // Email Headers
                                $headers = „MIME-Version: 1.0″ .“\r\n“;
                                $headers .=“Content-Type:text/html;charset=UTF-8″ . „\r\n“;
                                // Additional Headers
                                $headers .= „From: “ .$first_name_buyer. „<„.$email_buyer.“>“. „\r\n“;
                            
                                        
                                if(mail($toEmail, $subject, $body, $headers)){
                                    // Email Sent
                                    $msg .= ‚<p> Your seller of ‚.$name_of_item.‘ was successfully contacted via e-mail.</p>‘;
                                    $msgClass = ‚alert-success‘;
                                } else {
                                    // Failed
                                    $msg = ‚Information about your buy cannot be delivered to seller via e-mail. Please contact site admin for further help.‘;
                                    $msgClass = ‚alert-danger‘;
                                }
                            }
                                            
                                                                    
                                    }
                                    
                                    // Free result set
                                    mysqli_free_result($output);
                                } else{
                                    echo „No info about buyer obtained.“; // if no records in table
                                }
                            } else{
                                echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
                            };
    
            // close database connection
            mysqli_close($dbc);
         
          } else {
              echo  ‚<p class=“alert alert-danger“ > The selected operation cannot be performed. Please select YES for further buy confirmation. </p>‘; 
          }
    
        
        
    };  
  
    
    
        
?>
<!– **************************************** –>
<!– HTML code containing Form for submitting –>
<!– **************************************** –>
<!DOCTYPE html>
<html>
<head>
    <title> Bazaar Cart  </title>
    <link rel=“stylesheet“ href=“./css/bootstrap.min.css“> <!– bootstrap mini.css file –>
    <link rel=“stylesheet“ href=“./css/style.css“> <!– my local.css file –>
    <script src=“https://code.jquery.com/jquery-3.1.1.slim.min.js“ integrity=“sha384-A7FZj7v+d/sdmMqp/nOQwliLvUsJfDHW+k9Omg/a/EheAdgtzNs3hpfag6Ed950n“ crossorigin=“anonymous“></script>
        <script src=“https://cdnjs.cloudflare.com/ajax/libs/tether/1.4.0/js/tether.min.js“ integrity=“sha384-DztdAPBWPRXSA/3eYEEUWrWCy7G5KFbe8fFjk5JAIxUYHKkDx6Qin1DkWx51bBrb“ crossorigin=“anonymous“></script>
    
</head>
<body>
    <nav class=“navbar navbar-default“>
      <div class=“container“>
        <div class=“navbar-header“>   
        <?php // generate menu if user is loged in or not
         // old solution with cookies if(isset($_COOKIE[‚username‘])) { // loged in user
            if(isset($_SESSION[‚username‘])) { // loged in user
                echo ‚<a class=“navbar-brand“ href=“index.php“>Bazaar – best items for a best prices!</a>‘;
                echo ‚<a class=“navbar-brand“ href=“editprofile.php“> Edit profile </a>‘;
                echo ‚<a class=“navbar-brand“ href=“logout.php“> Logout ‚ .$_SESSION[‚username‘] .'</a>‘;
                if(isset($_SESSION[‚user_role‘])==’admin‘) { // if oged user is admin role
                   echo ‚<a class=“navbar-brand“ href=“admin.php“> Manage your page </a>‘;
               };
               require_once(‚sell_icon.php‘); // graphic menu item for selling your items
               require_once(‚cart_icon.php‘); // small cart icon in menu
             } else { // visitor without login
               echo ‚<a class=“navbar-brand“ href=“login.php“> Log In </a>‘;
               echo ‚<a class=“navbar-brand“ href=“signup.php“> Sign Up for better membership! </a>‘;
   
               echo ‚<a class=“navbar-brand“ href=“index.php“>Bazaar – best items for a best prices!</a>‘;
            }
        ?>   
         
        </div>
      </div>
    </nav>
    <div class=“container“ id=“container_1060″> 
        
        
      <?php if($msg != “): ?>
            <div class=“alert <?php echo $msgClass; ?>“><?php echo $msg; ?></div>
      <?php endif; ?>   
        
        <br> 
        <img id=“calcimage“ src=“./images/cart.png“ alt=“cart image“ width=“150″ height=“150″>
        <br>
      <h4> Cart item of user 
        <?php    echo $_SESSION[‚username‘];  // creating title of cart for users
                 echo “ with id –  {$_SESSION[‚users_id‘]} are:“; 
        ?>
        <br>
      </h4>
      <!– Showing content of the cart of appropriate user with items marked with users_id in filed cart_number –>
      <?php 
                /* Attempt MySQL server connection. Assuming you are running MySQL
            server with default setting (user ‚root‘ with no password) */
            $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);
            // Check connection
            if($dbc === false){
                die(„ERROR: Could not connect to database – stage of article listing. “ . mysqli_connect_error());
            }
      
                        
            // read all rows (data) from guestbook table in „test“ database
            $_usr_id = $_SESSION[‚users_id‘];   
            $sql = „SELECT * FROM bazaar_item WHERE cart_number=“.“‚$_usr_id'“.“ ORDER BY item_id ASC „;  // read items marked in cart_number with appropriate users_id
            /**************************************************************/
            /*  Output in Table – solution 1 – for debuging data from database  */
            /**************************************************************/
            // if data properly selected from guestbook database tabele
            
            echo „<br>“;
           
            echo „<br>“; echo „<br>“;
            if($output = mysqli_query($dbc, $sql)){
                if(mysqli_num_rows($output) > 0){  // if any record obtained from SELECT query
                    // create table output
                    echo „<table>“; //head of table
                        echo „<tr>“;
                            //echo „<th>id</th>“;
                            echo „<th>Name</th>“;
                            echo „<th>Price</th>“;
                            echo „<th>Category</th>“;
                            echo „<th>Screenshot1</th>“;
                            echo „<th>More info</th>“;
                            
                            
                        echo „</tr>“;
                    $cart_total_eur = 0; // initialize cariable calculating total price for items in cart
                    while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop
                        echo “ <div class=\“mailinglist\“> “ ;
                        echo „<tr>“;
                            //echo „<td>“ . $row[‚item_id‘] . „</td>“;
                            echo „<td class=\“item_name\“>“ . $row[‚name_of_item‘] . „</td>“;
                            echo „<td class=\“price\“>“ . $row[‚price_eur‘] . “ € </td>“;
                            $cart_total_eur += $row[‚price_eur‘];
                                        * convert category_id in to category and subcategory */
                                        $subcategory_id = $row[‚subcategory_id‘];
                                        $category_idsupl    = „“ ;
                                        $subcategory_idsupl = „“ ;
                                        // (*) — conversion of category and subcategory into category%id
                                            
                                            // create SELECT query for category and subcategory names from database
                                            $sql_supl = „SELECT category, subcategory FROM bazaar_category WHERE subcategory_id = „.“‚$subcategory_id'“ ;
                                            /*$output_supl = mysqli_query($dbc, $sql_supl);
                                            $row_supl = mysqli_fetch_array($output_supl);
                                            $category_id    = $row_supl[‚category‘] ;
                                            $subcategory_id = $row_supl[‚subcategory‘] ;
                                            echo „<td>“ . $category_id.“/“.$subcategory_id.“</td>“;*/
                                            // execute sql and populate data list with existing category in database
                                            if($output_supl = mysqli_query($dbc, $sql_supl)){
                                                if(mysqli_num_rows($output_supl) > 0){  // if any record obtained from SELECT query
                                                    while($row_supl = mysqli_fetch_array($output_supl)){ //next rows outputed in while loop
                                                        
                                                        $category_idsupl    = $row_supl[‚category‘] ;
                                                        $subcategory_idsupl = $row_supl[‚subcategory‘] ;
                                                        
                                                            
                                                    }
                                                    
                                                    
                                                    // Free result set
                                                    mysqli_free_result($output_supl);
                                                } else {
                                                    echo „There is no souch category-subcategory in category table. Please correct your error.“; // if no records in table
                                                }
                                            } else{
                                                echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
                                            }
                            echo „<td>“ . $category_idsupl.“/“.$subcategory_idsupl.“</td>“;
                            
                                $image_location = IMAGE_PATH.$row[‚screenshot1‘];
                            echo „<td id=\“gray_under_picture\“> <img  src=\“$image_location\“ alt=\“ screenshot of product primary \“  height=\“250\“> </td>“; 
                            echo ‚<td colspan=“1″><a id=“DEL“ href=“removefromcart.php?cart_number=‘.$row[‚cart_number‘]. ‚&amp;item_id=‘. $row[‚item_id‘] . ‚&amp;name_of_item=‘. $row[‚name_of_item‘] .'“> >> Remove from cart  </a></td></tr>‘; //construction of GETable link
                        echo „</tr>“;
                        echo “ </div> “ ;
                    }
                    echo „</table>“;
                    echo „<br><br>“;
                    echo „<p><center><h5>Total price for items in cart: <strong> $cart_total_eur </strong>€ </h5></center></p>“;
                    // Free result set
                    mysqli_free_result($output);
                } else{
                    echo „There is no item for sell. Please add one.“; // if no records in table
                }
            } else{
                echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
            }
            // Close connection
            mysqli_close($dbc); 
      
      ?>
     <!– Recapitulation of user delivery adress – important beacause is sent to seller with e-mail about succesfull buy of listened item –>
     <h4> Your delivery adress is: </h4>
        <?php 
            // connect to a database
            $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);
               // Check connection
               if($dbc === false){
                   die(„ERROR: Could not connect to database – stage of article listing. “ . mysqli_connect_error());
               }
   
           // get info about user from database  
           $users_id = $_SESSION[‚users_id‘]; 
           $sql = „SELECT * FROM bazaar_user WHERE users_id = „.“‚$users_id'“.“LIMIT 1″  ;
           if($output = mysqli_query($dbc, $sql)){
               if(mysqli_num_rows($output) > 0){  // if any record obtained from SELECT query
                   
                   while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop
                           $first_name = $row[‚first_name‘];
                           $lastname_name = $row[‚lastname_name‘];
                           $addresss = $row[‚addresss‘];
                           $city = $row[‚city‘];
                           $ZIPcode = $row[‚ZIPcode‘];
                           $email = $row[‚email‘];
                           ?>
                                <div id=“frame_green“>
                                
                                <br>
                                <h5> Please check your contact and delivery info, these information are important for
                                            seller of the items for correct contact and delivery! </h5>
                                        <br>
                                <table>
                                    <tr>
                                        
                                        
                                        <td>         
                                        <label>e-mail:</label>
                                        <input type=“text“  name=“nickname“ class=“form-control“ value=“<?php echo $email;  ?>“ disabled>
                                        <br>
                                        </td>
                                        <td>
                                        <label>First name:</label>
                                        <input type=“text“ name=“first_name“ class=“form-control“ value=“<?php echo $first_name; ?>“ disabled>
                                        <br>
                                        </td>
                                        <td>
                                        <label>Last name:</label>
                                        <input type=“text“ name=“lastname_name“ class=“form-control“ value=“<?php  echo $lastname_name;  ?>“ disabled>
                                        <br>
                                        </td>
                                    <tr>    
                                    </tr>   
                                        <td colspan=“3″>
                                        <label>Adress in form – Street Nr.:</label>
                                        <input type=“text“  name=“addresss“ class=“form-control“ value=“<?php  echo $addresss; ?>“ disabled>
                                        <br>
                                        </td>
                                    <tr>    
                                    </tr>   
                                        <td colspan=“3″>
                                        <label>City:</label>
                                        <input type=“text“ name=“city“ class=“form-control“ value=“<?php echo $city; ?>“ disabled>
                                        <br>
                                        </td>
                                    <tr>    
                                    </tr>   
                                        <td colspan=“3″>
                                        <label>ZIP code in form XXXXX:</label>
                                        <input type=“text“ name=“ZIPcode“ class=“form-control“ value=“<?php  echo $ZIPcode; ?>“ disabled>
                                        </td>
                                        
                                    </tr>   
                                </table>
                                <br>
                                        <h5> If any of displayed info need correction, please visit your profile page <a href=“editprofile.php“><u>here</u>. </a></h5>
                                </div> 
                           
                          <?php                                 
                   }
                   
                   // Free result set
                   mysqli_free_result($output);
               } else{
                   echo „Error while reading data.“; // if no records in table
               }
           } else{
               echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
           };
           // Close connection
            mysqli_close($dbc);        
        ?>
        <br>
      </h4>
       <form  method=“post“ action=“<?php echo $_SERVER[‚PHP_SELF‘]; ?>“>
          <input type=“hidden“ name=“users_id“ value=“<?php echo $_SESSION[‚users_id‘] ?>“ />
          <h5> For confirmation of buy select YES and click on red button bellow:</h5>
          <center><input type=“radio“ name=“confirm“ value=“Yes“ /> Yes   <br>
          <input type=“radio“ name=“confirm“ value=“No“ checked=“checked“ /> No </center><br><br>  
          <center><button type=“submit“ name=“submit“ class=“btn btn-danger btn-bg“> I confirm the purchase with the obligation to pay </button> </center>
  
          <br><br>
      </form>
     
 
    </div>
    
        
    <div class=“footer“> 
          <a class=“navbar-brand“ href=“https://cdesigner.eu“> Visit us on CDesigner.eu </a>
    </div>
        
      
</body>
</html>

Cart page generate GETable link to script removefromcart.php to enable remove item from cart. This is done by reverting cart_number filed of appropriate item from number equal to buyers users_id to zero. User is befor removing item also informed about that item is now visible for other portal user for buy. And second decision must not be available, because item will add another user to a cart.

removefromcart.php script

Content of our supplementary removal script follows here:

<!– **************************************************************** –>
<!– PHP „self“ code GET request for remove from cart                                 –>
<!– **************************************************************** –>
<!– Vrsion: 1.0        Date: 2.11.2020 by CDesigner.eu                                       –>
<!– **************************************************************** –>
<?php // leading part of page for simple header securing and basic variable setup
    require_once(‚appvars.php‘); // including variables for database
    session_start(); // start the session – must be added on all pages for session variable accessing
  // solution using SESSIONS with COOKIES for longer (30days) login persistency
    
  if(!isset($_SESSION[‚users_id‘])) { // if session is no more active
    if(isset($_COOKIE[‚users_id‘]) && isset($_COOKIE[‚username‘])) { // but cookie is set then renew session variables along them
      $_SESSION[‚users_id‘] = $_COOKIE[‚users_id‘];
            $_SESSION[‚username‘] = $_COOKIE[‚username‘];
            $_SESSION[‚user_role‘] = $_COOKIE[‚user_role‘]; // added for role
    }
   }
   
  // two variables for message and styling of the mesage with bootstrap
  $msg = “;
  $msgClass = “;
  // default values of auxiliary variables
  
?>
<!– ******************************************* –>
<!– script for removing item from cart          –>
<!– ******************************************* –>
<!– obtain GET data from cart.php and trough    –>
<!– POST submit remove goods from cart by       –>
<!– seting cart_number filed to 0 – notasigned  –>
<!– to any user                                 –>
<!– ******************************************* –>
<!DOCTYPE html>
<html>
<head>
  <title> Bazaar remove from cart – remove script </title>
  <link rel=“stylesheet“ href=“./css/bootstrap.min.css“> <!– bootstrap mini.css file –>
  <link rel=“stylesheet“ href=“./css/style.css“> <!– my local.css file –>
    <script src=“https://code.jquery.com/jquery-3.1.1.slim.min.js“ integrity=“sha384-A7FZj7v+d/sdmMqp/nOQwliLvUsJfDHW+k9Omg/a/EheAdgtzNs3hpfag6Ed950n“ crossorigin=“anonymous“></script>
        <script src=“https://cdnjs.cloudflare.com/ajax/libs/tether/1.4.0/js/tether.min.js“ integrity=“sha384-DztdAPBWPRXSA/3eYEEUWrWCy7G5KFbe8fFjk5JAIxUYHKkDx6Qin1DkWx51bBrb“ crossorigin=“anonymous“></script>
  
</head>
<body>
  <nav class=“navbar navbar-default“>
      <div class=“container“>
        <div class=“navbar-header“>    
          <a class=“navbar-brand“ href=“cart.php“>Return to your shopping car</a>
          <a class=“navbar-brand“ href=“index.php“> –> return to main shop page</a>
        </div>
      </div>
    </nav>
    <div class=“container“ id=“formcontainer“>  
    
      
    <?php if($msg != “): ?> <!– alert showing part –>
        <div class=“alert <?php echo $msgClass; ?>“><?php echo $msg; ?></div>
      <?php endif; ?> 
       
      <br> <!– logo on the center of the page –>
      <h4>Confirmation of removal item from cart.</h4>
      <br>
      <br> <!– logo on the center of the page –>
        <img id=“calcimage“ src=“./images/delicon.png“ alt=“del image“ width=“150″ height=“150″>
      <br>
       
            
      <?php // code for GET info about what to remove and submit removing approval
        if(isset($_GET[‚cart_number‘]) && isset($_GET[‚item_id‘]) && isset($_GET[‚name_of_item‘]) ){
            // take a data from GET link generated by adminscript
            $cart_number = htmlspecialchars($_GET[‚cart_number‘]);
            $item_id = htmlspecialchars($_GET[‚item_id‘]);
            $name_of_item = htmlspecialchars($_GET[‚name_of_item‘]);
           
           
        } else if (isset($_POST[‚cart_number‘]) && isset($_POST[‚item_id‘]) && isset($_POST[‚name_of_item‘])) { //grab score from POST – different behavior for removal
            $cart_number = htmlspecialchars($_POST[‚cart_number‘]);
            $item_id = htmlspecialchars($_POST[‚item_id‘]);
            $name_of_item = htmlspecialchars($_POST[‚name_of_item‘]);
          
        }  else  { //error info message
            echo ‚<p class=“alert alert-danger“> Please specify any cart item for removal. </p>‘;
        };
        if(isset($_POST[‚submit‘])){
             
            if($_POST[‚confirm‘] == ‚Yes‘ ){ // delete appropriate score post with imagescreenshot
              //delete the screenshotimage from the 
              $cart_number = htmlspecialchars($_POST[‚cart_number‘]);
              $item_id  = htmlspecialchars($_POST[‚item_id‘]);
              $name_of_item  = htmlspecialchars($_POST[‚name_of_item‘]);
             
             
              // conect to the database
              $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);
              //Delete score data from the database
              $sql = „UPDATE bazaar_item SET cart_number = ‚0‘ WHERE item_id = $item_id LIMIT 1“;
              // execute SQL
              mysqli_query($dbc, $sql);
              // close database connection
              mysqli_close($dbc);
              // confirm executed command
              echo ‚<p> The item ‚ . $name_of_item . ‚ with id<strong>‘ . $item_id . ‚</strong> was sucesfully removed from your cart and now is available in listening for sell
                    for another user. </p>‘;
           
            } else {
                echo  ‚<p class=“alert alert-danger“ > The selected item cannot be removed. </p>‘; 
            }
        } else if (isset($cart_number) && isset($item_id) && isset($name_of_item) ) {
            echo ‚<h5>Are you sure to remove ‚ . $name_of_item . ‚ from your cart? Item will be set for sell listening and can be bought by another user.</h5>‘; 
            // show short describtion of score for deletion
            echo ‚<p> <strong> item_id: </strong> ‚ . $item_id .  ‚<br> <strong> item name is: </strong>‘ . $name_of_item .
                 
                 ‚</p>‘; 
              
            //generating removing confirmation form      
            
            echo ‚<form method=“POST“ action=“removefromcart.php“>‘;   //not self but direct this script removecategory.php – we dont want include any GET data tahat previously send
            echo ‚<input type=“radio“ name=“confirm“ value=“Yes“ /> Yes   ‚; 
            echo ‚<input type=“radio“ name=“confirm“ value=“No“ checked=“checked“ /> No <br><br>‘;  
            
            echo ‚<input type=“hidden“ name=“cart_number“ value=“‚.$cart_number.'“  />‘; 
            echo ‚<input type=“hidden“  name=“item_id“ value=“‚.$item_id.'“  />‘;
            echo ‚<input type=“hidden“ name=“name_of_item“ value=“‚.$name_of_item.'“ />‘; 
            echo ‚<input type=“submit“ class=“btn btn-danger“ value=“submit“ name=“submit“ />‘; 
            echo ‚</form>‘; 

  

        };
        echo ‚<br><br>‘;
        echo  ‚<p> <a href = „cart.php“> &lt;&lt Back to your cart. </a></p>‘;
?>
    
      </div>
 
     <div class=“footer“> 
          <a class=“navbar-brand“ href=“https://cdesigner.eu“> Visit us on CDesigner.eu </a>
    </div>
    
      
</body>
</html>

Conclusion and final thoughts

Our cart solution implement simple way how to manage users items for buy. For further improvements is important mark sold items for admin removal or better mark them for removal by automated script after some time. Way how to do this, is mark item after succesfull buy comitt them from users_id to -1 (cart_number filed of that item in bazaar_item table). This mean item was sold and after some time they must be removed from portal.

Full aplcation code for further study can be obtained from github here.