Say I wanted to do UPDATE table SET name = 'bob' and UPDATE table SET age = 55 WHERE name = 'jim' how do I do them in the same mysql_query()?
EDIT: Since this question has a fair amount of views I'd like to point out that as of PHP 5.5 mysql_query and other mysql_* functions are now deprecated and shouldn't be used.
While the main problem is that you missed the closing bracket after bookamandheading, still I would like to advise you to refactor this request for example like this:
$keys = array("bookabandheading", "bookaband", "contactus", "aboutuslisten",
"contactusheading", "nightclubsheading", "acousticheading",
"schoolsheading", "privateheading", "concertsheading",
"festivalsheading", "submissions", "interns", "managementbio",
"latestnews", "artistofthemonth", "artistofthemonthphoto",
"artistofthemonthid", "listentoourartists", "musicianswanted",
"aboutus", "bshowcases", "bandavails");
$set = array();
foreach ($keys as $key) {
$set[] = sprintf(" %s = '%s' ", $key, mysql_escape_string($_POST[$key]));
}
$sql = mysql_query("UPDATE general SET " . implode(", ", $set));
It is much easier to maintain and also a bit more secure by escaping the input.
Update: add where statement example
$where = array();
$where[] = sprintf(" some_string = '%s' ", mysql_escape_string($some_string));
$where[] = sprintf(" some_integer = %d ", $some_integer);
$where = " WHERE " . implode(" AND ", $where);
$sql = mysql_query("UPDATE general SET " . implode(", ", $set) . " " . $where);
I believe you are looking for the following syntax:
INSERT INTO <table> (field1, field2, field3, ...)
VALUES ('value1', 'value2','value3', ...)
ON DUPLICATE KEY UPDATE
field1='value1', field2='value2', field3='value3', ...
Note: With
ON DUPLICATE KEY UPDATE, the affected-rows value per row is1if the row is inserted as a new row,2if an existing row is updated, and0if an existing row is set to its current values.
MySQL Documentation: INSERT ... ON DUPLICATE KEY UPDATE Statement
Yes, most databases allow this. Usually you have to delimit your SQL statements with something. In PostGRES and MySQL it's a semicolon (;). In Microsoft SQL server you should use the keyword GO. [ May 2013 Update: As of SQL Server 2012, you can and should use semicolons to delimit your statements. After SQL Server 2012 (ie. the next version and beyond) these will be mandatory. Using GO is now the deprecated way to do things in SQL2012 and beyond). ]
MySQL / PostGRES example:
DELETE FROM DUMMYTABLE_A where X=${value};
DELETE FROM DUMMYTABLE_B where X=${value};
DELETE FROM DUMMYTABLE_C where X=${value};
MS-SQL example:
DELETE FROM DUMMYTABLE_A where X=${value}
GO
DELETE FROM DUMMYTABLE_B where X=${value}
GO
DELETE FROM DUMMYTABLE_C where X=${value}
Better databases (ie. not MySQL) will also support transactions with BEGIN TRAN / COMMIT TRAN / ROLLBACK TRAN. Using transactions you can actually batch all the statements into one atomic operation, where if part of it failed, all three would be rolled back. See http://www.sqlteam.com/article/introduction-to-transactions for some more information about those.
Most likely all you need is the semicolons between your SQL statements though!
Jon's answer will work, but IMHO using join in LINQ to Entities is usually wrong, because it duplicates code in your model. I can rewrite Jon's query in a much simpler way in L2E:
var query = from customer in db.Customers
from order in customer.Orders
from product in order.Products
from info in product.Info
select new
{
customer.Name,
info.BriefDescription
}
That's about 50% of the typing and 0% of the duplicated code. Consider that your relationships have already been defined in your DB and in your model. Do you really want to duplicate them again in every query you write, and break your queries when you refactor your model?
I've never tried this, but I think you can use mysqli::multi_query. One of the good things about
mysql_queryrejecting multiple statements is that it immediately rules out some of the more common SQL injection attacks, such as adding'; DELETE FROM ... #to a statement. You might therefore want to be careful with multiple statements.