[RESOLVED] else for a database

jerrylouise

okies dokies i have this new feature of my site im making well i basically want to make some changes too it.

$query = "UPDATE `users_cookie_bin` SET `theme` = '$theme' WHERE `username` = '$usrname'";

okies thats all it dose is update a database the problem is i don't now now to make it so that it will add a new record if there is no record for the user in the database.

bpat1434

Ah, then you want to use an INSERT ON DUPLICATE UPDATE syntax....

INSERT INTO `users_cookie_bin` (`theme`, `username`) VALUES ('$theme', '$username')
ON DUPLICATE KEY UPDATE SET `theme` = '$theme'

Now, that requires that username is a Key inside of your table.

NogDog

Or use the REPLACE command.

bpat1434

Just be warned ... "REPLACE is a MySQL extension to the SQL standard" ... so if you're looking for something that is SQL standard (as in can be easily transported to Postgres or MSSQL or any RDBMS that adheres to the standard) use my suggestion. But if you're not looking for that, REPLACE is easier syntax but does the same as INSERT ... ON DUPLICATE KEY UPDATE ...

NogDog

Good point. I've used REPLACE in a number of MySQL-based applications because it's so convenient, that I just tend to think of it as "standard". 🙂

jerrylouise

ok it keeps erroring out. Both username and theme are varcar. Username has been tried as a primary key and a unique key both times failing.

There was an error, please try again.Query was empty

$query = "INSERT INTO `users_cookie_bin` (`theme`, `username`) VALUES ('$theme', '$usrname') ON DUPLICATE KEY UPDATE SET `theme` = '$theme'";
$query = mysql_query($sql);
if ( !mysql_query( $query ) ) {
    echo "There was an error, please try again."; 
    echo mysql_error( ); 	
}

halojoy

you use
$query = mysql_query($sql);

so rename first line to: $sql = "INSERT INTO

jerrylouise

im still getting the same error.

halojoy

well, the result of a mysql_query on $sql
we can call result

then we test the result ( we do not use mysql_query for this )

See this link examples: [man]mysql_query[/man]
try this

<?php

$sql = "INSERT INTO `users_cookie_bin` (`theme`, `username`) VALUES ('$theme', '$usrname') ON DUPLICATE KEY UPDATE SET `theme` = '$theme'";

// make a query using $sql string
$result = mysql_query( $sql );

if (!$result) {
    echo "There was an error, please try again. ";
    echo mysql_error( ); 
    exit(); //stop the script    

}

echo '<b>Good! Here are the results:</b><br>';

// fetch the rows of result, one by one, as long is a result
while ( $row = mysql_fetch_row($result) ) {

 echo '<pre>'.print_r( $row, true ).'</pre>';   

}

?>

bpat1434

Jerry:

Just so you know where you're going wrong, look at the highlighted line:

[color=green]$query = "INSERT INTO `users_cookie_bin` (`theme`, `username`) VALUES ('$theme', '$usrname') ON DUPLICATE KEY UPDATE SET `theme` = '$theme'";[/color]
[color=red]$query = mysql_query($sql);[/color]
[color=orange]if ( !mysql_query( $query ) ) {[/color]
    echo "There was an error, please try again.";
    echo mysql_error( );     

}

You first define $query as the query string to be executed (good 🙂 ).

Then you run an empty query ($sql) and save that output to the variable $query, overwriting the SQL statements that were previously there.

Then you use the $query variable again as the SQL part of the mysql_query() call, (which at this point $query is false since the red area returned false for no query), which errors out since false is an empty query.

If you remove the red line in the code above, you should see something other than an error message (or something other than the same error message).

Username should be just a unique key, not necessarily a primary index.

Hope that explanation helps some.

@: Why would we need to fetch the rows if we're INSERTing or UPDATEing a row? Just thought I'd mention that.

jerrylouise

halojoy wrote:

well, the result of a mysql_query on $sql
we can call result

then we test the result ( we do not use mysql_query for this )

See this link examples: [man]mysql_query[/man]
try this

<?php

$sql = "INSERT INTO `users_cookie_bin` (`theme`, `username`) VALUES ('$theme', '$usrname') ON DUPLICATE KEY UPDATE SET `theme` = '$theme'";

// make a query using $sql string
$result = mysql_query( $sql );

if (!$result) {
    echo "There was an error, please try again. ";
    echo mysql_error( ); 
    exit(); //stop the script    

}

echo '<b>Good! Here are the results:</b><br>';

// fetch the rows of result, one by one, as long is a result
while ( $row = mysql_fetch_row($result) ) {

 echo '<pre>'.print_r( $row, true ).'</pre>';   

}

?>

okies using this i get this error

There was an error, please try again. 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 'SET theme = 'Pink'' at line 1

halojoy

bpat1434 wrote:
@: Why would we need to fetch the rows if we're INSERTing or UPDATEing a row? Just thought I'd mention that.

Yes it was just a guess,
you know bpat1434, when it comes to MySQL, I am from Barcelona!

Manuel, shakes his head, grins, and says,
"No, Señor Fawlty, I know nothing; I am from Barcelona!"
and leaves.

bpat1434

Sorry, my bad. The query should be:

INSERT INTO `users_cookie_bin` (`theme`, `username`) VALUES ('$theme', '$usrname') ON DUPLICATE KEY UPDATE `users_cookie_bin` SET `theme` = '$theme'

Forgot the tablename in the UPDATE statement...

jerrylouise

errors out once again
There was an error, please try again. 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 'SET theme = 'Blue'' at line 1

<?php
}
else  if (isset($_GET['subpage']) && $_GET['subpage'] == 'upload') {
////constants////
$theme = $_POST['theme'];
///start the upload
$sql = "INSERT INTO `users_cookie_bin` (`theme`, `username`) VALUES ('$theme', '$usrname') ON DUPLICATE KEY UPDATE `users_cookie_bin` SET `theme` = '$theme'"; 
// make a query using $sql string 
$result = mysql_query( $sql ); 

if (!$result) { 
    echo "There was an error, please try again. "; 
    echo mysql_error( ); 
    exit(); //stop the script     

}
?>

bpat1434

Crap.... I wish I could read today!!

INSERT INTO `users_cookie_bin` (`theme`, `username`) VALUES ('$theme', '$usrname') ON DUPLICATE KEY UPDATE `theme` = '$theme'

That is the correct query. For whatever reason, I read it as differently before....

jerrylouise

bpat1434 wrote:
Crap.... I wish I could read today!!
INSERT INTO `users_cookie_bin` (`theme`, `username`) VALUES ('$theme', '$usrname') ON DUPLICATE KEY UPDATE `theme` = '$theme'
That is the correct query. For whatever reason, I read it as differently before....

^_ thank you very very much indeedies it works perfict now.

bpat1434

great!!