Jump to content


Photo

[Solve] Updating sql table not working


  • Please log in to reply
11 replies to this topic

#1 WhiteBlade

WhiteBlade
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 16 August 2006 - 06:12 PM

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

<?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>


#2 karthikeyan_coder

karthikeyan_coder
  • Members
  • PipPipPip
  • Advanced Member
  • 201 posts

Posted 16 August 2006 - 06:16 PM

can you show me the structure of that table here?
www.karthi.us

#3 WhiteBlade

WhiteBlade
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 16 August 2006 - 06:21 PM

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

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 16 August 2006 - 10:19 PM

USE an UPDATE query

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 WhiteBlade

WhiteBlade
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 17 August 2006 - 03:14 AM

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  ??? ??? :( :(

#6 WhiteBlade

WhiteBlade
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 17 August 2006 - 08:56 PM

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 !!

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 17 August 2006 - 09:00 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 WhiteBlade

WhiteBlade
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 17 August 2006 - 10:26 PM

ok so if I add

htmlentities($rang['id'])

and an id table it should work ?

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 17 August 2006 - 11:30 PM

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

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

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

UPDATE info_prix 
SET
       prix_1_gross = '$prix1', 
       prix_1_paquet = '$prix2'
WHERE id = 'x'

where x is the value of the id in the record to be updated
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#10 WhiteBlade

WhiteBlade
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 18 August 2006 - 12:10 AM

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.

<?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";
			}
?>


#11 WhiteBlade

WhiteBlade
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 18 August 2006 - 11:08 PM

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

#12 WhiteBlade

WhiteBlade
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 19 August 2006 - 02:51 AM

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



<?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>





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users