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

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]

OCI = Oracle Crappy Interface.

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

Andrew

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.

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

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.

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.

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.

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

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]

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