write to database during each foreach loop

I need to write the array results to a table and then increment the table 1

[mgiroir]

Hi,

I've been working on this for the last three days and just can't figure it out. This is what I want accomplished:

//1st piece of equipment returned in array
$query="SELECT e.equipmentid, e.equipmentname, e.equiprentalprice FROM equipment e WHERE equipmentid='$equip[0]'";
$result=mysql_query($query);
$num=mysql_numrows($result);

while ($row = mysql_fetch_assoc($result)) {
$product1 = $row["equipmentname"];
$product1price = $row["equiprentalprice"];
}

$equipmentid=mysql_result($result,$i,"equipmentid");
$equipmentname=mysql_result($result,$i,"equipmentname");
$equiprentalprice=mysql_result($result,$i,"equiprentalprice");

echo "<tr><td align=left valign=top><font face=Arial, Helvetica, sans-serif size=-1>";
echo "$equipmentid<BR></font>";
echo "$equipmentname<BR></a>";
echo "$equiprentalprice<BR>";

echo "</font></td>";
echo "</tr>";

//2nd piece os equipment returned in array
if($equip[1]) {
$query="SELECT e.equipmentid, e.equipmentname, e.equiprentalprice FROM equipment e WHERE equipmentid='$equip[1]'";
$result=mysql_query($query);
$num=mysql_numrows($result);

while ($row = mysql_fetch_assoc($result)) {
$product2 = $row["equipmentname"];
$product2price = $row["equiprentalprice"];
}

$equipmentid=mysql_result($result,$i,"equipmentid");
$equipmentname=mysql_result($result,$i,"equipmentname");
$equiprentalprice=mysql_result($result,$i,"equiprentalprice");

echo "<tr><td align=left valign=top><font face=Arial, Helvetica, sans-serif size=-1>";
echo "$equipmentid<BR></font>";
echo "$equipmentname<BR></a>";
echo "$equiprentalprice<BR>";

echo "</font></td>";
echo "</tr>";
}

//3rd piece of equipment returned in array
if($equip[2]) {
$query="SELECT e.equipmentid, e.equipmentname, e.equiprentalprice FROM equipment e WHERE equipmentid='$equip[2]'";
$result=mysql_query($query);
$num=mysql_numrows($result);

while ($row = mysql_fetch_assoc($result)) {
$product3 = $row["equipmentname"];
$product3price = $row["equiprentalprice"];
}

$equipmentid=mysql_result($result,$i,"equipmentid");
$equipmentname=mysql_result($result,$i,"equipmentname");
$equiprentalprice=mysql_result($result,$i,"equiprentalprice");

echo "<tr><td align=left valign=top><font face=Arial, Helvetica, sans-serif size=-1>";
echo "$equipmentid<BR></font>";
echo "$equipmentname<BR></a>";
echo "$equiprentalprice<BR>";

echo "</font></td>";
echo "</tr>";
}

and so on...

but dynamically based on the results of initial array from a form. I have been trying to do this with foreach and the foreach works fine, and overwrites the value of $product1 like I am telling it to do in the code below. But what I can't figure out is how to increment $product1 by 1 (from $product1 to $product2 then from $product2 to $product3) as the loops run so that when I insert the values into the table no values will be overwritten. Here is the code as it stands right now:

foreach($equip as $key => $equipmentid){
$query="SELECT e.equipmentid, e.equipmentname, e.equiprentalprice FROM equipment e WHERE equipmentid='$equipmentid'";
$result=mysql_query($query);
$num=mysql_numrows($result);

while ($row = mysql_fetch_assoc($result)) {
$product1 = $row["equipmentname"];
$product1price = $row["equiprentalprice"];
print_r($row);
}

$i=0;
while ($i < $num) {
$equipmentid=mysql_result($result,$i,"equipmentid");
$equipmentname=mysql_result($result,$i,"equipmentname");
$equiprentalprice=mysql_result($result,$i,"equiprentalprice");

echo "<td align=left valign=top><font face=Arial, Helvetica, sans-serif size=-1>";

echo "</font></td>";

$i++;
}
}

I can echo the rresults of the array and everything I need is displayed so I know that it's working right, but I just can't seem to figure out how to write the results to the database and then increment $product1 and $product1price by 1 where $product1 will become $product2 and $product1price will become $product2price. Any help would be GREATLY appreciated. Thanks for your time.

Marshall

[dmmh]

maybe its me but I dont see no UPDATE or INSERT syntax anywhere.....

[dmmh]

//count length of array
$num_equip = count($_POST['equip']);

