[Storing JSON in MySQL database]

I am making an application where the user can make a form.

 

The first pick a form name and a description (to be saved to the forms table)

Then they select the fields that they want to use from various drop downs, set the label and some bootstrap size properties with the aim being that once all of the fields have been chosen i will be able to create a form to show to end users.

A simple example of this could be:

Field Name ------ Field Label ------ Width ------ Order

First Name ----- Given Name ----- 6 ----- 1

Last Name ----- Surname ----- 6 ------ 2

(i wish i could make tables on here)

When these fields have been selected and related data amended, i am wanting to store them in the database so that i can query the data and build the form.

I have been thinking about the best way to do this as each form with have a different and unknown number of fields.

Should i have a link table that has a new row per field added to the form - something like this:

form_id ----- field_id

1 ----- 1

1 ----- 2

1 ----- 3

or should i look at storing the fields as JSON in the form table itself.

I have never gone down the JSON route and dont know how to interact with it or if it is a good route.

Advice here would be appreciated.

[Storing Images]

On 2/24/2024 at 7:37 AM, gizmola said:

I wouldn't worry about load testing until you have an MVP.  Unit tests are much more important in the development phase.

With that said, a fast and simple way of running a load test, is to use the apache benchmark (ab) program, that is part of the apache server.  It's a simple cli program that you can use to send a bunch of requests using multiple socket connections.  You can also do some minimal authentication and post requests with it.  

Beyond ab, there's a lot of other tools like Siege and JMeter, that have different strengths and use cases.

Thanks for this - i will look into what you have suggested when i get further through the project.

[Create folder in file structure from php application]

In my application, i want to be able to create web portals that can be accessed from the internet - the user will need permission to get to the portal and will need to log in.

In order to achieve this i think i need to create a folder in my file structure dynamically.

So for example, you name the website "portal1" click "make new website" and a folder called "portal1" is ceated in the file structure and several files dropped into it.

Is this possible and if so how would i go about it - or as always, is there another way i should be dealing with this?

[Creating array to POST with jQuery]

4 minutes ago, Barand said:

Yes.

That is what I was going to suggest if you didn't want to use the hidden field approach. That way you send just a single querystring which will be handled automatically to create the $_POST array

Perhaps

    $stmt = $pdo->prepare("INSERT INTO mytable (zoneType, zoneName, printValue) VALUES (?,?,?)");
    foreach ($_POST['zoneName'] as $k => $zn)  {
        if ($zn) {
            $stmt->execute([ $_POST['zoneType'][$k], $zn, $_POST['printValue'][$k] ]);
        }
    }

 

You are a wonderful human.

[Creating array to POST with jQuery]

$("#newZones").submit(function(ev) {
			ev.preventDefault()
			let fdata = $(this).serialize() + "&ajax=addNewZones";



			$.ajax({
				type: 'post',
				data: fdata,
				success: function(resp) {
					console.log(resp);
					$('.alert-container').html(resp).show()
					// if (resp == "User Created") {
					// 	$(':input', '#createNewUser')
					// 		.not(':button, :submit, :reset, :hidden')
					// 		.val('')
					// 		.prop('checked', false)
					// 		.prop('selected', false);
					// }
				}
			})
		})

 

function addNewZones($pdo)
{
	$sql = "INSERT INTO zones (name, print_value, event_site_attachment_id, zone_type_attachment_id) values (:name, :printVal, :siteId, :zTypeId)";
	$stmt = $pdo->prepare($sql);
	echo "<pre>";
	var_dump($_POST);
	echo "</pre>";
}

This is now showing what looks like the right output

array(4) {
  ["zoneType"]=>
  array(2) {
    [0]=>
    string(1) "1"
    [1]=>
    string(1) "1"
  }
  ["zoneName"]=>
  array(2) {
    [0]=>
    string(1) "1"
    [1]=>
    string(1) "3"
  }
  ["printValue"]=>
  array(2) {
    [0]=>
    string(1) "2"
    [1]=>
    string(1) "4"
  }
  ["ajax"]=>
  string(11) "addNewZones"
}

How does one go about looping through this array to insert it into the DB but only when a zone name is present.

[Creating array to POST with jQuery]

1 hour ago, Barand said:

