Board Logo
Go To Bottom

Printable Version  
[ Total Views: 609 | Total Replies: 6 | Thread Id: 5911 ]
Author: Subject: you people know eeeverything (non vw computer tech)
Memberamazer
Bishop of Volkswagenism
kombi pilot
******


Avatar


Posts: 3187
Threads: 308
Registered: August 26th, 2002
Member Is Offline

Location: Wollongong
Theme: UltimaBB Pro Blue ( Default )
Mood: Peachy!

posted on May 3rd, 2003 at 01:08 AM
you people know eeeverything (non vw computer tech)


Seems how I have been able to turn to veedub forum members for all sorts of help in the past so here goes again.

[size=4]SIMPLE SQL[/size]
This is for my php assignment at uni. I dont know databases. It's probably going to be very embarrassing but screw you all, it works, I just want it prettier ;) oookkkk.... The table holds a list of temperatures on given days for any given city. The html page takes the name of a city, a start and end date, and then a response page is sent back with the temperatures for the city between those dates.

CREATE TABLE TEMPS
(
a_city varchar(32) NOT NULL,
a_date varchar(10) NOT NULL,
a_temp integer NOT NULL,
a_strdate integer NOT NULL
);

that is the city name, the date in 02/05/2003 format, the date in 20030502 format and the temperature in deg C.

the php (pretty hopeless programming):
$stmt = OCIParse($conn,"SELECT * FROM TEMPS WHERE a_city = '$city' AND a_strdate > $start AND a_strdate < $end" );

OCIExecute($stmt);
print"Data for<br>$city";
print"<table border=1><tr><td>DATE</td><td>TEMP</td></tr>";

while(OCIFetchInto($stmt, $result)){
print"<TR><TD>$result[1]</TD>";
print"<TD>$result[2]</TD></TR>";
}

What I want to do is print the table out in chronological order.

There must be an easy way yes? Without putting all the data into an array?something in the select statement?

Thankyou all in anticipation. But dont wait too long you only got till midnight sunday to get me through it. "We're only 1/3 the way through. :P

[size=4]cheers[/size] :beer




Chris.... kombi pilot, oval dreamer... finisher #26971 2005 city to surf
http://members.ozemail.com.au/~amazer39/vwsigline.jpg
Super Administratorvanderaj
A.k.a.: Andrew van der Stock
Super Administrator
a suffusion of yellow
*********

Rank Avatar

Avatar


Posts: 3122
Threads: 437
Registered: August 26th, 2002
Member Is Offline

Location: Colorado Springs, CO, USA
Theme: UltimaBB Pro Yellow
Mood: In the family again

posted on May 3rd, 2003 at 01:24 AM


You should be using the date format rather than stuffing the date into the database in varchar format.

Read this:

http://www.mysql.com/doc/en/Date_and_time_functions.html 

Change the schema to this:

CREATE TABLE TEMPS
(
a_city varchar(32) NOT NULL,
a_date DATE NOT NULL,
a_temp integer NOT NULL,
);

The date is stored in such a way as to allow manipulation using the ORDER BY and DESC SQL keywords (=fast).

You'll need to convert $start and $end into something that can be compared with a_date, but the following SQL will work:

SELECT * FROM TEMPS WHERE a_city = '$city' AND a_date > $start AND a_date < $end ORDER BY a_date DESC

This will do the trick nicely.

Andrew

[Edited on 2-5-2003 by vanderaj]
Super Administratorvanderaj
A.k.a.: Andrew van der Stock
Super Administrator
a suffusion of yellow
*********

Rank Avatar

Avatar


Posts: 3122
Threads: 437
Registered: August 26th, 2002
Member Is Offline

Location: Colorado Springs, CO, USA
Theme: UltimaBB Pro Yellow
Mood: In the family again

posted on May 3rd, 2003 at 01:32 AM


OCI = Oracle Crappy Interface.

You should consider using PearDB to access the database. That way you can migrate to a decent database later.

Andrew
Super Administratorhelbus
A.k.a.: Pete S
Super Administrator
Mad fabricator, paint and body
*********

Rank Avatar

Avatar


Posts: 7386
Threads: 312
Registered: September 1st, 2002
Member Is Offline

Location: In the garage chopping cars into bits
Theme: UltimaBB Pro Blue
Mood: In the thinking chair

posted on May 3rd, 2003 at 02:02 AM


At this point in time I think I will go out into the garage and see if I can learn how to rebuild an automatic transmission.
:D




Memberfish
Wolfsburg Wizard
***


Avatar


Posts: 584
Threads: 32
Registered: April 3rd, 2003
Member Is Offline

Location: sunshine coast qld
Theme: UltimaBB Pro Blue ( Default )
Mood: just cruisin

shocked.gif posted on May 3rd, 2003 at 05:36 AM