//loop for echoing? :)
for ($i=0; $i<$num_equip; $i++){
$equip = mysql_real_escape_string($_POST['equip'][$i]);
$query="SELECT * FROM e WHERE equipmentid='$equip'";
$result=mysql_query($query);
$row = mysql_fetch_array($result));

//$product = $row['equipmentname']; //whats with the duplicate entries here?
//$productprice = $row['equiprentalprice']; //whats with the duplicate entries here?
$equipmentid = $row['equipmentid'];
$equipmentname = $row['equipmentname'];
$equiprentalprice = $row['equiprentalprice'];?>

<tr>
<td align="left" valign="top"><font face="Arial, Helvetica, sans-serif" size"=-1">
<?php echo $equipmentid;?></font><BR/>
<?php echo $equipmentname;?><BR/>
<?php echo $equiprentalprice;?><BR/>
</td>
</tr>
<? }//close outer loop

no need to use variable variable names...no need at all

when exactly do you need to insert and what?

[mgiroir]

Sorry about the INSERT...
Here is the full page before changing to your suggestion.

Posted wrong code , changing it now..
Here is the right code...

<?
include("dbinfo.inc.php");
include("conx.php");

$customerid=$_POST['customerid'];
$eventdate=$_POST['eventdate'];
$starteventdate=$_POST['starteventdate'];
$starteventtime=$_POST['starteventtime'];
$endeventdate=$_POST['endeventdate'];
$endeventtime=$_POST['endeventtime'];
$delivery=$_POST['delivery'];
$deliverytime=$_POST['deliverytime'];
$setupon=$_POST['setupon'];

echo "<table border=1 width=100%><tr>";

foreach($equip as $key => $equipmentid){
$query="SELECT e.equipmentid, e.equipmentname, e.equiprentalprice FROM equipment e WHERE equipmentid='$equipmentid'";
$result=mysql_query($query);
$num=mysql_numrows($result);

while ($row = mysql_fetch_assoc($result)) {
echo $row["equipmentid"];
echo $row["equipmentname"];
echo $row["equiprentalprice"];
$product1 = $row["equipmentname"];
$product1price = $row["equiprentalprice"];
print_r($row);
}

$i=0;
while ($i < $num) {
$equipmentid=mysql_result($result,$i,"equipmentid");
$equipmentname=mysql_result($result,$i,"equipmentname");
$equiprentalprice=mysql_result($result,$i,"equiprentalprice");

echo "<td align=left valign=top><font face=Arial, Helvetica, sans-serif size=-1>";
echo "$equipmentid<BR></font>";
echo "$equipmentname<BR></a>";
echo "$equiprentalprice<BR>";

echo "</font></td>";

$i++;
}
}

echo "</tr></table>";

print_r($equip);
echo "<BR><BR>";

$totalcost=($product1price + $product2price + $product3price + $product4price + $product5price + $product6price + $product7price + $product8price + $product9price + $product10price + $product11price + $product12price + $product13price + $product14price + $product15price + $product16price + $product17price + $product18price + $product19price + $product20price);

$query = "INSERT INTO rentals VALUES ('', '$customerid', '$eventdate', '$starteventdate', '$starteventtime', '$endeventdate', '$endeventtime', '$delivery', '$deliverytime', '$setupon', '$product1', '$product1price', '$product2', '$product2price', '$product3', '$product3price', '$product4', '$product4price', '$product5', '$product5price', '$product6', '$product6price', '$product7', '$product7price', '$product8', '$product8price', '$product9', '$product9price', '$product10', '$product10price', '$product11', '$product11price', '$product12', '$product12price', '$product13', '$product13price', '$product14', '$product14price', '$product15', '$product15price', '$product16', '$product16price', '$product17', '$product17price', '$product18', '$product18price', '$product19', '$product19price', '$product20', '$product20price', '$totalcost')";
mysql_query($query);
echo "Information Added Sucessfully...<BR><BR>";
include("rental_menu.php");

mysql_close();
?>

Which when run, updates the table fields product1 and product1price with the results of the last run through the foreach loop. Problem is that my table columns are named product1, product1price, product2, product2price, and so on. Therefore I would need the variables to be set as product1, product1price and so on. Each time the loop executes it fills an array with the correct info, is there a way to just extend the array with each pass of the loop? This is not in a production environment yet so I could change the column names if necessary.

[edited by: jatar_k at 8:29 pm (utc) on April 3, 2005]
[edit reason] fixed sidescroll [/edit]

[dmmh]

im wondering why you need this many columns for the products

wouldnt it just be easier to have a table which holds the different products? 3 columns in it (product_id, product_name and product_price for an example) and you have enough to store an infinite amount of products.

also, where do you get the variable $equip form?

