How to insert an array variable into mysql database?

PHP
1

Hello,
I work on a website which shows a users trades.

I want to give the user an option to filter the trades. Here is a screenshot of the filtering page:

filter
filter1171×442 53.7 KB

The user can select to see all trades starting from a selected date, until a selected date or between 2 selected dates.
The user can also select trades ope on selected weekdays. The user can select one weekday or any variation of weekdays.
The user can also select to filter trades by trade open hour

Here is the code which processes the form data

//////////////////////////////////////////////////////////////////////////////////////////////////////
/// Get MIN and MAX dates in case when start date / end date were not specified in filtering form  ///
//////////////////////////////////////////////////////////////////////////////////////////////////////
$minmax = get_min_max_dates(); 
//print_r($minmax);
foreach ($minmax as $ckey=>$cval){
$min_date = $cval['startDate'];
$max_date = $cval['endDate'];
}
 //////////////////////////////////////////////////////////////////////////////////////
 ///////                              code for period                           ///////
 ///////           Insert period form data in variables for sql query        	///////
 //////////////////////////////////////////////////////////////////////////////////////

         ////////////////////////////////////////////////////////////////////////////////////
	     ////     filter is selected. From date -  selected. To date -  not selected     ////
		 ////     meaning all dates from trading start date  until the end date          ////
		 ////////////////////////////////////////////////////////////////////////////////////
		if($_POST['period'] == 'filter'){
			
			if(!empty(trim($_POST['start_date'])) && (empty(trim($_POST['end_date'])))) {
			//echo 'start_date: '.trim($_POST['start_date']);
			$start_date = trim($_POST['start_date']);
			$end_date = $max_date;

		 ////////////////////////////////////////////////////////////////////////////////////    
		 ////    filter is selected. From date -  selected. To date -  selected          ////
		 ////     meaning all dates from selected start date  until selected end date    ////
		 ////////////////////////////////////////////////////////////////////////////////////
		}elseif(!empty(trim($_POST['start_date'])) && (!empty(trim($_POST['end_date'])))){
			//echo'start_date: '.trim($_POST['start_date']).'<br> end_date: '.trim($_POST['end_date']);
			$start_date = trim($_POST['start_date']);
			$end_date = trim($_POST['end_date']);

		 ////////////////////////////////////////////////////////////////////////////////
		 ////    filter is selected. From date -  not selected. To date -  selected  ////
		 ////    meaning all dates from start of trading until  the date selected    ////
		 ////////////////////////////////////////////////////////////////////////////////
		}elseif(empty(trim($_POST['start_date'])) && (!empty(trim($_POST['end_date'])))){
			//echo 'End date:' .trim($_POST['end_date']);
			$start_date = $min_date;
			$end_date = trim($_POST['end_date']);

		 //////////////////////////////////////////////////////////////////////////////////////////////////		
		 ////       filter is selected. from date -  not selected. to date -  not selected            /////
		 ////          ************   THIS SELECTION CREATES AN ERROR     ************                /////
		 ////  since the user selected the option for a  specific period but didn'n select any dates  /////
		 //////////////////////////////////////////////////////////////////////////////////////////////////
		}elseif(empty(trim($_POST['start_date'])) && (empty(trim($_POST['end_date'])))){
			echo '<b>ERROR</b>:<br> At least one date (start date / End date) munt be inserted<br>
			      <b> or</b><br> select: <i>All(default option)</i> in desired peroid';
			die();
		}
	   }else {
		    ///////////////////////////////////////////////////////////////////////////////////
		    ////     filter is selected. THe default option - All - is selected.          /////
			///////////////////////////////////////////////////////////////////////////////////
			//echo 'all';
			$period_selest = false;
			$start_date = $min_date;
			$end_date = $max_date;
			
			
		}
