PHP - Mysql Update Will Not Work

Hi I have the file "pback.php" which displays a mysql table.
One row allows me to update the data.
If I press "update it leads me to update.php, but there I just get white fields instead of the data from the id requested.
If I type anything in the field and press confirm it opens update_ac but just says error.
What do I do wrong?
Thanks!
Quote

pback.php

Code: [Select]
<?php
$host="xxx"; // Host name
$username="xxx"; // Mysql username
$password="xxx"; // Mysql password
$db_name="xxx"; // Database name
$tbl_name="sp_users"; // 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 ORDER BY user_id";
$sql="SELECT * from sp_users,sp_schools where sp_users.user_id=sp_schools.school_id ORDER BY school_name";
$result=mysql_query($sql);
$num_rows=mysql_num_rows($result);
?>
<style type="text/css">
<!--
.style2 {font-weight: bold}
.style3 {
font-family: Arial, Helvetica, sans-serif;
color: #000000;
}
.style10 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; color: #000000; }
#Layer1 {
position:absolute;
left:803px;
top:36px;
width:65px;
height:28px;
z-index:1;
}
#Layer2 {
position:absolute;
left:707px;
top:19px;
width:143px;
height:39px;
z-index:1;
}
-->
</style>

<title>User overview</title>
</html>
<div id="Layer2">
  <form action="<?= $_SERVER['PHP_SELF'] ?>" method="post">
<input type="hidden" name="foo" value="<?= $foo ?>" />
<input type="submit" name="submit" value="Refresh" />
</form></div>
<table width="779" border="0" align="left" cellpadding="0" cellspacing="1" bgcolor="#996600">
<tr>
<td width="777">
 
    <div align="left">
      <table width="779" border="1" cellspacing="0" cellpadding="3">
        <tr>
          <td colspan="6" align="center"><div align="center" class="style1 style3"><strong>SchoolPorta.com Users / Total: <?php echo $num_rows ?></strong></div></td>
          </tr>
        <tr>
          <td width="342" align="center"><span class="style2">school</span></td>
          <td width="62" align="center"><span class="style2">Name</span></td>
          <td width="104" align="center"><span class="style2">Lastname</span></td>
          <td width="130" align="center"><span class="style2">Email</span></td>
      <td width="64" align="center"><span class="style2">Update</span></td>
        </tr>
        <?php
while($rows=mysql_fetch_array($result)){
?>
        <tr>
          <td><span class="style10"><? echo $rows['school_name']; ?></span></td>
  <td><span class="style10"><? echo $rows['user_first_name']; ?></span></td>
          <td><span class="style10"><? echo $rows['user_surname']; ?></span></td>
          <td><span class="style10"><a href="mailto:<?php echo $rows['user_login']; ?>"><?php echo $rows['user_login']; ?></a></span></td>
          <td align="center"><a href="update.php?id=<? echo $rows['user_id']; ?>" class="style10">update</a></td>
        </tr>
        <?php
}
?>
      </table>
    </div></td>
</tr>
</table>

<div align="left">
  <p>&nbsp;   </p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>
    <?php
mysql_close();
?>
  </p>
</div>
Quote

update.php

Code: [Select]
<?php

$host="xxx"; // Host name
$username="xxx"; // Mysql username
$password="xxx"; // Mysql password
$db_name="xxx"; // Database name
//$tbl_name="sp_users"; // 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['user_id'];

?>
<?php
$sql="SELECT * from sp_users,sp_schools where sp_users.user_id=sp_schools.school_id";
$result=mysql_query($sql) or die("Problem with the query: $sql<br>" . mysql_query());
?>
<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"><div align="center" class="style1">Update data in mysql </div></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"><span class="style7">Name</span></td>
<td align="center"><span class="style7">Lastname</span></td>
<td align="center"><span class="style7">Email</span></td>
</tr>
<tr>
<td>&nbsp;</td>
<td align="center"><input name="name" type="text" id="name" value="<? echo $rows['user_first_name']; ?>"></td>
<td align="center"><input name="lastname" type="text" id="lastname" value="<? echo $rows['user_surname']; ?>" size="15"></td>
<td><input name="email" type="text" id="email" value="<? echo $rows['user_login']; ?>" size="15"></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input name="id" type="hidden" id="id" value="<? echo $rows['user_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();

?>
</body>
</html>
Quote

update_ac.php

Code: [Select]
<?php
$host="xxx"; // Host name
$username="xxx"; // Mysql username
$password="xxx"; // Mysql password
$db_name="xxx"; // Database name
$tbl_name="sp_users"; // 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 name='$user_name', lastname='$user_lastname', email='$email' WHERE id='$user_id'";
$result=mysql_query($sql);

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

else {
echo "ERROR";
}

?>

Can anyone post a generic update function to update mysql table.
The manual approach:

update $tablename
set $column1='a', $column2='b'
where $id=$value;

Hi all me again thank god for sites like this

