Page 1 of 1

mySQL query question

Posted: Thu Jul 21, 2005 4:08 pm
by bla5e
Whats the mysql_query line to either
1) Insert the data from the form if it doesnt already exist
or
2) If the data exists, to update it

Posted: Thu Jul 21, 2005 4:11 pm
by bla5e

Code: Select all

<?php
mysql_query("INSERT INTO `users` VALUES ('', '".$_POST['author']."' '".$_POST['email']."' WHERE `author` = '".$_POST['author']."' UPDATE '".$_POST['email']."'");

?>
thats what I was thinkin would work

Posted: Thu Jul 21, 2005 4:12 pm
by ol4pr0

Code: Select all

# INSERT
$query = "INSERT INTO <tablename> (field1,field1,field1,field1) VALUES (data1,data2,data3,data4)";
#update
$query = "UPDATE <tablename> set field1='data1',field2='data2' WHERE id='$id'";

Posted: Thu Jul 21, 2005 4:23 pm
by bla5e

Code: Select all

<?php
# INSERT
$query = "INSERT INTO `users` ('','author','email') VALUES ('', '".$_POST['author']."', '".$_POST['txt']."'";
#update
$query = "UPDATE `users` set id='',author=".$_POST['author'].",email=".$_POST['email'].", WHERE author=".$_POST['author']."";
#add blog
$query = "INSERT INTO `blog` VALUES ('', '".$_POST['author']."', '".$_POST['txt']."')";
?>
I dont get any errors, but its not doin anything to the mySQL database.
by nothing i mean, it doesnt add the blog post, or the new user, or update the old user

Posted: Thu Jul 21, 2005 4:30 pm
by ol4pr0

Code: Select all

<?php
# INSERT
$query = "INSERT INTO `users` ('author','email') VALUES ('".$_POST['author']."','".$_POST['txt']."'";
# UPDATE *** NO COMMA BEFORE THE WHERE
$query = "UPDATE `users` set id='',author='".$_POST['author']."',email='".$_POST['email']."' WHERE author='".$_POST['author']."'";
#add blog
$query = "INSERT INTO `blog` ('author','email') VALUES ('".$_POST['author']."','".$_POST['txt']."')";

// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// Insert a row of information into the table "example"
mysql_query($query) or die(mysql_error()); 
echo $query."<br/>";
?>

Posted: Thu Jul 21, 2005 4:37 pm
by bla5e
got an error
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 ''author','email') VALUES ('name','txt')' at line 1

Code: Select all

<?        
include ("config.php");
# INSERT
$query = "INSERT INTO `users` ('author','email') VALUES ('".$_POST['author']."','".$_POST['txt']."'";
# UPDATE *** NO COMMA BEFORE THE WHERE
$query = "UPDATE `users` set id='',author='".$_POST['author']."',email='".$_POST['email']."' WHERE author='".$_POST['author']."'";
#add blog
$query = "INSERT INTO `blog` ('author','email') VALUES ('".$_POST['author']."','".$_POST['txt']."')"; 
// Make a MySQL Connection
mysql_connect($server, $user, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error()); 
// Insert a row of information into the table "example"
mysql_query($query) or die(mysql_error()); 
echo $query."<br/>";
echo ("<center><h2> Blog Script </h2><Br><BR>");
echo ("<strong>".$font."Blog Added!</strong><br>");
echo ("<a href=\"index.php\">Back</a></font>");
?>

Posted: Thu Jul 21, 2005 4:43 pm
by bla5e
it doesnt work (i think) because each new query whipes the old one off.. how do we get it to know which one to use?

Posted: Thu Jul 21, 2005 5:56 pm
by ol4pr0

Code: Select all

// Make a MySQL Connection
mysql_connect($server, $user, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error()); 
// Insert a row of information into the table "example"
$query1 = mysql_query("INSERT INTO `users` ('author','email') VALUES ('".$_POST['author']."','".$_POST['txt']."'") or die(mysql_error()); 
if ($query1) {
	echo "First Query Succeful<br />";
	$query2 = mysql_query("UPDATE `users` set id='',author='".$_POST['author']."',email='".$_POST['email']."' WHERE author='".$_POST['author']."'") or die(mysql_error()); 
	if ($query2) {
		echo "Second Query Succeful<br />";
		$query3 = mysql_query("INSERT INTO `blog` ('author','email') VALUES ('".$_POST['author']."','".$_POST['txt']."')") or die(mysql_error()); 
		if ($query3) {
			echo "Thirth Query Succeful<br />";
		}
	}
}

Error

Posted: Thu Jul 21, 2005 8:19 pm
by bla5e

Code: Select all

<?        
include ("config.php");

mysql_connect($server, $user, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error()); 
// Insert a row of information into the table "example"
$query1 = mysql_query("INSERT INTO `users` ('id', 'author','email') VALUES ('','".$_POST['author']."','".$_POST['email']."')") or die(mysql_error()); 
if ($query1) {    
	echo "First Query Succeful<br />";    
	$query2 = mysql_query("UPDATE `users` set id='',author='".$_POST['author']."',email='".$_POST['email']."' WHERE author='".$_POST['author']."'") or die(mysql_error());     
	if ($query2) {        
		echo "Second Query Succeful<br />";        
		$query3 = mysql_query("INSERT INTO `blog` ('id', 'author','txt') VALUES ('', '".$_POST['author']."','".$_POST['txt']."')") or die(mysql_error());         
		if ($query3) {            
			echo "Thirth Query Succeful<br />";        
		}    
	}
}

echo $query."<br/>";
echo ("<center><h2> Blog Script </h2><Br><BR>");
echo ("<strong>".$font."Blog Added!</strong><br>");
echo ("<a href=\"index.php\">Back</a></font>");
?>
Error Code:
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 ''author','email') VALUES ('name','txt'' at line 1

Posted: Thu Jul 21, 2005 9:15 pm
by ol4pr0
Take a good look a that error line

near ''author','email')
see the double '' ?

And dont use the id... Keep it out !

insert (id,author,whatever) values ('','author','whatever') should be
insert (author,whatever) values ('author','whatever')

Posted: Fri Jul 22, 2005 9:56 am
by pickle
You can use REPLACE() syntax to do both your questions in 1 query. REPLACE will first try and find a matching row on a unique field. If it finds a match, it'll replace the data. If it doesn't, it'll add a new row.

REPLACE() syntax