Jump to content

Inserting values from array into MySQL database

axdskhmd

By axdskhmd
in PHP Coding Help

 Share

Recommended Posts

axdskhmd Newbie

axdskhmd

Posted
Posted (edited)

Hello there,

 

I'm really new at PHP and I've been reading several beginner tutorials so please accept my apologies for any stupid questions I may ask along the way.

 

I've gotten as far as installing XAMPP, set up a database plus PHP form and I'm struggling to figure out how to insert values from an array into my database.

 

I've learnt the code in one particular way (see beginner tutorials) so I was wondering if you could help me keeping this in mind. I know there'll be a million better ways to do what I'm doing but I fear I will be bamboozled with different code or differently structured code.

 

Anyway the tutuorials I'm reading don't see to cover how I can insert an array of values into my database, just singular values.

 

In the attached file, I have 10 rows of 2x text inputs (20 text inputs total). Each row allows the user to enter a CarID and CarTitle. I've commented out the jQuery which validates the inputs so I can build a rudimentary version of this validation with PHP.

 

I thought that because the line $sql="INSERT INTO carids_cartitles (CarID, CarTitle) VALUES ($id, $title)"; is inside the foreach, means that for each pair of values from the form it'd insert to the database.

 

It doesn't do this. If I enter two or more CarIDs and CarTitles, only one pair of values gets saved to the database.

 

I'm sorry if I haven't explained this well enough, any questions please let me know.

 

Many thanks for your help in advance.

form.php

Edited by axdskhmd
fastsol Regular Member

fastsol

Posted
Posted

This should work, it's tested to the point of making the $sql strings.  I didn't make a db to test the insert but it looks correct.  I changed a number of things around and made it much more maintainable.  I left your original input fields commented out so you can see the better way to do this.

<?php
if (isset($_POST['submit'])) {

$con = mysqli_connect("localhost","root","","my_database");

if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql = "";

foreach ($_POST['car'] as $car) {

	$id = (int)$car['CarID'];
	$title = mysql_real_escape_string(trim($car['CarTitle']));

	if(empty($id) && empty($title)){} // Do nothing if the id and title are empty
	else
	{
		if ($id <= 0) 
		{
			echo "CarIDs must be positive whole numbers please.";
		}
		elseif(empty($title))
		{
			echo 'CarTitle must not be empty.';
		}	
		else 
		{
			echo $sql="INSERT INTO `carids_cartitles` (`CarID`, `CarTitle`) VALUES ($id, '$title')"; // You forgot single quotes around the $title
		}
	}
	
	if (!mysqli_query($con,$sql)) // This needs to be inside the foreach loop to run each query.
	{
	  die('Error: ' . mysqli_error($con));
	}

}

mysqli_close($con); 
}
?>
<!DOCTYPE HTML>
<html>
<head>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<!--
<script type="text/javascript">
$(document).ready(function($) {

	$('[name="myForm"]').submit(function(event) {
		
		// all form fields empty?
		if ($('input:text').filter(function() { return $(this).val().trim().length > 0; }).length == 0) {

				event.preventDefault();
				$('input:text').css({'border':''});
				alert('All form fields empty!');
		
		}
		else {

			// pair validation
			$('form > div').each(function() {
			
				// pair(s) detected
				if (($(this).find('input').eq(0).val().trim().length > 0) || ($(this).find('input').eq(1).val().trim().length > 0)) {
				
					$(this).find('input').each(function() {

						if ($(this).val().trim().length == 0) {

							event.preventDefault();
							$(this).css({'border': '1px solid red'});

						}
						// post-validation clear border if individual input is no longer empty/numeric check passes
						else {

							$(this).css({'border': ''});
								
								if ($(this).index() === 0) {
								
									if (!$(this).val().match(/^\d*[0-9](|.\d*[0-9]|,\d*[0-9])?$/)) {
									
										event.preventDefault();
										$(this).css({'border': '1px solid blue'});
									
									}
								
								}
							
						}

					});

				}
				// end pair(s) detected
				
				// no pair(s) detected
				else {

					// post-validation clear border if both inputs no longer have values
					$(this).find('input').css({'border': ''});

				}
				// end no pair(s) detected
				
			});
			// end pair validation

		}
		// end all form fields empty?

	});
	// end submit event

});
// end document ready
</script>
-->
<style type="text/css">
input[type=text] { 
height: 20px; 
}

input[name*="CarID"] { 
width: 65px; 
}

input[name*="CarTitle"] { 
width: 450px; 
}