here is my prob

i have a page that edits then sends the info to a processing page to update the db but after i added a new bit i get a error

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order = '6', bi = '1', print = '1', online = '1', short_details = 'asdasda', ful' at line 1

im using get in my forms till its all working so i can see what is being passed to the processing page here is my code


the update page


<?
//Set no caching
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");
?>
<? include("protect/password_protect.php"); ?>
<?php
error_reporting(E_ALL);
include ("../includes/db_config.php");

mysql_connect($db_hostname,$db_username,$db_password);
@mysql_select_db($db_database) or die( "Unable to select database");
$query="SELECT * FROM `$db_table` WHERE `id` ='".$_GET['id']."'";

$result=mysql_query($query);

$num=mysql_num_rows($result);

mysql_close();


$i=0;
while ($i < $num) {

$id=mysql_result($result,$i,"id");
$order=mysql_result($result,$i,"order");
$pro_name=mysql_result($result,$i,"pro_name");
$short_details=mysql_result($result,$i,"short_details");
$full_details=mysql_result($result,$i,"full_details");
$bi=mysql_result($result,$i,"bi");
$print=mysql_result($result,$i,"print");
$online=mysql_result($result,$i,"online");


?>
<table width="515" height="315" >
<form name="form1" method="get" action="runupdate.php?id=<? echo"$id"?>">
  <tr>
    <th width="22%" scope="row"><p>Id</p></th>
    <td width="78%"><input class="udp" name="id" type="text" id="id" value="<? echo "$id"  ?>" size="3" maxlength="3" readonly="readonly"> Ordering:
 <select name="order">
 <option value="<? echo"$order"?>" selected>Is Now<? echo"$order"?></option>
    <option value="1">1</option>
    <option value="2">2</option>
    <option value="3">3</option>
    <option value="4">4</option>
    <option value="5">5</option>
    <option value="6">6</option>
    <option value="7">7</option>
    <option value="8">8</option>
    <option value="9">9</option>
    <option value="10">10</option>
  </select>
</td>
  </tr>
  <tr>
    <th scope="row"><p>Project Name</p></th>
    <td><input class="udp" name="pro_name" type="text" id="pro_name" value="<? echo "$pro_name"  ?>" size="40" MAXLENGTH="40"></td>
  </tr>
  <tr>
    <th scope="row"><p>Category</p></th>
    <td> Brand Identity:<input <?php if (!(strcmp("$bi",1))) {echo "checked=\"checked\"";} ?> name="bi" type="checkbox"  value="1" />
    Print:<input <?php if (!(strcmp("$print",1))) {echo "checked=\"checked\"";} ?> name="print" type="checkbox"  value="1" />
    Online:<input <?php if (!(strcmp("$online",1))) {echo "checked=\"checked\"";} ?> name="online" type="checkbox"  value="1" /></td>
  </tr>
  <tr>
    <th scope="row"><p>Short Details</p></th>
    <td><input class="udp" name="short_details" type="text" value="<? echo "$short_details"  ?>" size="50" MAXLENGTH="60"/></td>
  </tr>
  <tr>
    <th height="163" valign="top" scope="row"><p>Full Details</p></th>
    <td><textarea class="udp" name="full_details" cols="50" rows="10" id="full_details"><? echo "$full_details"?>
    </textarea>
      <input class="udp" type="submit" name="Update" id="Update" value="Update" /></td>
  </tr>
  <tr>
    <th scope="row">
    </form></th>
    </tr>
</table>
<? 
$i++;
}
?>


and this is the processing page
can any one help with the sql injection protection here please just an example will do thanks.



<?php
error_reporting(E_ALL);

include ("../includes/db_config.php");

$con = mysql_connect($db_hostname,$db_username,$db_password);
@mysql_select_db($db_database) or die( "Unable to select database");

$bi=mysql_result($result,$i,"bi");
$print=mysql_result($result,$i,"print");
$online=mysql_result($result,$i,"online");
$ud_pn=$_GET['pro_name'];
$ud_sd=$_GET['short_details'];
$ud_fd=$_GET['full_details'];
$ud_bi=$_GET['bi'];
$ud_print=$_GET['print'];
$ud_online=$_GET['online'];
$ud_order=$_GET['order'];


//// when i add order = '$ud_order' to the query it errors and i dont no why as it is layed out the same as pro_name and bi and print ect.

$query = "UPDATE $db_table SET pro_name = '$ud_pn', order = '$ud_order', bi = '$ud_bi', print = '$ud_print', online = '$ud_online', short_details = '$ud_sd', full_details = '$ud_fd' WHERE id = '".$_GET['id']."'";