in this line in your last posted code I mean:
foreach($equip as $key => $equipmentid){

nothing above it tells me where it originates

but I do think you need to sort the way you treat you columns/ tables and I think if you do, you will have a much better time handling all the stuff you are trying to do.

[mgiroir]

$equip comes from a form which is dynamically populated from another table. The population is done using check boxes. $equip[] holds the equipmentid value from each piece of equipment that was check before submitting. It would have made things alot easier if I could have figured out how to create another array which would have held the prices associated with the equipment that was checked. But that's another story about wasted time...lol.

Do you mean writing to a seperate table during the foreach loop? I have thought of this but because I was thinking it would be much more complicated than that, I never tried to do this. I guess I should try it now.

I definitely appreciate your help with this.

Marshall

[dmmh]

dood #*$!, dont do this lol, ill try to see what I can do

[dmmh]

am I right when I say I think you are using this to see what was ordered or something?

if so I have some minor suggestions with regards to the way you handle it all. I have some code which I think will work, but you will have to dump 38 columns from one of your tables which are redundant imo, amongst some other things.

best would be to duplicate some working tables and use those for that to do some testing untill you are satisfied

[mgiroir]

This link may help you to see exactly what I am trying to do. The first page, is of course all upcoming rentals, when you click on the customers name, you can see all rentals for that customer. When you add a rental, you can see that all information was passed sucessfully. The way I have it right now, it is working fine, but I would really like a more efficient way of doing this instead of:

//1st piece of equipment returned in array
$query="SELECT e.equipmentid, e.equipmentname, e.equiprentalprice FROM equipment e WHERE equipmentid='$equip[0]'";
$result=mysql_query($query);
$num=mysql_numrows($result);

while ($row = mysql_fetch_assoc($result)) {
$product1 = $row["equipmentname"];
$product1price = $row["equiprentalprice"];
}

$equipmentid=mysql_result($result,$i,"equipmentid");
$equipmentname=mysql_result($result,$i,"equipmentname");
$equiprentalprice=mysql_result($result,$i,"equiprentalprice");

echo "<tr><td align=left valign=top><font face=Arial, Helvetica, sans-serif size=-1>";
echo "$equipmentid<BR></font>";
echo "$equipmentname<BR></a>";
echo "$equiprentalprice<BR>";

echo "</font></td>";
echo "</tr>";

//2nd piece os equipment returned in array
if($equip) {
$query="SELECT e.equipmentid, e.equipmentname, e.equiprentalprice FROM equipment e WHERE equipmentid='$equip[1]'";
$result=mysql_query($query);
$num=mysql_numrows($result);

while ($row = mysql_fetch_assoc($result)) {
$product2 = $row["equipmentname"];
$product2price = $row["equiprentalprice"];
}

$equipmentid=mysql_result($result,$i,"equipmentid");
$equipmentname=mysql_result($result,$i,"equipmentname");
$equiprentalprice=mysql_result($result,$i,"equiprentalprice");

echo "<tr><td align=left valign=top><font face=Arial, Helvetica, sans-serif size=-1>";
echo "$equipmentid<BR></font>";
echo "$equipmentname<BR></a>";
echo "$equiprentalprice<BR>";

echo "</font></td>";
echo "</tr>";
}

//3rd piece of equipment returned in array
if($equip[2]) {
$query="SELECT e.equipmentid, e.equipmentname, e.equiprentalprice FROM equipment e WHERE equipmentid='$equip[2]'";
$result=mysql_query($query);
$num=mysql_numrows($result);

while ($row = mysql_fetch_assoc($result)) {
$product3 = $row["equipmentname"];
$product3price = $row["equiprentalprice"];
}

$equipmentid=mysql_result($result,$i,"equipmentid");
$equipmentname=mysql_result($result,$i,"equipmentname");
$equiprentalprice=mysql_result($result,$i,"equiprentalprice");

echo "<tr><td align=left valign=top><font face=Arial, Helvetica, sans-serif size=-1>";
echo "$equipmentid<BR></font>";
echo "$equipmentname<BR></a>";
echo "$equiprentalprice<BR>";

echo "</font></td>";
echo "</tr>";
}

and so on...

which is how it's set up now, so that I could work on other things while trying to figure this out.
I know it's not very easy on the eyes right now, but I am planning to clean up later.

[1][edited by: jatar_k at 9:52 pm (utc) on April 3, 2005]

Well they removed the link, I can send it to you if you like.

[dmmh]

pm me it

[mgiroir]

done

[dmmh]

ill look into it later :)

[dmmh]

so, regarding your page, I think you need to sort the way you handle your tables/ columns.

