[Solve] Updating sql table not working

[WhiteBlade]

Ok I made a small grid to display so price on a website and now I'M trying to make a control panel that will update the sql table.

My problem is that it only upgrade the last 2 price out of 6 and it not associating them with the rest of the table it crating a new column with all field empty and just the two price.

I don't know what to do.
My sql table have the fallowing columns
categorie  fabriquant  grandeur  prix_1_gross  nb_gross_paquet  prix_1_paquet

what I need to update are the prix_1_gross  and prix_1_paquet
categorie is all the same (cat1) fabriquant all the same (swarovski)

grandeur changes but need to be the same that before the update
same for nb_gross_paquet

sorry if the name are not clear for you guys, they are in french :P

Here's my code

[code]<?php
if ($REQUEST_METHOD=="POST")
  {
    mysql_query('REPLACE INTO info_prix (prix_1_gross, prix_1_paquet) VALUES ("'.mysql_escape_string($prix_1gross).'", "'.mysql_escape_string($prix_10gross).'")')
          or die(mysql_error());
  }

$sql_prix = "SELECT * FROM info_prix WHERE categorie='cat1' and fabriquant='Swarovski' ORDER BY grandeur";
$fabriquant = mysql_query($sql_prix) or die (mysql_error());
$prix = mysql_query($sql_prix) or die (mysql_error());
$fab=tep_db_fetch_array($fabriquant);
?>

<br>
<div class="Title"></div>
<br>
<form name="aboutusform" method="Post" action="">
<table cellspacing="0">
<td><b><?php echo $fab['fabriquant']; ?></b><br><br></td>
<?php echo "<tr><td width='75'>" . CAT_SIZE . "</td><td width='115'class=info_prix>" . CAT_1GROSS . "</td><td colspan='2' class=info_prix>" . CAT_PAQUET . "</td></tr>\n";
while ($rang=tep_db_fetch_array($prix)) {
echo "<tr>\n";
echo "<td width='75'>" . htmlentities($rang['grandeur']) . "</td>\n";
echo "<td width='115'><input type='text' name='prix_1gross' size='10' value='" . htmlentities($rang['prix_1_gross']) . "$'></td>\n";
echo "<td width='70'>" . htmlentities($rang['nb_gross_paquet']) . "</td>\n";
echo "<td width='82'><input type='text' name='prix_10gross' size='10' value='" . htmlentities($rang['prix_1_paquet']) . "$'></td>\n";
echo "</tr>\n";
}
?>
<tr>
  <td colspan="4" align="right"><br><input type="submit" name="Save" value="Save" style="width: 70px"</td>
</tr>
</form>
</table>[/code]

[karthikeyan_coder]

can you show me the structure of that table here?

[WhiteBlade]

My table is like this

categorie  text
fabriquant  text
grandeur  text
prix_1_gross  decimal(15,2)
nb_gross_paquet  text
prix_1_paquet  decimal(15,2)

like I said for the update categorie is always cat1 and fabriquant always Swarovski

grandeur is never the same but need to stay what it was before the update
same for nb_gross_paquet

the only thing that need an update are prix_1_gross and prix_1_paquet

[Barand]

USE an UPDATE query

[code]UPDATE info_prix
SET
      prix_1_gross = '$prix1',
      prix_1_paquet = '$prix2'
WHERE categorie='cat1' and fabriquant='Swarovski'[/code]

[WhiteBlade]

the update work but not correctly

It updating all the price for the last one I enter.

I probably need to code something with $i=0 but I never did that before so I really don't know how to do it  ??? ??? :( :(

[WhiteBlade]

Anyone know how to program something with $i = 0 and so on ?

I see this at some place in other script but I'M not able to make one that work for me, it just always make some error !!

[Barand]

To update a single record, each record needs to have a unique value by which it can be referenced. The easiest way is to have an auto_incrementing id but any column, or combination of columns that is unique, will do.

[WhiteBlade]

ok so if I add

htmlentities($rang['id'])

and an id table it should work ?

[Barand]

The WHERE clause dictates which records are updated. Son my previous query

[code]UPDATE info_prix
SET
      prix_1_gross = '$prix1',
      prix_1_paquet = '$prix2'
WHERE categorie='cat1' and fabriquant='Swarovski'[/code]

The price is changed in all records with categorie = 'cat1' and fabriquant = 'swarovski'

If each row has a unique id then you can limit the changes to the correct record by

[code]UPDATE info_prix
SET
      prix_1_gross = '$prix1',
      prix_1_paquet = '$prix2'
WHERE id = 'x'[/code]

where x is the value of the id in the record to be updated

[WhiteBlade]