Your POST array looks like this, as you separate the "ajax" value from the serialized data in your JSON string...

Array
(
    [ajax] => testdata
    [data] => zoneType%5B%5D=1&zoneName%5B%5D=aa&printValue%5B%5D=1&zoneType%5B%5D=1&zoneName%5B%5D=bb&printValue%5B%5D=2&zoneType%5B%5D=1&zoneName%5B%5D=cc&printValue%5B%5D=3
)

 

With my code, where only the serialized form data is sent (which includes the "ajax" value from a hidden form field) it looks like this...

Array
(
    [ajax] => testdata
    [zoneType] => Array
        (
            [0] => 1
            [1] => 1
            [2] => 1
        )

    [zoneName] => Array
        (
            [0] => aa
            [1] => bb
            [2] => cc
        )

    [printValue] => Array
        (
            [0] => 1
            [1] => 2
            [2] => 3
        )

)

 

is that becuase my code is "wrong" - should i be switching to your way that works or is there another way around.

[Creating array to POST with jQuery]

in the function to insert the data

function addNewZones($pdo, $zoneArr){
foreach ($zoneArr as $k => $v) {
}
}

is throwing : foreach() argument must be of type array|object, string given

[Creating array to POST with jQuery]

This seems to suggest that i have got it how i need it - i think

 

function addNewZones($pdo, $zoneArr)
{
	$params = array();
	parse_str($zoneArr, $params);
	echo ("<pre>");
	var_dump($params);
	echo "</pre>";
}

array(3) {
  ["zoneType"]=>
  array(3) {
    [0]=>
    string(1) "1"
    [1]=>
    string(1) "1"
    [2]=>
    string(1) "1"
  }
  ["zoneName"]=>
  array(3) {
    [0]=>
    string(1) "1"
    [1]=>
    string(1) "3"
    [2]=>
    string(1) "5"
  }
  ["printValue"]=>
  array(3) {
    [0]=>
    string(1) "2"
    [1]=>
    string(1) "4"
    [2]=>
    string(1) "6"
  }
}

 

[Creating array to POST with jQuery]

58 minutes ago, mac_gyver said:

if you put the form fields inside a form, and give them array names, you can use the jquery .serialize() method to get the form data using a single line of code - https://api.jquery.com/serialize/ you would use this in the data value in the .ajax call, e.g. data: $(this).serialize(),

next, if you put the dynamically to-be added markup inside a <template></template> tag, it is simple to dynamically add it - https://developer.mozilla.org/en-US/docs/Web/HTML/Element/template

if you put the closing </label> tag after the form field it corresponds to, you can leave out the for='...' attributes and the corresponding id='...' attributes, and make the labels work for the dynamically added fields.

Thanks for this.  I will certainly have a look at <template> and great tip on the lables.

[Creating array to POST with jQuery]

46 minutes ago, Barand said:

Here's a sample script

<?php

##
##  HANDLE AJAX REQUEST
##
    if (isset($_POST['ajax']))   {
        if ($_POST['ajax']=='testdata') {
            exit('<pre>' . print_r($_POST, 1) . '</pre>');      // JUST RETURNS THE ARRAY OF POSTED DATA FOR DISPLAY
        }
        exit();
    }
    
?>
<!DOCTYPE html>
<html lang="en">
<head>
   <meta charset="utf-8">
   <title>Example</title>
   <script src="https://code.jquery.com/jquery-3.6.4.min.js"></script>
   
   <script type='text/javascript'>
       $(function() {
           $("#form1").submit(function(ev) {
               ev.preventDefault()
               let fdata = $(this).serialize()
               console.log(fdata)
               $.post(
                   "",
                   fdata,
                   function(resp) {
                       $("#test-output").html(resp)
                   },
                   'TEXT'
               )
           })
       })
   </script> 