what the!!!! i'm going surfing. :o:o:o:o



Drink and drive your a bloody idiot!!! make it home you'r a legend !!!!
Memberamazer
Bishop of Volkswagenism
kombi pilot
******


Avatar


Posts: 3187
Threads: 308
Registered: August 26th, 2002
Member Is Offline

Location: Wollongong
Theme: UltimaBB Pro Blue ( Default )
Mood: Peachy!

posted on May 3rd, 2003 at 01:32 PM


damn. I searched php database when I should have been searching sql. You dont think of that at 1am. ORDER BY a_datestr is working fine for now. If I get time I'll change it. The reason i've got the datestr is because I didnt know there was a date format. Told you I know nuffin.

Are there alternatives for DESC? Obviously default must be ascending (cos thats how its working :P )

Thanks andrew.




Chris.... kombi pilot, oval dreamer... finisher #26971 2005 city to surf
http://members.ozemail.com.au/~amazer39/vwsigline.jpg
Memberamazer
Bishop of Volkswagenism
kombi pilot
******


Avatar


Posts: 3187
Threads: 308
Registered: August 26th, 2002
Member Is Offline

Location: Wollongong
Theme: UltimaBB Pro Blue ( Default )
Mood: Peachy!

posted on May 3rd, 2003 at 02:36 PM


Hmm gotta love the University of Wollongong. Last week, one of the main systems containing all sorts of assessment stuff, assignments, tests, subject discussion boards, lecture notes etc crashed and they lost 5 weeks worth because the backups either werent being done, or werent backing up properly when they were done. Has created a bit of havoc.

And now the system where we are running the webserver for this assignment appears to have crashed. Cant do anything. Not sure if its a good or a bad thing.:cool:




Chris.... kombi pilot, oval dreamer... finisher #26971 2005 city to surf
http://members.ozemail.com.au/~amazer39/vwsigline.jpg
Memberamazer
Bishop of Volkswagenism
kombi pilot
******


Avatar


Posts: 3187
Threads: 308
Registered: August 26th, 2002
Member Is Offline

Location: Wollongong
Theme: UltimaBB Pro Blue ( Default )
Mood: Peachy!

posted on May 3rd, 2003 at 06:50 PM


andrew!

how do I make a composite primary key so that a city can only get one temperature on any given date.

And if I try to insert something that already exists what will happen, will the insert fail? I'd like to overwrite the first record.




Chris.... kombi pilot, oval dreamer... finisher #26971 2005 city to surf
http://members.ozemail.com.au/~amazer39/vwsigline.jpg
Super Administratorvanderaj
A.k.a.: Andrew van der Stock
Super Administrator
a suffusion of yellow
*********

Rank Avatar

Avatar


Posts: 3122
Threads: 437
Registered: August 26th, 2002
Member Is Offline

Location: Colorado Springs, CO, USA
Theme: UltimaBB Pro Yellow
Mood: In the family again

posted on May 7th, 2003 at 12:01 AM


I don't know the answer to those two questions. I look after databases, not create them! :)

I suggest google, or if you're using Oracle, head off to metalink to find the answers you need.

Andrew
Memberbugmeister
Slammed & Awesome Dubber
**


No Avatar


Posts: 89
Threads: 25
Registered: September 3rd, 2002
Member Is Offline

Location: Logan - Brisbane
Theme: UltimaBB Pro Blue ( Default )

posted on May 7th, 2003 at 08:29 PM


For the date try comparing a string literal against a formatted date using TO_CHAR.

e.g WHERE TO_CHAR(MyDateCol, 'DD-MM-YYYY') = '07-05-2003'

And for the constraint.

ALTER TABLE MyTable ADD CONSTRAINT MyCon UNIQUE (MyCityCol, MyDateCol);

Make sure you format the date for the insert so that the time is 00:00:00, e.g. TO_DATE('07-05-2003 00:00:00', 'DD-MM-YYYY 24HH:MI:SS'), otherwise the constraint won't have the desired effect.

(Edited to Disable Smileys)

[Edited on 8-5-2003 by KruizinKombi]
Memberamazer
Bishop of Volkswagenism
kombi pilot
******


Avatar


Posts: 3187
Threads: 308
Registered: August 26th, 2002
Member Is Offline

Location: Wollongong
Theme: UltimaBB Pro Blue ( Default )
Mood: Peachy!

posted on May 12th, 2003 at 10:55 AM


cool thanks. I'll cut n paste that a store it somewhere for future reference.



Chris.... kombi pilot, oval dreamer... finisher #26971 2005 city to surf
http://members.ozemail.com.au/~amazer39/vwsigline.jpg


  Go To Top


Powered by GaiaBB, © 2011 The GaiaBB Group
(C) 2001-2025 Aussieveedubbers

[ Queries: 40 ] [ PHP: 1.1% - SQL: 98.9% ]