Little help in SQL

Status
Not open for further replies.

SaKIB

Active Member
598
2010
12
10
i am trying to edit 3 value from 7 values on a mysql table . But seems not working. Do i need to edit all values ?

using simple update command and using forms to put data.
 
13 comments
I think u should use something like this?

update tablename set column=value where uniquecolumn = value;

I hope you got what I wanted to say? Do it one by one and you will get it :)
 
I am trying but. it says working fine but now . ok here is the codes

Code:
<?php
$host="localhost"; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name="test"; // Database name 
$tbl_name="test_mysql"; // 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>Username</strong></td>
<td align="center"><strong>EMAIL</strong></td>
<td align="center"><strong>PASS</strong></td>
<td align="center"><strong>Update</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td><? echo $rows['username']; ?></td>
<td><? echo $rows['password']; ?></td>
<td><? echo $rows['email']; ?></td>

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



Code:
<?php
$host="localhost"; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name="test"; // Database name 
$tbl_name="test_mysql"; // 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_ac.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>UserName</strong></td>
<td align="center"><strong>Password</strong></td>
<td align="center"><strong>Email</strong></td>
</tr>
<tr>
<td>&nbsp;</td>
<td align="center"><input name="username" type="text" id="username" value="<? echo $rows['username']; ?>"></td>
<td align="center"><input name="password" type="text" id="password" value="<? echo $rows['password']; ?>" 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>

<?

// close connection 
mysql_close();

?>



Code:
<?php
$host="localhost"; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name="test"; // Database name 
$tbl_name="test_mysql"; // 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 username='$username', password='$password', email='$email' WHERE id='$id'";
$result=mysql_query($sql);

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

else {
echo "ERROR";
}

?>


IT SAYS THAT WORK DONE> BUT DOESN"T CHANGE THE VALUE IN MYSQL
 
SET username='$username', should be SET username='".mysql_real_escape_string($_POST['username'])."',

i don't see any $_POST in your update file
 
That is a real mish mash of code. :)

Looks like you are using the same db/table for all of it. You read an id and the user, pswd & email then pass the exact same data to be updated to itself.

Where is anything changing?
 
// get value of id that sent from address bar
$id=intval($_Post['id']);
$username = $_Post['username'];
$password = $_Post['password'];
$email = $_Post['email'];

For inserted/updated data use mysql_real_escape_string
 
I think you only need to update the 'update_ac.php' file:

PHP:
<?php
//I assume this is update_ac.php
$host="localhost"; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name="test"; // Database name 
$tbl_name="test_mysql"; // 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 username="'.mysql_real_escape_string($_POST['username']).'", password="'.mysql_real_escape_string($_POST['password']).'", email="'.mysql_real_escape_string($_POST['password']).'" WHERE id='.intval($_POST['id']);

/*
this query might work aswell ( note the different type of quotes used ) but use the one above
$sql="UPDATE $tbl_name SET username='mysql_real_escape_string($_POST['username'])', password='mysql_real_escape_string($_POST['password'])', email='mysql_real_escape_string($_POST['password'])' WHERE id=intval($_POST['id'])";

*/

/*
    In php these are thesame:

    $cool = "awesome";

    echo "lol this is $cool";  <- this shows : lol this is awesome
    echo 'lol this is '. $cool;  <- this shows : lol this is awesome
*/

/*
    the dot ( . ) acts as glue.
    For example:

    $this = 'Hi';
    $still = ', how';
    $works = ' are you';

    echo $this . $still . $works . 'doing?'; <- This shows : Hi, how are you doing?
    
    so does this btw:
    echo $this.$still.$works.'doing?'; ( the space doesn't matter )
*/

/*
    Now about the $_POST[''] variable I used in your query ( line 14 )
    
    Whenever you submit a form [ remember the <form method="post".. u used in your HTML ? ] to a script, the data you send
    gets stored in the $_POST array.
    
    For example:
    <input type="text" name="wjunction" /> ( let's assume you entered sucks and pressed submit )
    
    it will be handled in the script with:
    $_POST['wjunction']
    
    So if you do this in your script:
    
    echo $_POST['wjunction'];
    
    That will show the word sucks
*/

$result=mysql_query($sql);

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

else {
echo "ERROR";
}

?>
edit:
Added a bit of info as it seems you are quite new with this.
Not sure if what I said is correct, i'm really tired atm :D
 
Last edited:
I started writing this for my site.


Just adapt it for you DB, it seems odd to use three files.

PHP:
<?
	$query = "SELECT * FROM site WHERE active=1";
	$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
	echo "<form action='' method='POST'>"
		."<table cellpadding=2 cellspacing=1 border=1 bgcolor=white align=center width=850>"
		."<tr style='background:lightblue'><th>Project ID</th><th>Project Name</th><th>Total Hits Paid</th><th>Hits Sent</th><th>Site URL</th><th>Active</th><th>Select</th></tr>";

	while($row = mysql_fetch_assoc($result))
		{
		extract($row);
		echo "<tr><td>$id</td><td>$sitename</td><td>$maxhits</td><td>$totalhits</td><td>$siteurl</td><td>$active</td><td><input type='checkbox' name='id' value='$id'></input></td></tr>";
		}
	echo"<tr><td colspan='7' align='right'><input type='submit' name='edit' value='Edit'/></td></tr>";

	if (isset($_POST['edit']))
		{
		$editid = $_POST['id'];
		$query = "SELECT * FROM site WHERE id=$editid";
		$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
		while($row = mysql_fetch_assoc($result))
			{
			extract($row);
			echo "<tr><td>$id</td><td><input type='hidden' name='id' value='$id'><input type=text name=sitename value='$sitename'/></td><td><input type='text' name='maxhits' value='$maxhits'/></td><td><input type=text name='totalhits' value='$totalhits'/></td><td><input type=text name='siteurl' value='$siteurl'/></td><td><input size='2' type=text name='active' value='$active'></td><td></input></td></tr>";
			}
		echo "<tr><td colspan='7' align='right'><input type='submit' name='update' value='Update'/></td></tr>";
		}

	if (isset($_POST['update']))
		{
		$id = $_POST['id'];
		$sitename = $_POST['sitename'];
		$maxhits = $_POST['maxhits'];
		$totalhits = $_POST['totalhits'];
		$siteurl = $_POST['siteurl'];
		$active = $_POST['active'];
		mysql_query("UPDATE site SET sitename='$sitename', maxhits='$maxhits', totalhits='$totalhits', siteurl='$siteurl', active='$active' WHERE id='$id'");
        echo "<meta http-equiv='refresh' content='0;url=''/>";
  		}

	echo "</table>";
?>
 
Status
Not open for further replies.
Back
Top