MySQL query doesnt work

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

Erm, that would be because you aren't setting anything. You just have a WHERE clause no set.

Code: Select all

UPDATE bleh SET foo = bar WHERE bleh ....
EDIT: Looks like you lost your Set somewhere in between us helping you with your syntax.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Nice catch jaimel...

Code: Select all

<?php
$sql = "UPDATE settings SET 
        `title`= '$title', 
        `username`= '$name', 
        `email`= '$email', 
        `tablewidth`= '$width', 
        `menubar`= '$menubar', 
        `postorder`= '$postorder', 
        `posting`= '$posting', 
        `kwikpost`= '$kwikpost', 
        `maxshow`= '$maxshow', 
        `im`= '$im', 
        `links`= '$links', 
        `logo`= '$logo', 
        `menubarlocation`= '$menubarlocation', 
        `kwikpostlocation`= '$kwikpostlocation'
        WHERE `somefield` = $somevalue"; 
?>
adamb10
Forum Commoner
Posts: 91
Joined: Sat Jun 24, 2006 7:44 pm

Post by adamb10 »

Well I'm lost, put it that way. My webhost account has been hacked and everything was deleted. Anyway this is what comes up...

Code: Select all

Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in /home/adamb10/public_html/ub2/sources/admin/savesettings.php on line 14
Heres the code...

Code: Select all

$r = mysql_query($sql);
print mysql_affected_rows($r);
$sql = "UPDATE settings WHERE `title`='".$title."', `username`='".$name."', `email`='". $email."', `tablewidth`='".$width."', `menubar`='".$menubar."', `postorder`='".$postorder."', `posting`='".$posting."', `kwikpost`='".$kwikpost."', `maxshow`='".$maxshow."', `im`='".$im."', `links`='".$links."', `logo`='".$logo."', `menubarlocation`='".$menubarlocation."', `kwikpostlocation`='".$kwikpostlocation."' or print(mysql_error())";
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Post by daedalus__ »

I refer you to what Jamiel and Everah said.

I also refer you to the MySQL manual:

http://dev.mysql.com/doc/
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

adamb10, without sounding rude, please look at your code carefully and try figure things out yourself before rushing to post a problem. You are setting your $sql variable after you've already tried to execute a query using that exact variable before it was even set.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

adamb10 wrote:Anyway this is what comes up...

Code: Select all

Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in /home/adamb10/public_html/ub2/sources/admin/savesettings.php on line 14
Heres the code...

Code: Select all

$r = mysql_query($sql);
print mysql_affected_rows($r);
$sql = "UPDATE settings WHERE `title`='".$title."', `username`='".$name."', `email`='". $email."', `tablewidth`='".$width."', `menubar`='".$menubar."', `postorder`='".$postorder."', `posting`='".$posting."', `kwikpost`='".$kwikpost."', `maxshow`='".$maxshow."', `im`='".$im."', `links`='".$links."', `logo`='".$logo."', `menubarlocation`='".$menubarlocation."', `kwikpostlocation`='".$kwikpostlocation."' or print(mysql_error())";
Your code flow and syntax have problems. Try this...

Code: Select all

<?php
$sql = "UPDATE settings SET
        `title`= '$title',
        `username`= '$name',
        `email`= '$email',
        `tablewidth`= '$width',
        `menubar`= '$menubar',
        `postorder`= '$postorder',
        `posting`= '$posting',
        `kwikpost`= '$kwikpost',
        `maxshow`= '$maxshow',
        `im`= '$im',
        `links`= '$links',
        `logo`= '$logo',
        `menubarlocation`= '$menubarlocation',
        `kwikpostlocation`= '$kwikpostlocation'
        WHERE `somefield` = $somevalue";
if (!$r = mysql_query($sql))
{
    die('Could not run the query ' . $sql . ': ' . mysql_error());
}

if (!mysql_affected_rows($r))
{
    echo 'There was not changes to the record';
}
else
{
    echo 'The record updated successfully!';
}
?>
adamb10
Forum Commoner
Posts: 91
Joined: Sat Jun 24, 2006 7:44 pm

Post by adamb10 »

<?php
$sql = "UPDATE settings SET
`title`= '$title',
`username`= '$name',
`email`= '$email',
`tablewidth`= '$width',
`menubar`= '$menubar',
`postorder`= '$postorder',
`posting`= '$posting',
`kwikpost`= '$kwikpost',
`maxshow`= '$maxshow',
`im`= '$im',
`links`= '$links',
`logo`= '$logo',
`menubarlocation`= '$menubarlocation',
`kwikpostlocation`= '$kwikpostlocation'
WHERE `somefield` = $somevalue";
if (!$r = mysql_query($sql))
{
die('Could not run the query ' . $sql . ': ' . mysql_error());
}

if (!mysql_affected_rows($r))
{
echo 'There was not changes to the record';
}
else
{
echo 'The record updated successfully!';
}
?>
Ok, here is the entire file...

Code: Select all

<?
//savesettings.php

//essential functions to life
db_connect();

