Updating A Database

StevenF · 02-05-2008, 02:46 PM

Hello,

I've been messing around with this query for a while now and can't get it to work as intended!

Code:

$query = 'UPDATE althlete SET 	
	
   athlete = "'.mysql_real_escape_string($ud_name).'",
   regno = "'.mysql_real_escape_string($ud_regno).'",
   gender = "'.mysql_real_escape_string($ud_gender).'",
   age = "'.mysql_real_escape_string($ud_age).'",
   eventno = "'.mysql_real_escape_string($ud_eventno).'",
   distance = "'.mysql_real_escape_string($ud_distance).'",
   stroke = "'.mysql_real_escape_string($ud_stroke).'",
   time = "'.mysql_real_escape_string($time).'",
   place = "'.mysql_real_escape_string($place).'",
   WHERE athlete_number = $ud_athletenumber';

Basically, I have a database which holds data on swimmers. There is a field called athlete_number which is an Autonumber. I want to update the row where numbers match, but it doesn't seem to do anything. If I take the last line away, then every row in the table gets replaced with the data entered. So it's something to do with the last line.

Edit: $ud_athletenumber is a hidden form field which contains the autonumber.

Any help appreciated.

- Steven

RobertK · 02-05-2008, 03:09 PM

One problem is that, I think, mySQL doesn't like double quotes. However, you'll never get a match because variables in single quoted strings aren't escaped.

So:

SQL Code:

WHERE athlete_number = $ud_athletenumber';

Should be:

SQL Code:

WHERE athlete_number = '.$ud_athletenumber;

StevenF · 02-05-2008, 03:14 PM

Quote:

Originally Posted by RobertK

One problem is that, I think, mySQL doesn't like double quotes. However, you'll never get a match because variables in single quoted strings aren't escaped.

So:

SQL Code:

WHERE athlete_number = $ud_athletenumber';

Should be:

SQL Code:

WHERE athlete_number = '.$ud_athletenumber;

Oh, I see. I had the single quote at the end to close to whole query, like so:

Code:

$query = 'UPDATE althlete SET 	
	
   athlete = "'.mysql_real_escape_string($ud_name).'",
   regno = "'.mysql_real_escape_string($ud_regno).'",
   gender = "'.mysql_real_escape_string($ud_gender).'",
   age = "'.mysql_real_escape_string($ud_age).'",
   eventno = "'.mysql_real_escape_string($ud_eventno).'",
   distance = "'.mysql_real_escape_string($ud_distance).'",
   stroke = "'.mysql_real_escape_string($ud_stroke).'",
   time = "'.mysql_real_escape_string($time).'",
   place = "'.mysql_real_escape_string($place).'",
   WHERE athlete_number = $ud_athletenumber';

However, I changed it to what you wrote and it made no difference. Thanks though :)

RobertK · 02-05-2008, 03:47 PM

As I said, MySQL doesn't like double-quotes. Try:

PHP Code:


			
$query = 'UPDATE althlete SET     

    

   athlete = \''.mysql_real_escape_string($ud_name).'\',

   regno = \''.mysql_real_escape_string($ud_regno).'\',

   gender = \''.mysql_real_escape_string($ud_gender).'\',

   age = \''.mysql_real_escape_string($ud_age).'\',

   eventno = \''.mysql_real_escape_string($ud_eventno).'\',

   distance = \''.mysql_real_escape_string($ud_distance).'\',

   stroke = \''.mysql_real_escape_string($ud_stroke).'\',

   time = \''.mysql_real_escape_string($time).'\',

   place = \''.mysql_real_escape_string($place).'\',

   WHERE athlete_number = '.$ud_athletenumber;

StevenF · 02-05-2008, 04:07 PM

Thanks Robert, but still nothing. I'm not receiving any error, it's just not updating for some reason. Are the backslashes used to escape or separate the quotes?

buggabill · 02-05-2008, 07:23 PM

I personally use double quotes and they seem to work for me. One thing that looks a bit weird is the table name. Is that correct?

SQL Code:

$query = 'UPDATE althlete SET

compare

SQL Code:

$query = 'UPDATE athlete SET

One more thing, you have an extra comma before the WHERE clause.

PHP Code:


			
   place = "'.mysql_real_escape_string($place).'",

   WHERE athlete_number = $ud_athletenumber';

should be:

PHP Code:


			
   place = "'.mysql_real_escape_string($place).'"

   WHERE athlete_number = $ud_athletenumber';

