multiple row creation

[dannyone]

is it possible to have a html checkbox that when selected stores in a database on multiple rows with 3 values. this is what i need to do,

i need to create 1 checkbox

 

checkbox  9-12 Monday

 

then when the user select it, to store multiples values in my database, for example

 

Markerid,  Time,    day

111            9-10    mon

111            10-11    mon

111            11-12    mon

 

so that when i come to look it up it will be easier to do. i have been struggling with this for ages and cant get my head round it. i have done 1 that splits up the values using"," but i dont know how to return them all in a query.

 

any help would be much appreciated

 

Thanks

[kickstart]

Hi

 

How variable is the data? Ie, do you just have a few of these checkboxes or loads of them?

 

If not a massive number then I would be inclined to have a 2 dimensional array containing the time and day abbreviations, with the key of the first dimension being linked to the id of the checkbox.  The build up you insert by looping through the 2nd dimension of the array for the passed 1st dimension.

 

For example

$WhatToInsert['9-12 Monday'][0] = '\'9-10\',\'mon\'';

$WhatToInsert['9-12 Monday'][1] = '\'10-11\',\'mon\'';

$WhatToInsert['9-12 Monday'][2] = '\'11-12\',\'mon\'';

$WhatToInsert['9-12 Tuesday'][0] = '\'9-10\',\'tue\'';

$WhatToInsert['9-12 Tuesday'][1] = '\'10-11\',\'tue\'';

$WhatToInsert['9-12 Tuesday'][2] = '\'11-12\',\'tue\'';

 

$sql1 = "Insert Into Appointments (AppointmentId, HourFrame, Day) Values ";

$sql2 = "";

foreach ($WhatToInsert[$TickedCheckBox) as $value)

{

$sql2 .= (($sql2) ? ',' : '')."($AppointmentId,$value)";

}

$sql = $sql1.$sql2.")"

 

This could be built up from data held in tables rather than hard coded if you wanted.

 

All the best

 

Keith

[dannyone]

Thank you keith for your help! l have a good read through 2 dimensional arrays.

 

i was planning on having 10 checkboxes, two for each day.

 

ie.      checkbox  mon  9-12

          checkbox  mon  12-4

          checkbox  tue  9-12

          checkbox  tue  12-4

etc...

 

do your think this will be two many?

 

i have created this design in my database, if that would be easier to implement using the 2 dimensional arrays?

 

thanks

[kickstart]

Hi

 

It depends on how flexible you want it to be.

 

If you only have 2 time periods per day then I would be inclined to just store those basic time periods rather than breaking it down to individual hours.

 

However my assumption was that you needed to book time frames, some of which would overlap others. Eg, you might have a 9-5 Mon, and a 9-12, and a 12-5, and a 9-10, etc, so you would need to record them all (ie, I assumed you were writing something to allocate people to jobs in various time periods in the day).

 

It might be best to explain fully exactly what you are trying to do with the system and then someone might have an alternative idea.

 

All the best

 

Keith

[rs_nayr]

is it possible to have a html checkbox that when selected stores in a database on multiple rows with 3 values. this is what i need to do,

i need to create 1 checkbox

 

checkbox  9-12 Monday

 

then when the user select it, to store multiples values in my database, for example

 

Markerid,  Time,    day

111            9-10    mon

111            10-11    mon

111            11-12    mon

 

so that when i come to look it up it will be easier to do. i have been struggling with this for ages and cant get my head round it. i have done 1 that splits up the values using"," but i dont know how to return them all in a query.

 

any help would be much appreciated

 

Thanks

i think you want to do is to insert multiple records,

if one checkbox is selected,, you want to insert this

records

111        9-10    mon

to this respective fields - Markerid,  Time,    day

if more than one checkbox is checked,, you want to insert this

set of values

1. (111            9-10    mon)

2. (111            10-11    mon)

3. (111            11-12    mon)

to the fields - markerid,  Time,  day

 

if this is what you mean

put your sql insert query on a for loop

 

 

[dannyone]

thank you both for your replies.

i am trying to create a system that compares 3 users timetables and suggests available timeslots. the 1st user has a set timetable which i have done. but the 2nd and 3rd user must be able to say when they are free.

i was trying to group times together to that there would be less checkboxes on the page.

