Jump to content

[Solve] Updating sql table not working


WhiteBlade

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.