like they say (and this one is one of my favorites): 'if it ain't broken, dont fix it'.

May be so, but lets review this simple scenario?
What happens if you somewhere in the future, have more products to sell/ rent? Now you may have 20 and you script('s) are 'optimised' for this, but you will have to re-code large portions of your site to incorporate the changes as well as add more columns, which now may store redundant data ((NULL's) im quite positive they do all ready).

lets change all that.
your 'rentals' table should have, based on the code you gave us', 13 columns instead of the 30 or so you use now.

Now, you use a seperate column for each rentable product, correct me if I am wrong, even if the product was rented for the id or not.

the columns you should have are the following, in the exact order I use them (and im guessing for column names here, but youll know what I mean)

table rentals:
colums: id, rental_id, customer_id, event_date, start_event_date, start_event_time, end_event_date, end_event_time, delivery, delivery_time, setup_on, product, product_price

now, somewhere in the script, which needs to change, every rental issued by a client will get a seperate new rental id from a db query (SELECT MAX(rental_id) as rental_id FROM rentals) and you add 1 to it to generate a new rental id manually. if a user rents one product and the last rental id was 256, the new value of the column 'rental_id' would be 257, for one row (product). If he/she rents 20 products, you would have all your 20 produtcs rented all at once, the rental_id would be 257 for all 20 rows......

now, if you do a query like 'SELECT * FROM rentals WHERE rental_id = '$rental_id'', you will get multiple rows, but only if a rental issued holds more then 1 product available in your inventory and the customer chose more then 1 ;)

you see what Im getting at? each rental id might or might not use more then 1 row in the table, but they would still have the same rental id, rental date, etc etc, basically all columns would contain the same data, except the 2 holding the product name (better use product id here) and product price

this will allow for the ability of your company to grow without you having to recode all/ most of it

im hoping im making myself clear here, english isntmy primary language :)

so I suggest the following, after you understand what I mean: duplicate the rental table you have now, name it rental_copy or something and apply to it the column names I just mentioned and ditch columns 'product2' through 'product_price20'

then get back to me/ us

there's even better ways to do this, but im taking small steps here, so I hope you dont mind :)

[mgiroir]

Actually, I was using arrays to enter the first product selected into product1 and it's associated price put into product1price and so on. Therefore the products selected basically "stacked" for lack of a better word... into the table starting with product1 and working toward product20, therefore if the client wanted to rent less than 21 products at a time I was safe.

I do understand where you are going with this, my table structure now looks like this:

CREATE TABLE rentals (
id int(6) NOT NULL auto_increment,
rental_id int(6) DEFAULT '0' NOT NULL,
customer_id int(6) DEFAULT '0' NOT NULL,
rental_agree_numb varchar(25),
event_date varchar(10),
start_event_date varchar(5),
start_event_time varchar(7),
end_event_date varchar(5),
end_event_time varchar(7),
delivery char(1),
delivery_time varchar(7),
delivery_charge decimal(7,2),
sp_address1 varchar(50) NOT NULL,
sp_address2 varchar(50) NOT NULL,
sp_city varchar(35) NOT NULL,
sp_state char(2) NOT NULL,
sp_zip varchar(10) NOT NULL,
setup_on varchar(6),
product varchar(50),
product_qty int(4),
product_price decimal(7,2),
total decimal(7,2),
total_cost decimal(7,2),
PRIMARY KEY (id),
UNIQUE id (id),
KEY id_2 (id),
KEY rental_agree_numb (rental_agree_numb)
);

There are a few other columns there, because I have been working on other parts of this to accomodate for everything I need, but I'll worry about those later.

I'm gonna use:
$query="SELECT MAX(rental_id) as rental_id FROM rentals";
$result=mysql_query($query);
$rental_id=mysql_result($result,$i,'rental_id');
$rental_id = $rental_id + 1;

to increment $rental_id

[mgiroir]

It is now writing to the rentals table during each loop. I'm using the same code I gave earlier, so that we can see the same code. I will learn this better that way. Current code from insert.php follows:

<?
include("dbinfo.inc.php");
include("conx.php");

$customerid=$_POST['customerid'];
$eventdate=$_POST['eventdate'];
$starteventdate=$_POST['starteventdate'];
$starteventtime=$_POST['starteventtime'];
$endeventdate=$_POST['endeventdate'];
$endeventtime=$_POST['endeventtime'];
$delivery=$_POST['delivery'];
$deliverytime=$_POST['deliverytime'];
$setupon=$_POST['setupon'];

$query="SELECT MAX(rental_id) as rental_id FROM rentals";
$result=mysql_query($query);
$rental_id=mysql_result($result,$i,'rental_id');
$rental_id = $rental_id + 1;

