PHP Adding new records

[jakehall109]

Hi im basically making a simple site/database to manage our backups at work.

I need to be able to add a months worth of backups (20 or so tapes) into the same 'submit' so they all add at the same time.

I have 3 forms

Month, tape and location.
The month and location will stay the same for the months backups but the tape number will change.
How do i easily have the option to add multiple numbers into a form but keep the month/location the same (I hope im explaining it correct)
This is my first attempt at something like this

My Forms

<form action="insert.php" method="post">
<p style="text-align: center;">Month: <input type="text" name="Month" /></p>
<p style="text-align: center;">Tape: <input type="text" name="Tape" /></p>
<p style="text-align: center;">Location: <input type="text" name="Location" /></p>
<p style="text-align: center;"><input type="submit" value="Submit Query" /></p>

</form>

Insert.php

<?php
$con = mysql_connect("localhost","    ","    ");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("backups", $con);

$sql="INSERT INTO `2007` (Month, Tape, Location)
VALUES
('$_POST[Month]','$_POST[Tape]','$_POST[Location]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Record added";


mysql_close($con);
?>

Any helpful pointers would be great!

 

[Linkex]

Here is the quick and dirty method.

<form action="insert.php" method="post">
<p style="text-align: center;">Month: <input type="text" name="Month" /></p>
<p style="text-align: center;">Tape numbers: <input type="text" name="Tape1" /><input type="text" name="Tape2" /><input type="text" name="Tape3" /></p>
<p style="text-align: center;">Location: <input type="text" name="Location" /></p>
<p style="text-align: center;"><input type="submit" value="Submit Query" /></p>

</form>

<?php
$con = mysql_connect("localhost","    ","    ");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("backups", $con);

$sql="INSERT INTO `2007` (Month, Tape, Location)
VALUES
('$_POST[Month]','$_POST[Tape1]','$_POST[Location]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Record added";

$sql="INSERT INTO `2007` (Month, Tape, Location)
VALUES
('$_POST[Month]','$_POST[Tape2]','$_POST[Location]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Record added";

$sql="INSERT INTO `2007` (Month, Tape, Location)
VALUES
('$_POST[Month]','$_POST[Tape3]','$_POST[Location]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Record added";

mysql_close($con);
?>

Have you considered using the primary index as the tape number. You then just add as many tapes as needed and pull the ID numbers back out afterwards.

 

[Deceptor]

I'm assuming the tape numbers will be a range? Eg: 2000 - 2005 (to add 5 entries). Assuming this the simplist approach would be:

<form action="insert.php" method="post">
	<p style="text-align: center;">Month: <input type="text" name="month" /></p>
	<p style="text-align: center;">Tape Range: <input type="text" name="tape_begin" /> <input type="text" name="tape_end" /></p>
	<p style="text-align: center;">Location: <input type="text" name="location" /></p>
	<p style="text-align: center;"><input type="submit" value="Submit Query" /></p>
</form>

The PHP, I've also prevented sql injections and restructured the code a little with some crude error preventions and what not, should work but I haven't tested:

<?php
// establish a crude connection
$dbConnection = mysql_connect('localhost', '---', '---') || die('Could not connect: ' . mysql_error());
mysql_select_db('backups', $dbConnection) || die('Could not select database: ' . mysql_error());

// parse our incoming data
$month = trim($_POST['month']) || die('You did not enter a month.');
$location = trim($_POST['location']) || die('You did not enter a location.');
$tapeBegin = intval($_POST['tape_begin']);
$tapeEnd = intval($_POST['tape_end']);

// sanitize our data
$tapeBegin = max($tapeBegin, 0);		// ensures the tape begin starts at a minimum of 0
$tapeEnd = max($tapeBegin, $tapeEnd);	// ensures the tape end is the same or greater than tape begin

// build our insert statement
$sqlInsert = array(); // we'll collect the statements here 

while ($tapeBegin <= $tapeEnd)
{
	$sqlInsert[] = "('" . mysql_escape_string($month) . "', '" . mysql_escape_string($location) . "', $tapeBegin)";
	$tapeBegin++;
}

$sqlInsert = 'insert into `2007` (`month`, `location`, `tape`) values ' . implode(',', $sqlInsert);

// perform our insert
mysql_query($sqlInsert, $dbConnection) || die('Inserting records failed: ' . mysql_error());

// close our connection
mysql_close($dbConnection);

echo 'Records inserted successfully.';

?>

Hope that helps!

 

[aln]

NO.

Do not use mysql_*. Validate your input properly. Use prepared statements.

 

[Deceptor]

NO.

Do not use mysql_*. Validate your input properly. Use prepared statements.

Actually prepared statements serve no advantage over using the built in methods, but they do protect against you forgetting to validate an input yourself. Though I do prefer the prepared statements it's only in MySQLi and I wanted to keep things simple so he could follow what I wrote

Obviously security is a different aspect, and ultimately a different topic

 

[aln]

Actually prepared statements serve no advantage over using the built in methods, but they do protect against you forgetting to validate an input yourself. Though I do prefer the prepared statements it's only in MySQLi and I wanted to keep things simple so he could follow what I wrote

Prepared statements should actually be faster if you're running the same query over and over but the reason you use them is for the security aspect. Validating your input anyway is a bit like double bagging it and you'll be glad that you did when some critical bug is found in the code sometime in the future.

Personally I use PDO (though I'd advise using a framework and sticking with that in general, but I do a lot of bespoke crap) and find an OO lib to be a lot neater and easier to understand as long as you understand OO in very simple terms.

Obviously security is a different aspect, and ultimately a different topic

No!

Jokes aside, security is part of development and it doesn't take a lot of effort to eliminate these potential problems.

Also you should note mysql_* is deprecated as of PHP 5.5.0, for good reason. In addition to this, more and more people are moving away from MySQL in general, so using anything MySQL specific is generally bad form.

Not that I mean to give you a lecture, but I'm totally giving you a lecture. Stuff like the above is why PHP developers are often frowned upon in the wider programming community.

 

[Linkex]

You do realise that while what you say may be credible, presenting it like that will incline people to disregard it.

First thought when I saw the big NO was "what a *****". If the thread had been longer, I might have simply skipped your post. Which would be a shame because the point you make is actually valid, but presented in an abrasive manner.

Not trying to pick a fight, you might not be aware that's how you have come across. If that was your intent then disregard this.

 

[aln]

You do realise that while what you say may be credible, presenting it like that will incline people to disregard it.

First thought when I saw the big NO was "what a *****". If the thread had been longer, I might have simply skipped your post. Which would be a shame because the point you make is actually valid, but presented in an abrasive manner.

Not trying to pick a fight, you might not be aware that's how you have come across. If that was your intent then disregard this.

I thought it was blatantly obvious a joke mate, but if that wasn't obvious then it should be now. Either way I wanted his attention and I got it.

Thanks for the feedback though. No worries.

 

[Deceptor]

Prepared statements should actually be faster if you're running the same query over and over but the reason you use them is for the security aspect. Validating your input anyway is a bit like double bagging it and you'll be glad that you did when some critical bug is found in the code sometime in the future.

Personally I use PDO (though I'd advise using a framework and sticking with that in general, but I do a lot of bespoke crap) and find an OO lib to be a lot neater and easier to understand as long as you understand OO in very simple terms.



No!

Jokes aside, security is part of development and it doesn't take a lot of effort to eliminate these potential problems.

Also you should note mysql_* is deprecated as of PHP 5.5.0, for good reason. In addition to this, more and more people are moving away from MySQL in general, so using anything MySQL specific is generally bad form.

Not that I mean to give you a lecture, but I'm totally giving you a lecture. Stuff like the above is why PHP developers are often frowned upon in the wider programming community.

Oh I wholeheartedly agree, but I don't slam newcomers with every thing they need to know right from the get go (no offense to the OP, but certainly a newcomer).

As for PHP devs being frowned upon (in general), can't say I see that in my line of work but that could be different where you are

 

[jakehall109]

oops sorry didn't want to start an argument

I'm definitely a newcomer, prepared to learn and jump into it

This is to be used by myself and maybe another Engineer internally. There should be no funny data input by myself so validations etc are not totally needed right now but would be helpful going forward. Security isn't going to be a problem in our environment
I'm using this data to also test out my Query's i have to display the data which is the more important part

All of this is very helpful didn't expect the code to be written out for me, so many thanks for that Has given me a lot more to look at and modify my existing basic code

 

[aln]

I'm sure there will be mistakes in this, but this is how I'd write it assuming its Q&D.

// parse our incoming data
$month = trim($_POST['month']) || die('You did not enter a month.');
$location = trim($_POST['location']) || die('You did not enter a location.');
$tapeBegin = intval($_POST['tape_begin']);
$tapeEnd = intval($_POST['tape_end']);
$conn = '';

// sanitize our data
$tapeBegin = max($tapeBegin, 0);        // ensures the tape begin starts at a minimum of 0	
$tapeEnd = max($tapeBegin, $tapeEnd);    // ensures the tape end is the same or greater than tape begin

try
{
	$sql = 'INSERT INTO 2007(month, location, tape) VALUES (:month, :location, :tape)';

	$conn = new PDO('mysql:host=localhost;dbname=backups', $username, $password);
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$conn->beginTransaction();
	$stmt = $conn->prepare($sql);

	/*
	* I'm not sure this tapeBegin stuff does 
	* exactly what you want it to do.
	*/
	while ($tapeBegin <= $tapeEnd)
	{
		$stmt->bindParam(':month',$month);
		$stmt->bindParam(':location',$location);
		$stmt->bindParam(':tape',$tapeBegin, PDO::PARAM_INT);
		$stmt->execute();
		
		$tapeBegin++;
	}
	
	$conn->commit();
	//handle success here
}
catch(PDOException $e)
{
	/*
	* In the event of failure, no data will be inserted.
	* It's easier to redo a set than clean a mess.
	*/
	$conn->rollBack();
	//handle error here
}

//this closes the db connection
unset($conn);