Using php to insert multiple rows into a MySQL database?

[5v3d3b0]

I've got a form for booking rooms on a university campus where a lecturer would input data to put a request into a database. These requests would then be checked by an administrator to potentially book that room for the lecturer.
Part of the form is what week(s) would you like the room for? It has 16 checkboxes: 1-15 and an "all" button which selects weeks 1-12 (the normal length of a semester)
So maybe they would make a request for room X, 9-10am, weeks 1,2,3,5,8,9 or something like that.
In my request table I don't want "1,2,3,5,8,9" to come up because it will be difficult to translate this into an actual booking at some point.
What I really want is for a separate row to be created for every week selected, so if they select 6 different weeks, the database will have 6 records with only the week changing.
I'm not very experienced so I'm not sure how to do it..
The only 2 ways I can think of: Either make the value for each checkbox an entire sql statement and build it that way somehow, which seems like a very messy and probably not secure way. Or use some sort of loop where I loop through the weeks, but I wouldn't really know where to start with that

Any help would be much appreciated!

[Kickstart]

Hi

You can use a single SQL statement.

Something like this:-

[5v3d3b0]

Ok, would I have the names of the week checkboxes as "week[]", with values 1-15 then? Rather than the "week_1", "week_2" etc that I have currently.

And how does that array work? (sorry for noobness)
I assume the NULL value is for where the request_id will be (auto increment), then the number of the week selected, and then what is the bookingnumber? An autoincrement value or a fixed value?
Not too sure about the impode command either
But I kind of get what you're saying, just not exactly how it works

[Kickstart]

Hi

You can have an array of input fields on a form. Just have an array of check boxes on the form, and give them values which you want to use.

NULL is for the auto increment field. Not really necessary but something I like to use.

Booking number was just an example field. Not sure what other fields you want to have on that table.

Implode takes an array and returns a string consisting of the array elements put together, separated with the string in the first argument.

So

[Frost]

[Kickstart]

[Dalemac]

Don't forget to sanitise your inputs before inserting into the database, and escaping them again before outputting the the browser.
____________________
YBR125 -> GPZ500S -> SL1000

[5v3d3b0]

NICE, it's working well! I still need to actually incorporate it into the form I'm using, but that can wait until tomorrow

[oldpink]

[Kickstart]

[oldpink]

[Dalemac]

[oldpink]

[Dalemac]

[Frost]

Efficiency is the bane is nice code.

I've written a thousand lines of tidy readable code and had it execute in 2ms. Sure if i'd hunted for efficiency right from the start it might have executed in 1ms. But it's going to have to be executed a whole fuck ton of times before those 1ms add up to equalling the development time required to figure out some messy code. If the script is going to get run that much it's probably going to end up on some faster hardware at some point which totally negates the problem anyway
Premature optimisation has lead to some disgusting code, some of which actually becomes inefficient as people aren't able to pick and choose what bits to use but instead are faced with the choice of taking the whole lot, or rewriting it.
Code it how you'd want to read it, run it and see how it performs. If it runs okay, perfect. If it's slow analyse it and find the slow bit (often an SQL query), then optimise that bit leaving the rest readable.

[oldpink]

no offence taken

computers have been part of my life since I was 16 (1980)
now they are my way of life and how I make a living for the most part
being self taught and dyslexic makes for some interesting coding sometimes
with some unexpected results but I get there in the end

I've recently started playing around with HTML5 and finding it fun after spending years ignoring it
and spending too much time on PHP so I decided to expand into an area I hadn't looked at since my days of debugging Adobe in 1999
and I'm surprised how easily I'm picking it up
and how I can embed custom PHP when I need it or to convert the PHP to HTML if needed
____________________
I have become comfortably numb

Theory & hazard 24-may 2016, CBT 8th June 2016, MOD 1 2nd Aug 2016 Mod 2 2nd-Nov 2016 - Current bike CBR 600 RR

[Kickstart]

Hi

Afraid ignoring efficiency until the last moment lands up giving major problems. After all, being forced to split things up over multiple machines leads to even harder maintenance.

Slow queries can cause big problems, but multiple fairly slow queries in a loop are likely even worse. At work been going over the slow query log. Knocked about 4 hours per day of slow queries from that so far (and that is despite rewriting a product searcher so that it is far more powerful and will cope with spelling mistakes).

Unfortunately performance these days seems to be a dirty word.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing

[Dalemac]

[5v3d3b0]

I've come into another little problem: The lecturer can select a duration for the lecture. I've created a loop which saves the corresponding periods into an array:

$startperiod = $_POST['time_from'];
$duration = $_POST['duration'];
$total = $startperiod + $duration;

$periods = array();

for ($i=$startperiod; $i<$total; $i++)
{
$periods[] = "($i)";
}


This is fine, and the previous bit is fine, but I now need this to happen: For example, if you choose weeks 1,2,3,4, at 9am for 3 hours, the corresponding data is:

week 1 timeslot 1
week 1 timeslot 2
week 1 timeslot 3
week 2 timeslot 1
week 2 timeslot 2 etc etc etc

I was trying to go along the lines of making a third array to store data in the form: (NULL, $week, $timeslot) but I don't know how to incorporate data from 2 arrays

[Kickstart]

Hi

How do you intend to input these fields on the form?

Using arrays gets to be a bit more difficult when you want an input form which is effectively arrays of arrays.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing

[5v3d3b0]

The weeks are checkboxes, the starting time and duration are drop down boxes.
Starting times have a value from 1-9 so if they choose 9am it's effectively period 1.
Duration is the same value as the dropdown, which is 1-4
Since I have the loop, my periods array holds all the corresponding periods, and the week one has the weeks.

So I need an array that loops through the periods array for every week in the week array.

[Kickstart]

Hi

Something like this:-

[5v3d3b0]

I couldn't get that bit of code to work, not sure why. But I came up with my own solution:

[Dalemac]

[5v3d3b0]

Ok so I'm taking 2 variables $TimeFrom, and $duration from user inputs where they select what time a lecture starts, and how long it is.
I'm then making an array $PeriodsArray to store all the corresponding periods. eg. 10am with a duration of 2 = periods 2 and 3.

The $WeekArray stores what weeks are selected by the user via checkboxes which all have then name "week[]". There are a total of 15 but any combination of those can be stored.

$x and $y are the number of entries in the week and periods arrays.

I then make a third array $AllPeriodsArray which will store all of the combinations of weeks and periods. So if I have weeks 2 3 and 5, periods 1 and 2, I want:
week 2 period 1
week 2 period 2
week 3 period 1
week 3 period 2 etc etc
And in the database I'll input a separate entry for every one of these combinations.

I get it by looping through all the periods selected, for every week selected.

And the output is in the format NULL (because I have an autoincremented ID field in my db, week, period, and then a load of other variables which I haven't talked about here.

Does that make sense? And am I over complicating a simple problem?