</head>
<body>
    <form id='form1'>
    <input type='hidden' name='ajax' value='testdata'>
      <div class="col">
        <label for="zoneType" class="form-label">Zone Type</label>
        <select name="zoneType[]" class="form-select zoneType">
          <option value="1">Normal Zone</option><option value="2">Special Zone</option>                            
        </select>
      </div>
      <div class="col">
        <label for="zoneName" class="form-label">Zone Name</label>
        <input type="text" class="form-control simple-string zoneName" name="zoneName[]">
      </div>
      <div class="col">
        <label for="printValue" class="form-label">Print Value</label>
        <input type="text" class="form-control simple-string printValue" name="printValue[]">
      </div>
      <br>
      
      <div class="col">
        <label for="zoneType" class="form-label">Zone Type</label>
        <select name="zoneType[]" class="form-select zoneType">
          <option value="1">Normal Zone</option><option value="2">Special Zone</option>                            
        </select>
      </div>
      <div class="col">
        <label for="zoneName" class="form-label">Zone Name</label>
        <input type="text" class="form-control simple-string zoneName" name="zoneName[]">
      </div>
      <div class="col">
        <label for="printValue" class="form-label">Print Value</label>
        <input type="text" class="form-control simple-string printValue" name="printValue[]">
      </div>
      <br>
      
      <div class="col">
        <label for="zoneType" class="form-label">Zone Type</label>
        <select name="zoneType[]" class="form-select zoneType">
          <option value="1">Normal Zone</option><option value="2">Special Zone</option>                            
        </select>
      </div>
      <div class="col">
        <label for="zoneName" class="form-label">Zone Name</label>
        <input type="text" class="form-control simple-string zoneName" name="zoneName[]">
      </div>
      <div class="col">
        <label for="printValue" class="form-label">Print Value</label>
        <input type="text" class="form-control simple-string printValue" name="printValue[]">
      </div>
      <br>
    <input type='submit'>
    </form>
    <div id='test-output'>
    </div>
</body>
</html>

[edit]...

NOTE - your form's input fields need name attributes

Amazing, i have modified your code a little so that it fits with how i have done everything else (and i can understand it in the future)

		$("#newZones").submit(function(ev) {
			ev.preventDefault()
			let fdata = $(this).serialize()
			console.log(fdata)
			$.ajax({
				type: 'post',
				data: {
					'ajax': 'addNewZones',
					'data': fdata,

				},
				success: function(resp) {
					$('.alert-container').html(resp).show()
				}
			})
		})

and

if (isset($_POST['ajax'])) {
	switch ($_POST['ajax']) {
		case 'addNewZones':
			exit(addNewZones($pdo, $_POST['data']));
			break;
	}
}

Once the data has been sent into PHP function. Do i need to unserialise it to insert it?

Also, if the zone name field is blank the whole row of 3 fields need to be removed from the array that is sent.  Would this be a filter?

 

[Creating array to POST with jQuery]

Landed here:

Seems to be working but i dont know if there is a more optimal way of doing this.  Feels a bit like a hack.

$('#addZonesButton').on('click', function() {
			$zNameArr = []
			$zPrintArr = []
			$zTypeArr = []
			$data = [];
			$('.zoneType').each(function() {
				$zType = $(this).val()
				$zTypeArr.push($zType)

			})
			$('.zoneName').each(function() {
				$zName = $(this).val()
				$zNameArr.push($zName)
			})
			$('.printValue').each(function() {
				$pVal = $(this).val()
				$zPrintArr.push($pVal)
			})

			for (i = 0; i < $zNameArr.length; i++) {

				$data[i] = Array($zTypeArr[i], $zNameArr[i], $zPrintArr[i]);
			}
	
			$filteredData = $data.filter(function(x) {
				/* here, x is an array, not an object */
				return !(x.some(element => element === (undefined || null || '')))
			});
			
		})

The data is output as:

 

[
  [
    "1",
    "1",
    "p1"
  ],
  [
    "1",
    "3",
    "p3"
  ],
  [
    "1",
    "2",
    "p2"
  ]
]

Would have preferred this to have keys also.

Now i need to send it to php with ajax and insert it.

[Creating array to POST with jQuery]

I am not sure if this is getting me closer or further away.

$('#addZonesButton').on('click', function() {
  $zNameArr = []
  $zPrintArr = []
  $('.zoneType').each(function() {
    $zType = $(this).val()

  })
  $('.zoneName').each(function() {
    $zName = $(this).val()
    $zNameArr.push($zName)
  })
  $('.printValue').each(function() {
    $pVal = $(this).val()
    $zPrintArr.push($pVal)
  })
		})

 

[Creating array to POST with jQuery]

