Back To Programming / Scripting / Coding Forum

mySQL database UPDATE table SET.... help

  • mykh
  • Novice
  • Novice
  • No Avatar
  • Joined: Oct 25, 2003
  • Posts: 16
  • Status: Offline

Post October 31st, 2003, 7:15 pm

Bigwebmaster wrote:
Have you tested to make sure data is stored in the $username and $id variables? In other words they are not blank correct? Also I know you said you had the MySQL table setup, but do you have an ID in that MySQL table that matches the ID that you are sending through the $id variable?

Also, what version of MySQL and PHP are you using?

Finally have you tried to run that MySQL query manually through MyPHPAdmin or some other interface if you have it, just to make sure it actually works on your database?

I am just shooting blanks in the dark here, since you really haven't provided me much to work with but your little snippet of code. Hopefully one of these blanks will hit something soon :)

yes I have tested everything, and i can get anyone to register from website so through php, no problem, i can delete too, but the one thing is the UPDATE function that dont work

yes there is an id and it does match, if u want i'll post up all my code on my site and you can see it

on my comp i'm runnign the latest php and mysql (using PHPTriad -PHP, mySQL, Apache server), on my website i'm not sure but i believe my host is also using the latest versions
yes I have used phpMyAdmin to make it work and it once again works no problem

i'll go put up all the code that i got, for everything related to this and maybe u could make heads or tales of the problem.
the url will be
http://www.dragonsoftheweb.com/fs/
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post October 31st, 2003, 7:15 pm

  • mykh
  • Novice
  • Novice
  • No Avatar
  • Joined: Oct 25, 2003
  • Posts: 16
  • Status: Offline

Post October 31st, 2003, 7:32 pm

ok, all the files are up on
http://www.dragonsoftheweb.com/fs
had to put them in txt format so u cna read them, and dont know how to make code appear using xml yet
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Joined: Dec 20, 2002
  • Posts: 8922
  • Loc: Seattle, WA & Phoenix, AZ
  • Status: Offline

Post October 31st, 2003, 10:59 pm

Well I have gone through many of your files and I am as really stumped as you are. If there is a problem in the code I am not seeing it. The only thing that I see that varies with your structure in some of your files is at times you will use:

Code: [ Select ]
$db = mysql_select_db("fs",$conn) or die(mysql_error());


and other times you will use:

Code: [ Select ]
$db = mysql_select_db("fs");


The only other thing I can suggest is to first view your mysql logs and httpd log files if you can. On my system the httpd and mysql logs are located at:

/var/log/mysqld.log
/var/log/httpd/error_log
/var/log/httpd/access_log

The log files might give you some clues. You might consider also turning the debugging level up higher in your conf files so that more errors/warnings show up that might put you in the right direction.

Also another thing I usually do when I am stumped is insert print/echo command after each line of code you execute so you can see exactly what is going on with everything. I would usually print out all variables after each line so I can evalute everything and see if anything looks wrong there. The fact that you are not getting any internal server errors is telling you that your syntax is right, and probably some kind of logical error exists (which are probably the hardest ones to figure out, just like we haven't figured it out yet).

Anyway unless someone else has some ideas, I am all out. If you do figure this out I would love to know what the problem is.
Ozzu Hosting - Want your website on a fast server like Ozzu?
  • mykh
  • Novice
  • Novice
  • No Avatar
  • Joined: Oct 25, 2003
  • Posts: 16
  • Status: Offline

Post November 1st, 2003, 8:24 am

thanks for the help, i'm gonna go try to find my error log folders on my server, hpefully i'll be able to solve this error
  • bveditz
  • Novice
  • Novice
  • No Avatar
  • Joined: Nov 21, 2003
  • Posts: 15
  • Status: Offline

Post November 21st, 2003, 8:09 pm

Bigwebmaster wrote:
Have you tested to make sure data is stored in the $username and $id variables? In other words they are not blank correct? Also I know you said you had the MySQL table setup, but do you have an ID in that MySQL table that matches the ID that you are sending through the $id variable?

I agree with you here, looking at his code it should work. My experience with these issues has always been bad data. If I were him, I'd verify the data he's trying to input is valid as well as verify that it matches the format he's trying to input. For instance, $username and Username fields are both text and $id and id are both numbers. Don't know how strict mysql is on this, but it would kill other brands sql.

Another thought is to post the same query to the screen as you send to mssql. After it prints to the screen, verify it looks correct and isn't adding any quotes or anything, then copy the statement into phpmyadmin or mysql directly and run it.
  • Voetsjoeba
  • Novice
  • Novice
  • User avatar
  • Joined: Nov 21, 2003
  • Posts: 31
  • Loc: Belgium, Ghent.
  • Status: Offline

Post November 22nd, 2003, 2:48 am

I believe you needed double quotes around the values:

UPDATE tablename SET field = "theNewValue" WHERE field = "theOldValue";

In PHP that would become:

Code: [ Select ]
$conn = mysql_connect("host","username","pw");
$q = "UPDATE tablename SET field = \"$newValue\" WHERE field = \"$theOldValue\"";
$result = mysql_query($q,$conn);
  1. $conn = mysql_connect("host","username","pw");
  2. $q = "UPDATE tablename SET field = \"$newValue\" WHERE field = \"$theOldValue\"";
  3. $result = mysql_query($q,$conn);


Give it a try :)
  • mykh
  • Novice
  • Novice
  • No Avatar
  • Joined: Oct 25, 2003
  • Posts: 16
  • Status: Offline

