Need basic syntax help: INSERT/WHERE not working...

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
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Need basic syntax help: INSERT/WHERE not working...

Post by JAB Creations »

What am I doing wrong here? I just want to insert the IP address in to the database on the row where the username = $user. I have all the correct things selected but it's the syntax I'm messing up somewhere at the end...?

Code: Select all

//$mysql_register = mysql_query("INSERT INTO public_accounts ip_0 VALUES  inet_aton('".$ipaddress."') WHERE username = '".$user."'");
//$mysql_register = mysql_query("INSERT INTO public_accounts (ip_0) VALUES (inet_aton('".$ipaddress."')) WHERE username = '".$user."'");
 
$mysql_register = mysql_query("INSERT INTO public_accounts (date_0, ip_0) VALUES (NOW(), inet_aton('".$ipaddress."')) WHERE username = '".$user."'");
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Need basic syntax help: INSERT/WHERE not working...

Post by jaoudestudios »

If you are doing that surely you mean UPDATE?

It is worth being more specific with your queries too, i.e.

Code: Select all

 
$q = "UPDATE public_accounts SET date_0 = '".time()."', ip_0 = '".$ipaddress."' WHERE username = '".$user."'";
 
I would put some protection on there too, just in case of sql injection
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Need basic syntax help: INSERT/WHERE not working...

Post by JAB Creations »

This isn't doing anything when the script is executed...not sure why.

Code: Select all

$query2 = mysql_query("UPDATE public_accounts SET ip_0 = inet_aton('".$ipaddress."') WHERE username = '$username'");
ip_0 / int(11) for IP addresses. The registration works fine and I've tried to emulate that however...

Code: Select all

$mysql_register = mysql_query("INSERT INTO public_accounts (username, password, email, activation_key, date_0, ip_0, status) VALUES ('$username', '$password', '$email', '$activation_key', NOW(), inet_aton('".$ipaddress."'), 'Activation Pending')");
...of course I don't want to update anything save the IP address when they authenticate. I'm really stuck on this and I'm not getting any MySQL errors or anything. :banghead:
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Need basic syntax help: INSERT/WHERE not working...

Post by jaoudestudios »

Are you using a linux server? Dont send the query to the mysql server through php, echo it out to the screen and then copy and paste it to the mysql command line and it will give you more feedback on errors etc
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Need basic syntax help: INSERT/WHERE not working...

Post by JAB Creations »

Not sure how else to do that but here I go...

Code: Select all

$query2 = mysql_query("UPDATE public_accounts SET ip_0 = inet_aton('".$ipaddress."') WHERE username = '$username'");
echo $query2;
1

Code: Select all

$query2 = "UPDATE public_accounts SET ip_0 = inet_aton('".$ipaddress."') WHERE username = '$username'";
echo $query2;
UPDATE public_accounts SET ip_0 = inet_aton('6x.xx.xxx.xxx') WHERE username = ''
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Need basic syntax help: INSERT/WHERE not working...

Post by jaoudestudios »

There you go...

$username is empty!

what is the function inet_aton?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Need basic syntax help: INSERT/WHERE not working...

Post by jaoudestudios »

Also I would not parse $username, no need.
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Need basic syntax help: INSERT/WHERE not working...

Post by JAB Creations »

I kept changing the syntax and I'm still getting used to it.

This works...

Code: Select all

$query2 = mysql_query("UPDATE public_accounts SET ip_0 = inet_aton('".$ipaddress."') WHERE username = '".mysql_real_escape_string($_POST['username'])."'");
Thanks. :mrgreen:
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Need basic syntax help: INSERT/WHERE not working...

Post by jaoudestudios »

That looks better.

No problem, glad it works.
Post Reply