Inserting multiple (5 or 10) records at a time?

[wmguk]

Hey guys,

 

Im looking to make a system with two fields, just size and price

 

I want to give the option to type in 5 different sizes and prices at the same time, before clicking the insert button.

 

I cant seem to work out how to make it submit 5 different rows, without have price1, cost1, price2, cost2 and then a

<?PHP
if cost2 = "0" { 
//STOP
}else{
//INSERT $cost2
}
?>

 

is there another better way to do this?

 

thanks.

[br0ken]

Setup your input boxes like this:

 

<input type="text" name="price_1" value="" /> - <input type="text" name="size_1" value="" /><br />
<input type="text" name="price_2" value="" /> - <input type="text" name="size_2" value="" /><br />
<input type="text" name="price_3" value="" /> - <input type="text" name="size_3" value="" /><br />
<input type="text" name="price_4" value="" /> - <input type="text" name="size_4" value="" /><br />
<input type="text" name="price_5" value="" /> - <input type="text" name="size_5" value="" /><br />

 

And then collect and store the data like this:

 

for($i = 1; $i <= 5; $i++)
{
  $price = number_format($_REQUEST["price_".$i], 2, ".", "");
  $size = number_format($_REQUEST["size_".$i], 0, "", "");
  mysql_query("INSERT INTO table (size, price) VALUES ($size, $price);
}

 

I haven't tested this but it should work and if not, it's not far off what you need.

[paulman888888]

Yep br0ken got it in 1.

 

What is with the title of the topic?

5 or 10?

[wmguk]

excellent thank you

 

I wasnt sure if I wanted 10 or just 5 to insert lol, I will make a couple of pages and test them out.

 

thats super speedy help

 

Cheers

Drew

[br0ken]

If you're not sure how many inputs you want this will allow to have a variable number of them. Simply change the variable in count and it will change the number of inputs and automatically know how many to process.

 

$count = 10;

echo "<input type=\"hidden\" name=\"count\" value=\"$count\" />\n";

for($i = 1;$i <= $count;$i++)
{
  echo "<input type=\"text\" name=\"price_$i\" value=\"\" /> - ";
  echo "<input type=\"text\" name=\"size_$i\" value=\"\" /><br />\n";
}

 

And here's the updated processing code. It gets the amount of inputs from the hidden input field, count.

 

$count = (int)$_REQUEST['count'];
for($i = 1; $i <= $count; $i++)
{
  $price = number_format($_REQUEST["price_".$i], 2, ".", "");
  $size = number_format($_REQUEST["size_".$i], 0, "", "");
  mysql_query("INSERT INTO table (size, price) VALUES ($size, $price);
}

[wmguk]

Oh that is superb!!! thank you for your help, i've not actually used the insert system yet, but just the first section works brilliantly.

 

Cheers

 

[wmguk]

ok, now how do i get it to ignore any that are empty?

 

basically if i set count = 10, but i only have 8 to enter, i would need to leave 2 empty...

 

can this be done?

[br0ken]

<?php

$count = (int)$_REQUEST['count'];
for($i = 1; $i <= $count; $i++)
{
  $price = number_format($_REQUEST["price_".$i], 2, ".", "");
  $size = number_format($_REQUEST["size_".$i], 0, "", "");

  if ($price > 0 && $size > 0)
    mysql_query("INSERT INTO table (size, price) VALUES ($size, $price)");
}

?>

 

You mean like that?

[wmguk]

Sorry for the delay, yes that great! that works,

 

I'm now working on an edit page, i still wanted to show 10 empty boxes, but if when it was created there were 4 inputs, how can i show these and then 6 empty boxes?

 

I have tried working on the original code, so far I have this:

 

$countresult = mysql_query("SELECT * FROM sizes WHERE originalprod = '$refnum' ");
$used = mysql_num_rows($countresult); //Shows 4 rows
$count = (10 - $used) ; // I want to show 10 boxes each time, so 10 - 4 = 6

 

<?PHP //Needs to show the 4 fields from the database and then 6 empty ones

while ($row=mysql_fetch_assoc($countresult))
{
$r = 1;$r <= $used;$r++ ; //Assign the numbering for the fields

echo "<input type='text' class='boxes' name='size_$r' value='";
echo $row['size'];
echo "'>";

echo "<input type='text' class='boxes' name='cost_$r' value='";
echo $row['cost']; 
echo "'>";
}

//Now show the empty input boxes
for($i = $used+1;$i <= $count; $i++)
{
echo "<input type='text' class='boxes' name='size_$i' value=''>";
echo "<input type='text' class='boxes' name='cost_$i' value=''>"; 
}
?>

 

Now where $i is used for naming the input boxes size_1,2,3 etc this works, if there is 4 fields already in the DB then it starts from 5 so that is working, however the numbering of the input boxes already in the DB doesnt work. there are 4 shown, but they are all called size_2 and cost_2.... they arent numbering correctly...

 

any ideas what I've done wrong?

 

Cheers

[wmguk]

how can i just generate an incremental number to add on to the size_ and cost_?

[br0ken]

Each time you re-enter your while loop you set $r as 1 and then increment it to 2.

 

This should also number your boxes correctly and display the empty ones.

 

 

<?php
$r = 1;
while ($row=mysql_fetch_assoc($countresult))
{
echo "<input type='text' class='boxes' name='size_$r' value='";
echo $row['size'];
echo "'>";

echo "<input type='text' class='boxes' name='cost_$r' value='";
echo $row['cost']; 
echo "'>";

  $r++ ;
}

//Now show the empty input boxes
for($i = $i;$i <= $count; $i++)
{
echo "<input type='text' class='boxes' name='size_$i' value=''>";
echo "<input type='text' class='boxes' name='cost_$i' value=''>"; 
}
?>

[wmguk]

Excellent thank you, thats sorted that, now How can i get it to only update the boxes with values, and not do anything with the empty values?

 

There could be up to 10 results for this, but there might only be 5, so is there a way to check for a value != "" and then update?

 

//there could be 10 items where originalprod = 1 so it needs to loop through the current records and update them all...
$sizeresult = mysql_query("SELECT * FROM sizes WHERE originalprod = '$refnum'");
while ($row=mysql_fetch_assoc($sizeresult))
{
$sizeref = $row['refnum'];

echo $sizeref ; 
echo "<br>";

mysql_query("UPDATE sizes SET size = '$size, cost = '$cost' WHERE refnum = '$sizeref'");
}

 

This is the whole page script....

 

<?
include ("connection.php");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }mysql_select_db($db, $con);

//CHECK TO UPDATE THE IMAGE
if (empty($_POST['uploadedfile']))
{   
$pic = $_POST['pic']; 
}
else
{

// Where the file is going to be placed 
$target_path = "../../images/tiles/";

// File Upload Script.
$target_path = $target_path . basename( $_FILES['uploadedfile']['name']); 
$_FILES['uploadedfile']['tmp_name']; 

if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
    echo "";
} else{
    echo "There was an error uploading the file called: ".  basename( $_FILES['uploadedfile']['name']). 
    ", please try again!<br><br>";

$pic = basename( $_FILES['uploadedfile']['name']);
} 
}
//Get Variables of Product
  $refnum = $_POST['refnum'];
  $name = $_POST['name'];
  $style = $_POST['style'];
  $colour = $_POST['colour'];
  $type = $_POST['type'];
  