if (!mysql_query($query,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "$ud_pn record updated<p>With:<p>Short Details:<p>$ud_sd<p>Full Details:<p>$ud_fd<p>Category:<p>Brand Identity:$ud_bi</br>
Print:$ud_print</br>
Online:$ud_online</br><p>
Ordering:$ud_order</br> ";





mysql_close($con)

?>

Hi guys,

I have an update page where users can update their information, i have a different update page which works fine with almost the same code, but this one wont update anything.

it does show the data but it wont update it,
can u help please?



<?php
session_start();

include ("../../global.php");
//welcome messaage
$username=$_SESSION['username'];
echo "$username";

$query=mysql_query("SELECT id FROM users WHERE username='$username'");
while($row = mysql_fetch_assoc($query))
{
$user_id = $row['id'];
}



$ref=$_GET['reference'];


if (isset($_POST['register']) && $_POST['register']){



$title = addslashes(strip_tags($_POST['title']));






$update=mysql_query("UPDATE msg SET title ='$title' WHERE reference='$ref'");






}



?>



<html>



<head>
    </head>



<body>



<form action='edit-msgs.php' method='POST' enctype='multipart/form-data'>



  Title:<br />
    <input type='text' name='title'  id='title' value='<?php 



$getdata = "SELECT title FROM msg WHERE reference='$ref' AND referal_id='$user_id'";
$result = mysql_query($getdata);
$row = mysql_fetch_assoc($result);



echo $row['title'];?>'><p />




    <input type='submit' name='register' value='Update'>
</form>
</body>
</html>




   

Sorry I'm still new to php. So I'm trying to get my update statement to work. I will post the statement I have for it, I will also post sql code from the database it is trying to update and I will also post my form page. Appreciate any help, thanks.


Here is my update statement...


Code: [Select]
<?php

if (isset($_POST['update']))
{          
foreach($_POST['update'] as $update_id)
{             
$query = "UPDATE FROM users WHERE course_id = $update_id";             
mysqli_query($dbc, $query) or die ('can\'t update course');          

$course_id  = $_POST['course_id'];
$course_name = $_POST['course_name'];
$student_id = $_POST['student_id'];
$sql = "INSERT INTO users (course_id, course_name, student_id) VALUES ('$course_id', '$course_name', '$student_id');"; 
$db->exec($sql);

}
echo 'course has been updated.<br />';   
}

?>


Here is the page that the update statement is on...


Code: [Select]
<html>
<head>
<title>Update User</title>
</head>
<body>
<form method="post" action="update_user2.php">
<?php
$dbc = mysqli_connect('localhost', 'se266_user', 'pwd', 'se266') 
or die(mysql_error());
//delete users
echo '<b>Delete or Update User</b>.<br />';
if (isset($_POST['remove']))
{          
foreach($_POST['delete'] as $delete_id)
{             
$query = "DELETE FROM users WHERE course_id = $delete_id";             
mysqli_query($dbc, $query) or die ('can\'t delete course');          
}        
echo 'user has been deleted.<br />';   
}

if (isset($_POST['update']))
{          
foreach($_POST['update'] as $update_id)
{             
$query = "UPDATE FROM users WHERE course_id = $update_id";             
mysqli_query($dbc, $query) or die ('can\'t update course');          

$course_id  = $_POST['course_id'];
$course_name = $_POST['course_name'];
$student_id = $_POST['student_id'];
$sql = "INSERT INTO users (course_id, course_name, student_id) VALUES ('$course_id', '$course_name', '$student_id');"; 
$db->exec($sql);

}
echo 'course has been updated.<br />';   
}

//display users info with checkbox to delete  
$query = "SELECT * FROM users";  
$result = mysqli_query($dbc, $query);  
while($row = mysqli_fetch_array($result))
{      
echo '<input type="checkbox" value="' .$row['course_id'] . '" name="delete[]" />'; 
echo ' ' .$row['course_name'] .' '. $row['student_id'];
echo '<br />';  
}
mysqli_close($dbc);

?>
<input type="submit" name="remove" value="Remove" />
<input type="submit" name="update" value="Update" />
<br>
<br>
<form method="GET" action="update_user2.php">
<label for="course_id">Course ID:</label>
<input type="text" id="course_id" name="course_id" /><br />
<label for="course_name">Course Name:</label>
<input type="text" id="course_name" name="course_name" /><br />

<label for="course_name">Student ID:</label>

<input type="text" id="student_id" name="student_id" /><br />



</form>
</body>
</html>


Here is my sql database...

Code: [Select]
DROP DATABASE IF EXISTS se266;
CREATE DATABASE se266;


use se266;
CREATE TABLE users (
  course_id        INT(11)        NOT NULL   AUTO_INCREMENT,
  course_name       VARCHAR(50),
  student_id     VARCHAR(50)    NOT NULL,

  PRIMARY KEY (course_id)
);
-- create the users and grant priveleges to those users
GRANT SELECT, INSERT, DELETE, UPDATE
ON se266.*
TO se266_user@localhost
IDENTIFIED BY 'pwd';

INSERT INTO users (course_id, course_name, student_id)
VALUES ('se255', 'Web Design Using PHP', '43253256');
use se266;
SELECT * FROM users;