Resend my activation email : Register : Log in 
BCF: Bike Chat Forums


Using php to insert multiple rows into a MySQL database?

Reply to topic
Bike Chat Forums Index -> The Geek Zone Goto page 1, 2  Next
View previous topic : View next topic  
Author Message

5v3d3b0
World Chat Champion



Joined: 24 Sep 2006
Karma :

PostPosted: 19:21 - 09 Feb 2013    Post subject: Using php to insert multiple rows into a MySQL database? Reply with quote

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 Confused

Any help would be much appreciated!
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :

PostPosted: 19:48 - 09 Feb 2013    Post subject: Reply with quote

Hi

You can use a single SQL statement.

Something like this:-

Code:

$WeekArray = array();
foreach($_REQUEST['week'] AS $WeekSelected)
{
$WeekArray[] = "(NULL, $WeekSelected, $BookingNumber)";
}
$sql = "INSERT INTO SomeTable (Id, WeekSelected, BookingNumber) VALUES".implode(',', $WeekArray);


Multiple rows inserted with a single SQL statement which is easily built up.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

5v3d3b0
World Chat Champion



Joined: 24 Sep 2006
Karma :

PostPosted: 20:00 - 09 Feb 2013    Post subject: Reply with quote

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 Laughing
But I kind of get what you're saying, just not exactly how it works
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :

PostPosted: 20:27 - 09 Feb 2013    Post subject: Reply with quote

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

Code:

$SomeArray = array('The', 'cat', 'sat', 'on', 'the', 'mat');
echo implode(' - ', $SomeArray);


would put out The - cat - sat - on - the - mat

Using this you can lump together all the inserts (separated by a ,) without bothering to sort out whether it is the first element and whether to use a comma.

You could do it just by concatenating the strings, which is a bit less readable.

Code:

$WeekString = '';
foreach($_REQUEST['week'] AS $WeekSelected)
{
$WeekString .= (($WeekString == '') ? '' : ',')."(NULL, $WeekSelected, $BookingNumber)";
}
$sql = "INSERT INTO SomeTable (Id, WeekSelected, BookingNumber) VALUES".$WeekString;


All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

Frost
World Chat Champion



Joined: 26 May 2004
Karma :

PostPosted: 20:29 - 09 Feb 2013    Post subject: Reply with quote

Kickstart wrote:
Hi

You can use a single SQL statement.

All the best

Keith


That's your answer for EVERYTHING. Laughing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :

PostPosted: 20:35 - 09 Feb 2013    Post subject: Reply with quote

Frost wrote:

That's your answer for EVERYTHING. Laughing


Not far off.

Wait until I land up giving him a PHP stored procedure to search a field containing a php serialized array Very Happy (admittedly written to make a point of how much of a silly idea it was to store serialized arrays in a table).

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

Dalemac
World Chat Champion



Joined: 15 Oct 2006
Karma :

PostPosted: 20:36 - 09 Feb 2013    Post subject: Reply with quote

Don't forget to sanitise your inputs before inserting into the database, and escaping them again before outputting the the browser.
____________________
YBR125 -> GPZ500S -> SL1000
 Back to top
View user's profile Send private message You must be logged in to rate posts

5v3d3b0
World Chat Champion



Joined: 24 Sep 2006
Karma :

PostPosted: 21:01 - 09 Feb 2013    Post subject: Reply with quote

NICE, it's working well! I still need to actually incorporate it into the form I'm using, but that can wait until tomorrow Very Happy
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

oldpink
World Chat Champion



Joined: 02 Aug 2006
Karma :

PostPosted: 21:46 - 09 Feb 2013    Post subject: Reply with quote

Kickstart wrote:
Frost wrote:

That's your answer for EVERYTHING. Laughing


Not far off.


I like the KISS approach to coding anything nice & neat and one step at a time as I find it far easier to debug
multiple changes / entry's means more scope for error Thumbs Up
____________________
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
 Back to top
View user's profile Send private message Visit poster's website You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :

PostPosted: 22:01 - 09 Feb 2013    Post subject: Reply with quote

oldpink wrote:

I like the KISS approach to coding anything nice & neat and one step at a time as I find it far easier to debug
multiple changes / entry's means more scope for error Thumbs Up


Largely agree, but doing a load of inserts in one statement is just a simple step and also massively faster.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

oldpink
World Chat Champion



Joined: 02 Aug 2006
Karma :

PostPosted: 22:09 - 09 Feb 2013    Post subject: Reply with quote

Kickstart wrote:


Largely agree, but doing a load of inserts in one statement is just a simple step and also massively faster.

All the best

Keith


not when your dyslexic like me Laughing
though I do use multiple inserts when it suits the task I am doing
esp in the early stages of coding a site
later on when I get into the finer points I tend to go step by step to make it easier to spot issues
and allow me to take into account other parameters I may need to add
____________________
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
 Back to top
