I need to get rows from the database where the records are of one month. I tried this SELECT:
$result = mysql_query("SELECT * FROM my_table WHERE DATEPART('month', date_column)=11");
In database is a lot of rows that have a date in the 11. month, but i do not get any results. Can anyone help me? Thank!
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
When you enclose expressions in parentheses, Postgres interprets the result as a tuple -- essentially a struct or record.
So, your statement:
SELECT (
nextval('"KPI_MEASURE_ID_seq"'::regclass),
now(),
kpi_project.id,
kpi_measure.kpi_frequency_id,
kpi_metric.id ,
kpi_measure.branch ,
sum(kpi_measure.value)
)
is returning one value. That value is a record.
Databases that do not support tuples would return an error.
The solution is to remove the parentheses.
A bit of a complex version due to the lack of analytic functions in MySQL; I'm assuming it's the SUM of val1 you need;
SELECT SUM(val1) val1, MAX(val2) val2
FROM (
SELECT s1.val1, COUNT(s3.val1) grouping, s1.val2 val2
FROM sequence s1
LEFT JOIN sequence s2 ON s1.val1 >= s2.val1
LEFT JOIN sequence s3 ON s3.val1 = s2.val1 - 1 AND s2.val2 <> s3.val2
GROUP BY s1.val1
) a
GROUP BY grouping
ORDER BY grouping
An SQLfiddle to test with.
If you add grouping to the selection list to make the order obvious, you get
val1 val2 grouping
15 1 0
40 2 1
65 1 2
There is no
DATEPARTfunction in MySQL. UseMONTH(date_column)orEXTRACT(MONTH FROM date_column)instead.