I seem to be collecting the data with the following

$('#addZonesButton').on('click', function() {
			$zTypes = $('.zoneType').each(function() {
				$zType = $(this).val()
				console.log($zType);
			})
		})

but how would i then create the array needed in order to post it

[Creating array to POST with jQuery]

Hi All,

I have a form that allows the user to provide data in input boxes)

I have a button that allows the user to add more rows (the same as row 1 repeated)

I am using Ajax to post this form but my question is, how do i build the array in jquery of all of the inputs that will then be posted to PHP to insert into the database.

The form looks like this:

<div class="row mb-3 zone-row">
  <div class="col">
    <label for="zoneType" class="form-label">Zone Type</label>
    <select id="zoneType" class="form-select zoneType">
      <option value="1">Normal Zone</option><option value="2">Special Zone</option>							
    </select>
  </div>
  <div class="col">
    <label for="zoneName" class="form-label">Zone Name</label>
    <input type="text" class="form-control simple-string zoneName" id="zoneName">
  </div>
  <div class="col">
    <label for="printValue" class="form-label">Print Value</label>
    <input type="text" class="form-control simple-string printValue" id="printValue">
  </div>
</div>

when clicking the button to add another row - it adds:

<div class="row mb-3 extra-row">
  <div class="col">
    <select id="" class="form-select zoneType"> 
      <option value="1">Normal Zone</option>
      <option value="2">Special Zone</option> 
    </select>
  </div>
  <div class="col">
    <input type="text" class="form-control simple-string zoneName" id="">
  </div>
  <div class="col">
    <input type="text" class="form-control simple-string printValue" id="">
  </div>
</div>

i expect that this will need to be some sort of each function?

[Storing Images]

12 hours ago, gizmola said:

Scalability is also a concern.  Here is some food for thought.

What is important for scalability is only the maximum number of concurrent users per second.  You need tools to help you simulate this type of load before you really can get an understanding of any potential bottlenecks or what concurrent load your system can handle and still operate.

Assumption #1: you have a monolithic server. 

What this means is that your application will run in a stack where everything (other than the database) will run on the same server.

1. Sessions will use the filesystem
2. images will be stored on the filesystem
3. Database can be running on the same server or not
4. Reverse proxy will run on the same server (assuming you are employing one).

This sort of setup is typical, and has limited scalability, and suffers from contention issues when load increases.  If this is how your production will run, you at least want to learn a bit about how your setup performs as load increases.  Everything takes memory, and databases don't work well if they run out of memory or resources.  A frequent mistake people make in setting up a database is to provide inadequate memory allocation.  Databases are pretty much always given their own dedicated machine to run, for anything that isn't a hobby or non-commercial endeavor.

Advantages to storing data on the filesystem:

1. Filesystem already buffers data and is highly efficient
2. The cost of returning data is only IO + bandwidth
   1. Stored in a database, a read of a blob requires IO + network delivery to application + network delivery to client
   2. Stored in the db, blob storge and retrieval is often non-optimal
   3. Stored in the db, blobs balloon the size of the database dataset, making database caching less effective, and can also slow down queries that touch the table(s) with the blobs in them.  

In terms of security, you will need to store the files in a location that is not within web space (ie. under the webroot).  It is easy enough to write the routine you need that returns the data from a location on the filesystem. 

Storing in a database does have this advantage, in terms of the potential for scalability:

1. Making the application scalable is simpler, as you can have n-1 application servers connecting to the same database (and this is the 1st level typical of a move to a scalable architecture from what started as a monolithic app)
2. This is another reason to start with a reverse proxy even with a monolithic architecture.
   1. Once you have app server #2 you have broken sessions
      1. You can move sessions into a database or distributed cache like memcached or redis
         1. Moving sessions into a DB can add a lot of load to the db
      2. You can use the reverse proxy to pin sessions (ie. "sticky sessions") to a specific app server.  Usually this is done using a cookie that the reverse proxy adds and subsequently uses to keep traffic coming back to the same app server once an initial connection is made.

 

The other ways to scale and application that uses images stored on a filesystem:

• Use an NFS server or NAS appliance
  • I've worked for a number of companies with large amounts of data and files.  In some cases, the problem could be solved with a NAS device, which servers can then mount using NFS as a client.
