...

View Full Version : PHP/MySQL editing (strange result)



tobiasf
07-14-2012, 11:56 AM
What is wrong? I have information "Successful" but nothing is changed in MySQL table :(

http://krajeto.linuxpl.info/panel1/list_records1.php

list_records1


<?php
$host="localhost"; // Host name
$username="****"; // Mysql username
$password="***"; // Mysql password
$db_name="**"; // Database name
$tbl_name="***"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
?>

<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4"><strong>List data from mysql </strong> </td>
</tr>

<tr>
<td align="center"><strong>imie</strong></td>
<td align="center"><strong>nazwisko</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>Update</strong></td>
</tr>

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

<tr>
<td><? echo $rows['imie']; ?></td>
<td><? echo $rows['nazwisko']; ?></td>
<td><? echo $rows['email']; ?></td>

// link to update.php and send value of id
<td align="center"><a href="update1.php?id=<? echo $rows['id']; ?>">update</a></td>
</tr>

<?php
}
?>

</table>
</td>
</tr>
</table>

<?php
mysql_close();
?>

update1.php



<?php
$host="localhost"; // Host name
$username="****"; // Mysql username
$password="***"; // Mysql password
$db_name="**"; // Database name
$tbl_name="***"; // Table name
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id that sent from address bar
$id=$_GET['id'];

// Retrieve data from database
$sql="SELECT * FROM $tbl_name WHERE id='$id'";
$result=mysql_query($sql);
$rows=mysql_fetch_array($result);
?>

<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<form name="form1" method="post" action="update_ac1.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>&nbsp;</td>
<td colspan="3"><strong>Update data in mysql</strong> </td>
</tr>
<tr>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
</tr>
<tr>
<td align="center">&nbsp;</td>
<td align="center"><strong>imie</strong></td>
<td align="center"><strong>nazwisko</strong></td>
<td align="center"><strong>Email</strong></td>
</tr>
<tr>
<td>&nbsp;</td>
<td align="center">
<input name="imie" type="text" id="imie" value="<? echo $rows['imie']; ?>">
</td>
<td align="center">
<input name="nazwisko" type="text" id="nazwisko" value="<? echo $rows['nazwisko']; ?>" size="15">
</td>
<td>
<input name="email" type="text" id="email" value="<? echo $rows['email']; ?>" size="15">
</td>
</tr>
<tr>
<td>&nbsp;</td>
<td>
<input name="id" type="hidden" id="id" value="<? echo $rows['id']; ?>">
</td>
<td align="center">
<input type="submit" name="Submit" value="Submit">
</td>
<td>&nbsp;</td>
</tr>
</table>
</td>
</form>
</tr>
</table>

<?php
// close connection
mysql_close();
?>

update_ac1.php


<?php
$host="localhost"; // Host name
$username="****"; // Mysql username
$password="***"; // Mysql password
$db_name="**"; // Database name
$tbl_name="***"; // Table name
Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// update data in mysql database
$sql="UPDATE $tbl_name SET imie='$imie', nazwisko='$nazwisko', email='$email' WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated.
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='list_records1.php'>View result</a>";
}

else {
echo "ERROR";
}

?>

tangoforce
07-14-2012, 12:53 PM
Using $result=mysql_query() will only tell you that the SQL ran successfully and not that it actually worked.

