Update Only Against Unique Id Number

justlukeyou · October 6, 2012

Hi,

I have the following update code however it updates each account in the database. Can anyone please advise how I can read an update on the 'id' number of the member.

Should I be looking to insert something like this:

WHERE id=$someid";

But im not sure how to allocate the 'id' to 'someid'.

function mysql_real_escape_array($t)
{
   return array_map("mysql_real_escape_string",$t);
}
function trim_array($ar)
{
   return array_map("trim",$ar);
}
if(isset($_POST['form_id']))
{
   $_POST = mysql_real_escape_array($_POST);
   $_POST = trim_array($_POST);
   $error = "";
   if(!isset($_POST['category']) || empty($_POST['category'])) {
    $error = "Please select a category.";
   }
   if(!isset($_POST['website']) || empty($_POST['website'])) {
    $error.= " Please enter a Website Domain.";
   }
   if(!isset($_POST['company']) || empty($_POST['company'])) {
    $error.= " Please enter a Company Name.";
   }
   if(!isset($_POST['building']) || empty($_POST['building'])) {
    $error.= " Please enter a Building Name or Number.";
   }
   if(!isset($_POST['streetname']) || empty($_POST['streetname'])) {
    $error.= " Please enter a Street Name.";
   }

 if(!isset($_POST['town']) || empty($_POST['town'])) {
    $error.= " Please enter your Town.";
   }
   if(!isset($_POST['state']) || empty($_POST['state'])) {
    $error.= " Please enter a State.";
   }
   if(!isset($_POST['postcode']) || empty($_POST['postcode'])) {
    $error.= " Please enter a Zip Code/Post Code.";
   }
   if(!isset($_POST['country']) || empty($_POST['country'])) {
    $error.= " Please select your country.";
   }
   if(!isset($_POST['aboutcompany']) || empty($_POST['aboutcompany'])) {
    $error.= " Please enter details about your company.";
   }
   if($error == "")
   {
    $sql = "
    UPDATE
	    users
    SET
	    category = '".$_POST['category']."',
 linkcategory = '".str_replace(' ', '-',strtolower($_POST['category']))."',
	    firstname = '".$_POST['firstname']."',
	    surname = '".$_POST['surname']."',
	    email = '".$_POST['email']."',
	    website = '".$_POST['website']."',
	    company = '".$_POST['company']."',
	    building = '".$_POST['building']."',
	    streetname = '".$_POST['streetname']."',
  town = '".$_POST['town']."',
	    state = '".$_POST['state']."',
	    postcode = '".$_POST['postcode']."',
	    aboutcompany = '".$_POST['aboutcompany']."',
  country = '".$_POST['country']."'";
    $result = mysql_query($sql) or die("An error occurred ".mysql_error());

   }

Jessica · October 6, 2012

$id = (int) $_POST['form_id'];

Then add your where clause.

justlukeyou · October 6, 2012

Big thanks,

When I try the following code theire are no errors on the page but when I try to update the profile it creates the following error message:

An error occurred 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 id=' at line 18

I haven't seen an error like this and line 18 is the header.

It took ten minutes to put the update code together but I cant get it to work on just the users ID.

<?php
$id = (int) $_POST['form_id'];
function mysql_real_escape_array($t)
{
   return array_map("mysql_real_escape_string",$t);
}
function trim_array($ar)
{
   return array_map("trim",$ar);
}
if(isset($_POST['form_id']))
{
   $_POST = mysql_real_escape_array($_POST);
   $_POST = trim_array($_POST);
   $error = "";
   if(!isset($_POST['category']) || empty($_POST['category'])) {
    $error = "Please select a category.";
   }
   if(!isset($_POST['website']) || empty($_POST['website'])) {
    $error.= " Please enter a Website Domain.";
   }
   if(!isset($_POST['company']) || empty($_POST['company'])) {
    $error.= " Please enter a Company Name.";
   }
   if(!isset($_POST['building']) || empty($_POST['building'])) {
    $error.= " Please enter a Building Name or Number.";
   }
   if(!isset($_POST['streetname']) || empty($_POST['streetname'])) {
    $error.= " Please enter a Street Name.";
   }

 if(!isset($_POST['town']) || empty($_POST['town'])) {
    $error.= " Please enter your Town.";
   }
   if(!isset($_POST['state']) || empty($_POST['state'])) {
    $error.= " Please enter a State.";
   }
   if(!isset($_POST['postcode']) || empty($_POST['postcode'])) {
    $error.= " Please enter a Zip Code/Post Code.";
   }
   if(!isset($_POST['country']) || empty($_POST['country'])) {
    $error.= " Please select your country.";
   }
   if(!isset($_POST['aboutcompany']) || empty($_POST['aboutcompany'])) {
    $error.= " Please enter details about your company.";
   }
   if($error == "")
   {
    $sql = "
    UPDATE
	    users
    SET
	    category = '".$_POST['category']."',
 linkcategory = '".str_replace(' ', '-',strtolower($_POST['category']))."',
	    firstname = '".$_POST['firstname']."',
	    surname = '".$_POST['surname']."',
	    email = '".$_POST['email']."',
	    website = '".$_POST['website']."',
	    company = '".$_POST['company']."',
	    building = '".$_POST['building']."',
	    streetname = '".$_POST['streetname']."',
  town = '".$_POST['town']."',
	    state = '".$_POST['state']."',
	    postcode = '".$_POST['postcode']."',
	    aboutcompany = '".$_POST['aboutcompany']."',
  country = '".$_POST['country']."',
  WHERE id=$int";
    $result = mysql_query($sql) or die("An error occurred ".mysql_error());

   }
}
?>

kicken · October 6, 2012

  WHERE id=$int";

There is no such variable as $int. You want a different variable name. Think about it.

Andy123 · October 6, 2012

As kicken wrote, $int is nothing, which will make your SQL query invalid. What you would want to do is to use $id instead, although this variable should probably be set based on a session instead of a post value. I assume you don't want users to be able to update other users' data by changing a form value.

Also, just a few notes after quickly viewing your code; the MySQL extension you are using will be removed, so unless you have a very good reason for using it (e.g. maintaining a legacy system), then I strongly suggest that you take a look at PDO or Mysqli. If it were me, I would use an array for storing your errors instead of a string; I find this to be a much better approach. It is, after all, what the array data structure is there for. Then you can just check if the array is empty instead. It also gives you the advantage that you can loop through your errors when you want to display them and perhaps make a nice unordered or ordered list. It will also make it easier to manipulate the result if you want to use AJAX in the future; then you can encode the array to JSON and make it all nice and pretty. Okay, that was kind of besides the point, but just a heads up.

Edited October 6, 2012 by Andy123

Pikachu2000 · October 6, 2012

Using a non-existent variable shouldn't cause that error, it should just cause no affected rows. However, the comma before WHERE will cause that error.

You still need to change $int to the proper variable also.

Andy123 · October 7, 2012

Using a non-existent variable shouldn't cause that error, it should just cause no affected rows. However, the comma before WHERE will cause that error.

Yes, that is correct. I have no idea why on Earth I wrote otherwise.

ignace · October 7, 2012

Using a non-existent variable shouldn't cause that error, it should just cause no affected rows. However, the comma before WHERE will cause that error.

Yes it will:

UPDATE users SET foo = 'bar' WHERE id =

Throws that error because there is no right value which happens with a non existing variable.

Edited October 7, 2012 by ignace

Barand · October 7, 2012

not that error

$sql = "UPDATE users SET foo = 'bar', WHERE id =";
$res = mysql_query($sql) or die(mysql_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 'WHERE id =' at line 1

without comma

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 '' at line 1

Edited October 7, 2012 by Barand

justlukeyou · October 7, 2012

Hi,

I have changed int to id but I am using this to determine if someone if logged in. if ($_SESSION['userLoggedIn'])

Should I be using that?

I have tried to look into PDO before but I dont undertand what the difference between MySQL and PDO is. If I moved from using MySQL to PDO what do I need to do?

$id = (id) $_POST['form_id'];
function mysql_real_escape_array($t)
{
   return array_map("mysql_real_escape_string",$t);
}
function trim_array($ar)
{
   return array_map("trim",$ar);
}
if(isset($_POST['form_id']))
{
   $_POST = mysql_real_escape_array($_POST);
   $_POST = trim_array($_POST);
   $error = "";
   if(!isset($_POST['category']) || empty($_POST['category'])) {
    $error = "Please select a category.";
   }
   if(!isset($_POST['website']) || empty($_POST['website'])) {
    $error.= " Please enter a Website Domain.";
   }
   if(!isset($_POST['company']) || empty($_POST['company'])) {
    $error.= " Please enter a Company Name.";
   }
   if(!isset($_POST['building']) || empty($_POST['building'])) {
    $error.= " Please enter a Building Name or Number.";
   }
   if(!isset($_POST['streetname']) || empty($_POST['streetname'])) {
    $error.= " Please enter a Street Name.";
   }

 if(!isset($_POST['town']) || empty($_POST['town'])) {
    $error.= " Please enter your Town.";
   }
   if(!isset($_POST['state']) || empty($_POST['state'])) {
    $error.= " Please enter a State.";
   }
   if(!isset($_POST['postcode']) || empty($_POST['postcode'])) {
    $error.= " Please enter a Zip Code/Post Code.";
   }
   if(!isset($_POST['country']) || empty($_POST['country'])) {
    $error.= " Please select your country.";
   }
   if(!isset($_POST['aboutcompany']) || empty($_POST['aboutcompany'])) {
    $error.= " Please enter details about your company.";
   }
   if($error == "")
   {
    $sql = "
    UPDATE
	    users
    SET
	    category = '".$_POST['category']."',
 linkcategory = '".str_replace(' ', '-',strtolower($_POST['category']))."',
	    firstname = '".$_POST['firstname']."',
	    surname = '".$_POST['surname']."',
	    email = '".$_POST['email']."',
	    website = '".$_POST['website']."',
	    company = '".$_POST['company']."',
	    building = '".$_POST['building']."',
	    streetname = '".$_POST['streetname']."',
  town = '".$_POST['town']."',
	    state = '".$_POST['state']."',
	    postcode = '".$_POST['postcode']."',
	    aboutcompany = '".$_POST['aboutcompany']."',
  country = '".$_POST['country']."',
  WHERE id=$id";
    $result = mysql_query($sql) or die("An error occurred ".mysql_error());

   }
}

Andy123 · October 7, 2012

Yes it will:
UPDATE users SET foo = 'bar' WHERE id =
Throws that error because there is no right value which happens with a non existing variable.

That was my initial thought. I let myself convince too easily sometimes.

Hi,

I have changed int to id but I am using this to determine if someone if logged in. if ($_SESSION['userLoggedIn'])

Should I be using that?

I have tried to look into PDO before but I dont undertand what the difference between MySQL and PDO is. If I moved from using MySQL to PDO what do I need to do?

You are typecasting to "id", which is not valid. You should still typecast to int, like this:

$id = (int) $_POST['form_id'];

This is the ID that you will use in your WHERE clause (WHERE id = $id). I think Jessica just posted this as an example, so you still have to populate $id somehow. In this example, it is populated based on a POST value. If you want to go with this approach (not sure if it is appropriate for your domain), then you have to add an element with the name "form_id" to your HTML form and give it a value. This is the value that will then be used in your SQL query. Like I mentioned before, you will probably not want to do this, because users can alter anything that comes from the client. Or at least you will want to validate it somehow. Let's say that you store the user ID in a session; then you could simply do like this:

$id = (int) $_SESSION['userID'];

Regarding PDO, you will certainly still be able to use MySQL. It's just a more efficient way of connecting to the database (and I think more secure as well). It also gives you the advantage of being database independent, so if you want to switch from one database vendor to another in the future, all you have to do is to edit your connection string accordingly. PDO and Mysqli are the recommended extensions for connecting to databases. I am not all too sure what MySQLi is all about because I use PDO, but MySQLi seems to be the object oriented way of connecting to MySQL (it is the newer version of the extension you are currently using). It is specific to MySQL, so my guess would be that it is tweaked for MySQL. I recommend using PDO for more flexibility, but I am not very familiar with MySQLi.

You can find an example of how to use PDO here and how to connect to MySQL. Under normal circumstances, this should all work out of the box, even if you are running on a local installation of PHP. If you have problems making it work, ensure that your mysql.sock is set in your php.ini (search for "pdo").

Edited October 7, 2012 by Andy123

Pikachu2000 · October 7, 2012

That was my initial thought. I let myself convince too easily sometimes.

No, the undefined variable won't cause the error the OP got. That particular error is caused by the comma before WHERE. In this case, I was wrong about it causing no affected rows if it were simply the undefined variable. That would only happen if the undefined variable was in quotes in the query string, and there were no fields that matched an empty string.

ignace · October 7, 2012

not that error

$sql = "UPDATE users SET foo = 'bar', WHERE id =";
$res = mysql_query($sql) or die(mysql_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 'WHERE id =' at line 1

without comma

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 '' at line 1

I stand corrected. I really thought it would yield the same error, I even checked it on sqlfiddle to make sure.. selective blindness

justlukeyou · October 7, 2012

Sorry but Im getting very confused by this.

If I use this, is it reading the id from the database?

$id = (int) $_POST['form_id'];

If so can you please advise what is wrong with the following code?

$id = (int) $_POST['form_id'];
function mysql_real_escape_array($t)
{
   return array_map("mysql_real_escape_string",$t);
}
function trim_array($ar)
{
   return array_map("trim",$ar);
}
if(isset($_POST['form_id']))
{
   $_POST = mysql_real_escape_array($_POST);
   $_POST = trim_array($_POST);
   $error = "";
   if(!isset($_POST['category']) || empty($_POST['category'])) {
    $error = "Please select a category.";
   }
   if(!isset($_POST['website']) || empty($_POST['website'])) {
    $error.= " Please enter a Website Domain.";
   }
   if(!isset($_POST['company']) || empty($_POST['company'])) {
    $error.= " Please enter a Company Name.";
   }
   if(!isset($_POST['building']) || empty($_POST['building'])) {
    $error.= " Please enter a Building Name or Number.";
   }
   if(!isset($_POST['streetname']) || empty($_POST['streetname'])) {
    $error.= " Please enter a Street Name.";
   }

 if(!isset($_POST['town']) || empty($_POST['town'])) {
    $error.= " Please enter your Town.";
   }
   if(!isset($_POST['state']) || empty($_POST['state'])) {
    $error.= " Please enter a State.";
   }
   if(!isset($_POST['postcode']) || empty($_POST['postcode'])) {
    $error.= " Please enter a Zip Code/Post Code.";
   }
   if(!isset($_POST['country']) || empty($_POST['country'])) {
    $error.= " Please select your country.";
   }
   if(!isset($_POST['aboutcompany']) || empty($_POST['aboutcompany'])) {
    $error.= " Please enter details about your company.";
   }
   if($error == "")
   {
    $sql = "
    UPDATE
	    users
    SET
	    category = '".$_POST['category']."',
 linkcategory = '".str_replace(' ', '-',strtolower($_POST['category']))."',
	    firstname = '".$_POST['firstname']."',
	    surname = '".$_POST['surname']."',
	    email = '".$_POST['email']."',
	    website = '".$_POST['website']."',
	    company = '".$_POST['company']."',
	    building = '".$_POST['building']."',
	    streetname = '".$_POST['streetname']."',
  town = '".$_POST['town']."',
	    state = '".$_POST['state']."',
	    postcode = '".$_POST['postcode']."',
	    aboutcompany = '".$_POST['aboutcompany']."',
  country = '".$_POST['country']."';
  WHERE id=$int";
    $result = mysql_query($sql) or die("An error occurred ".mysql_error());

Pikachu2000 · October 7, 2012

How are we supposed to know what's wrong with it? We aren't standing over your shoulder, and we really have no way of knowing what happens when you run the code, whether anything happens, or there's a white screen, or what the errors are, or if your PC simply bursts out in flames, or perhaps Krakatoa erupts when you press the enter key.

Jessica · October 7, 2012

No, it's not reading it from the database, it's getting the POSTed value.

  "country = '".$_POST['country']."';
  WHERE id=$int";

1. Why do you have a ; after the country?

2. What is $int?

You need to read about type casting in PHP if you don't understand what that line of code does.

And SQL injection/data santizing

Edited October 7, 2012 by Jessica

justlukeyou · October 7, 2012

Hi,

I shall look into typecasting, but what is it. Trying to find a plain english explanation for PHP is harder than writing code.

When I run the following code I get this error message:

An error occurred 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 id=0' at line 18

But line 18 is this " <link rel="stylesheet" href="/stylesheet.css" type="text/css" />"

$id = (int) $_POST['form_id'];
function mysql_real_escape_array($t)
{
   return array_map("mysql_real_escape_string",$t);
}
function trim_array($ar)
{
   return array_map("trim",$ar);
}
if(isset($_POST['form_id']))
{
   $_POST = mysql_real_escape_array($_POST);
   $_POST = trim_array($_POST);
   $error = "";
   if(!isset($_POST['category']) || empty($_POST['category'])) {
    $error = "Please select a category.";
   }
   if(!isset($_POST['website']) || empty($_POST['website'])) {
    $error.= " Please enter a Website Domain.";
   }
   if(!isset($_POST['company']) || empty($_POST['company'])) {
    $error.= " Please enter a Company Name.";
   }
   if(!isset($_POST['building']) || empty($_POST['building'])) {
    $error.= " Please enter a Building Name or Number.";
   }
   if(!isset($_POST['streetname']) || empty($_POST['streetname'])) {
    $error.= " Please enter a Street Name.";
   }

 if(!isset($_POST['town']) || empty($_POST['town'])) {
    $error.= " Please enter your Town.";
   }
   if(!isset($_POST['state']) || empty($_POST['state'])) {
    $error.= " Please enter a State.";
   }
   if(!isset($_POST['postcode']) || empty($_POST['postcode'])) {
    $error.= " Please enter a Zip Code/Post Code.";
   }
   if(!isset($_POST['country']) || empty($_POST['country'])) {
    $error.= " Please select your country.";
   }
   if(!isset($_POST['aboutcompany']) || empty($_POST['aboutcompany'])) {
    $error.= " Please enter details about your company.";
   }
   if($error == "")
   {
    $sql = "
    UPDATE
	    users
    SET
	    category = '".$_POST['category']."',
 linkcategory = '".str_replace(' ', '-',strtolower($_POST['category']))."',
	    firstname = '".$_POST['firstname']."',
	    surname = '".$_POST['surname']."',
	    email = '".$_POST['email']."',
	    website = '".$_POST['website']."',
	    company = '".$_POST['company']."',
	    building = '".$_POST['building']."',
	    streetname = '".$_POST['streetname']."',
  town = '".$_POST['town']."',
	    state = '".$_POST['state']."',
	    postcode = '".$_POST['postcode']."',
	    aboutcompany = '".$_POST['aboutcompany']."',
  country = '".$_POST['country']."',
  WHERE id=$id";
    $result = mysql_query($sql) or die("An error occurred ".mysql_error());

   }

Jessica · October 7, 2012

Now you've got the damn comma back in there. It's like you just bang on the keyboard and hope it works, right?

The words ARE IN ENGLISH.

What does the word "type" mean?

What does the word "casting" mean?

Is google somehow broken on your computer? http://en.wikipedia.org/wiki/Type_conversion

Barand · October 7, 2012

An error occurred 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 id=0' at line 18

Are you reading any any of the replies?

justlukeyou · October 7, 2012

Hi Barand,

I have read them but I don't understand them. I wish I did understand them, if I did I would be able to resolve this.

Can you please advise what part is wrong?

Should I be allocating the I'd number to $int and then at the say UPDATE these fields WHERE id = $int

Or should I be trying to do something else.

Christian F. · October 8, 2012

... so if you want to switch from one database vendor to another in the future, all you have to do is to edit your connection string accordingly.

This is not entirely correct, I'm afraid. While it is true that PDO support multiple database systems, it does not translate the SQL sentences. It is not a Database Abstraction Layer (DBA), but an Data Access Abstraction layer (DAA). So unless you're using nothing but the most simplest of SQL queries, you'll still have to translate the queries whenever you change the underlying database engine.

ignace · October 8, 2012

Which means that using a simple LIMIT binds you to mysql and PDO becomes nothing more than a condom around mysql.

Edited October 8, 2012 by ignace

justlukeyou · October 8, 2012

How do I update the database using the member id number?

Barand · October 8, 2012

$id = (int) $_POST['form_id'];

Then add your where clause.

justlukeyou · October 8, 2012

Hi,

I tried that but couldn't get it to work.

$_SESSION['userID'] = 'test';
$id = (int) $_POST['form_id'];
function mysql_real_escape_array($t)
{
   return array_map("mysql_real_escape_string",$t);
}
function trim_array($ar)
{
   return array_map("trim",$ar);
}
if(isset($_POST['form_id']))
{
   $_POST = mysql_real_escape_array($_POST);
   $_POST = trim_array($_POST);
   $error = "";
   if(!isset($_POST['category']) || empty($_POST['category'])) {
    $error = "Please select a category.";
   }
   if(!isset($_POST['website']) || empty($_POST['website'])) {
    $error.= " Please enter a Website Domain.";
   }
   if(!isset($_POST['company']) || empty($_POST['company'])) {
    $error.= " Please enter a Company Name.";
   }
   if(!isset($_POST['building']) || empty($_POST['building'])) {
    $error.= " Please enter a Building Name or Number.";
   }
   if(!isset($_POST['streetname']) || empty($_POST['streetname'])) {
    $error.= " Please enter a Street Name.";
   }

 if(!isset($_POST['town']) || empty($_POST['town'])) {
    $error.= " Please enter your Town.";
   }
   if(!isset($_POST['state']) || empty($_POST['state'])) {
    $error.= " Please enter a State.";
   }
   if(!isset($_POST['postcode']) || empty($_POST['postcode'])) {
    $error.= " Please enter a Zip Code/Post Code.";
   }
   if(!isset($_POST['country']) || empty($_POST['country'])) {
    $error.= " Please select your country.";
   }
   if(!isset($_POST['aboutcompany']) || empty($_POST['aboutcompany'])) {
    $error.= " Please enter details about your company.";
   }
   if($error == "")
   {
    $sql = "
    UPDATE
	    users
    SET
	    category = '".$_POST['category']."',
 linkcategory = '".str_replace(' ', '-',strtolower($_POST['category']))."',
	    firstname = '".$_POST['firstname']."',
	    surname = '".$_POST['surname']."',
	    email = '".$_POST['email']."',
	    website = '".$_POST['website']."',
	    company = '".$_POST['company']."',
	    building = '".$_POST['building']."',
	    streetname = '".$_POST['streetname']."',
  town = '".$_POST['town']."',
	    state = '".$_POST['state']."',
	    postcode = '".$_POST['postcode']."',
	    aboutcompany = '".$_POST['aboutcompany']."',
  country = '".$_POST['country']."',
	    WHERE
	  id = " . $_SESSION['userID']; 
    $result = mysql_query($sql) or die("An error occurred ".mysql_error());

When I use I get the following message:

An error occurred 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 id = test' at line 18

But I dont know how to turn 'test' into the actually id number of the member.

Sign In

Update Only Against Unique Id Number

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information