benanamen

I did study the picture for a little bit and it is clear you definitely have design issues even without knowing what it does. From your description it is apparent we need to define exactly what this app is going to do and the rules regarding that before a single table is created. So if you have not done so already, put together a detailed requirement list for this app including a much more detailed description of the app. Think in terms of a blueprint for a house. With a proper blueprint any Builder could build the house without asking questions.

A picture is for hanging on the wall. Please post the actual SQL dump. I have no idea what all the pretty colors mean and cannot see how tables are tied together. I can't import a picture to my database and recreate your schema and I cant run queries against a picture. It will be helpful to have an overview of what this app is.

It's pretty clear that your database design is completely wrong. Unless you fix it correctly, everything you do with the database will be some sort of hack. You were offered to have experts review your database design at no charge yet you don't take advantage of it. I don't understand that. If you go into coding with a "it works so I'll use it" attitude you're not going to get very far.

The first if.

@ajoo, best thing to do is post an actual SQL dump of your DB schema along with sample data. The database is the foundation of all the code you are going to write so it is very important that the DB is correct FIRST. There are reasons to use UNION as Barand has said, but when it comes to noobs on forums using it, they database is usually designed incorrectly.

In that particular example I would say that should be one table people. A lecturer could also be a pupil and vise versa. You would end up duplicating data with two tables.

Lol! We both have the same exact post time so you missed me by seconds. Still +1 for the right answer.

Depending on a button name to be submitted for your script to work is wrong and will completely fail in certain circumstances, one being in IE. I suspect you are testing it on <= IE8. You need to use if ($_SERVER['REQUEST_METHOD'] == 'POST') Then do additional checks.

When I see someone using UNION that waves a red flag to me about the DB design. @Barand, with the info OP has provided, wouldnt you say there is an underlying DB design problem here?

I had a feeling you were trying to do something with pagination. This is a classic XY Problem. http://xyproblem.info/ I am surprised the other experts didn't see through what you were asking. I am not inclined to get into this, but you are doing it all wrong. You have now provided enough information that the others here will now be able to properly help you with the "real' problem.

Allow me to get beyond your "problem". What exactly is it that you are doing that you need to generate 100 links?

I can't really make it any simpler than it is. Did you even try the examples on the page? SELECT something FROM somewhere WHERE this OR that OR that OR that OR that_too. You spent no more than 5 minutes on it from the time you replied back.

You are looking for the OR operator. https://www.techonthenet.com/mysql/or.php

That is really the start of your answer. You are storing your data incorrectly which will force you to continually create hacks to manage your data. Look up and learn "Database Normalization".

It would appear your database is set up incorrectly. Category should be the category ID tied to a separate category table.

That is not any kind of login code.

