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