either 9-12 or 12-4, monday to friday. there is no flexibility in times, i just want them to insert in hour slots so that i can compare them with other hour slots to see which times are free during the day for 3 users.

so when the user selects the 9-12 monday it would insert multiple records into my database

 

Markerid,  Time,    day

111            9-10    mon

111            10-11    mon

111            11-12    mon

 

and if the user selects 9-12 monday and 12-4 friday

 

Markerid,  Time,    day

111            9-10    mon

111            10-11    mon

111            11-12    mon

111            12-1  fri

111            1-2    fri

111            2-3    fri

111          3-4    fri

 

is 2 dimensional arrays the best way?

 

Thanks

[kickstart]

Hi

 

A 2 dimensional array just provides a convenient way to store the various 1 hour time slots for each of the longer time slots. The example code I gave you above should provide the basis of it. You need to declare the array and decode the input fields to decide on the value of $TickedCheckBox (the example assumes a single value for this, but you could easily loop through several selected check boxes).

 

In a bit more detail, to set up the insert statement to add all the individual records. This assumes that the check boxes on screen are named "912Monday", "124Monday", "912Tuesday", etc, and that the markerid is numeric and stored in $Markerid.

 

$WhatToInsert = array();
$WhatToInsert['912Monday'] = array();
$WhatToInsert['912Monday'][0] = '\'9-10\',\'mon\'';
$WhatToInsert['912Monday'][1] = '\'10-11\',\'mon\'';
$WhatToInsert['912Monday'][2] = '\'11-12\',\'mon\'';
$WhatToInsert['912Tuesday'] = array();
$WhatToInsert['912Tuesday'][0] = '\'9-10\',\'tue\'';
$WhatToInsert['912Tuesday'][1] = '\'10-11\',\'tue\'';
$WhatToInsert['912Tuesday'][2] = '\'11-12\',\'tue\'';
$WhatToInsert['912Wednesday'] = array();
$WhatToInsert['912Wednesday'][0] = '\'9-10\',\'wed\'';
$WhatToInsert['912Wednesday'][1] = '\'10-11\',\'wed\'';
$WhatToInsert['912Wednesday'][2] = '\'11-12\',\'wed\'';
$WhatToInsert['912Thursday'] = array();
$WhatToInsert['912Thursday'][0] = '\'9-10\',\'thu\'';
$WhatToInsert['912Thursday'][1] = '\'10-11\',\'thu\'';
$WhatToInsert['912Thursday'][2] = '\'11-12\',\'thu\'';
$WhatToInsert['912Friday'] = array();
$WhatToInsert['912Friday'][0] = '\'9-10\',\'fri\'';
$WhatToInsert['912Friday'][1] = '\'10-11\',\'fri\'';
$WhatToInsert['912Friday'][2] = '\'11-12\',\'fri\'';
$WhatToInsert['124Monday'] = array();
$WhatToInsert['124Monday'][0] = '\'9-10\',\'mon\'';
$WhatToInsert['124Monday'][1] = '\'10-11\',\'mon\'';
$WhatToInsert['124Monday'][2] = '\'11-12\',\'mon\'';
$WhatToInsert['124Tuesday'] = array();
$WhatToInsert['124Tuesday'][0] = '\'9-10\',\'tue\'';
$WhatToInsert['124Tuesday'][1] = '\'10-11\',\'tue\'';
$WhatToInsert['124Tuesday'][2] = '\'11-12\',\'tue\'';
$WhatToInsert['124Wednesday'] = array();
$WhatToInsert['124Wednesday'][0] = '\'9-10\',\'wed\'';
$WhatToInsert['124Wednesday'][1] = '\'10-11\',\'wed\'';
$WhatToInsert['124Wednesday'][2] = '\'11-12\',\'wed\'';
$WhatToInsert['124Thursday'] = array();
$WhatToInsert['124Thursday'][0] = '\'9-10\',\'thu\'';
$WhatToInsert['124Thursday'][1] = '\'10-11\',\'thu\'';
$WhatToInsert['124Thursday'][2] = '\'11-12\',\'thu\'';
$WhatToInsert['124Friday'] = array();
$WhatToInsert['124Friday'][0] = '\'9-10\',\'fri\'';
$WhatToInsert['124Friday'][1] = '\'10-11\',\'fri\'';
$WhatToInsert['124Friday'][2] = '\'11-12\',\'fri\'';


