MySQL update not working

[mortalex]

Hey, i'm new here and i need help.

I'm trying to write an update query so i can update mulitple items in a database, but it aint working..

Ive used the mysql_error() thingy, but it still does not display any errors. All it does is nothing, the web page doesnt update, and neither does the database in MySQL.

Is the syntax wrong or what?

My code:

<h1>Update Items</h1>
<p>Update items below.</p> 


<?php
$db_host = 'X;
$db_user = 'X';
$db_pwd = 'X';
$database = 'X';
$table = 'pricelist';
if (!mysql_connect($db_host, $db_user, $db_pwd))    
	die("Can't connect to database");
if (!mysql_select_db(X))
    	die("Can't select database");

// sending query
$sql="SELECT * FROM $table";
$result=mysql_query($sql);

// Count table rows 
$count=mysql_num_rows($result);
?>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">
<tr> 
<td>
<table width="500" border="0" cellspacing="1" cellpadding="0">


<tr>
<td align="center"><strong>Id</strong></td>
<td align="center"><strong>Item</strong></td>
<td align="center"><strong>Price</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><? $id[]=$rows['id']; ?><? echo $rows['id']; ?></td>
<td align="center"><input name="item[]" type="text" id="item" value="<? echo $rows['item']; ?>"></td>
<td align="center"><input name="price[]" type="text" id="price" value="<? echo $rows['price']; ?>"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="4" align="center"><input type="submit" name="Update" value="Update"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>
<?php

//define each variable
$item= $_POST['item'][$i];
$price = $_POST['price'][$i];


// Check if button name "Submit" is active, do this 
if($Update){
for($i=0;$i<$count;$i++){
$sql1="UPDATE $table SET item='$item', price='$price' WHERE id='$id[$i]'";
$result1=mysql_query($sql1);
mysql_error();
}
}

if($result1){
header("location:pricelistdata.php");
}
mysql_close();
?>

Cheers.

[fatihpiristine]

change your tags.
<?php //code here ?>

<? $id[]=$rows['id']; ?><? echo $rows['id']; ?>

[mortalex]

Why would that work, i've enabled short tags in the php.cfg file, so why would changing them let it work.
I'll give it a go, but all the stuff is at school, so i'll let you know how it went

Cheers

Oh do you mean to make them all the same tags, not one pair simple version and the other the elongated version?

Could be the case, it's just that i left this pieco of work and came back to it a couple of weeks later... my php style had changed.

[php_daemon]

You've misplaced $item and $price definitions. $i that is used is not defined yet. Try rearranging things:

// Check if button name "Submit" is active, do this 
if($Update){
for($i=0;$i<$count;$i++){
//define each variable
$item= mysql_real_escape_string($_POST['item'][$i]);
$price = mysql_real_escape_string($_POST['price'][$i]);
$sql1="UPDATE $table SET item='$item', price='$price' WHERE id='$id[$i]'";
$result1=mysql_query($sql1);
mysql_error();
}
}

That's not all though. Where does the $id come from? I can't see one in your form, let alone the script. Add a hidden field for each row in the form and pick it up as you do the $item and $price.

Also, note how I used mysql_real_escape_string() to validate the user input.

[edit]
Oh I see where does $id come from. Well, I'd rather go the hidden field way -- it's a lot more efficient.
[/edit]

[genericgene]

Guys, I have this problem witha form- suggestions.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/genericads.com.au/httpdocs/photoads/contact.php on line 15
a major decryption error occurred. [logged]

[fatihpiristine]

wheres your code?? contact.php_?

[mortalex]

Oh I see where does $id come from. Well, I'd rather go the hidden field way -- it's a lot more efficient.

What do you mean the hidden field way? Sorry i'm new to this.

[fatihpiristine]

What do you mean the hidden field way? Sorry i'm new to this.

you can use hidden fields to keep datas which need not to be seen by browsers...

[mortalex]

Ok i've looked into the hidden field suggestion, but really i still don't have a clue as to what to do. Doesn't this only stop it from being shown in the web browser, does it do anything else?

Cheers

[fatihpiristine]

www.w3c.com

[ryan_vietnow]

its just like a textbox which the users does not see.

.

[mortalex]

Ok i've hidden the field, but it's still not working. To be honest i still don't really understand how this hidden field type will help me?

Its connecting to the database, its displaying all the data, but its just not updating the database, is it something to do with the syntax of the update code?

//define each variable
$item= mysql_real_escape_string($_POST['item'][$i]);
$price = mysql_real_escape_string($_POST['price'][$i]);
// Check if button name "Submit" is active, do this
if($Update){
for($i=0;$i<$count;$i++){
$sql1="UPDATE pricelist SET item='$item', price='$price' WHERE id='$id[$i]'";
$result1=mysql_query($sql1);
mysql_error();
}

Cheers

[vidalweb]

[QUOTE = mortalex; 460.588]

Anche con il passare del tempo questo post e vecchio, ma lo faccio ancora la soluzione. Spero che questo possa aiutare qualcuno.

Tanti saluti da vidalweb!
[CODE]
<? php
//************************************************ ************************************************** ***************
$ host = "localhost"; / / Hostname
$ username = "root"; / / username Mysql
$ password = ""; / / MySQL password
$ database = "test"; / / Nome del database

$ link = @ mysql_connect ( "$ host", "$ username", "$ password") or die ( "Errore di connessione:". mysql_error ());
@ mysql_select_db ( "$ database") or die ( "Errore database data di apertura!". mysql_error ());
//************************************************ ************************************************** ****************

/ / Table_name
$ table_name = "oggetto";

if ($ _POST [ 'Aggiorna']) (/ / Se ricevi variabile pulsante Aggiorna.

/ / Seleziona tutti i record di dati nella tabella "oggetto" e metterle in $ risultato.
sql_update $ = "SELECT * FROM $ nome_tabella ORDER BY id ASC";
$ result1 = mysql_query ($ sql_update);

while ($ row = @ mysql_fetch_assoc ($ result1)) (
$ nome = mysql_real_escape_string ($ _POST [ "items_". $ row [id]]);
$ prezzo = mysql_real_escape_string ($ _POST [ "price_". $ row [id]]);

WHERE id nome $ str = "UPDATE elemento di $ table_name = '$', prezzo = '$ prezzo' = '$ row [id]'";

$ update_complete = @ mysql_query ( "$ str", $ link) or die ( "Errore nel inserimento");

) / / Close, mentre

/ / echo "--- Update Complete ---";
##################################################
if (empty ($ update_complete)) (
header ( "Location: prova9.php? id = fatto");
) / / Close if $ update_complete
##################################################

) / / Chiude se $ _POST [ 'Submit']

?>

<? php
/ / Invio query
SQL2 $ = "SELECT * FROM $ nome_tabella ORDER BY id ASC";
$ risultato = mysql_query ($ SQL2);
?>

oggetti Update h1>

oggetti Update di seguito. p>

Id strong> td>
Voce strong> td>
Prezzo strong> td>
tr>

<? php
while ($ rows = mysql_fetch_array ($ result)) (
?>



align="center"> td>
align="center"> " type = "text" value = "<? php echo $ rows [ 'item'];? > "/> td>
align="center"> " type = "text" value = "<? php echo $ rows [ 'prezzo'];? > "/> td>
tr>

<? php

) / / Close, mentre $ righe

?>

td>

tr>

table>

td>

tr>

form>

table>

<? php
/ / Connection Close
@ mysql_close ($ link);
?>
[/ CODE]
VIDALWEB SOLUCTION [/ QUOTE]