View user's profile Send private message Visit poster's website You must be logged in to rate posts

Dalemac
World Chat Champion



Joined: 15 Oct 2006
Karma :

PostPosted: 22:42 - 09 Feb 2013    Post subject: Reply with quote

oldpink wrote:
Kickstart wrote:


Largely agree, but doing a load of inserts in one statement is just a simple step and also massively faster.

All the best

Keith


not when your dyslexic like me Laughing
though I do use multiple inserts when it suits the task I am doing
esp in the early stages of coding a site
later on when I get into the finer points I tend to go step by step to make it easier to spot issues
and allow me to take into account other parameters I may need to add


As your skills grow you automatically start to develop your code to be as efficient as possible. When you start chasing 1000's of a second loading time, you know you are a competent developer.
____________________
YBR125 -> GPZ500S -> SL1000
 Back to top
View user's profile Send private message You must be logged in to rate posts

oldpink
World Chat Champion



Joined: 02 Aug 2006
Karma :

PostPosted: 23:07 - 09 Feb 2013    Post subject: Reply with quote

Dale_Mckeown wrote:

As your skills grow you automatically start to develop your code to be as efficient as possible. When you start chasing 1000's of a second loading time, you know you are a competent developer.


I run some pretty well developed sites with a shit lot of custom coding and have done for over 10 years
I can have 3000 + members running very complex searches on a board with 330,000+ members with 4.5 million posts
with very low load times at most times of the day

I also do the coding for two sites that sell products online
one is an OSC based site again with a lot of custom features and complex search criteria
that includes extra data fields that I included for the products
and an auction site using Geodesic auction software again that heavily custom coded to suit our needs

and as we deal in cannabis related subjects and product's we need to be 110% on top of security
at all times as your customers rely on our integrity to keep there personal info personal
____________________
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
 Back to top
View user's profile Send private message Visit poster's website You must be logged in to rate posts

Dalemac
World Chat Champion



Joined: 15 Oct 2006
Karma :

PostPosted: 23:18 - 09 Feb 2013    Post subject: Reply with quote

oldpink wrote:
Dale_Mckeown wrote:

As your skills grow you automatically start to develop your code to be as efficient as possible. When you start chasing 1000's of a second loading time, you know you are a competent developer.


I run some pretty well developed sites with a shit lot of custom coding and have done for over 10 years
I can have 3000 + members running very complex searches on a board with 330,000+ members with 4.5 million posts
with very low load times at most times of the day

I also do the coding for two sites that sell products online
one is an OSC based site again with a lot of custom features and complex search criteria
that includes extra data fields that I included for the products
and an auction site using Geodesic auction software again that heavily custom coded to suit our needs

and as we deal in cannabis related subjects and product's we need to be 110% on top of security
at all times as your customers rely on our integrity to keep there personal info personal


Sorry, I wasn't intending to mock your skills. I apologise if you took it that way. Karma I just describing my own experiences over the last 5 years.

I have done some pretty nifty stuff too. I wrote my own MVC framework, SQL PDO API, WordPress plugins, and even a link shortener.

I love solving problems Smile
____________________
YBR125 -> GPZ500S -> SL1000
 Back to top
View user's profile Send private message You must be logged in to rate posts

Frost
World Chat Champion



Joined: 26 May 2004
Karma :

PostPosted: 23:34 - 09 Feb 2013    Post subject: Reply with quote

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 Razz
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.
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

oldpink
World Chat Champion



Joined: 02 Aug 2006
Karma :

PostPosted: 23:37 - 09 Feb 2013    Post subject: Reply with quote

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 Laughing
with some unexpected results Razz 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
 Back to top
View user's profile Send private message Visit poster's website You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :

PostPosted: 10:29 - 10 Feb 2013    Post subject: Reply with quote

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
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

Dalemac
World Chat Champion



Joined: 15 Oct 2006
Karma :

PostPosted: 10:59 - 10 Feb 2013    Post subject: Reply with quote

Kickstart wrote:
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


Half the problem is clients/companies only want to pay minimal. They don't really understand performance issues, and as far as they are concerned once functionality is done development stops. No matter how many times you say something needs refactoring to improve performance, they are always hesitant to provide more development time. They then wonder why it is slow or crashes!

The way I overcome this is to just write the best code I can possibly write, even if it means providing functionality takes longer. I often find that more time will be provided if functionality is missing, but not if performance needs looking at!

Just my own experiences.

Dale
____________________
YBR125 -> GPZ500S -> SL1000
 Back to top
View user's profile Send private message You must be logged in to rate posts

5v3d3b0
World Chat Champion



Joined: 24 Sep 2006
Karma :