• Use a file storage service
  • A could example of this is AWS S3. Some ISP's have their own alternative, and there are even consumer grade services like Dropbox you can make use of if you look into it.  Whether or not this is smart or feasible comes again down to the application infrastructure, but as a rule of thumb, you are more likely to have the app experience feel similar if the object storage is local/within your hosting infrastructure.  For example, if you had a server hosted by Linode, they offer an S3 compatible alternative service, and I'd look into that.  
    • The downside here is additional costs for the storage of the assets and possibly egress costs to retrieve.  There are a lot of different "Object Storage" companies out there, and they are intrinsically scalable, so it wouldn't hurt to do some research.
      • Take this list with a grain of salt, but here's a way to start looking at the possible vendors: https://www.g2.com/categories/object-storage-solutions  

This is a a really great answer and i appreciate it.  Again, alot of this is very new to me and there will be a lot of research required.  I think the s3 option could be a good one for me as i plan to host on AWS.

 

You have also raised a lot of very good points that i am yet to look into.

 

When, would you suggest in the application build do you start testing load.  I am pretty early in the process at the minute but as i have never load tested an application before, i wonder when would be the best time to start?

[Function per field or select all data]

4 hours ago, gizmola said:

There are design patterns like Model-View-Controller (MVC) that already address these ideas.  Most frameworks implement the MVC pattern.

How is this relevant to you?

Your user data structure should be described in a "User" model class.  

Frameworks give you a lot of base class functionality, but even without using a framework, you should be able to take the idea, and create your own.

The models of the popular frameworks are otherwise known as "object relational mapper" aka ORM libraries, and of these there are a couple different pattern flavors:  the Data Mapper pattern (example: Doctrine ORM, Cycle ORM) or the Active Record pattern (Laravel Eloquent, CakePHP, others).

Looking into the API's these libraries provide, can be the basis of your own home-grown highly simplified model classes.  The important idea to grasp, is that a model class in an ORM is the blueprint for how relational data is mapped to a php object when read, and stored into the database (or deleted) when written.

The popular ORM's do a lot more than that, but as a starting point it's good to just think about those simple needs.

To the point of setters and getters this often comes down to philosophy and pragmatism.  It is entirely possible to create a model class for a table that uses one or both of the __get and __set magic methods to provide you with $user->getCol1() and $user->setCol1() functionality without having to actually implement those methods.

Conversely however, most ORM's require you to provide the attributes and setter/getter functions, and there are certainly cases where those are valuable to have.  

However, when all is said and done, the bigger question is:  why don't you use one of these many ORM libraries instead of rolling your own ill conceived libraries?

 

Thanks for this - the simple answer is that i have learned a language (learning) and i am yet to have any expereience of the concepts that you are describing.  I havnt yet explored the world of OOP and clearly this is something that i will need to have a look into.  I havnt used any frameworks at all.

I think this is going to need to be something that i explore to help me move from very amateur.

I appreciate your comments and i will certainly look at adding frameworks to the reading list while i try to "master" the basics.

[Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number]

Thank you all for the explanation.  I am learning and many of the concepts in discussion are new to me so takes a while for me to properly understand.  I appreciate your patience while i learn.

[Function per field or select all data]

25 minutes ago, Barand said:

Whichever approach you use, do NOT connect to the database inside every function. Connect once to the db at start of each script and pass the connection to the functions. (The connection is the slowest part of the process)

So the function calls are

getFirstName($pdo, $id)
getLastName($pdo, $id)
getEmail($pdo, $id)
getDietary($pdo, $id)
getX($pdo, $id)

I'd go with a single function

$user = getUserData($pdo, $user_id);                              // get an array containing the user data
if ($user) {
    $fullname = $user['firstname'] . ' ' . $user['lastname'];     // use array elements as required
}