//user logged in?
if(session_is_registered('ub2')){

if(empty($password) && empty($password2)){
    
$r = mysql_query($sql);
print mysql_affected_rows($r);
$sql = "UPDATE settings WHERE `title`='".$title."', `username`='".$name."', `email`='". $email."', `tablewidth`='".$width."', `menubar`='".$menubar."', `postorder`='".$postorder."', `posting`='".$posting."', `kwikpost`='".$kwikpost."', `maxshow`='".$maxshow."', `im`='".$im."', `links`='".$links."', `logo`='".$logo."', `menubarlocation`='".$menubarlocation."', `kwikpostlocation`='".$kwikpostlocation."' or print(mysql_error())";     

    success('Settings saved.<br>  Click <a href = "?action=admin">here</a> to go back to the admin   panel.');
	}
	if($password != "") {
	   if($password == $password2){
	   mysql_query ("UPDATE settings WHERE `title`='".$title."', `username`='".$name."', `email`='". $email."', `tablewidth`='".$width."', `menubar`='".$menubar."', `postorder`='".$postorder."', `posting`='".$posting."', `kwikpost`='".$kwikpost."', `maxshow`='".$maxshow."', `im`='".$im."', `links`='".$links."', `logo`='".$logo."', `menubarlocation`='".$menubarlocation."', `kwikpostlocation`='".$kwikpostlocation."', `password`='".$password."'") or print(mysql_error()); 	
	    success('Settings saved.<br>  Click <a href = "?action=admin">here</a> to go back to the admin panel.');
	       } else {
	       error('The following error was returned:<br>
	              <b>The 2 passwords did not match</b>');
	              }
	              }
	   
	
	} else {
	error('You do not have permission to access this page because:<br>
	<b>You are not logged in</b>');
	}

?>
The diff. between the first query and the 2nd is the 2nd saves the password and the first does not. Like I said before I cant think today so thats why I am acting like a n00b to simple problems. :(
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You realize you have yet to update your query with correct SQL syntax? Even if the code we have developed for you works, the SQL is going to kill it. Your update query syntax is totally wrong. Please reread the last two posts I've made regarding the query, substitute the WHERE clause field and comparison value for your own, then report your code.

Trying to use the code you are posting will never work.
adamb10
Forum Commoner
Posts: 91
Joined: Sat Jun 24, 2006 7:44 pm

Post by adamb10 »

Code: Select all

<?
//savesettings.php

//essential functions to life
db_connect();

//user logged in?
if(session_is_registered('ub2')){

if(empty($password) && empty($password2)){
    
$r = mysql_query($sql);
print mysql_affected_rows($r);
mysql_query("UPDATE settings SET `title`='".$title."', `username`='".$name."', `email`='". $email."', `tablewidth`='".$width."', `menubar`='".$menubar."', `postorder`='".$postorder."', `posting`='".$posting."', `kwikpost`='".$kwikpost."', `maxshow`='".$maxshow."', `im`='".$im."', `links`='".$links."', `logo`='".$logo."', `menubarlocation`='".$menubarlocation."', `kwikpostlocation`='".$kwikpostlocation."'");   

    success('Settings saved.<br>  Click <a href = "?action=admin">here</a> to go back to the admin   panel.');
	}
	if($password != "") {
	   if($password == $password2){
	   mysql_query ("UPDATE settings SET `title`='".$title."', `username`='".$name."', `email`='". $email."', `tablewidth`='".$width."', `menubar`='".$menubar."', `postorder`='".$postorder."', `posting`='".$posting."', `kwikpost`='".$kwikpost."', `maxshow`='".$maxshow."', `im`='".$im."', `links`='".$links."', `logo`='".$logo."', `menubarlocation`='".$menubarlocation."', `kwikpostlocation`='".$kwikpostlocation."', `password`='".$password."'") or print(mysql_error()); 	
	    success('Settings saved.<br>  Click <a href = "?action=admin">here</a> to go back to the admin panel.');
	       } else {
	       error('The following error was returned:<br>
	              <b>The 2 passwords did not match</b>');
	              }
	              }
	   
	
	} else {
	error('You do not have permission to access this page because:<br>
	<b>You are not logged in</b>');
	}

?>
Removed the Where clause, replaced w/ SET.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

You need a WHERE clause otherwise every row is going to be updated. You are still doing a mysql_query($sql); when $sql does not yet exist.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

Are you maybe wanting to do an INSERT?
adamb10
Forum Commoner
Posts: 91
Joined: Sat Jun 24, 2006 7:44 pm

Post by adamb10 »

The code simply updates the settings for the software. I dont need Insert.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

So there is only one row in the Settings Table?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Everah wrote:Please reread the last two posts I've made regarding the query, substitute the WHERE clause field and comparison value for your own, then report your code.
Substitute, not remove. Look at your code flow and read it logically to yourself. Doing that will make it stand out a little bit what jamiel is trying to tell you.

Code: Select all

<?php
/**
 * What is the value of $sql in this statement...
 **/
$r = mysql_query($sql);

/**
 * Without a valid $r, this will error on you
 **/
print mysql_affected_rows($r);

/**
 * Update SQL syntax is UPDATE `table` SET `fieldname` = 'value' WHERE `searchfield` = 'searchvalue'
 **/
mysql_query("UPDATE settings SET `title`='".$title."', `username`='".$name."', `email`='". $email."', `tablewidth`='".$width."', `menubar`='".$menubar."', `postorder`='".$postorder."', `posting`='".$posting."', `kwikpost`='".$kwikpost."', `maxshow`='".$maxshow."', `im`='".$im."', `links`='".$links."', `logo`='".$logo."', `menubarlocation`='".$menubarlocation."', `kwikpostlocation`='".$kwikpostlocation."'");   
?>
You may need to go back to the beginning and start fresh, running the code through some logic to get an idea of what it wants to do.
adamb10
Forum Commoner
Posts: 91
Joined: Sat Jun 24, 2006 7:44 pm

Post by adamb10 »

I see. The Where clause only updates the tables/fields that are well...updated. :)
Post Reply