CODEWITHSUNDEEP
phpsql
Thavia Farias
Thavia Farias

Reputation: 261

How to increment a variable and insert sql php?

this is my first time programming and I need a little help on this one. I have a code that I get the $year from a form and i want to increment it until an end year and insert the result in the data base:

include 'conect.php';

$user=  $_POST['user'];
$school= $_POST['school'];
$year= $_POST['year'];
$endyear= $_POST['endyear'];

The insertion into the database should repeat those variables until $endyear, something like this:

ROW 1:
user: John
school: UFPS
year: 2010 

ROW 2:
user: John
school: UFPS
year: 2011

ROW 3:
user: John
school: UFPS
year: 2012

What if I have to increment more then one Variable? for example: 

$user= $_POST['user']; 
$school= $_POST['school']; 
$year= $_POST['year']; 
$endyear= $_POST['endyear']; 
$day= $_POST['startday']; 
$endday = $_POST['endday'];

And increment until an $endyear and also endday?

result: ROW 1: user: John school: UFPS year: 2010 day:1 ROW 2: user: John school: UFPS year: 2011 day:2 ROW 3: user: John school: UFPS year: 2012 day:3

Upvotes: 1

Views: 524

Answers (3)

Jack M.
Jack M.

Reputation: 1170

The first thing you want is a decent PHP tool to connect to your database, like PDO or an addon like ADOdb. Assuming you already have built your table structure, using PDO, it would go like this:

for( $i = intval($year); $i <= intval($endyear); $i++ )
{
   $sql = "INSERT INTO ".DATABASE_TABLE_NAME_CONSTANT." (user, school, year) VALUES (?,?,?)";
   $q = $conn->prepare($sql);
   $q->execute(array($user,$school,$i));
}

More info on PDO here and here

You should also get into the habit of keeping all your table names in constants. The reason is that some hosting services limit you to 1 database. Keeping it in constants would allow you to run a sandbox version of your project alongside a production version in the same database. All you need is a different prefix, like in the sandbox, you prefix tablenames with sb_ and in production, with pr_. For example:

define("USERS_TABLE", $config["tbl_prefix"]."users");

Upvotes: 0

Revent
Revent

Reputation: 2109

Assuming you have sanitized your POST data against SQL injection attacks, you can use a loop:

for( $y = $year; $y <= $endyear; $y++ )
{
   $query = "INSERT INTO [yourtablename] (user, school, year) VALUES ('$user', '$school', $y)";
   // execute query
}

You could also potentially build and extended query in the loop and execute it once afterwards.

$query = "INSERT INTO [yourtablename] (user, school, year)";
for( $y = $year; $y <= $endyear; $y++ )
{
   $query .= "VALUES ('$user', '$school', $y)".($y < $endyear ? ',': '');
}
// execute query

Upvotes: 3

woofmeow
woofmeow

Reputation: 2408

You can use auto_increment (on the database .. I don't know what you are using) and set a default value to the starting year.

Upvotes: -1

Related Questions