Post November 22nd, 2003, 1:57 pm

Voetsjoeba wrote:
I believe you needed double quotes around the values:

UPDATE tablename SET field = "theNewValue" WHERE field = "theOldValue";

In PHP that would become:

Code: [ Select ]
$conn = mysql_connect("host","username","pw");
$q = "UPDATE tablename SET field = "$newValue" WHERE field = "$theOldValue"";
$result = mysql_query($q,$conn);
  1. $conn = mysql_connect("host","username","pw");
  2. $q = "UPDATE tablename SET field = "$newValue" WHERE field = "$theOldValue"";
  3. $result = mysql_query($q,$conn);


Well the double quotes were the first thing I used, (look at the previous versions of code on this page)
but the backslashes I'll try, never saw a command line like that with backslashes though

Give it a try :)
  • Voetsjoeba
  • Novice
  • Novice
  • User avatar
  • Joined: Nov 21, 2003
  • Posts: 31
  • Loc: Belgium, Ghent.
  • Status: Offline

Post November 23rd, 2003, 2:44 am

The backslashes are there to make sure PHP doesn't see the " as the ending of the string definition.

Example:

$a = "Variable goes here"; //valid
$b = "This is a double quote: \""; //valid
$c = "This is a double quote: ""; //invalid

By the way, " is not the same as ' for MySQL.
  • mykh
  • Novice
  • Novice
  • No Avatar
  • Joined: Oct 25, 2003
  • Posts: 16
  • Status: Offline

Post November 23rd, 2003, 9:43 am

Voetsjoeba wrote:
The backslashes are there to make sure PHP doesn't see the " as the ending of the string definition.

Example:

$a = "Variable goes here"; //valid
$b = "This is a double quote: ""; //valid
$c = "This is a double quote: ""; //invalid

By the way, " is not the same as ' for MySQL.

I tried it with the backslashes and it didn't work, and yes, I do know there is a difference b/w the " and the ' and I already tried both and neither work.
  • Voetsjoeba
  • Novice
  • Novice
  • User avatar
  • Joined: Nov 21, 2003
  • Posts: 31
  • Loc: Belgium, Ghent.
  • Status: Offline

Post November 23rd, 2003, 12:55 pm

Hmn ... then it should work. Are you sure the database and table exist, and that you have the right to edit them ?
  • mykh
  • Novice
  • Novice
  • No Avatar
  • Joined: Oct 25, 2003
  • Posts: 16
  • Status: Offline

Post November 23rd, 2003, 1:14 pm

Voetsjoeba wrote:
Hmn ... then it should work. Are you sure the database and table exist, and that you have the right to edit them ?


I though I already said this, but if not, the whole server mySQl PhP, etc are all on my machine its localhost. so I have the editing rights and the table is fine. Anyways, thanks for all the help guys and I finally got it to work

Code: [ Select ]
<?php
session_start();
echo "Hi \n";
echo $_SESSION["username"];
//echo "\n";
$id = $_SESSION["id"];
//echo $id;
echo "\n";
//echo $user;

/*$result = MYSQL_QUERY("INSERT INTO users (strength)". "VALUES ('$strup')"); */
    $conn = mysql_connect("localhost","adm","alphax") or die(mysql_error());
    $db = mysql_select_db("fs",$conn) or die(mysql_error());
    $sql = "UPDATE users SET username = '$user' WHERE id = $id";
    $result = mysql_query($sql);
echo "<p>Done!<br>";
    echo "Your username is now $user !!!";
    
    echo "<p><a href='account.php'>Back To Account</a>";
    echo "<br><a href='main.php'>Warehouse</a>";
    echo "<br><a href='index.php'>Home</a>";
include("bottom.php");
?>
  1. <?php
  2. session_start();
  3. echo "Hi \n";
  4. echo $_SESSION["username"];
  5. //echo "\n";
  6. $id = $_SESSION["id"];
  7. //echo $id;
  8. echo "\n";
  9. //echo $user;
  11. /*$result = MYSQL_QUERY("INSERT INTO users (strength)". "VALUES ('$strup')"); */
  12.     $conn = mysql_connect("localhost","adm","alphax") or die(mysql_error());
  13.     $db = mysql_select_db("fs",$conn) or die(mysql_error());
  14.     $sql = "UPDATE users SET username = '$user' WHERE id = $id";
  15.     $result = mysql_query($sql);
  16. echo "<p>Done!<br>";
  17.     echo "Your username is now $user !!!";
  18.     
  19.     echo "<p><a href='account.php'>Back To Account</a>";
  20.     echo "<br><a href='main.php'>Warehouse</a>";
  21.     echo "<br><a href='index.php'>Home</a>";
  22. include("bottom.php");
  23. ?>


don't know why it wasnt working earlier, but I played around with the code a bit changed very small things couple times, and changed variable names also and now it works.
Hopefully I'll have the game up and running in about a month I'll be srue to post here once it is done.
  • mykh
  • Novice
  • Novice
  • No Avatar
  • Joined: Oct 25, 2003
  • Posts: 16
  • Status: Offline

Post November 23rd, 2003, 1:16 pm

now I got another question, let's say I want to display every username in the table what would be the code?

1, 2

Post Information

  • Total Posts in this topic: 27 posts
  • Users browsing this forum: No registered users and 151 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.