PostPosted: 14:19 - 10 Feb 2013    Post subject: Reply with quote

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 Confused
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :

PostPosted: 16:55 - 10 Feb 2013    Post subject: Reply with quote

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
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

5v3d3b0
World Chat Champion



Joined: 24 Sep 2006
Karma :

PostPosted: 17:11 - 10 Feb 2013    Post subject: Reply with quote

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.
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :

PostPosted: 17:40 - 10 Feb 2013    Post subject: Reply with quote

Hi

Something like this:-

Code:

$WeekArray = array();
foreach($_REQUEST['week'] AS $WeekSelected)
{
$WeekSelected = intval($WeekSelected);
If ($WeekSelected > 0)
{
if (intval($REQUEST['StartTime_'.$WeekSelected]) > 0 AND intval($REQUEST['EndTime_'.$WeekSelected]) > 0 )
{
if (intval($REQUEST['StartTime_'.$WeekSelected]) <= intval($REQUEST['EndTime_'.$WeekSelected]) > 0 )
{
for($iCnt = intval($REQUEST['StartTime_'.$WeekSelected]) ; $iCnt <= intval($REQUEST['EndTime_'.$WeekSelected]) ; $iCnt++)
{
$WeekArray[] = "(NULL, $WeekSelected, $iCnt)";
}
}
}
}
}
$sql = "INSERT INTO SomeTable (Id, WeekSelected, TimeSlot) VALUES".implode(',', $WeekArray);


Don't know what the other fields you want inserted are, but easy enough to add those.

This code is working on the basis that the value of the week selected is used as an extension for the names of the start and end time drop down lists. Also that the week selected is an integer (ie, possibly an id to a field on another table), same for the time periods.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

5v3d3b0
World Chat Champion



Joined: 24 Sep 2006
Karma :

PostPosted: 11:20 - 13 Feb 2013    Post subject: Reply with quote

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

Code:

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


$periods = array();

echo "Periods: </br>";
for ($i=$startperiod; $i<$total; $i++)
  {
  $periods[] = "$i";
  }



$WeekArray = array();

foreach($_REQUEST['week'] AS $WeekSelected)
{
$WeekArray[] = "$WeekSelected";
}


$y = count($periods);
$x = count($WeekArray);


$array1 = array();

for($xCnt=0; $xCnt<$x; $xCnt++){
   
    for($yCnt=0; $yCnt<$y; $yCnt++){
       
        $array1[] = "(NULL, $WeekArray[$xCnt],  $periods[$yCnt])";

    }   
}


Very Happy
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

Dalemac
World Chat Champion



Joined: 15 Oct 2006
Karma :

PostPosted: 12:24 - 13 Feb 2013    Post subject: Reply with quote

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

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


$periods = array();

echo "Periods: </br>";
for ($i=$startperiod; $i<$total; $i++)
{
$periods[] = "$i";
}



$WeekArray = array();

foreach($_REQUEST['week'] AS $WeekSelected)
{
$WeekArray[] = "$WeekSelected";
}


$y = count($periods);
$x = count($WeekArray);


$array1 = array();

for($xCnt=0; $xCnt<$x; $xCnt++){

for($yCnt=0; $yCnt<$y; $yCnt++){

$array1[] = "(NULL, $WeekArray[$xCnt], $periods[$yCnt])";

}
}

Very Happy


You really shouldn't have to go into O(n^2) complexity for that (Nested for loop)

Actually, i'm a bit baffled as to what you are trying to do anyway. This looks to me like one of those rare occurrences that code somehow works but baffles others as to how it actually does it's stuff.

If you could provide more information around what inputs there are, and what the output of array1 should look like, i will probably be able to clean this up for you.

Dale
____________________
YBR125 -> GPZ500S -> SL1000
 Back to top
View user's profile Send private message You must be logged in to rate posts

5v3d3b0
World Chat Champion



Joined: 24 Sep 2006
Karma :

PostPosted: 14:37 - 13 Feb 2013    Post subject: Reply with quote

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?
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts
Old Thread Alert!

The last post was made 12 years, 119 days ago. Instead of replying here, would creating a new thread be more useful?
  Display posts from previous:   
This page may contain affiliate links, which means we may earn a small commission if a visitor clicks through and makes a purchase. By clicking on an affiliate link, you accept that third-party cookies will be set.

Post new topic   Reply to topic    Bike Chat Forums Index -> The Geek Zone All times are GMT
Goto page 1, 2  Next
Page 1 of 2

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum

Read the Terms of Use! - Powered by phpBB © phpBB Group
 

Debug Mode: ON - Server: birks (www) - Page Generation Time: 0.27 Sec - Server Load: 1.37 - MySQL Queries: 16 - Page Size: 153.61 Kb