Ok here is what I have now.

The update work just for the last line of my array.
If I do a change on the first line of the arrray the page just refresh and nothing is change in the database, but if I edit the last line it work perfectly this is what I have right now.

[code]<?php
if ($REQUEST_METHOD=="POST")
  {
    tep_db_query("UPDATE info_prix SET prix_1_gross = '$prix_1gross', prix_1_paquet = '$prix_10gross' WHERE id='$id' and categorie='cat1' and fabriquant='Swarovski'")
          or die(tep_db_error());
  }
$sql_prix = "SELECT * FROM info_prix WHERE categorie='cat1' and fabriquant='Swarovski' ORDER BY grandeur";
$fabriquant = tep_db_query($sql_prix) or die (tep_db_error());
$prix = tep_db_query($sql_prix) or die (tep_db_error());
$fab=tep_db_fetch_array($fabriquant);
?>

<br>
<div class="Title"></div>
<br>
<form method="Post" action="">
<table cellspacing="0">
<td><b><?php echo $fab['fabriquant']; ?></b><br><br></td>
<?php echo "<tr>
<td width='5'>Id</td>
<td width='85'>" . CAT_SIZE . "</td>
<td width='115'>" . CAT_1GROSS . "</td>
<td colspan='2'>" . CAT_PAQUET . "</td>
</tr>\n";

while ($rang=tep_db_fetch_array($prix)) {
echo "<tr>\n";
echo "<td width='5'><input type='text' 'name='id' readonly='readonly' style='border-style: none;' size='1' value='" . htmlentities($rang['id']) . "'></td>\n";
echo "<td width='85'>" . htmlentities($rang['grandeur']) . "</td>\n";
echo "<td width='115'><input type='text' name='prix_1gross' size='10' value='" . htmlentities($rang['prix_1_gross']) . "'>$</td>\n";
echo "<td width='70'>" . htmlentities($rang['nb_gross_paquet']) . "</td>\n";
echo "<td width='90'><input type='text' name='prix_10gross' size='10' value='" . htmlentities($rang['prix_1_paquet']) . "'>$</td>\n";
echo "</tr>\n";
}
?>[/code]

[WhiteBlade]

Any idea, I keep trying thing but it still not working :(

[WhiteBlade]

I FINALLY FOUND HOW !!!!

After looking at several source I found how the for work and I thought that it whould be more appropriate that a while so here's my solution :D

Thanks for your help it did guide me to the right thing in the end  ;) ;) ;D



[code]<?php
if ($REQUEST_METHOD=="POST")
  {
for($i=0; $i<count($id); $i++) {
tep_db_query("UPDATE info_prix SET prix_1_gross = " . $prix_1gross[$i] . ", prix_1_paquet = " . $prix_10gross[$i] . " WHERE id= " . $id[$i] . "")
          or die(tep_db_error());
}
  }
$sql_prix = "SELECT * FROM info_prix WHERE categorie='" . $HTTP_GET_VARS['catID'] . "' and fabriquant='Swarovski' ORDER BY grandeur";
$fabriquant = tep_db_query($sql_prix) or die (tep_db_error());
$prix = tep_db_query($sql_prix) or die (tep_db_error());
$fab=tep_db_fetch_array($fabriquant);
?>

<br>
<div class="Title"></div>
<br>
<form method="Post" action="">
<table cellspacing="0">
<td><b><?php echo $fab['fabriquant']; ?></b><br><br></td>
<?php
echo "<tr>
<td></td>
<td width='85'>" . CAT_SIZE . "</td>
<td width='115'>" . CAT_1GROSS . "</td>
<td colspan='2'>" . CAT_PAQUET . "</td>
</tr>\n";
for($i=0; $i<tep_db_num_rows($prix); $i++) {
echo "<tr>\n";
echo "<td width='5'><input type='hidden' 'name='id[]' readonly='readonly' style='border-style: none;' size='1' value='" . tep_db_result($prix,$i,0) . "'></td>\n";
echo "<td width='85'>" . tep_db_result($prix,$i,3) . "</td>\n";
echo "<td width='115'><input type='text' name='prix_1gross[]' size='10' value='" . tep_db_result($prix,$i,4) . "'>$</td>\n";
echo "<td width='70'>" . tep_db_result($prix,$i,5) . "</td>\n";
echo "<td width='90'><input type='text' name='prix_10gross[]' size='10' value='" . tep_db_result($prix,$i,6) . "'>$</td>\n";
echo "</tr>\n";
}

?>
<tr>
  <td colspan="4" align="right"><br><input type="submit" name="Save" value="Save" style="width: 70px"</td>
</tr>
</form>
</table>[/code]