updating problem in mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
concatenate_man
Forum Newbie
Posts: 13
Joined: Tue Dec 09, 2003 1:22 am
Location: Australia

updating problem in mysql

Post by concatenate_man »

Hi folks. I have a problem with my code. basically i have three files to update a database table in mysql. the first

Code: Select all

<?php 
$connection = mysql_connect('localhost');

$db = @mysql_select_db(marcat, $connection);
?>

<html>
<head>
	<title>MarCat Buyer Update</title> 
</head>
<body bgcolor="#000000" text="#FFFFFF" link="#0000FF"
     vlink="#000080" alink="#FF0000">
    <p align="center"><img src="images/logo.gif" width="850" height="89"></p>
<p>
<center>
<h2>MarCat Buyer Update Page</h2>
</center>
<p>
<hr>
<form action="modify_buyer_results.php" method="POST">
<table border="1" >
  <tr>
      <td>BUYERS</td>
      <td>
      <?php
      echo '<form><select name=Name>';
      $names = mysql_query("SELECT Name FROM BUYERS ORDER BY Name");
      while ($row = mysql_fetch_array($names))
		&#123;
		  $name_entry=$row&#1111;"Name"];
		  echo "<option value=$name_entry>$name_entry\n";
		&#125;
      echo "</form>";
      ?>
      </td>
      <td>
      <input type="Submit" name="submit" value="Select Buyer">
      </td>
  </tr>
</table>
<p>

<hr><p>


</body>
</html>
connects to the database and lists the 'Name' field of the BUYERS table and allows you to select one "name" and then calls the next file.

the second file.

Code: Select all

<html>
<head>
	<title>MarCat Buyer Update</title> 
</head>
<body bgcolor="#000000" text="#FFFFFF" link="#0000FF"
     vlink="#000080" alink="#FF0000">
    <p align="center"><img src="images/logo.gif" width="850" height="89"></p>
<p>
<center>
<h2>MarCat Buyer Update Page</h2>
</center>
<p>
<hr>
<?php
$connection = mysql_connect('localhost');
$db = @mysql_select_db(marcat, $connection);
$database_name = $_POST&#1111;'Name'];
$query = " SELECT 	*
			FROM BUYERS
			WHERE Name = '$database_name'";
				

$result = mysql_query($query);

$buyers = mysql_fetch_array($result);
mysql_close();		
?>
<form action="buyer_updated.php" method="POST">
<table border="0">
  <tr>
    <td>Name: </td><td><input type="text" name="ud_name" value="<? echo "$buyers&#1111;Name]"?>"></td>
  </tr>
  <tr>
    <td>Address: </td><td><input type="text" name="ud_address" value="<? echo "$buyers&#1111;Address]"?>"></td>
  </tr>
  <tr>
    <td>Phone: </td><td><input type="text" name="ud_phone" value="<? echo "$buyers&#1111;Phone]"?>"></td>
  </tr>
  <tr>
    <td>Company: </td><td><input type="text" name="ud_company" value="<? echo "$buyers&#1111;Company]"?>"></td>
  </tr>
  <tr>
    <td>ABN: </td><td><input type="text" name="ud_abn" value="<? echo "$buyers&#1111;ABN]"?>"></td>
  </tr>
  <tr>
    <td colspan="2"><input type="Submit" value="Update Buyer"></td>
</table>
</form>
<hr>
</body>
</html>
selects all fields in the BUYERS table for the name specified and displays these values in a form that allows you to modify the values, which then calls file three.

file three updates the fields and echo's Record Updated.

Code: Select all

<html>
<head>
	<title>MarCat Buyer Update</title> 
</head>
<body bgcolor="#000000" text="#FFFFFF" link="#0000FF"
     vlink="#000080" alink="#FF0000">
    <p align="center"><img src="images/logo.gif" width="850" height="89"></p>
<p>
<center>
<h2>MarCat Buyer Update Page</h2>
</center><p><hr>
<?php



$database_name = $_POST&#1111;'Name'];
$ud_name=$_POST&#1111;'ud_name'];
$ud_address=$_POST&#1111;'ud_address'];
$ud_phone=$_POST&#1111;'ud_phone'];
$ud_company=$_POST&#1111;'ud_company'];
$ud_abn=$_POST&#1111;'ud_abn'];

$connection = mysql_connect('localhost');
$db = @mysql_select_db(marcat, $connection);

$query="UPDATE BUYERS WHERE Name='$database_name' SET Name='$ud_name' Address='$ud_address' Phone='$ud_phone' Company='$ud_company' ABN='$ud_abn'";
mysql_query($query);
echo "Record Updated";
mysql_close();
?>
<hr>
</body>
</html>
File three doesn't appear to be working, can i call
$database_name = $_POST['Name'];
in the third file from the first file or is there another problem that i can't see..

any help appreciated

