[ Total Views: 609 | Total Replies: 6 | Thread Id: 5911 ] |
|
amazer
Bishop of Volkswagenism
kombi pilot
    
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

|
|
vanderaj
A.k.a.: Andrew van der Stock
Super Administrator
a suffusion of yellow
       
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]
|
|
vanderaj
A.k.a.: Andrew van der Stock
Super Administrator
a suffusion of yellow
       
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
|
|
helbus
A.k.a.: Pete S
Super Administrator
Mad fabricator, paint and body
       
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.
|
|
fish
Wolfsburg Wizard
 
Posts: 584
Threads: 32
Registered: April 3rd, 2003
Member Is Offline
Location: sunshine coast qld
Theme: UltimaBB Pro Blue ( Default )
Mood: just cruisin
|
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 !!!!
|
|
amazer
Bishop of Volkswagenism
kombi pilot
    
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

|
|
amazer
Bishop of Volkswagenism
kombi pilot
    
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.
Chris.... kombi pilot, oval dreamer... finisher #26971 2005 city to surf

|
|
amazer
Bishop of Volkswagenism
kombi pilot
    
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

|
|
vanderaj
A.k.a.: Andrew van der Stock
Super Administrator
a suffusion of yellow
       
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
|
|
bugmeister
Slammed & Awesome Dubber

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]
|
|
amazer
Bishop of Volkswagenism
kombi pilot
    
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

|
|