Ok, got it. When it comes to doing something in twig I don't know what should be obvious yet. You have already shown me several twig things that were not obvious to me. I thought ?{{ {'id': get.id}|url_encode }} was some sort of dynamic twig thing to get the whole url replacing $_SERVER['SCRIPT_NAME']. I pretty much have the jist of using TWIG with mysql now thanks to you and kicken. It was only 4 days ago that I even really looked at twig. So here is the (hopefully) final version of the template and pre-template code. I am sure there is probably some fine tuning that can be done somewhere. Any further comments or improvements welcome. Current Template <form class="form-horizontal" action="{{ form_action }}?{{ { 'p': get.p, 'id': get.id }|url_encode }}" method="post"> <fieldset> <legend> {% if get.action == 'add' %}Add{% endif %}{% if get.action == 'edit' %}Edit{% endif %} Membership Types </legend> <!-- Select Basic --> <div class="form-group {{ error.membership_category_id ? 'has-error' : '' }}"> <label class="col-md-4 control-label" for="membership_category_id">Membership Category <span style="color: #FF0000;">*</span></label> <div class="col-md-4"> <select id="membership_category_id" name="membership_category_id" class="form-control"> <option value="" style="display:none">Select Category</option> {% for option in category %} <option value="{{ option.membership_category_id }}" {% if form_data.membership_category_id is defined and option.membership_category_id == form_data.membership_category_id %}selected{% endif %}>{{ option.membership_category }}</option> {% endfor %} </select> </div> </div> <!-- Text input--> <div class="form-group {{ error.membership_type ? 'has-error' : '' }}"> <label class="col-md-4 control-label" for="membership_type">Membership Type <span style="color: #FF0000;">*</span></label> <div class="col-md-4"> <input id="membership_type" name="membership_type" type="text" placeholder="Membership Type" class="form-control input-md" value="{{ form_data.membership_type ?? '' }}"> </div> </div> <!-- Text input--> <div class="form-group {{ error.membership_type_description ? 'has-error' : '' }}"> <label class="col-md-4 control-label" for="membership_type_description">Membership Type Description <span style="color: #FF0000;">*</span></label> <div class="col-md-4"> <input id="membership_type_description" name="membership_type_description" type="text" placeholder="Membership Type Description" class="form-control input-md" value="{{ form_data.membership_type_description ?? '' }}"> </div> </div> <div class="form-group"> <label class="col-md-4 control-label"></label> <div class="col-md-4"> <label class="checkbox-inline" for="is_promo"> <input type="checkbox" name="is_promo" id="is_promo" value="1" {% if form_data.is_promo %}checked{% endif %} > Promotional </label> <label class="checkbox-inline" for="is_active"> <input type="checkbox" name="is_active" id="is_active" value="1" {% if form_data.is_promo %}checked{% endif %} > Active </label> </div> </div> <div class="form-group"> <div class="col-md-offset-4 col-md-4"> {% if get.id is not null %}<input type="hidden" name="id" value="{{ get.id }}">{% endif %} <input type="submit" name="submit" value="Submit" class="btn btn-primary"> </div> </div> </fieldset> </form> <?php $error = []; if ($_SERVER['REQUEST_METHOD'] == 'POST') // update or add record { //------------------------------------------------------------------------------------ // Trim Data, Check Missing Fields //------------------------------------------------------------------------------------ include './includes/validate_membership_types.php'; //------------------------------------------------------------------------------------ // Check for errors //------------------------------------------------------------------------------------ if ($error) { show_form_errors($error); } //------------------------------------------------------------------------------------ // Update Data //------------------------------------------------------------------------------------ elseif (isset($_GET['id'])) { $sql = "UPDATE membership_types SET membership_category_id =?, membership_type =?, membership_type_description =?, is_promo =?, is_active =? WHERE membership_type_id=?"; $stmt = $pdo->prepare($sql); $stmt->execute(array( $_POST['membership_category_id'], $_POST['membership_type'], $_POST['membership_type_description'], $_POST['is_promo'], $_POST['is_active'], $_POST['id'] )); die(header("Location: {$_SERVER['SCRIPT_NAME']}?p=list_membership_types&edit")); } //------------------------------------------------------------------------------------ // Insert Data //------------------------------------------------------------------------------------ else { $sql = "INSERT INTO membership_types (membership_category_id, membership_type, membership_type_description, is_promo, is_active) VALUES (?, ?, ?, ?, ?)"; $stmt = $pdo->prepare($sql); $stmt->execute(array( $_POST['membership_category_id'], $_POST['membership_type'], $_POST['membership_type_description'], $_POST['is_promo'], $_POST['is_active'] )); die(header("Location: {$_SERVER['SCRIPT_NAME']}?p=list_membership_types&insert")); } $form_data = $_POST; } //------------------------------------------------------------------------------------ // Initial form for editing record //------------------------------------------------------------------------------------ elseif (isset($_GET['id'])) { $sql = "SELECT membership_category_id, membership_type, membership_type_description, is_promo, is_active FROM membership_types WHERE membership_type_id=?"; $stmt = $pdo->prepare($sql); $stmt->execute(array( $_GET['id'] )); $form_data = $stmt->fetch(PDO::FETCH_ASSOC); } //------------------------------------------------------------------------------------ // Initial form for adding record //------------------------------------------------------------------------------------ else { $form_data = array(); } //------------------------------------------------------------------------------------ // Pass $form_data to template //------------------------------------------------------------------------------------ echo $twig->render('form_membership_types.twig', [ 'form_action' => $_SERVER['SCRIPT_NAME'], 'get' => $_GET, 'form_data' => $form_data, 'error' => $error, 'category' => $pdo->query('SELECT membership_category_id, membership_category FROM membership_category')->fetchAll(), ]); ?>

Still not right. It needs the GET p page name value. On submit goes to http://localhost/mydev/index.php?id=1 This works: <form class="form-horizontal" action="{{ form_action }}?p={{ get.p }}{% if get.id %}&id={{ get.id }}{% endif %} " method="post"> Is there an improvement to that? FYI: 'form_action' => $_SERVER['SCRIPT_NAME'],

<form method="post" action="{{ {'id': get.id}|url_encode }}"> @Jaques1, This does not work as is. This is a working url: http://localhost/mydev/index.php?p=edit_newdev&id=1 Doing what you posted results in <form class="form-horizontal" method="post" action="id=1"> To the URL http://localhost/mydev/id=1

What would you suggest for a function for the missing required fields? The validation include is just simple ifs that add errors to the error array if there is a missing field. The validation currently is just for missing required fields and a function call converting certain empty fields to null.

