odd display of mysql table contents?

#21

On 1 Jun 2011 16:37:50 GMT, "Erick T. Barkhuis"
<erick.use-net (AT) ardane (DOT) c.o.m> wrote:

Quote:

geoff (AT) invalid (DOT) invalid:

On 1 Jun 2011 12:49:49 GMT, "Erick T. Barkhuis"
erick.use-net (AT) ardane (DOT) c.o.m> wrote:

geoff (AT) invalid (DOT) invalid:

You can see the phpmyadmin view of the definition for DT at

http://www.micro-active.com/dt.gif

Or, taking the line from the exported sql files from phpmyadmin,

1. results_p1

`DT` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,

OK. So your DT-field always contains a timestamp. Then why are you
coding
if ($row['DT'] !=''){
since that will never be false.

Now, would you please try and see what this does:

$sql = "SELECT wm_results_p1.*, ";
$sql .= " FROM_UNIXTIME(UNIX_TIMESTAMP(DT)) as DTnew ";
$sql .= " FROM wm_results_p1 ";
$sql .= " ORDER by FROM_UNIXTIME(UNIX_TIMESTAMP(DT)) ";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
print_r($row);
}

When running this in a browser, have a look at the generated source.

There is some 5MB of generated source so guess I shouldn't put that
here!

Rightly so, Geoff.

There are lots of arrays (one for each row? in which case some 200
arrays), the first one starts

Array
(
[0] => 0000-00-00 00:00:00
[DT] => 0000-00-00 00:00:00
[1] => login_name
[login_name] => login_name

and ends

[604] => 1970-01-01 01:00:00
[DTnew] => 1970-01-01 01:00:00
)

There we are. This row is actually in your table.
Why don't you simply get rid of it?

Erick,

In fact I have just discovered that too! I copied the table and cut
down the number of rows and by a stroke of luck didn't delete the row
with the 01-01-1970 etc in it - so was able to see it.

I cannot imagine how it got there - but have now deleted it and I can
relax again!

Many thanks for your help.

Cheers

Geoff

#22

El 01/06/2011 19:14, geoff (AT) invalid (DOT) invalid escribió/wrote:

Quote:

Forget PHP by now and run this query in your favourite SQL browser:

SELECT
UNIX_TIMESTAMP('0000-00-00'),
UNIX_TIMESTAMP(0),
UNIX_TIMESTAMP('Australia'),
UNIX_TIMESTAMP(NULL)

Sorry Álvaro - I don't know what you mean!

Can you say a little more?

Do you know what SQL is and how to run it?

Yes, and I tried running the above as SQL in phpmyadmin - but I didn't
learn anything from that!

Is that what you had in mind?

I had in mind trying to help you understand your code, but I suppose you
just want a finished snippet you can copy and paste. Sorry for wasting
your time.

Álvaro

No, no I was only too happy to try your idea - just wasn't sure what
you meant.

However, I have found the reason for the 01-10-1970 row - goodness
knows how it got there but it was in the table as an entry!!

It's only that you are inserting dates without proper validation and you
are converting from/to Unix timestamps. Given that 1st Jan 1970 is the
Unix Epoch (the "0" timestamp) it's easy to store 1970 when you have an
invalid or empty date.

--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

#23

On Thu, 02 Jun 2011 11:03:56 +0200, "Álvaro G. Vicario"
<alvaro.NOSPAMTHANX (AT) demogracia (DOT) com.invalid> wrote:

Quote:

However, I have found the reason for the 01-10-1970 row - goodness
knows how it got there but it was in the table as an entry!!

It's only that you are inserting dates without proper validation and you
are converting from/to Unix timestamps. Given that 1st Jan 1970 is the
Unix Epoch (the "0" timestamp) it's easy to store 1970 when you have an
invalid or empty date.

Álvaro

when you mention inserting dates without proper validation - the
data/time is inserted by mysql when other data is entered into the
table. I wouldn't need/be able to validate that - would I?

Cheers

Geoff

#24

On May 31, 9:10*pm, ge... (AT) invalid (DOT) invalid wrote:

Quote:

On Tue, 31 May 2011 17:55:42 +0100, ge... (AT) invalid (DOT) invalid wrote:

this image shows the kind of results displayed,

http://www.micro-active.com/field-names.gif

Why the line with 01-01-1970 and the names of the columns in the
table?

Cheers

Geoff

Because you are using mysql_fetch_array() with its default result
type, just as described in the manual (You DID read the manual didn't
you?).

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

odd display of mysql table contents?

comp.databases.mysql comp.databases.mysql

Re: odd display of mysql table contents? - 06-01-2011 , 12:18 PM

Re: odd display of mysql table contents? - 06-02-2011 , 04:03 AM

Re: odd display of mysql table contents? - 06-02-2011 , 06:30 AM

Re: odd display of mysql table contents? - 06-22-2011 , 07:25 AM