---
title: "till mysql"
type: "forum-thread"
url: "https://www.webforum.nu/amne/databaser-sql/45348-till-mysql"
topic: "Databaser & SQL"
topic_url: "https://www.webforum.nu/amne/databaser-sql"
author: "ampy"
published: "2002-06-06T11:28:01.000Z"
updated: "2002-06-11T19:33:47.000Z"
replies: 14
views: 349
page: 1
pages: 1
language: "sv-SE"
site: "webForum — webforum.nu"
rights: "Upphovsrätten till varje inlägg tillhör dess författare."
attribution: "Citera som: webForum, https://www.webforum.nu/amne/databaser-sql/45348-till-mysql"
---

# till mysql

## #1 — ampy, 2002-06-06T11:28Z

```
strSQL = "Select count(*) as antal, " & _
" year(datum) & '-' & right('0' & month(datum),2) & '-' & right('0' & day(datum),2)  as inLoggad " & _
"from inloggningar " & _ 
"WHERE vad = 'loggain' " & _ 
"AND datediff('d',datum,now()) <= 9 " & _
"group by year(datum) & '-' & right('0' & month(datum),2) & '-' & right('0' & day(datum),2) " & _
"order by 2 desc "
```

Hur skriver man denna kod till en mysql-databas, denna är anpasad till en access-databas.

Permalänk: https://www.webforum.nu/p/45348

## #2 — LarsG, 2002-06-06T11:46Z

Funktionen datediff finns inte i Mysql 

```
datediff('d',datum,now())
```

borde kunna skrivas som

```
to_days(now()) - to_days(datum)
```

För att få ut en del av ett datum kan du använda funktionen extract

```
extract(year from datum)
extract(month from datum)
extract(day from datum)
```

Permalänk: https://www.webforum.nu/p/690488

## #3 — LAMP, 2002-06-06T11:58Z

```
... WHERE datum < SUBDATE(NOW(),INTERVAL 9 DAY)
```

Permalänk: https://www.webforum.nu/p/690498

## #4 — ampy, 2002-06-10T11:54Z

OK, koden ser nu ut så här:

```
strSQL = "Select count(*) as antal, " & _
" extract(year from datum) & '-' & extract(month from datum) & '-' & extract(day from datum) as inLoggad " & _
"from inloggningar " & _ 
"WHERE vad = 'loggain' " & _ 
"AND to_days(now()) - to_days(datum) <= 9 " & _
"group by extract(year from datum) & '-' & extract(month from datum) & '-' & extract(day from datum) " & _
"order by 2 desc "

Set oRs = Connect.Execute (strSQL)

while not oRS.eof
response.write "" & year(oRs("inloggad")) & "-" & right("0" & month(oRs("inloggad")),2) & "-" & right("0" & day(oRs("inloggad")),2) & ": " & oRS("antal") & " inloggningar<br>"
oRS.movenext
wend
```

Men då skriver den ut ngt helt konstigt, detta skriver den ut:

1899-12-30: 12 inloggningar

Vilket inte stämmer, vad kan vara felet i koden?

Permalänk: https://www.webforum.nu/p/695136

## #5 — LarsG, 2002-06-10T18:34Z

Stämmer antal?

& betyder inte strängkonkatenering i Mysql utan bitwise and.
Du måste använda functionen concat

```
strSQL = "Select count(*) as antal, " & _
" concat(cast(extract(year from datum) as char(4)) ,'-', " &_
" cast(extract(month from datum) as char(2)) , '-' , " & _
" cast(extract(day from datum) as char(2)) as inLoggad " & _
"from inloggningar " & _ 
"WHERE vad = 'loggain' " & _ 
"AND to_days(now()) - to_days(datum) <= 9 " & _
"group by " & _
" concat(cast(extract(year from datum) as char(4)) ,'-', " &_
" cast(extract(month from datum) as char(2)) , '-' , " & _
" cast(extract(day from datum) as char(2)) " & _
"order by 2 desc "
```

Permalänk: https://www.webforum.nu/p/695374

## #6 — ampy, 2002-06-11T18:48Z

Då får jag detta fel:

```
Microsoft OLE DB Provider for ODBC Drivers error '80040e09' 

[TCX][MyODBC]You have an error in your SQL syntax near '(extract(year from datum) as char(4)) ,'-', cast(extract(month from datum) as c' at line 1
```

Permalänk: https://www.webforum.nu/p/695928

## #7 — LarsG, 2002-06-11T18:57Z

Det saknas )