if (empty($_POST['clear']))
$clear = "off"; 
else 
$clear = "on";

if (empty($_POST['special']))
$special = "off"; 
else 
$special = "on";

//Update the product information
mysql_query("UPDATE products SET name = '$name', style = '$style', type='$type', colour = '$colour', clear = '$clear', special = '$special', pic = '$pic' WHERE refnum = '$refnum'");

//---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
//This is the area that needs looking at
//Find refnum for each size and cost
$sizeresult = mysql_query("SELECT * FROM sizes WHERE originalprod = '$refnum'");
while ($row=mysql_fetch_assoc($sizeresult))
{
$sizeref = $row['refnum'];

echo $sizeref ; 
echo "<br>";

mysql_query("UPDATE sizes SET size = '$size, cost = '$cost' WHERE refnum = '$sizeref'");
}
/*


//Run the count information
$count = 10;
for($i = 1; $i <= $count; $i++)
{
  $cost = number_format($_REQUEST["cost_".$i], 2, ".", "");
  $size = $_REQUEST['size_'."$i"];
if ($cost > 0 && $size > 0)

//Change the size and Cost
mysql_query("UPDATE sizes SET size = '$size, cost = '$cost' WHERE refnum = '$sizeref'");
} }
/*
//GET ORIGINAL LOWEST COST
$result2 = mysql_query("SELECT * FROM products WHERE refnum = '$refnum'");
while ($row=mysql_fetch_assoc($result2))
{
$originalcost = $row['lowprice'];

//GET LOWEST COST JUST ENTERED
$result = mysql_query("SELECT * FROM sizes WHERE originalprod = '$refnum' ORDER BY cost ASC LIMIT 1");
while ($row=mysql_fetch_assoc($result))
{
$lowprice = $row['cost'];


//COMPARE TO FIND REAL LOWEST COST & INSERT TO THE DB
if ($originalcost < $lowprice) {
$insertprice = $originalcost ; 
}else{
$insertprice = $lowprice ;
}
$sql = "UPDATE products SET lowprice = '$insertprice' WHERE refnum = '$refnum'";
mysql_query( $sql , $con ) or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql<br><br>" );
}
}
mysql_close($con);
?>

[wmguk]

any ideas about looping through to generate the update query

 

How about if size_1 has a value then add to update query, then size_2 etc etc

 

whats the best way?

[br0ken]

The best way is to try what you think and see if it works. If not, alter things slightly to make it work. The psuedo code you wrote in your last post would do the trick so don't be scared to try it out.

How about if size_1 has a value then add to update query, then size_2 etc etc

 

You know how to do if statements and check if a variable has no value so combine this and you'll learn something (that you already know).

 

Never be scared to try things for yourself.