Update multiple tables help....

[googlit]

Hi guys, im looking for some help with a little project.

 

i have several tables relating to products and their attributes (price etc).

 

i have created a query that displays the data that i need but i want to be able to edit multiple price fields and then submit the update back to the tables correctly. 

 

i have tried several tutorials but cant get it to work.

 

this is my basic query code any help would be a life saver....

 

Thanks 

<?php
$username="username";$password="tpassword";$host="localhost";
$database="table";mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$sql = "SELECT ps_product.id_product, ps_product.price, ps_product.available_for_order, ps_product_lang.id_product, ps_product_lang.name FROM ps_product, ps_product_lang, ps_manufacturer WHERE ps_product.id_product = ps_product_lang.id_product AND ps_product_lang.id_lang = '2' ";
?>

<table width="500">
<tr>
<td width="300"><b>Product</b></td>
<td><b>Price</b></td>
  </tr>
  </table>
				  
<?php
$sql = mysql_query($sql) or die(mysql_error());

if(mysql_num_rows($sql) > 0){
?>
    
    <?php
        while ($row_sql = mysql_fetch_assoc($sql)){ 
    ?>
	
            <?php echo 
				'<table width="500">
				  <tr>
					<td width="300">'.$row_sql["name"].'</td>
					<td>'.$row_sql["price"].'</td>
				  </tr></table>';
			
			?>
    <?php
        }      
    ?>
   

<?php
}
?>

[bsmither]

"I want to be able to edit multiple price fields and then submit the update back to the tables."

 

This implies you need a form. Within the form, you use input elements. The inputs will be text type elements with the existing value coming from $row_sql["price"]

 

The element name must also contain a reference as to which row from the table the price came from. So, perhaps use name="product_price['.$row_sql["id_product"].']"

 

There is a redundancy because the query may be returning both: ps_product.id_product and ps_product_lang.id_product. (I don't know if that is true.) They are the same value so you don't need both in the list of columns in the query. Remove ps_product_lang.id_product. It can still be used elsewhere in the query.

 

Let us know when you have coded the form.

Edited April 23, 2014 by bsmither

[googlit]

Thanks for the help so far....
 
this is what i have. i have managed to get the form set up but i cannot get it to submit the data to the DB.
 
(code below)
 
im getting this error also:
 
 
Notice: Undefined index: name in [/size]C:\xampp\htdocs\query.php on line [/size]49

Notice: Array to string conversion in [/size]C:\xampp\htdocs\query.php on line [/size]56
Could not enter data: Unknown column 'Array' in 'field list'[/size]
 
 
Once again thanks for any help, its very appreciated. 
 
 
 
 
 
 
 

<?php
$username="****";$password="*****";$host="localhost";
$database="****";
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$sql = "SELECT ps_product.id_product, ps_product.price, ps_product_lang.name FROM ps_product, ps_product_lang WHERE ps_product.id_product = ps_product_lang.id_product";
?>

<table width="500">
<tr>
<td width="300"><b>Product</b></td>
<td><b>Price</b></td>
  </tr>
  </table>
				  
<?php
$sql = mysql_query($sql) or die(mysql_error());

if(mysql_num_rows($sql) > 0)
{
	while ($row_sql = mysql_fetch_assoc($sql))
	{ 
     echo'<form action="" method="post">
			<table width="500">
			  <tr>
				<td width="300">'.$row_sql["name"].'</td>
				<td><input name="price['.$row_sql["id_product"].']" type="text" id="name" value="'.$row_sql["price"].'"></td>
				<td><input name="update" type="submit" id="update" value="Update"></td>
			  </tr>
			 
			</table>
		</form>';
    
     }      
}
?>

<?php

if(isset($_POST['update']))
{
	mysql_connect($host,$username,$password);
	@mysql_select_db($database) or die( "Unable to select database");	
}

   $name = $_POST['name'];
   $price = $_POST['price'];


$update = "INSERT INTO ps_product ".
	" (price) ".
	" VALUES ".
	"($price)";
	
$retval = mysql_query($update);
if(! $retval )
{
  die('Could not enter data: ' . mysql_error());
}	
echo "Entered data successfully\n";
mysql_close();


?>

[bsmither]

Good start. Let's look at a few things:

 

For every iteration of the while loop (mysql_fetch_assoc), you are creating a form and a table. By creating x-number of forms, each with it's own distinct inputs, you will only be able to Submit one form at a time - that is, one record at a time. Your task is to be able to update many records at a time. Take the <form> and <table> outside the loop, leaving just the <tr> and <td> inside. Also, move the Submit input outside the table structure but still inside the form block.

 

Look again at the names of the form's input elements. Only those names will appear in the POST array. So, the statement $name = $_POST['name']; is causing one error. Please determine if, at this point, you need $name for anything.

 

Again, look carefully at the names of the form's input elements. Hand-write what you think the final HTML will be for the input's name attribute. We have created an array of inputs, so the POSTed result will also be an array.

 

If it helps, have your browser show you the source HTML it received. (You will need to discover how you would get the browser you are using to show the source.)