Jump to content

Archived

This topic is now archived and is closed to further replies.

WhiteBlade

[Solve] Updating sql table not working

Recommended Posts

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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
USE an UPDATE query

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

Share this post


Link to post
Share on other sites
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  ??? ??? :( :(

Share this post


Link to post
Share on other sites
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 !!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.