problem creating online inventory table

rek

I am trying to create a table/form where a mysql database table can be updated directly online. The table is called 'inv', and the file is called inv.php, where the form posts to itself updating multiple categories at once whenever the "update" button is clicked.

The editable categories are all input as "text" while an auto-increment category (called 'no') is input as "hidden." Now I can view the table perfectly fine, but I can only update the first row of the table. It seems anything past the first row is not getting posted whenever the "update" button is clicked. Does anyone know how to fix this, or what am I doing wrong, or any better of doing this?
Any help would be appreciated. thanks.

Here is my script:

if(isset($_POST['update']))
		{
			$itemno = $_POST['itemno'];
			$lotshow = $_POST['lotshow'];
			$qty = $_POST['qty'];
			$pm = $_POST['pm'];
			$size = $_POST['size'];
			$conc = $_POST['conc'];
			$vol = $_POST['vol'];
			$location = $_POST['location'];
			$notes = $_POST['notes'];

		$no = $_POST['no'];

		require_once('mysqladmin.php');
		$query = "UPDATE inv SET itemno='$itemno', lotshow='$lotshow', qty='$qty', pm='$pm', size='$size', conc='$conc', vol='$vol', location='$location', notes='$notes' WHERE no='$no'";
		$result = mysql_query($query);


	}
	require_once('mysqladmin.php');
	$query = "SELECT * FROM inv WHERE location != ' ' ORDER BY location";
	$result = mysql_query ($query);

	?>

	<form action=inv.php method=post>
	<center><input type=submit name=update value=Update></center>
	<table border=1 bordercolor=000000 cellpadding=0 cellspacing=0>
	<tr bgcolor=00ff00><td>Item</td>
		<td>Lot</td>
		<td>Qty.</td>
		<td>Size</td>
		<td>Conc.</td>
		<td>Vol.</td>
		<td>Location</td>
		<td>notes</td>
	</tr>


	<?php


	while ($row = mysql_fetch_array ($result, MYSQL_ASSOC))

	{
	?><input type=hidden name=no value="<?php echo "$row[no]";?>">
	<tr><td><input type=text name=itemno value="<?php echo "$row[itemno]";?>"></td>
		<td><input type=text name=lotshow value="<?php echo "$row[lotshow]";?>" size=7></td>
		<td><input type=text name=qty value="<?php echo "$row[qty]";?>" size=3></td>
		<td><input type=text name=size value="<?php echo "$row[size]";?>" size=5></td>
		<td><input type=text name=conc value="<?php echo "$row[conc]";?>" size=6></td>
		<td><input type=text name=vol value="<?php echo "$row[vol]";?>" size=5></td>
		<td><input type=text name=location value="<?php echo "$row[location]";?>" size=7></td>
		<td><input type=text name=notes value="<?php echo "$row[notes]";?>"size=25></td>

	</tr>
	</form>
	<?php

	}

m_tt

Can you show us the 'inv' table structure?

Also i'm a bit confused... do you just UPDATE a current inventory table or are you actually INSERTing new entries?

rek

inv table structure:

Field Type Attributes Null Default Extra Action
no int(4) No auto_increment

nono varchar(10) No

itemno varchar(100) No

lot varchar(100) No

lotshow varchar(15) No

qty decimal(10,0) No 0

pm varchar(10) No

size varchar(10) No

conc varchar(20) No

vol varchar(15) No

location varchar(30) No

mw varchar(40) No

buff varchar(100) No

assay varchar(100) No

notes varchar(100) No

right now, I'm trying to get it to update a current inventory table, but I was going to also add an insert option after this.

m_tt

Ok sorry I just realized what you are doing here...

The reason it's only updating the first record is because you are only telling it to update the first record

When you output the input fields, you are only giving them one name and repeating it over and over.

For example
<input type=text name=lotshow value="<?php echo "$row[lotshow]";?>" size=7>

The name is 'lotshow'... then the next inventory item has the same exact name.

What you need to do is create an array of variables like this:

Instead of:

Then loop these variables in your UPDATE query

Does this make sense? I'll try to get you a better example.....after lunch 😉

rek

Thanks for replying.
I guess I'm not familiar with how to create an array for rows. I have about a thousand rows, so are you suggesting to do lotshow[0], lotshow[1],...lotshow[1000], or is there a way to do that in an $array variable?

m_tt

Originally posted by rek
Thanks for replying.
I guess I'm not familiar with how to create an array for rows. I have about a thousand rows, so are you suggesting to do lotshow[0], lotshow[1],...lotshow[1000], or is there a way to do that in an $array variable?

rek,

Yes that is exactly what i'm saying. Unfortunately, the way you are trying to modify/update your inventory this is the only method that will work.

Then upon clicking the update button you would have to run 1000 queries (for example) -- or however many items you have listed on the page.

I would recommend you either break this down into different pages (ie: 25 items per page), or modify 1 inventory item at a time.

redjim

rek or m@tt,
I am trying to do something like this but what I want to do is list all the items like this:
<td><?php echo $productid;?></td>
<td><?php echo $description;?></td>
<td><a href="ihavenoideawhattoputhere"><?php echo $count;?></a></td>

so that when some one clicks on the $count hyperlink, a text field opens in the next space over on the table and I can update the quantity by typing in newquantity and hitting enter.

I assume that there is going to be some js involved in it.
Do you guys know where I might find something like this??
Thanks, redjim