form > div { 
margin-bottom: 4px; 
}

.error { 
border: 1px solid red; 
}

.not_numeric { 
border: 1px solid blue; 
}
</style>
</head>
<body>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post" name="myForm">

<?php
$num = 10;
for($x=0; $x<$num; $x++)
{
	echo '<div>';
	echo '<input type="text" name="car['.$x.'][CarID]" value="" maxlength="20" />';
	echo '<input type="text" name="car['.$x.'][CarTitle]" value="" maxlength="175" />';
	echo '</div>';
}

?>
<!--<div>
<input type="text" name="car[][CarID]" value="" maxlength="20" />
<input type="text" name="car[][CarTitle]" value="" maxlength="175" />
</div>
<div>
<input type="text" name="car[][CarID]" value="" maxlength="20" />
<input type="text" name="car[][CarTitle]" value="" maxlength="175" />
</div>
<div>
<input type="text" name="car[][CarID]" value="" maxlength="20" />
<input type="text" name="car[][CarTitle]" value="" maxlength="175" />
</div>
<div>
<input type="text" name="car[][CarID]" value="" maxlength="20" />
<input type="text" name="car[][CarTitle]" value="" maxlength="175" />
</div>
<div>
<input type="text" name="car[][CarID]" value="" maxlength="20" />
<input type="text" name="car[][CarTitle]" value="" maxlength="175" />
</div>
<div>
<input type="text" name="car[][CarID]" value="" maxlength="20" />
<input type="text" name="car[][CarTitle]" value="" maxlength="175" />
</div>
<div>
<input type="text" name="car[][CarID]" value="" maxlength="20" />
<input type="text" name="car[][CarTitle]" value="" maxlength="175" />
</div>
<div>
<input type="text" name="car[][CarID]" value="" maxlength="20" />
<input type="text" name="car[][CarTitle]" value="" maxlength="175" />
</div>
<div>
<input type="text" name="car[][CarID]" value="" maxlength="20" />
<input type="text" name="car[][CarTitle]" value="" maxlength="175" />
</div>
<div>
<input type="text" name="car[][CarID]" value="" maxlength="20" />
<input type="text" name="car[][CarTitle]" value="" maxlength="175" />
</div>
--><input type="submit" value="submit" name="submit"/>
</form>
</body>
</html>
axdskhmd Newbie

axdskhmd

Posted
  • Author
Posted

Hi fastsol,

 

Thank you very much for taking the time to help me with this. I've found a lot useful about your reply, particularly finding a way of inserting multiple values to the database by including the mysqli_query in the foreach.

 

I've been tinkering around with the code, trying to understand the bits I didn't at first.

 

I changed (isset($_POST['submit'])) to ($_SERVER["REQUEST_METHOD"] == "POST") as I've seen somewhere that the submit value can be spoofed, so wanted to take the extra precaution that the form was actually being submitted (unless I'm incorrect?).

 

This is what I'm using now:

<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {

$con = mysqli_connect("localhost","root","","my_database");

if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql = $errors = "";

foreach ($_POST['car'] as $car) {

	$id = mysql_real_escape_string(trim($car['CarID']));
	$title = mysql_real_escape_string(trim($car['CarTitle']));
	
	// detect pairs
	if ((strlen($id) > 0) || (strlen($title) > 0)) {
		
		// check for empty input and error
		if ((strlen($id) == 0) || (strlen($title) == 0)) {
			$errors = "<br />At least one or more pairs has no ID or Title.";
		}
		// then check for 0-9 only and error
		elseif (!preg_match('/^[0-9]*$/', $id)) {
			$errors = "<br />Numbers only in ID.";
		}
		// validation passed, insert to database
		else {
			$sql="INSERT INTO carids_cartitles (CarID, CarTitle) VALUES ($id, '$title')";
			if (!mysqli_query($con,$sql)) {
				die('Error: ' . mysqli_error($con));
			}
		}
		
	}
	// end detect pairs
}
// end foreach

mysqli_close($con); 
}
?>
<!DOCTYPE HTML>
<html>
<head>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<!--<script type="text/javascript">
$(document).ready(function($) {

	$('[name="myForm"]').submit(function(event) {
		
		// all form fields empty?
		if ($('input:text').filter(function() { return $(this).val().trim().length > 0; }).length == 0) {

				event.preventDefault();
				$('input:text').css({'border':''});
				alert('All form fields empty!');
		
		}
		else {

			// pair validation
			$('form > div').each(function() {
			
				// pair(s) detected
				if (($(this).find('input').eq(0).val().trim().length > 0) || ($(this).find('input').eq(1).val().trim().length > 0)) {
				
					$(this).find('input').each(function() {

						if ($(this).val().trim().length == 0) {

							event.preventDefault();
							$(this).css({'border': '1px solid red'});

						}
						// post-validation clear border if individual input is no longer empty/numeric check passes
						else {

							$(this).css({'border': ''});
								
								if ($(this).index() === 0) {
								
									if (!$(this).val().match(/^\d*[0-9](|.\d*[0-9]|,\d*[0-9])?$/)) {
									
										event.preventDefault();
										$(this).css({'border': '1px solid blue'});
									
									}
								
								}
							
						}

					});

				}
				// end pair(s) detected
				
				// no pair(s) detected
				else {

					// post-validation clear border if both inputs no longer have values
					$(this).find('input').css({'border': ''});

				}
				// end no pair(s) detected
				
			});
			// end pair validation

		}
		// end all form fields empty?

	});
	// end submit event

});
// end document ready
</script> -->
<style type="text/css">
input[type="text"] { 
height: 20px; 
}