$sql1 = "Insert Into Appointments (Markerid, Time, day) Values ";
$sql2 = "";
foreach ($WhatToInsert as $key => $value)
{
  if (isset($_REQUEST[$key])
  {
    foreach ($WhatToInsert[$key) as $value2)
    {
        $sql2 .= (($sql2) ? ',' : '')."($Markerid,$value2)";
    }
  }
}
$sql = $sql1.$sql2.")";

 

Hope that gives you a few ideas.

 

All the best

 

Keith

[dannyone]

Thanks so much for your help keith, i was trying to read more about 2 dimensional arrays so i could understand your answer better.

i have had a go at changing the code to suit mine and create the checkboxs. the html side of the page works but as soon as i insert the arrays code nothing appears on my page. can you advise me where im going wrong, sorry for been such a noob!

 

<?php
require "auth.php";
require "config1.php";
?>
<h3>Welcome <?php echo $_SESSION['SESS_FIRST_NAME'], " ".$_SESSION['SESS_LAST_NAME'];?></h3>

please select your times were you are available

<html>
<head>
<title>My Page</title>
</head>
<body>
<form method="post" action="<?php echo $PHP_SELF;?>">
<div align="left"><br>
9-12 Monday<input type="checkbox" name="912Monday[]" value="$WhatToInsert"><br>
12-4 Monday<input type="checkbox" name="124Monday[]" value="$WhatToInsert"><br>
9-12 Tuesday<input type="checkbox" name="912Tuesday[]" value="$WhatToInsert"><br>
12-4 Tuesday<input type="checkbox" name="124Tuesday[]" value="$WhatToInsert"><br>
9-12 Wednesday<input type="checkbox" name="912Wednesday[]" value="$WhatToInsert"><br>
12-4 Wednesday<input type="checkbox" name="124Wednesday[]" value="$WhatToInsert"><br>
9-12 Thursday<input type="checkbox" name="912Thursday[]" value="$WhatToInsert"><br>
12-4 Thursday<input type="checkbox" name="124Thursday[]" value="$WhatToInsert"><br>
9-12 Friday<input type="checkbox" name="912Friday[]" value="$WhatToInsert"><br>
12-4 Friday<input type="checkbox" name="124Friday[]" value="$WhatToInsert"><br>
<input type="submit" value="submit" name="submit">
</form>
<br>
</div>
</form>
</body>
</html>

<?php

$WhatToInsert = array();
$WhatToInsert['912Monday'] = array();
$WhatToInsert['912Monday'][0] = '\'9-10\',\'mon\'';
$WhatToInsert['912Monday'][1] = '\'10-11\',\'mon\'';
$WhatToInsert['912Monday'][2] = '\'11-12\',\'mon\'';
$WhatToInsert['912Tuesday'] = array();
$WhatToInsert['912Tuesday'][0] = '\'9-10\',\'tue\'';
$WhatToInsert['912Tuesday'][1] = '\'10-11\',\'tue\'';
$WhatToInsert['912Tuesday'][2] = '\'11-12\',\'tue\'';
$WhatToInsert['912Wednesday'] = array();
$WhatToInsert['912Wednesday'][0] = '\'9-10\',\'wed\'';
$WhatToInsert['912Wednesday'][1] = '\'10-11\',\'wed\'';
$WhatToInsert['912Wednesday'][2] = '\'11-12\',\'wed\'';
$WhatToInsert['912Thursday'] = array();
$WhatToInsert['912Thursday'][0] = '\'9-10\',\'thu\'';
$WhatToInsert['912Thursday'][1] = '\'10-11\',\'thu\'';
$WhatToInsert['912Thursday'][2] = '\'11-12\',\'thu\'';
$WhatToInsert['912Friday'] = array();
$WhatToInsert['912Friday'][0] = '\'9-10\',\'fri\'';
$WhatToInsert['912Friday'][1] = '\'10-11\',\'fri\'';
$WhatToInsert['912Friday'][2] = '\'11-12\',\'fri\'';
$WhatToInsert['124Monday'] = array();
$WhatToInsert['124Monday'][0] = '\'12-1\',\'mon\'';
$WhatToInsert['124Monday'][1] = '\'1-2\',\'mon\'';
$WhatToInsert['124Monday'][2] = '\'2-3\',\'mon\'';
$WhatToInsert['124Monday'][3] = '\'3-4\',\'mon\'';
$WhatToInsert['124Tuesday'] = array();
$WhatToInsert['124Tuesday'][0] = '\'12-1\',\'tue\'';
$WhatToInsert['124Tuesday'][1] = '\'1-2\',\'tue\'';
$WhatToInsert['124Tuesday'][2] = '\'2-3\',\'tue\'';
$WhatToInsert['124Tuesday'][3] = '\'3-4\',\'tue\'';
$WhatToInsert['124Wednesday'] = array();
$WhatToInsert['124Wednesday'][0] = '\'12-1\',\'wed\'';
$WhatToInsert['124Wednesday'][1] = '\'1-2\',\'wed\'';
$WhatToInsert['124Wednesday'][2] = '\'2-3\',\'wed\'';
$WhatToInsert['124Wednesday'][3] = '\'3-4\',\'wed\'';
$WhatToInsert['124Thursday'] = array();
$WhatToInsert['124Thursday'][0] = '\'12-1\',\'thu\'';
$WhatToInsert['124Thursday'][1] = '\'1-2\',\'thu\'';
$WhatToInsert['124Thursday'][2] = '\'2-3\',\'thu\'';
$WhatToInsert['124Thursday'][3] = '\'3-4\',\'thu\'';
$WhatToInsert['124Friday'] = array();
$WhatToInsert['124Friday'][0] = '\'12-1\',\'fri\'';
$WhatToInsert['124Friday'][1] = '\'1-2\',\'fri\'';
$WhatToInsert['124Friday'][2] = '\'2-3\',\'fri\'';
$WhatToInsert['124Friday'][3] = '\'3-4\',\'fri\'';


$sql1 = "Insert Into test2 (Marker_ID, Timeslot, Days) Values ";
$sql2 = "";
foreach ($WhatToInsert as $key => $value)
{
  if (isset($_REQUEST[$key])
  {
    foreach ($WhatToInsert[$key]) as $value2)
    {
        $sql2 .= (($sql2) ? ',' : '')."('" .$_SESSION['login'] . "',$value2)";
    }
  }
}
$sql = $sql1.$sql2.")";
?>

 

Thanks again

[kickstart]

Hi

 

Had a bit more of a play and tried test running it this time.

 

<?php
require "auth.php";
require "config1.php";
?>
<h3>Welcome <?php echo $_SESSION['SESS_FIRST_NAME'], " ".$_SESSION['SESS_LAST_NAME'];?></h3>

please select your times were you are available

<html>
<head>
<title>My Page</title>
</head>
<body>
<form method="post" action="<?php echo $PHP_SELF;?>">
<div align="left"><br>
9-12 Monday<input type="checkbox" name="912Monday" value="Whatever"><br>
12-4 Monday<input type="checkbox" name="124Monday" value="Whatever"><br>
9-12 Tuesday<input type="checkbox" name="912Tuesday" value="Whatever"><br>
12-4 Tuesday<input type="checkbox" name="124Tuesday" value="Whatever"><br>
9-12 Wednesday<input type="checkbox" name="912Wednesday" value="Whatever"><br>
12-4 Wednesday<input type="checkbox" name="124Wednesday" value="Whatever"><br>
9-12 Thursday<input type="checkbox" name="912Thursday" value="Whatever"><br>
12-4 Thursday<input type="checkbox" name="124Thursday" value="Whatever"><br>
9-12 Friday<input type="checkbox" name="912Friday" value="Whatever"><br>
12-4 Friday<input type="checkbox" name="124Friday" value="Whatever"><br>
<input type="submit" value="submit" name="submit">
</form>
<br>
</div>
</form>
</body>
</html>

<?php

$WhatToInsert = array();
$WhatToInsert['912Monday'] = array('\'9-10\',\'mon\'','\'10-11\',\'mon\'','\'11-12\',\'mon\'');
$WhatToInsert['912Tuesday'] = array('\'9-10\',\'tue\'','\'10-11\',\'tue\'','\'11-12\',\'tue\'');
$WhatToInsert['912Wednesday'] = array('\'9-10\',\'wed\'','\'10-11\',\'wed\'','\'11-12\',\'wed\'');
$WhatToInsert['912Thursday'] = array('\'9-10\',\'thu\'','\'10-11\',\'thu\'','\'11-12\',\'thu\'');
$WhatToInsert['912Friday'] = array('\'9-10\',\'fri\'','\'10-11\',\'fri\'','\'11-12\',\'fri\'');
$WhatToInsert['124Monday'] = array('\'12-1\',\'mon\'','\'1-2\',\'mon\'','\'2-3\',\'mon\'','\'3-4\',\'mon\'');
$WhatToInsert['124Tuesday'] = array('\'12-1\',\'tue\'','\'1-2\',\'tue\'','\'2-3\',\'tue\'','\'3-4\',\'tue\'');
$WhatToInsert['124Wednesday'] = array('\'12-1\',\'wed\'','\'1-2\',\'wed\'','\'2-3\',\'wed\'','\'3-4\',\'wed\'');
$WhatToInsert['124Thursday'] = array('\'12-1\',\'thu\'','\'1-2\',\'thu\'','\'2-3\',\'thu\'','\'3-4\',\'thu\'');
$WhatToInsert['124Friday'] = array('\'12-1\',\'fri\'','\'1-2\',\'fri\'','\'2-3\',\'fri\'','\'3-4\',\'fri\'');


$sql1 = "Insert Into test2 (Marker_ID, Timeslot, Days) Values ";
$sql2 = "";
foreach ($WhatToInsert as $key => $value)
{
  if (isset($_REQUEST[$key]))
  {
    foreach ($WhatToInsert[$key] as $value2)
    {
        $sql2 .= (($sql2) ? ',(' : '(')."'" .$_SESSION['login'] . "',$value2)";
    }
  }
}
$sql = (($sql2) ? $sql1.$sql2 : "");
echo $sql;
?>

 

This should work, but you will need to change the echo of the $sql  string to insteadconnect to the database and execute it.

 

All the best

 

Keith

[dannyone]

Thanks alot keith for your help and time.. i have been trying to get the code you posted to work but i just don't know what im doing.

where is the best place to read about these types of methods?

what did you mean about

" $sql  string to insteadconnect to the database and execute it."

i tried googling insteadconnect mysql, but it didnt help

 

sorry to seem thick im just poor at mysql php

 

thanks again

 

 

[kickstart]

Hi

 

Sorry, what I meant was that all I have done is generated the SQL that you need to run. I have just displayed this SQL.

 

What you need to do is to connect to the database and then execute that SQL.

 

Something like this at the end:-

 

$conn = mysql_connect("YOUR DATABASE HOST NAME", "YOUR DATABASE USERID", "YOUR DATABASE PASSWORD") or die ('Error connecting to mysql database!');
mysql_select_db("THE NAME OF YOUR DATABASE");
$query=mysql_query($sql);

 

Put that in the script, replacing the "echo $sql" at the bottom, and change the bits in capitals to the appropriate ones for your database.

 

All the best

 

Keith

[dannyone]

thanks again keith!

i have been messing about with the code u gave me, coz it was inserting double times and no days at first, but now i have it working!

 

seriously, thank u so much, i have been stuck on that for ages! ur a legend!

 

thanks again

 

[dannyone]

i have another quick question, i have been using the code and all is great.

however is there a way to stop duplicate arrays been entered for the user? or a way to limit the time a user can submit there times? its just if the user submits times then comes back and does it again it just creates new records so there are duplicates in the database. im not sure if this can be done using phpmyadmin or it is a function in php. i have used update else insert before but not using arrays.

can anyone help?

 

thanks again

[kickstart]

Hi

 

I think that would be down to the design.

 

Probably the best thing to do would be for each user to have an id (you may already have that in your database design) and to record that on the table with the various time periods. That way when they come back and try and reenter it you can either bring the details back, refuse to allow them to reenter the details, or to just delete the old records before you insert the new ones.

 

All the best

 

Keith

[dannyone]

ok thanks again keith il try and sort it out

 

thanks