```
strSQL = "Select count(*) as antal, " & _
" concat(cast(extract(year from datum) as char(4)) ,'-', " &_
" cast(extract(month from datum) as char(2)) , '-' , " & _
" cast(extract(day from datum) as char(2))) as inLoggad " & _
"from inloggningar " & _ 
"WHERE vad = 'loggain' " & _ 
"AND to_days(now()) - to_days(datum) <= 9 " & _
"group by " & _
" concat(cast(extract(year from datum) as char(4)) ,'-', " &_
" cast(extract(month from datum) as char(2)) , '-' , " & _
" cast(extract(day from datum) as char(2))) " & _
"order by 2 desc "
```

Permalänk: https://www.webforum.nu/p/695939

## #8 — ampy, 2002-06-11T19:03Z

Får fortfarande samma fel och jag hittar inte felet i koden. Ser inte ut så att det saknas någon ) eller (

Permalänk: https://www.webforum.nu/p/695946

## #9 — LarsG, 2002-06-11T19:09Z

jag läste mysql's manual. SOm vanligt så följer dom inte ANSI sql standard.

Blir det bättre utan cast?

```
strSQL = "Select count(*) as antal, " & _
" concat(extract(year from datum)  ,'-', " &_
" extract(month from datum)  , '-' , " & _
" extract(day from datum) ) as inLoggad " & _
"from inloggningar " & _ 
"WHERE vad = 'loggain' " & _ 
"AND to_days(now()) - to_days(datum) <= 9 " & _
"group by " & _
" concat(extract(year from datum) ,'-', " &_
" extract(month from datum) , '-' , " & _
" extract(day from datum) ) " & _
"order by 2 desc "
```

Permalänk: https://www.webforum.nu/p/695949

## #10 — ampy, 2002-06-11T19:11Z

Nu fungerar det bra  :e 

Vart läste du mysql's manual?

Permalänk: https://www.webforum.nu/p/695952

## #11 — LarsG, 2002-06-11T19:14Z

<https://www.mysql.com>

documentation

Permalänk: https://www.webforum.nu/p/695956

## #12 — ampy, 2002-06-11T19:17Z

En annan fråga som vi lika gärna kan ta i denna tråd.

Varför skriver denna kod inte ut någonting, jag får inte felmeddelande heller:

```
SQL = "Select id from members where bild = 'bild_yes'"

Randomize
Set RS = Connect.Execute(SQL)
if  not rs.eof then
    bilder = rs.getrows()
    antalBilder = ubound(bilder,2) + 1
    For i = 0 to 4
          if i = antalBilder then exit for
        post = Int(RND*(antalBilder - i))
	Response.write "<a href='krypin.asp?id="&bilder(0,post)&"'><img src='krypinbild/bild"&bilder(0,post)&".jpg' border='0' width='80' height='105'></a> "
                bilder(0,post) = bilder(0,antalbilder-i-1)

Next
end if
Rs.Close
Set Rs = Nothing
```

Permalänk: https://www.webforum.nu/p/695958

## #13 — LarsG, 2002-06-11T19:26Z

Du får lägga in testutskrifter så att du ser vad som händer.

```
dim debug
debug = true
SQL = "Select id from members where bild = 'bild_yes'"

Randomize
Set RS = Connect.Execute(SQL)
if  not rs.eof then
    if  debug then  response.write "Data found <br>"
    bilder = rs.getrows()
    antalBilder = ubound(bilder,2) + 1
    if  debug then response.write "Antal bilder:" & antalBilder & "<br>"
    For i = 0 to 4
          if i = antalBilder then exit for
        post = Int(RND*(antalBilder - i))
                if  debug then response.write "Post:" & post & "<br>"
	Response.write "<a href='krypin.asp?id="&bilder(0,post)&"'><img src='krypinbild/bild"&bilder(0,post)&".jpg' border='0' width='80' height='105'></a> "
                bilder(0,post) = bilder(0,antalbilder-i-1)

Next
end if
Rs.Close
Set Rs = Nothing
```

Sen, när det fungerar, så är det bara att ändra till

```
debug = false
```

Permalänk: https://www.webforum.nu/p/695965

## #14 — ampy, 2002-06-11T19:29Z

Nu skriver den inte ut någonting heller.

Permalänk: https://www.webforum.nu/p/695967

## #15 — LarsG, 2002-06-11T19:33Z

Då finns det inga poster där villkoret

```
bild='bild_yes'
```

är uppfyllt. Kan du köra något interaktivt verktyg (Mysql front kanske) och se vad som finns i tabellen.

Permalänk: https://www.webforum.nu/p/695970

---

Tråden på webben: https://www.webforum.nu/amne/databaser-sql/45348-till-mysql