you people know eeeverything (non vw computer tech)

amazer kombi pilot

3,187 posts · joined Aug 2002 · 3 May 2003, 1:08 am

#43570

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

SIMPLE SQL
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

cheers :beer

Chris.... kombi pilot, oval dreamer... finisher #26971 2005 city to surf

vanderaj Administrator a suffusion of yellow

3,130 posts · joined Aug 2002 · 3 May 2003, 1:24 am

#43572

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 Administrator a suffusion of yellow

3,130 posts · joined Aug 2002 · 3 May 2003, 1:32 am

#43573

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 Administrator Mad fabricator, paint and body

7,386 posts · joined Sep 2002 · 3 May 2003, 2:02 am

#43578

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.
😀

EJ22 into Bay Microbus - Finished project build thread- click on this spot here for info

fish Wolfsburg Wizard

584 posts · joined Apr 2003 · 3 May 2003, 5:36 am

#43584

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 kombi pilot

3,187 posts · joined Aug 2002 · 3 May 2003, 1:32 pm

#43618

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 kombi pilot

3,187 posts · joined Aug 2002 · 3 May 2003, 2:36 pm

#43633

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 kombi pilot

3,187 posts · joined Aug 2002 · 3 May 2003, 6:50 pm

#43676

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 Administrator a suffusion of yellow

3,130 posts · joined Aug 2002 · 7 May 2003, 12:01 am

#44941

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

89 posts · joined Sep 2002 · 7 May 2003, 8:29 pm

#45171

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 kombi pilot

3,187 posts · joined Aug 2002 · 12 May 2003, 10:55 am

#46440

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