I'm currently working on a new feature for a website. Basically, we use a calendar system, and i'm trying to create a method to "one-click signup" to every event.
To make things simple, the table is called "table_name", and the field i want to work with will be "field_name".
I've got most of the code and theory down. However, the crucial part, to avoid simply creating loads of database fields and thus putting loads of strain on the server, is detecting the current minimum (SELECT MIN) and the current maximum (SELECT MAX) from field_name.
Here's the 2 queries i'd be wanting to run (it's extracted from PHP, so forgive any syntax errors);
SELECT MAX(field_name) FROM table_name WHERE field_name > '115'
SELECT MIN(field_name) FROM table_name WHERE field_name > '115'
What i want to do is for every value between 115 and the maximum, i want it to run a specific query. Basically, i guess i want to assign a variable to field_name so that i can post this value to the database along with a bunch of other values that will be going to other fields in the same table of the same DB.
It's hard to explain what i want to achieve without being able to pop out my brain and show you what i'm thinking. I've only started actually working with SQL and PHP at any sort of decent level a few days ago.
Here's the first few lines of the sort of code i'd be using (table names and most field names edited for obvious reasons), which has been tested and works, but might also help explain my situation;
$sql="INSERT INTO table_name (field_name,field_2,field_3,field_4,field_5,field_6,field_7,timestamp,field_9)
This would go on right up until a field_name was a really high number, but the problem is that going above the current max in "field_name" may cause errors at user level (and then they'd complain i broke it, etc). Additionally, it's a lot of copy-pasting of lines, as well as a lot of queries to run, which causes unnecessary server load. It's also not completely futureproof because eventually, we'll get to a field_name value of 1001, and i'd have to update the code yet again for the next 1000.
I really just want it to find out the max value in field_name, assign a variable i can paste into the query above (e.g. "(VAR,'1','0','0','1','2','','1274920279','4')", where "VAR" would represent the variable), and then run that query potentially saving myself a lot of time, and moreover making it cleaner code.
I've no problem making it work for a single ID in "field_name" as it can be manually written without working on a procedure for the variable, but making it a variable within a query is something i'm not so sure about.
As there's multiple users using this system, i need to actually develop this before i can push it out to a more live environment, and as such, i can adapt the code to work with other fields such as the one representing the user ID (in this case, field_name isn't the user ID, it's the event ID).
I'm confident what i'm after can be done with around 10 lines including the query execution itself.
As said earlier, i'm fairly new to PHP and SQL, but i've got numerous years in Website Design itself (working with Joomla! CMS, and SMF Forum currently), as well as more experience in Desktop Application development (and around 8 or so years of computer tech/support/system building and optimisation). I feel that learning "the backbone of the web" so to speak would be beneficial to my skillset, and may help me in progressing my career.
Sorry for the lengthy post.
Any ideas on how i can achieve what i'm after?