cheers,
confused....
User avatar
DuFF
Forum Contributor
Posts: 495
Joined: Tue Jun 24, 2003 7:49 pm
Location: USA

Post by DuFF »

On page 2 add this into the form:

Code: Select all

<input type="hidden" name="database_name" value="<php echo $database_name; ?>"
Then on the third page you can use the variable like this:

Code: Select all

$database_name = $_POST&#1111;'database_name']
concatenate_man
Forum Newbie
Posts: 13
Joined: Tue Dec 09, 2003 1:22 am
Location: Australia

Post by concatenate_man »

Thanks Duff but it still doesn't work, am I missing something or what, cause i can call on the database to view, insert and delete but just can't update it. any help would be appreciated.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

are all the fields strings in your table?

if one is an integer, you shouldn't need the quotes around em..


in any case, instead of this :

Code: Select all

mysql_query($query);
try this

Code: Select all

mysql_query($query) or die(MySQL_Error());
Will at least tell us what's going on.
concatenate_man
Forum Newbie
Posts: 13
Joined: Tue Dec 09, 2003 1:22 am
Location: Australia

Post by concatenate_man »

Hi, i have done as infolock said, I have changed all my field types to varchar's so that shouldn't be a problem and I have added the or die(MySQL_Error()); part to the third file. this is the error message I get
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 'WHERE Name=' ' SET Name='Joe_Cu
to me that still says it is not reading in the name of the record to be changed from this query statement

Code: Select all

$query="UPDATE BUYERS WHERE Name='$database_name' SET Name='$ud_name' 
	Address='$ud_address' Phone='$ud_phone' Company='$ud_company' ABN='$ud_abn'";
from the third file from above.
I can't seem to find an example to show me how to do update a table record from choosing a value from the record. there are plenty that show how to update a record if you specify the value in your code....

Does anyone know how to do this or know of an example I could look at to get fimiliar with...
cheers
Joe
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Indeed the query is wrong. Should be:

Code: Select all

$query="UPDATE BUYERS SET Name='$ud_name',   Address='$ud_address', Phone='$ud_phone' Company='$ud_company', ABN='$ud_abn' WHERE Name='$database_name'";
The order does matter ;)
concatenate_man
Forum Newbie
Posts: 13
Joined: Tue Dec 09, 2003 1:22 am
Location: Australia

Post by concatenate_man »

ok, i have changed the order so that
$query="UPDATE BUYERS SET Name='$ud_name', Address='$ud_address', Phone='$ud_phone' Company='$ud_company', ABN='$ud_abn' WHERE Name='$database_name'";
now i don't get any of the error messages but none of the fields are updated.
should i keep going or just drop the update functionality from my site( although it would make my life easier if it had an update function)?
dakkonz
Forum Commoner
Posts: 69
Joined: Sat Dec 27, 2003 2:55 am
Location: Asia

Post by dakkonz »

I have a problem:
i cant seem to get UPDATE working....
using "mysql_affected_rows()" I get a value of -1....
is it something got to do with my where statement??

$query = "UPDATE client (clientid, name, cemail, comName, title, contact, mailadd) VALUES ('$clientid1', '$name1', '$cemail1', '$comName1', '$title1', '$contract1', '$mailadd1') WHERE cacc={$_GET['eid']}";
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

concatenate_man wrote:now i don't get any of the error messages but none of the fields are updated.
should i keep going or just drop the update functionality from my site( although it would make my life easier if it had an update function)?
Try to echo $query and then run it using phpmyadmin or similar tool... perhaps there are no rows matching your WHERE clause...
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

dakkonz wrote: using "mysql_affected_rows()" I get a value of -1....
PHP manual wrote: If the last query failed, this function will return -1.
dakkonz wrote: is it something got to do with my where statement??
no, your query is completely wrong
dakkonz wrote: $query = "UPDATE client (clientid, name, cemail, comName, title, contact, mailadd) VALUES ('$clientid1', '$name1', '$cemail1', '$comName1', '$title1', '$contract1', '$mailadd1') WHERE cacc={$_GET['eid']}";
MySQL manual wrote: 6.4.5 UPDATE Syntax

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2, ...]
[WHERE where_definition]
[LIMIT #]
dakkonz
Forum Commoner
Posts: 69
Joined: Sat Dec 27, 2003 2:55 am
Location: Asia

Post by dakkonz »

okay thanks
concatenate_man
Forum Newbie
Posts: 13
Joined: Tue Dec 09, 2003 1:22 am
Location: Australia

Post by concatenate_man »

Thnaks to all who chipped in the problem is now solved, i changed this

Code: Select all

<input type="hidden" name="database_name" value="<php echo $database_name; ?>"
to this

Code: Select all

<input type="hidden" name="database_name" value="<? echo $database_name; ?>">;
and by a miracle of God (or some other Demon) everything worked.

if any noobs want a copy of all three working files to play with just let me know and I'll send it on.

cheers
Joe
Post Reply