function getUserData($pdo, $id)
{
    $res = $pdo->prepare("SELECT firstname
                               , lastname
                               , email
                               , diet
                               , gender
                               , x
                               , y
                               , z
                          FROM user
                          WHERE id = ?      
                          ");
    $res->execute([$id]);
    return $res->fetch();
}

 

Thank you for this.  You will be happy to know that i am now using the function($pdo, $var, $var) way of working.  Should probably have included in the previous post.

 

Single function - got it.

[Function per field or select all data]

So i could have a set of functions that get a single piece of data:

getFirstName($id)
getLastName($id)
getEmail($id)
getDietary($id)
getX($id)

and a function that calls these singles and builds the view:

getUserInfo($id){

getFirstName($id)
getLastName($id)
getEmail($id)
getDietary($id)
getX($id)

}

 

or i could just getAllUserData()

and just send one query to the database.

[Pass variables from page to page]

9 hours ago, mac_gyver said:

each page must enforce what the current user can do and see on that page, for a non-logged in user, for a logged in user, and for a logged in administrator.

if the current user is not logged in, they can only do and see what you have coded on that page for a non-logged in user to do and see. if they are a logged in user and the user ID in the URL is the same as the currently logged in user ID, they can perform actions like editing their own data and seeing all of their own data. if the currently logged in user is a administrator, he/she would be able to pick any user and be able to perform actions like editing that user's normal data and seeing all that user's normal data, and be able to edit/see additional data, such as permissions, edit history, site activity, ip history, ...

if you aren't already doing so, your login code should store the user id (autoincrement primary index) in a session variable to indicate who the currently logged in user is. if there is a logged in user, you would query on each page request to get any other user data, such as - username, email, permissions, ...

This is what i thought and hoped the answer would be and is the approach that i am taking.  Thanks for confirming that for me.

[Function per field or select all data]

So i have a user form that collects:

First Name
Last Name
Email
Dietry Requirements
Gender
X
Y
Z

 

When pulling up this user information i could create one function

getAllUserData()

that gets all the user data from the DB and builds the display.

But later i will need to get the users name - which would be its own function or maybe the email address that would be its own function.

The question is, if i know that i am going to have a function that pulls the users name and a function that pulls the users email address, do i bother with a function that pulls all the user data in one - or would just pulling individual fields in their own function be a better option.

The database connection comment was more about each function having to establish a connection and send a query to the database - it could be 10 or more queries to get all of the data that i need for the user rather than just one.

I am just trying to weigh up benefits of each approach and wondered what may be concidered standard.  I have always pulled all the data that i need in one function and if there is a case where i need slightly different data, i just make another function.

[Storing Images]

7 hours ago, requinix said:

For simple applications, storing the file on a server is typically the easiest approach: file gets uploaded, you stick it somewhere that's accessible by the web server, and you can use a direct URL straight to the image.

Breaks down when you have multiple servers. Doesn't support cases where you don't want just anybody on the internet to be able to see the image (though in this case you probably do want that). And backing up those images can be a pain in the ass.

The alternative is storing the image in your database. Has overhead, like having to know the image file type and needing to provide some sort of script that can render the image, but the benefits may be worth it.

Thanks for this - there are likely going to be 10's of thousands of users.  Does this make the database approach more favourable.  I actually dont want the general internet to be able to see the images, they are used for ID purposes.

[Storing Images]

Hi All,

I have an application with users - users need to have a photo stored on their account.  In the past i have literally saved the image in a folder on the server but i wondering what the best, most efficient, most secure, most space saving method would be to deal with user pictures.

Any suggestions here would be welcome.

Thanks

[Function per field or select all data]

Hi All,

I am building an application that has users.

My questions is, when showing the user information, would it be better to have a seperate function for every item that i want to retrieve (first name, last name, email address) or should i just have a function that builds the whole data set and gets all the fields that i need in one go.

I am thinking about not duplicating my work as i will need to get the users name in other places and will need to get their email in other places.

Is it is a good idea to have a function to get each piece of data one by one or is this overkill?  I will need 10 functions to run and 10 database connections just to show basic user info.

[Pass variables from page to page]

Hi All,

I have a page that lists users - clicking a user will take you to a page where you edit the user.

I know that i can use get to pass the users ID from page A to page B but i am trying to avoid users being able to hijack the URL and navigate around like that.

I am trying to make sure that i prevent people seing what they shouldnt with permissions and sessions but i wanted to enquire if there was a better way to get around rather than get.

I have read that sessions could be an option but how would i set the session on a button click when i would need JS for the button click and PHP to set the session.

Would this be an AJAX job calling a php function.

Any suggestions on the best way would be appreciated.