Thank you. Starting from a blank page I used the code from #37. The problem of overriding fields is fixed and now you can Insert and Update from a single twig template. Additionally, you can also insert and update from a single php file, something I didn't have before. The problem I now have is with the dropdown. The dropdown is used for both the insert and update. Currently the query to get that data is part of the update record code. A new record is not being updated, so how do I best go about handling it? I also see that you show a function to get the update data. I assume that is a better way to go about getting the data. I will come to that when I get everything else sorted out. ($membership_data = load_membership_data($_GET['id']);) Aside from what is mentioned, any other issues at this point? The part for the dropdown. Where would this go now? $options = array(); foreach ($stmt as $row2) { $options[] = [ 'selected' => isset($membership_category_id) && $membership_category_id == $row2['membership_category_id'], 'value' => $row2['membership_category_id'], 'label' => $row2['membership_category'] ]; } Current version of pre-template code <?php // TODO: Change arrays to new syntax [] $error = array(); if ($_SERVER['REQUEST_METHOD'] == 'POST') // update or add record { //------------------------------------------------------------------------------------ // Trim Data, Check Missing Fields //------------------------------------------------------------------------------------ include './includes/validate_membership_types.php'; //------------------------------------------------------------------------------------ // Check for errors //------------------------------------------------------------------------------------ if ($error) { show_form_errors($error); } //------------------------------------------------------------------------------------ // Update Data //------------------------------------------------------------------------------------ elseif (isset($_GET['id'])) { die('Record Updated'); //TODO: Temp Dev $sql = "UPDATE membership_types SET membership_category_id =?, membership_type =?, membership_type_description =?, is_promo =?, is_active =? WHERE membership_type_id=?"; $stmt = $pdo->prepare($sql); $stmt->execute(array( $_POST['membership_category_id'], $_POST['membership_type'], $_POST['membership_type_description'], $_POST['is_promo'], $_POST['is_active'], $_POST['id'] )); die(header("Location: {$_SERVER['SCRIPT_NAME']}?p=list_membership_types&edit")); } //------------------------------------------------------------------------------------ // Insert Data //------------------------------------------------------------------------------------ else { // @TODO add new record /*$sql = "INSERT INTO membership_types (membership_category_id, membership_type, membership_type_description, is_promo, is_active) VALUES (?, ?, ?, ?, ?)"; $stmt = $pdo->prepare($sql); $stmt->execute(array( $_POST['membership_category_id'], $_POST['membership_type'], $_POST['membership_type_description'], $_POST['is_promo'], $_POST['is_active'] )); die(header("Location: {$_SERVER['SCRIPT_NAME']}?p=list_membership_types&insert"));*/ die('Record Added'); // Temp Dev } $membership_data = $_POST; } //------------------------------------------------------------------------------------ // Initial form for editing record //------------------------------------------------------------------------------------ elseif (isset($_GET['id'])) { //$membership_data = load_membership_data($_GET['id']); $sql = "SELECT membership_category_id, membership_type, membership_type_description, is_promo, is_active FROM membership_types WHERE membership_type_id=?"; $stmt = $pdo->prepare($sql); $stmt->execute(array( $_GET['id'] )); $membership_data = $stmt->fetch(PDO::FETCH_ASSOC); } //------------------------------------------------------------------------------------ // Initial form for editing record //------------------------------------------------------------------------------------ else { $membership_data = array(); } //------------------------------------------------------------------------------------ // Pass $membership_data to template //------------------------------------------------------------------------------------ echo $twig->render('form_membership_types.twig', array( 'action' => 'Edit', 'form_action' => $_SERVER['SCRIPT_NAME'], 'get' => $_GET, 'data' => $membership_data, 'error' => $error )); ?>

EXACTLY! For edit mode, we start off with a table list of records which also includes an edit button on each row. That button POST's the ID which is used to query for the edit data. That is the exact problem I am now seeing. I never noticed the problem before. So the edit button should GET instead of POST if I am understanding correctly. So in the function would I then be using if ($_SERVER['REQUEST_METHOD'] == 'POST') AND if ($_SERVER['REQUEST_METHOD'] == 'GET'){ ? From a clean slate, no code, what is the optimum way to use a single twig template for add and edit? That's really the bottom line. What I was doing is obviously not right.

Yeah, I forgot about that. I was posting from my phone away from my DEV setup. It seems I should have done my twig testing inside the app. I would have seen that right away. Funny you mentioned that. That is something i noticed yesterday while I was testing. I checked the app and it has the same problem. Never noticed it before since the fields I had been dealing with were all required fields. Back to the drawing board!

If you look at post #20, i do have it as 3 strictly separated cases there and that is also how the original non-twig template is. Only difference that will be now, is you showed me a cleaner option to not repeat all the if statements and by putting it to a function, will tremendously reduce the code in the app. So this updated function along with a ternary in the template field to = ' ' as in value="{{ membership_type ?? '' }}" should do it right? function data_source($form_fields) { $field_data = []; foreach ($form_fields as $form_field) { if (!empty($_POST[$form_field])) { $field_data[$form_field] = $_POST[$form_field]; } if (!empty($row[$form_field])) { $field_data[$form_field] = $row[$form_field]; } } return $field_data; }

Where you have the empty set in the else, the original template had a ternary to set to empty in the form field instead so really the else should not be there in which case the logic would work as it has been. I had just not tested it in the app yet to notice that issue.