input[name*="CarID"] { 
width: 65px; 
}

input[name*="CarTitle"] { 
width: 450px; 
}

form > div { 
margin-bottom: 4px; 
}

.error { 
border: 1px solid red; 
}

.not_numeric { 
border: 1px solid blue; 
}
</style>
</head>
<body>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post" name="myForm">
<?php
$num = 10;
for($x=0; $x<$num; $x++) {
	echo '<div>';
	echo '<input type="text" name="car['.$x.'][CarID]" value="" maxlength="20" />';
	echo '<input type="text" name="car['.$x.'][CarTitle]" value="" maxlength="175" />';
	echo '</div>';
}
?>
<input type="submit" value="submit" name="submit" />
<?php if ($_SERVER["REQUEST_METHOD"] == "POST") { echo $errors; } ?>
</form>
</body>
</html>

I was just wondering, what is the difference between the way you have set the arrays to array_combine? It appears I am able to validate the values just the same. I'm not saying your way is wrong (what do I know!). Just curious and learning :)

 

I've been thinking over the past few days what it is I actually want to do. I've come to the conclusion that I'll need to effectively replicate the jQuery validation I've already got but with PHP (I will need to check the CarID against values in the database, if a duplicate is found, show an error).

 

So I need to apply the following to the PHP validation:

 

1. Retain all values entered in case validation fails

2. Within a pair of text inputs that has a value, detect which one doesn't have a value and show an error next to that specific input

3. Ensure CarID is 0-9 only with the regex, otherwise show an error next to that specific input

4. Check CarID against database, if same CarID is found on database, show error next to that specific input "CarID already registered!", otherwise insert to database (I'm just looking to get points 1-3 down and learn this myself, the hard way lol).

 

Firstly I've tried to modify your code to retain values after validation:

 

echo '<input type="text" name="car['.$x.'][CarID]" value="'.$id.'" maxlength="20" />';

 

This doesn't work. I know if I wasn't using the foreach to generate the form fields, I could use something like:

 

<input type="text" name="car[0][CarID]" value="<?php if ($_SERVER["REQUEST_METHOD"] == "POST") { echo $valueofthisid; } ?>" maxlength="20" />

 

The thing is, the only way I can see of doing it this way is to hard-code 20x inputs, each with their own unique value and error message. I.e. CarID1, CarID2, CarTitle1, CarTitle2, CarID1error1, CarTitle1error1 etc - and that just strikes me as inefficient.

 

May I ask upon your genius once more? Or do you think it'd have to be done the hard way?

axdskhmd Newbie

axdskhmd

Posted
  • Author
Posted

Hi Barand,

 

I've always played around with a bit of code here and there but have always wanted to be able to produce it myself and not use other's code without learning. I just need to be shown the tools and how to use them, a month ago I wasn't able to put an if/else block together so I'm always building on the knowledge.

 

Tutorials are only getting me so far, trying to find answers on forums that other people have asked (like the empty() not checking if a form's input value is truly empty - frustrating one this).

 

The inspiration recently for me to pursue coding has come from my employer's support, my main role there is nothing to do wth web development however with my rudimentary skills I have helped them out and they are encouraging me to learn.

 

So I hope this doesn't look like I want people to do anything for me, I won't benefit that way, I need examples and an explanation of what the code's doing. If I don't understand the code I won't be able to produce it myself and one day I hope to be able to teach others.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.