To determine if the SQL actually worked you need to use mysql_affected_rows() which will return the number of rows affected, updated, deleted etc. Link (http://uk3.php.net/manual/en/function.mysql-affected-rows.php). Note that it will return 0 if nothing was updated which PHP will treat as a false / boolean result. Anything else is 1 or more and treated as a true.

This is how you may want your code to look:


$sql="UPDATE $tbl_name SET imie='$imie', nazwisko='$nazwisko', email='$email' WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated.
if(($result) and (mysql_affected_rows($result))){
echo "Successful";

tobiasf
07-14-2012, 03:11 PM
Thank you :) But what can be wrong. I have something like that as error


Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in /home/krajeto/domains/krajeto.linuxpl.info/public_html/panel1/update_ac.php on line 17
ERROR

tangoforce
07-14-2012, 03:46 PM
Go into phpmyadmin and click the SQL tab in your database. Paste in your SQL there and then replace the variables with some text. Submit the SQL and see if it gives you an error message.

Post back here for someone to help with (sorry I'm about to go to work so won't be able to reply until tomorrow)

Fou-Lu
07-14-2012, 05:48 PM
This parameter is actually incorrect: mysql_affected_rows($result). Mysql_affected_rows (like mysql_insert_id) doesn't actually operate at a query level, it operates at a connection level.
So you can either provide it with no argument which will use the last opened connection as its default, or you can pass it an explicit connection. You haven't assigned an explicit connection to the mysql_connect result, so you either have to add a variable to assign that to and use that variable, or just leave it void.


BTW, its also possible that its simply issuing a violation constraint for keys. You haven't declared any of these variables, so it will attempt to update with an empty string.

tobiasf
07-15-2012, 08:47 AM
Fou-Lu, you had right :) :) Thank you very much :)

I have another problem. I would like to delete records with php. I added code but it doesn't work. Do you know why?


<?php

$host="localhost"; // Host name
$username="******"; // Mysql username
$password="*******"; // Mysql password
$db_name="*******"; // Database name
$tbl_name="****"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql="INSERT INTO lista_atol2 SELECT * FROM zakwalifikowani2 WHERE akademik='Atol' ";


$sql1="SELECT * FROM $tbl_name ";

$result=mysql_query($sql);

?>

<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4"><strong>List kwaterunkowa Atol </strong> </td>
</tr>

<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center"><strong>imie</strong></td>
<td align="center"><strong>nazwisko</strong></td>
<td align="center"><strong>adres</strong></td>
<td align="center"><strong>nr_niu</strong></td>
<td align="center"><strong>p</strong></td>
<td align="center"><strong>mail</strong></td>
<td align="center"><strong>Pokoj</strong></td>
<td align="center"><strong>Aktual.</strong></td>
</tr>

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

<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
<td><? echo $rows['imie']; ?></td>
<td><? echo $rows['nazwisko']; ?></td>
<td><? echo $rows['adres']; ?></td>
<td><? echo $rows['nr_niu']; ?></td>
<td><? echo $rows['plec']; ?></td>
<td><? echo $rows['email']; ?></td>
<td><? echo $rows['pokoj']; ?></td>
<td align="center"><a href="update.php?id=<? echo $rows['id']; ?>">aktualizuj</a></td>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>

<?php

if($delete){
for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i];
$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
$result = mysql_query($sql);
}
// if successful redirect to delete_multiple.php
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete_list_records1.php\">";
}
}

}

?>

</table>
</td>
</tr>
</table>

<?php
mysql_close();
?>

tangoforce
07-15-2012, 01:18 PM
This parameter is actually incorrect: mysql_affected_rows($result). Mysql_affected_rows (like mysql_insert_id) doesn't actually operate at a query level, it operates at a connection level.

Sounds about right.. Thats the second blunder I've made this week - I'm suffering with man-flu...

@tobias: For future ref, you can always look up functions yourself easily on php.net to find out their parameters and check things:

www.php.net/<funcion_name> - eg:
www.php.net/mysql_affected_rows

That would take you straight to the page that tells you about it and how to use the parameters. I did that yesterday and got in a tizz-wozz with the parameter however its easy to double check it :thumbsup:

Apologies for the confusion..

Fou-Lu
07-15-2012, 04:56 PM
Yep, that's an easy error to make and one I've done many times.

The new code doesn't declare many of the required variables:
$delete
$count
$checkbox

You can use $_POST['checkbox'] or $_GET['checkbox'], but you'll need to create a form first. Ignore the $delete and just use the isset check on the $_POST/$_GET checkbox instead; checkboxes are only supplied if at least one option was checked.
$count is presumably that of the number of boxes checked. Don't bother with this either as looping is slow, simply change the query to use an IN clause.


if (isset($_POST['checkbox']))
{
$achkd = array_map($_POST['checkbox'], 'intval');
$sql = 'DELETE FROM ' . $tbl_name . ' WHERE id IN (' . implode(', ', $_POST['checkbox']) . ')';
}

And go from there.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum