[SOLVED] Inserting into MySQL with extra fields

[The Midnighter]

I've got a script going really nice, what it does right now is allows you to add an item into inventory. I want to add the functionality to be able to add more than one item, on the same invoice however. The only difference would be the serial number, so I've got a field that allows you to simply add more serial numbers, to the same invoice. This works nicely.

 

I've also passed the variable to the php script that does the work, that says just how many items (serial numbers) there are going to be added to the invoice.

 

This is where I'm stuck,

 

I'm trying to figure out a way to enter all of these items into the MySQL database with the same invoice number. Assume the HTML form works, as it does and has been tested.

 

	if ($manu) 
{ 
	$sql = "INSERT INTO `tables2` (`ID`, `INVOICE DATE`, `SUPPLIER`, `INVOICE`, `MANUFACTURER`, `MODEL`, `DESCRIPTION`, `SERIAL`, `PRICE`, `END USER`, `CUSTOMER PRICE`, `MEI Invoice`, `UPC`) VALUES (NULL, '$invoiceDate.', '$supplier', '$invoice', '$manufacturer', '$model', '$description', '$serial', '$price', '$endUser', '$customerPrice', '$warrantyStart', '$upc');";
	$query = mysql_query($sql);

	if (!$query)
	{
		echo ('Query failed! <br>sql = '.$sql.' <br>error = ' . mysql_error());
	}
	else
	{
		echo ("Query passed!");
	}
}
else
{
	echo ('Something broke, likely the lack of query');
}

 

This is the code that adds a SINGLE item into the database,

What I was thinking of doing was something like...

 

$i=0; // The obvious
$enteredNum = $_POST['serNumbers']; // The number of serial numbers

while ($i < $enteredNum)
{
       		$sql = "INSERT INTO `tables2` (`ID`, `INVOICE DATE`, `SUPPLIER`, `INVOICE`, `MANUFACTURER`, `MODEL`, `DESCRIPTION`, `SERIAL`, `PRICE`, `END USER`, `CUSTOMER PRICE`, `MEI Invoice`, `UPC`) VALUES (NULL, '$invoiceDate.', '$supplier', '$invoice', '$manufacturer', '$model', '$description', '$serial[$i]', '$price', '$endUser', '$customerPrice', '$warrantyStart', '$upc');";
               $i++;
}

 

Of course the problem with this is I'd need a way to have an array of serials, so I'd do this...

 

	$i = 0;
	$newSerial = array();
	while ($i < $numSerial)
	{
		$newSerial[$i] = $_POST['serial[$i]'];
		$i++;
	}

 

I hope you can see what I'm trying to do here, thanks for the help in advance!

[Ken2k7]

Why is ID set to null?

 

You can do this -

 

HTML -

<form method="post">
     Serial 1: <input type="text" name="serial[]" />
     Serial 2: <input type="text" name="serial[]" />
     Serial 3: <input type="text" name="serial[]" />
     Serial 4: <input type="text" name="serial[]" />
     Serial 5: <input type="text" name="serial[]" />
     <input type="submit" name="submit" value="submit" />
</form>

 

See the name attribute? Well, it's in an array format.

 

PHP -

<?php
if (isset($_POST['submit'])) {
     $serials = $_POST['serial']; // this will be an array of serial numbers, but there may be empty values, so be careful!
     // the starting SQL
     $sql = "INSERT INTO table2 (`INVOICE DATE`, `SUPPLIER`, `INVOICE`, `MANUFACTURER`, `MODEL`, `DESCRIPTION`, `SERIAL`, `PRICE`, `END USER`, `CUSTOMER PRICE`, `MEI Invoice`, `UPC`) VALUES";
     foreach ($serials as $key => $serial) {
          $sql .= "('$invoiceDate.', '$supplier', '$invoice', '$manufacturer', '$model', '$description', '$serial', '$price', '$endUser', '$customerPrice', '$warrantyStart', '$upc'),";
     }
     $sql = rtrim($sql, ',');
     mysql_query($sql);

That's just an example. You get the idea right?

[The Midnighter]

Oh yes! That's beautiful and exactly what I was trying to do.

muchly obliged, wish this forum had reputation so I could add to yours!

[Ken2k7]

One last thing I forgot to add in, in the foreach loop, make sure you check if $serial is empty or not. If it is, do nothing. If it's not empty, append the string.

[The Midnighter]

One last thing I forgot to add in, in the foreach loop, make sure you check if $serial is empty or not. If it is, do nothing. If it's not empty, append the string.

 

Actually what's really nice is I've already taken care of that back in the HTML form, it looks like this:

 

To initialize:

$current = "0";
$snom = $_REQUEST['snum'];
   if (!$snom || $snom == "0") { $snom = "1"; }

 

To use:

<?php 
	  while ($current < $snom ) { echo "<input name=\"serial[]\" id=\"serial[]\" type=\"text\" size=\"40\" />"; $current++; } ?><a href="index.php?snum=<?php echo $snom+1; ?>">Add</a> / <a href="index.php?snum=<?php echo $snom-1; ?>">Del</a>

 

So it basically just uses the number of fields that you require =) And there's also a Javascript script in the HTML that checks to see if it's blank anyway, so.. =)

 

Again, muchly obliged!