That would cause some issues.

StevenF · 02-05-2008, 07:31 PM

Thanks for that Bill.

"Athlete" is spelt wrong in the table, I know, instead of changing it I just made the query match it, so the problem doesn't lie there.

I have also removed that last comma, then changed the last line to the way Robert had wrote. So:

Code:

place = "'.mysql_real_escape_string($place).'"
   WHERE athlete_number = $ud_athletenumber';

Became

Code:

place = "'.mysql_real_escape_string($place).'"
   WHERE athlete_number = '.$ud_athletenumber;

It now works as intended! I can now search the database, insert a new row through a form and update the data through a form :)

Thanks a lot
Steven

buggabill · 02-05-2008, 07:51 PM

Crap... I copied the wrong text... Sorry about that.

StevenF · 02-05-2008, 08:35 PM

Quote:

Originally Posted by buggabill

Crap... I copied the wrong text... Sorry about that.

Not a problem. I'm really pleased with what I've done so far!

Though, I did need some help along the way. I just want to put it into practice now!

EyeDentify · 02-07-2008, 12:41 PM

StevenF.

Try this one below and see if it helps.

Note the way i´ve put (') and (").

This is how i usally do it. but also with sprintf sometimes.

and i found that you used an extra "," comma on the last column to be updated before the WHERE clause. Guess that could be a problem.

And make sure all table and column names are correct

PHP Code:


			
$query = "UPDATE althlete SET    
athlete = '" . mysql_real_escape_string($ud_name) . "',
regno = '" . mysql_real_escape_string($ud_regno) . "',
gender = '" . mysql_real_escape_string($ud_gender) . "' ,
age = '" . mysql_real_escape_string($ud_age) . "',
eventno = '" . mysql_real_escape_string($ud_eventno) . "',
distance = '" . mysql_real_escape_string($ud_distance) . "',
stroke = '" . mysql_real_escape_string($ud_stroke) . "',
time = '" . mysql_real_escape_string($time) . "',
place = '" . mysql_real_escape_string($place) . "'
WHERE athlete_number = '" . $ud_athletenumber . "'";

StevenF · 02-07-2008, 12:46 PM

EyeDentify, thanks for that. I have already solve the problem though, it's a few posts up. I basically have what you have, but with the quotes in the original order.

Salathe · 02-07-2008, 01:18 PM

Now see, this is where using string formatting can make things much easier to handle. Compare all of the examples above to:

PHP Code:


			
$query = sprintf("

    UPDATE athlete

    SET

        athlete  = '%s',

        regno    = '%s',

        gender   = '%s',

        age      = '%s',

        eventno  = '%s',

        distance = '%s',

        stroke   = '%s',

        time     = '%s',

        place    = '%s'

    WHERE

        athlete_number = %d

    ;",

    mysql_real_escape_string($ud_name),

    mysql_real_escape_string($ud_regno),

    mysql_real_escape_string($ud_gender),

    mysql_real_escape_string($ud_age),

    mysql_real_escape_string($ud_eventno),

    mysql_real_escape_string($ud_distance),

    mysql_real_escape_string($ud_stroke),

    mysql_real_escape_string($time),

    mysql_real_escape_string($place),

    (int) $ud_atheletenumber

);

It's much, much easier to spot mistakes when the code is formatted well.

buggabill · 02-07-2008, 01:53 PM

I like that method Salathe. I may try to start using it. It seems to be quite a bit cleaner.

I do have a question though. Is there a significant (if any) performance penalty using this method as you are calling another function - sprintf - to assign the value of $query?

StevenF · 02-07-2008, 04:41 PM

Thanks Salathe, I've never seen it done that way before. I'm really knew to php so I have a lot to learn.

Can I ask what the "%s and %d is please?

Salathe · 02-07-2008, 06:43 PM

The formatting codes are explained in PHP Manual: sprintf. Of course there is a performance penalty to calling a function over, say, concatenating strings. However, the gain in code readability (and time spent not looking for the typing mistakes!!!) far outweighs the extra tiny fractions of a second.

TlcAndres · 02-07-2008, 08:58 PM

%s and %d and so on can be used to specify what the expected input it when using sprintf for example

PHP Code:


			
$var = sprintf('this is a %d number','too')

It won't work because %d is used to accept numerical values (if not mistaken)

buggabill · 02-07-2008, 09:09 PM

Point taken Salathe. It does seem better than the way I have been doing it.