echo "<table border=1 width=100%><tr>";

foreach($equip as $key => $equipmentid){
$query="SELECT e.equipmentid, e.equipmentname, e.equiprentalprice FROM equipment e WHERE equipmentid='$equipmentid'";
$result=mysql_query($query);
$num=mysql_numrows($result);

while ($row = mysql_fetch_assoc($result)) {
echo $row["equipmentid"];
echo $row["equipmentname"];
echo $row["equiprentalprice"];
$product = $row["equipmentname"];
$productprice = $row["equiprentalprice"];
print_r($row);
}

$i=0;
while ($i < $num) {
$equipmentid=mysql_result($result,$i,"equipmentid");
$equipmentname=mysql_result($result,$i,"equipmentname");
$equiprentalprice=mysql_result($result,$i,"equiprentalprice");

echo "<td align=left valign=top><font face=Arial, Helvetica, sans-serif size=-1>";
echo "$equipmentid<BR></font>";
echo "$equipmentname<BR></a>";
echo "$equiprentalprice<BR>";

echo "</font></td>";
echo "</tr></table>";

print_r($equip);
echo "<BR><BR>";

$query = "INSERT INTO rentals VALUES ('','$rental_id', '$customer_id', '$rental_agree_numb', '$event_date', '$start_event_date', '$start_event_time', '$end_event_date', '$end_event_time', '$delivery', '$delivery_time', '$delivery_charge', '$sp_address1', '$sp_address2', '$sp_city', '$sp_state', '$sp_zip', '$setup_on', '$product', '$product_qty', '$product_price', '$total', '$total_cost')";
mysql_query($query);

$i++;
}
}

echo "Information Added Sucessfully...<BR><BR>";
include("rental_menu.php");

mysql_close();
?>

[edited by: jatar_k at 11:12 pm (utc) on April 6, 2005]

[mgiroir]

Ok, I re-adjusted alot of things to accomodate for the extra things that I needed. Everything appears to be working fine, but I cannot figure out how to group the results per rental id. I have an index page that is supposed to list all upcoming rentals. The SELECT at the top of this page is:

$query="select * from rentals left join customers on rentals.customer_id = customers.customer_id WHERE event_date >= '$today' ORDER BY event_date";

Now, this does list all upcoming rentals based on date equal to or greater than today. However it displays a row for each of the rows created in the rentals table... another words the rental listed with a rental_id of 1 has like four entries in the table (one for each item selected). How can I group this. Should I query for only the rental_id's and throw the results of this query into an array then foreach thru the array while I query the table per rental_id?

[dmmh]

I think you could best echo out the rental id and all the stuff which stays the same per rental, once and do a for loop for the products

[dmmh]

im to damned tired to do some proper thinking atm, but Ive used this on my site somewhere, ill get back on it :)

[dmmh]

well, come to think of it, your option seems the easiest yes, adjusting the queries seems the easiest thing to do

[mgiroir]

I finally did get this work, by using a foreach to go thru the array and query the database for the rental_id. I then used:

$k=0;
while ($k < $num) {
$product=mysql_result($result,$k,"product");
$product_qty=mysql_result($result,$k,"product_qty");
$product_price=mysql_result($result,$k,"product_price");

echo "<tr><td align=right><font face=Arial, Helvetica, sans-serif size=-1>($product_qty)</td><td align=left><font face=Arial, Helvetica, sans-serif size=-1> - $product</td><td align=right><font face=Arial, Helvetica, sans-serif size=-1>$product_price</td></tr>";

$k++;
}

to list all the equipment that was rented. I do have a problem though. When using this, the equipment listed, is listed backwards. Another words, if it were in the table in the following order:

tables
chairs
game
game

it would then be listed on the index page in this order:

game
game
chairs
tables

any idea on how this may be fixed, so that the equipment is listed in the same order as it resides in the database. I use the following query in the foreach:

$query="select * FROM rentals left join customers on rentals.customer_id = customers.customer_id WHERE rental_id = '$rental_id' ORDER BY event_date";
$result=mysql_query($query);

Any ideas?

[mgiroir]

Well, after several hours of searching thru code, I realized that I had added ORDER BY event_date to the sql statement.

[dmmh]

havent been here, sorry, glad you got it fixed. any thing else you wanne know perhaps?

[mgiroir]

just trying to change everything around right now, so that all scripts work. I don't have any specific questions at the moment but I definitely appreciate the offer. After I make sure everything is working correctly, I will try to post as much of the code as possible to help anyone else having similar issues.

Your help was invaluable.

[dmmh]

glad I could help out, gl with the business :)