/////////////////////////////////////////////////////////////////////////		
////                       code for days                             ////
////      Insert intraday form data in variables for sql query       ////
/////////////////////////////////////////////////////////////////////////
if(isset($_POST['weekday'])) { 
	  $day_select = true;
	  $days = join(",", $_POST['weekday']);
		        
}else{
	  $day_select = false;
	  $days = [0,1,2,3,4,5,6];
	  $days = join(",", $days);
	  
	  
	 } 

///////////////////////////////////////////////////////////////////
///                   code for intraday                    ////////
/// Insert intraday form data in variables for sql query   ////////
///////////////////////////////////////////////////////////////////
if(isset($_POST['intraday'])){
$hours = ($_POST['intraday']);

switch ($hours) {
    case "am":
        $hour_start = '00:00';
		$hour_end = '11:59';
		//echo $hour_start.'<br>';
		//echo $hour_end;
        break;
    case "pm":
        $hour_start = '12:00';
		$hour_end = '23:59';
		//echo $hour_start.'<br>';
		//echo $hour_end;
        break;
    case "on":
        $hour_start = ($_POST['start']);
		$hour_end = ($_POST['end']);
	    //echo $hour_start.'<br>';
		//echo $hour_end;
         break;
}//End switch ($hours) 
}//End if(isset($_POST['intraday'])
else{
   $hour_start = '00:00';
   $hour_end = '23:59';
   $intraday_select = false;

}

I want to insert $start_date, $end_date, $days, $hour_start, $hour_end
into a table so that a user can seee various trades e.g/ EUR/USD trades or DAX index trades using the selected filtering criteria without selecting them over and over again until a new filtering criteria is selected.

  1. Is it correct to save dates an time as text ?
  2. My biggest problem is how to insert the $days array into the database?
    as it can be (1), (1, 2, 4), or (2, 3, 4, 5). in other words it can be a 1 item array to 5 items array
2

Why can’t you use a DATETIME column type for a date/time, or a date or time column if they are separate? Saving them as text may make sorting and comparisons more tricky.

Surely you would have a column for each, and allow NULLs on each column that can be left blank?

3

Let me first suggest that this would be a wonderful use case for javascript storage.

4

No. It will make searching by date or time difficult, as the database will only see the value as a string, which as far as it is concerned has no relation at all to date or time. Use date or time specific data types, that’s why they exist.

5

Won’t it be simpler to turn an array into a string e.g. (1, 3, 5, 6) to ‘1356’ ?

6

It will make the insert part easy. But it won’t be so simple to select data by what’s in that column.

One option to put it all in a single column, and still be in some way accessible, would be a bitwise column. But it really depends on how you need to retrieve the data and work with it after insertion.

This is what database design is about. It’s easy to insert any old data, in any form, that’s no problem at all.
It’s when you come to have to select very specific data, that’s when things can be very difficult, if you did not design the data structure just right for the task in hand.

7

Stuffing the array into a single column is a violation of first normal form.

8

Which only really matters if your use case relies on access individual members of the column instead of the column wholesale.

I feel we’re overcomplicating the idea of “Store User Preferences” by extrapolating it to a full database design.

The use case outlined is a simple Store-and-Pull-Row, no selecting subcolumns or anything like that.

1 Like
9

Re-reading this today, it’s clearer, yet less clear.

I think we need to know more about the actual data that needs to be stored.
Why do we need an array of days (on insert)?
I think the lines are being blurred between what is required for the select data and what is required for the insert data.
Is the user form query triggering a select? Or, as it seems, is it triggering an insert? Or is the insert, putting the data into the database in the first place for the user selection?

Presumably, a “trade” is an event that happens at a particular time.
A single time an event happens can be recorded as Date Time or a Time Stamp.

I’m confused as to why you would need an array of days inserting for a “trade”. Unless the user form is resulting in an insert, as in you are recording what people are searching.

10

As I understand the OP, the request is “Take the user’s settings on this form, and save them, so that the next time they come to this page, they don’t have to fill the form out again.”

1 Like
11

That is clearer, so it is recording what people have searched for.

Should this be cookies or something instead?

12

S’why i suggested localStorage. But cookies would work too.

1 Like
13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.