Return bool from query

[frizzle]

Hi there,

I have a mySQL system with a news publishing part in it:
Admins can create new items with text in it, and they have an option to
create 'fulltexts', so you'd get "read more ..." on the front page,
click it and read the fulltext.

Is there a possibility for mySQL (query) to check if 'fulltext' is
empty or not, and only return true or false, so i don't have to put the
whole fulltext into the mysql_fetch_array() to decide wether or not to
show 'read_more', or should i create an extra boolean field in the DB
saying fulltext y/n ?

Greetings Frizzle.

[Jerry Stuckle]

frizzle wrote:

Hi there,

I have a mySQL system with a news publishing part in it:
Admins can create new items with text in it, and they have an option to
create 'fulltexts', so you'd get "read more ..." on the front page,
click it and read the fulltext.

Is there a possibility for mySQL (query) to check if 'fulltext' is
empty or not, and only return true or false, so i don't have to put the
whole fulltext into the mysql_fetch_array() to decide wether or not to
show 'read_more', or should i create an extra boolean field in the DB
saying fulltext y/n ?

Greetings Frizzle.

Put it in a separate table with just the article's id and the text. If
the id exists in the second table, there is more text.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[frizzle]

Jerry Stuckle wrote:

frizzle wrote:

Hi there,

I have a mySQL system with a news publishing part in it:
Admins can create new items with text in it, and they have an option to
create 'fulltexts', so you'd get "read more ..." on the front page,
click it and read the fulltext.

Is there a possibility for mySQL (query) to check if 'fulltext' is
empty or not, and only return true or false, so i don't have to put the
whole fulltext into the mysql_fetch_array() to decide wether or not to
show 'read_more', or should i create an extra boolean field in the DB
saying fulltext y/n ?

Greetings Frizzle.

Put it in a separate table with just the article's id and the text. If
the id exists in the second table, there is more text.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

This would have me quering against an extra table. Would'nt it be
quicker to add an extra boolean field then?

Frizzle.

[Jerry Stuckle]

frizzle wrote:

Jerry Stuckle wrote:

frizzle wrote:

Hi there,

I have a mySQL system with a news publishing part in it:
Admins can create new items with text in it, and they have an option to
create 'fulltexts', so you'd get "read more ..." on the front page,
click it and read the fulltext.

Is there a possibility for mySQL (query) to check if 'fulltext' is
empty or not, and only return true or false, so i don't have to put the
whole fulltext into the mysql_fetch_array() to decide wether or not to
show 'read_more', or should i create an extra boolean field in the DB
saying fulltext y/n ?

Greetings Frizzle.

Put it in a separate table with just the article's id and the text. If
the id exists in the second table, there is more text.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

This would have me quering against an extra table. Would'nt it be
quicker to add an extra boolean field then?

Frizzle.

Why? Joins are quick. Probably a lot faster than unnecessarily
processing empty text fields in the first table.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[frizzle]

Jerry Stuckle wrote:

frizzle wrote:

Jerry Stuckle wrote:

frizzle wrote:

Hi there,

I have a mySQL system with a news publishing part in it:
Admins can create new items with text in it, and they have an option to
create 'fulltexts', so you'd get "read more ..." on the front page,
click it and read the fulltext.

Is there a possibility for mySQL (query) to check if 'fulltext' is
empty or not, and only return true or false, so i don't have to put the
whole fulltext into the mysql_fetch_array() to decide wether or not to
show 'read_more', or should i create an extra boolean field in the DB
saying fulltext y/n ?

Greetings Frizzle.
Put it in a separate table with just the article's id and the text. If
the id exists in the second table, there is more text.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

This would have me quering against an extra table. Would'nt it be
quicker to add an extra boolean field then?

Frizzle.

Why? Joins are quick. Probably a lot faster than unnecessarily
processing empty text fields in the first table.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

I understand what you're saying. I think i'm underestimating the speed
of Joins (mySQL)
Not to be a nag, but would it still be faster to add an extra field to
the original table, 'fulltext_available' tinyint(1), which tells me to
look for it or not?

And, am i wrong believing that if i use 'SELECT fieldname1, name2 etc.'
in the query, it leaves the unmentioned fields unbothered, thus not
being influenced by their size/contents ?

Frizzle.

[Jerry Stuckle]

frizzle wrote:

Jerry Stuckle wrote:

frizzle wrote:

Jerry Stuckle wrote:
frizzle wrote:
>Hi there,
>
>I have a mySQL system with a news publishing part in it:
>Admins can create new items with text in it, and they have an option to
>create 'fulltexts', so you'd get "read more ..." on the front page,
>click it and read the fulltext.
>
>Is there a possibility for mySQL (query) to check if 'fulltext' is
>empty or not, and only return true or false, so i don't have to put the
>whole fulltext into the mysql_fetch_array() to decide wether or not to
>show 'read_more', or should i create an extra boolean field in the DB
>saying fulltext y/n ?
>
>Greetings Frizzle.
>

Put it in a separate table with just the article's id and the text. If
the id exists in the second table, there is more text.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
This would have me quering against an extra table. Would'nt it be
quicker to add an extra boolean field then?

Frizzle.

Why? Joins are quick. Probably a lot faster than unnecessarily
processing empty text fields in the first table.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

I understand what you're saying. I think i'm underestimating the speed
of Joins (mySQL)
Not to be a nag, but would it still be faster to add an extra field to
the original table, 'fulltext_available' tinyint(1), which tells me to
look for it or not?

And, am i wrong believing that if i use 'SELECT fieldname1, name2 etc.'
in the query, it leaves the unmentioned fields unbothered, thus not
being influenced by their size/contents ?

Frizzle.

Yes and no. It depends on the implementation of the database.

In MySQL, everything in a table is kept in one file. So if you have
your full text in that table, a table scan will read everything in the
table and throw away the columns you don't want. If you have a lot of
text in relation to the rest of the row data, this can be significant
overhead. If the full text is in a separate table, it will only be read
when you request information from that table.

Additionally, is the absolute speed necessary? Are you talking dozens
(or even hundreds) of queries per second? I doubt it.

Keeping large amounts of data you only occasionally access is generally
a better way of doing things. Putting a T/F column in the table just
makes for another column you have to manage (and violates normalization
principles).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[frizzle]

Jerry Stuckle wrote:

frizzle wrote:

Jerry Stuckle wrote:

frizzle wrote:

Jerry Stuckle wrote:
>frizzle wrote:
>
>
>>Hi there,
>>
>>I have a mySQL system with a news publishing part in it:
>>Admins can create new items with text in it, and they have an option to
>>create 'fulltexts', so you'd get "read more ..." on the front page,
>>click it and read the fulltext.
>>
>>Is there a possibility for mySQL (query) to check if 'fulltext' is
>>empty or not, and only return true or false, so i don't have to put the
>>whole fulltext into the mysql_fetch_array() to decide wether or not to
>>show 'read_more', or should i create an extra boolean field in the DB
>>saying fulltext y/n ?
>>
>>Greetings Frizzle.
>>
>
>Put it in a separate table with just the article's id and the text. If
>the id exists in the second table, there is more text.
>
>--
>==================
>Remove the "x" from my email address
>Jerry Stuckle
>JDS Computer Training Corp.
>js*******@attglobal.net
>==================
This would have me quering against an extra table. Would'nt it be
quicker to add an extra boolean field then?

Frizzle.
Why? Joins are quick. Probably a lot faster than unnecessarily
processing empty text fields in the first table.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

I understand what you're saying. I think i'm underestimating the speed
of Joins (mySQL)
Not to be a nag, but would it still be faster to add an extra field to
the original table, 'fulltext_available' tinyint(1), which tells me to
look for it or not?

And, am i wrong believing that if i use 'SELECT fieldname1, name2 etc.'
in the query, it leaves the unmentioned fields unbothered, thus not
being influenced by their size/contents ?

Frizzle.

Yes and no. It depends on the implementation of the database.

In MySQL, everything in a table is kept in one file. So if you have
your full text in that table, a table scan will read everything in the
table and throw away the columns you don't want. If you have a lot of
text in relation to the rest of the row data, this can be significant
overhead. If the full text is in a separate table, it will only be read
when you request information from that table.

Additionally, is the absolute speed necessary? Are you talking dozens
(or even hundreds) of queries per second? I doubt it.

Keeping large amounts of data you only occasionally access is generally
a better way of doing things. Putting a T/F column in the table just
makes for another column you have to manage (and violates normalization
principles).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

I don't know (yet) how much queries there will be, but i guess not
*that* much.
My lack of english causes me te seize your intention in the last part.
Do you mean i should put the fulltexts in another extra table?
I btw hardly doubt if it will be neccesary, since those texts won't be
that big either.
(For mySQL / PHP then ... )
Again, it's a news system, there won't be a whole version of The Da
Vinci Code behind 'read more'.

Frizzle.

[bobzimuta]

SELECT IF( CHAR_LENGTH( `text_column` ) > 0, 1, 0 ) AS fulltext_exists
FROM table WHERE .....

will return fulltext_exists as 1 if `text_column` has a length greater
than 0. Otherwise returns 0
Want to get just the first 50 characters from the fulltext as the
summary or intro?

SELECT LEFT( `text_column`, 50 ) AS summary FROM table WHERE ....

[Jerry Stuckle]

frizzle wrote:

Jerry Stuckle wrote:

frizzle wrote:

Jerry Stuckle wrote:
frizzle wrote:
>Jerry Stuckle wrote:
>
>
>
>>frizzle wrote:
>>
>>
>>
>>>Hi there,
>>>
>>>I have a mySQL system with a news publishing part in it:
>>>Admins can create new items with text in it, and they have an option to
>>>create 'fulltexts', so you'd get "read more ..." on the front page,
>>>click it and read the fulltext.
>>>
>>>Is there a possibility for mySQL (query) to check if 'fulltext' is
>>>empty or not, and only return true or false, so i don't have to put the
>>>whole fulltext into the mysql_fetch_array() to decide wether or not to
>>>show 'read_more', or should i create an extra boolean field in the DB
>>>saying fulltext y/n ?
>>>
>>>Greetings Frizzle.
>>>
>>
>>Put it in a separate table with just the article's id and the text. If
>>the id exists in the second table, there is more text.
>>
>>--
>>==================
>>Remove the "x" from my email address
>>Jerry Stuckle
>>JDS Computer Training Corp.
>>js*******@attglobal.net
>>==================
>
>
>This would have me quering against an extra table. Would'nt it be
>quicker to add an extra boolean field then?
>
>Frizzle.
>

Why? Joins are quick. Probably a lot faster than unnecessarily
processing empty text fields in the first table.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
I understand what you're saying. I think i'm underestimating the speed
of Joins (mySQL)
Not to be a nag, but would it still be faster to add an extra field to
the original table, 'fulltext_available' tinyint(1), which tells me to
look for it or not?

And, am i wrong believing that if i use 'SELECT fieldname1, name2 etc.'
in the query, it leaves the unmentioned fields unbothered, thus not
being influenced by their size/contents ?

Frizzle.

Yes and no. It depends on the implementation of the database.

In MySQL, everything in a table is kept in one file. So if you have
your full text in that table, a table scan will read everything in the
table and throw away the columns you don't want. If you have a lot of
text in relation to the rest of the row data, this can be significant
overhead. If the full text is in a separate table, it will only be read
when you request information from that table.

Additionally, is the absolute speed necessary? Are you talking dozens
(or even hundreds) of queries per second? I doubt it.

Keeping large amounts of data you only occasionally access is generally
a better way of doing things. Putting a T/F column in the table just
makes for another column you have to manage (and violates normalization
principles).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

I don't know (yet) how much queries there will be, but i guess not
*that* much.
My lack of english causes me te seize your intention in the last part.
Do you mean i should put the fulltexts in another extra table?
I btw hardly doubt if it will be neccesary, since those texts won't be
that big either.
(For mySQL / PHP then ... )
Again, it's a news system, there won't be a whole version of The Da
Vinci Code behind 'read more'.

Frizzle.

Yes, I would put the fulltext in a separate table. The join takes very
little overhead (since primary keys have an index associated).

Very often if you have large amounts of text (several hundred bytes
minimum) which you don't always need to access you can save processing
time by putting it in its own table.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Jerry Stuckle]

frizzle wrote:

Jerry Stuckle wrote:

frizzle wrote:

Jerry Stuckle wrote:
frizzle wrote:
>Jerry Stuckle wrote:
>
>
>
>>frizzle wrote:
>>
>>
>>
>>>Hi there,
>>>
>>>I have a mySQL system with a news publishing part in it:
>>>Admins can create new items with text in it, and they have an option to
>>>create 'fulltexts', so you'd get "read more ..." on the front page,
>>>click it and read the fulltext.
>>>
>>>Is there a possibility for mySQL (query) to check if 'fulltext' is
>>>empty or not, and only return true or false, so i don't have to put the
>>>whole fulltext into the mysql_fetch_array() to decide wether or not to
>>>show 'read_more', or should i create an extra boolean field in the DB
>>>saying fulltext y/n ?
>>>
>>>Greetings Frizzle.
>>>
>>
>>Put it in a separate table with just the article's id and the text. If
>>the id exists in the second table, there is more text.
>>
>>--
>>==================
>>Remove the "x" from my email address
>>Jerry Stuckle
>>JDS Computer Training Corp.
>>js*******@attglobal.net
>>==================
>
>
>This would have me quering against an extra table. Would'nt it be
>quicker to add an extra boolean field then?
>
>Frizzle.
>

Why? Joins are quick. Probably a lot faster than unnecessarily
processing empty text fields in the first table.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
I understand what you're saying. I think i'm underestimating the speed
of Joins (mySQL)
Not to be a nag, but would it still be faster to add an extra field to
the original table, 'fulltext_available' tinyint(1), which tells me to
look for it or not?

And, am i wrong believing that if i use 'SELECT fieldname1, name2 etc.'
in the query, it leaves the unmentioned fields unbothered, thus not
being influenced by their size/contents ?

Frizzle.

Yes and no. It depends on the implementation of the database.

In MySQL, everything in a table is kept in one file. So if you have
your full text in that table, a table scan will read everything in the
table and throw away the columns you don't want. If you have a lot of
text in relation to the rest of the row data, this can be significant
overhead. If the full text is in a separate table, it will only be read
when you request information from that table.

Additionally, is the absolute speed necessary? Are you talking dozens
(or even hundreds) of queries per second? I doubt it.

Keeping large amounts of data you only occasionally access is generally
a better way of doing things. Putting a T/F column in the table just
makes for another column you have to manage (and violates normalization
principles).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

I don't know (yet) how much queries there will be, but i guess not
*that* much.
My lack of english causes me te seize your intention in the last part.
Do you mean i should put the fulltexts in another extra table?
I btw hardly doubt if it will be neccesary, since those texts won't be
that big either.
(For mySQL / PHP then ... )
Again, it's a news system, there won't be a whole version of The Da
Vinci Code behind 'read more'.

Frizzle.

I should also add - something like

SELECT COUNT(id) FROM texttable WHERE id=1;

is quite quick. Chances are since id is a primary key, MySQL won't even
go to the table itself - it will just see if it exists in the index.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Steve]

On Tue, 07 Feb 2006 14:42:57 -0800, frizzle wrote:

Jerry Stuckle wrote:

frizzle wrote:

> Jerry Stuckle wrote:
>
>>frizzle wrote:
>>
>>>Jerry Stuckle wrote:
>>>
>>>
>>>>frizzle wrote:
>>>>
>>>>
>>>>>Hi there,
>>>>>
>>>>>I have a mySQL system with a news publishing part in it:
>>>>>Admins can create new items with text in it, and they have an option to
>>>>>create 'fulltexts', so you'd get "read more ..." on the front page,
>>>>>click it and read the fulltext.
>>>>>
>>>>>Is there a possibility for mySQL (query) to check if 'fulltext' is
>>>>>empty or not, and only return true or false, so i don't have to put the
>>>>>whole fulltext into the mysql_fetch_array() to decide wether or not to
>>>>>show 'read_more', or should i create an extra boolean field in the DB
>>>>>saying fulltext y/n ?
>>>>>
>>>>>Greetings Frizzle.
>>>>>
>>>>
>>>>Put it in a separate table with just the article's id and the text. If
>>>>the id exists in the second table, there is more text.
>>>>
>>>>--
>>>>==================
>>>>Remove the "x" from my email address
>>>>Jerry Stuckle
>>>>JDS Computer Training Corp.
>>>>js*******@attglobal.net
>>>>==================
>>>
>>>
>>>This would have me quering against an extra table. Would'nt it be
>>>quicker to add an extra boolean field then?
>>>
>>>Frizzle.
>>>
>>
>>Why? Joins are quick. Probably a lot faster than unnecessarily
>>processing empty text fields in the first table.
>>
>>--
>>==================
>>Remove the "x" from my email address
>>Jerry Stuckle
>>JDS Computer Training Corp.
>>js*******@attglobal.net
>>==================
>
>
> I understand what you're saying. I think i'm underestimating the speed
> of Joins (mySQL)
> Not to be a nag, but would it still be faster to add an extra field to
> the original table, 'fulltext_available' tinyint(1), which tells me to
> look for it or not?
>
> And, am i wrong believing that if i use 'SELECT fieldname1, name2 etc.'
> in the query, it leaves the unmentioned fields unbothered, thus not
> being influenced by their size/contents ?
>
> Frizzle.
>

Yes and no. It depends on the implementation of the database.

In MySQL, everything in a table is kept in one file. So if you have
your full text in that table, a table scan will read everything in the
table and throw away the columns you don't want. If you have a lot of
text in relation to the rest of the row data, this can be significant
overhead. If the full text is in a separate table, it will only be read
when you request information from that table.

Additionally, is the absolute speed necessary? Are you talking dozens
(or even hundreds) of queries per second? I doubt it.

Keeping large amounts of data you only occasionally access is generally
a better way of doing things. Putting a T/F column in the table just
makes for another column you have to manage (and violates normalization
principles).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

I don't know (yet) how much queries there will be, but i guess not
*that* much.
My lack of english causes me te seize your intention in the last part.
Do you mean i should put the fulltexts in another extra table?
I btw hardly doubt if it will be neccesary, since those texts won't be
that big either.
(For mySQL / PHP then ... )
Again, it's a news system, there won't be a whole version of The Da
Vinci Code behind 'read more'.

Frizzle.

Always normalise your data model first. Then, if it isn't fast enough, add
in shortcuts. Databases ( even mysql (: ) are really powerful - but that
also means that you can really screw everything up if you don't stand back
first, and get a proper idea of what you're trying to do first.

There must be a copy of C.J.Date on the internet somewhere - it must be 30
years old by now.

Steve

[pjSoni]

Hi

Try to do like when admin enters article he should enter <SPLIT> tag
from where you want break with article so when you read from db try to
split from split tag.

Regards,
Jatin

[frizzle]

pjSoni wrote:

Hi

Try to do like when admin enters article he should enter <SPLIT> tag
from where you want break with article so when you read from db try to
split from split tag.

Regards,
Jatin

Thanks all for the replies.
I understand what bobzimuta and pjSoni are saying with the intro thing,
but i need two different fields, so the intro in the fulltext can
differ from just te intro.

I think i'll go with Jerry's: SELECT COUNT(id) FROM texttable WHERE
id=1;
But the id of the fulltext would have to match the exact id of the
record with intro and other info?
Would it matter if i added LIMIT 1 to it, so the DB won't go on?

How careful should i be asigning extra indexes? I read somewhere, that
if i asign indexes to fields that aren't going te be updated a lot,
it's not bad, otherwise it would take a lot of time ...

And now i also have second thoughts about my field in the topic records
with the number of replies.
It goes against normalization, but i thought it would save a lot of
calculating time ...

Frizzle.

[Jerry Stuckle]

frizzle wrote:

pjSoni wrote:

Hi

Try to do like when admin enters article he should enter <SPLIT> tag
from where you want break with article so when you read from db try to
split from split tag.

Regards,
Jatin

Thanks all for the replies.
I understand what bobzimuta and pjSoni are saying with the intro thing,
but i need two different fields, so the intro in the fulltext can
differ from just te intro.

I think i'll go with Jerry's: SELECT COUNT(id) FROM texttable WHERE
id=1;
But the id of the fulltext would have to match the exact id of the
record with intro and other info?
Would it matter if i added LIMIT 1 to it, so the DB won't go on?

How careful should i be asigning extra indexes? I read somewhere, that
if i asign indexes to fields that aren't going te be updated a lot,
it's not bad, otherwise it would take a lot of time ...

And now i also have second thoughts about my field in the topic records
with the number of replies.
It goes against normalization, but i thought it would save a lot of
calculating time ...

Frizzle.

Yes, it has to match the key of the other table. It would also the
primary key of that entry. Being a primary key, it would be unique.

Also, if your use INNODB tables you can make it a foreign key
referencing the other table.

You shouldn't need to add LIMIT 1 to the query because you have a unique
index on the field. MySQL should be able to fetch everything it needs
from the index (which, unlike the table, is in order).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[frizzle]

Jerry Stuckle wrote:

frizzle wrote:

pjSoni wrote:

Hi

Try to do like when admin enters article he should enter <SPLIT> tag
from where you want break with article so when you read from db try to
split from split tag.

Regards,
Jatin

Thanks all for the replies.
I understand what bobzimuta and pjSoni are saying with the intro thing,
but i need two different fields, so the intro in the fulltext can
differ from just te intro.

I think i'll go with Jerry's: SELECT COUNT(id) FROM texttable WHERE
id=1;
But the id of the fulltext would have to match the exact id of the
record with intro and other info?
Would it matter if i added LIMIT 1 to it, so the DB won't go on?

How careful should i be asigning extra indexes? I read somewhere, that
if i asign indexes to fields that aren't going te be updated a lot,
it's not bad, otherwise it would take a lot of time ...

And now i also have second thoughts about my field in the topic records
with the number of replies.
It goes against normalization, but i thought it would save a lot of
calculating time ...

Frizzle.

Yes, it has to match the key of the other table. It would also the
primary key of that entry. Being a primary key, it would be unique.

Also, if your use INNODB tables you can make it a foreign key
referencing the other table.

You shouldn't need to add LIMIT 1 to the query because you have a unique
index on the field. MySQL should be able to fetch everything it needs
from the index (which, unlike the table, is in order).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Why is this unlike the table in order? Since both are probably created
in the same instance. The Fulltext indexes have a bigger chance *not*
to be in order, since they could be added later ...

And what exactly do you mean with a foreign key ?

Frizzle.

(Thanks a lot for your effort to help me!)

[Jerry Stuckle]

frizzle wrote:

Jerry Stuckle wrote:

frizzle wrote:

pjSoni wrote:
Hi

Try to do like when admin enters article he should enter <SPLIT> tag

from where you want break with article so when you read from db try to

split from split tag.

Regards,
Jatin
Thanks all for the replies.
I understand what bobzimuta and pjSoni are saying with the intro thing,
but i need two different fields, so the intro in the fulltext can
differ from just te intro.

I think i'll go with Jerry's: SELECT COUNT(id) FROM texttable WHERE
id=1;
But the id of the fulltext would have to match the exact id of the
record with intro and other info?
Would it matter if i added LIMIT 1 to it, so the DB won't go on?

How careful should i be asigning extra indexes? I read somewhere, that
if i asign indexes to fields that aren't going te be updated a lot,
it's not bad, otherwise it would take a lot of time ...

And now i also have second thoughts about my field in the topic records
with the number of replies.
It goes against normalization, but i thought it would save a lot of
calculating time ...

Frizzle.

Yes, it has to match the key of the other table. It would also the
primary key of that entry. Being a primary key, it would be unique.

Also, if your use INNODB tables you can make it a foreign key
referencing the other table.

You shouldn't need to add LIMIT 1 to the query because you have a unique
index on the field. MySQL should be able to fetch everything it needs
from the index (which, unlike the table, is in order).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Why is this unlike the table in order? Since both are probably created
in the same instance. The Fulltext indexes have a bigger chance *not*
to be in order, since they could be added later ...

And what exactly do you mean with a foreign key ?

Frizzle.

(Thanks a lot for your effort to help me!)

First of all, check out foreign keys in the MySQL documentation:
http://dev.mysql.com/doc/refman/5.0/...eign-keys.html

No, the table wouldn't necessarily be in order (your original one
wouldn't either - SQL is by default unordered). But the index built on
the primary key is always ordered (so the engine can do a quick binary
search of the index). Therefore, MySQL can determine whether a record
exists or not by searching the index and doesn't need to load the file
itself in the query I showed you.

But the advantage is you wouldn't have to read the full text data every
time you need to access the summary information. You only get the full
text when you request it.

Remember - MySQL reads the entire row, even if you only ask for part of
the data, because everything's in one file.

Also, if you have a request which requires a full table scan, having the
full text in a separate table improves the table scan (unless, of
course, you're searching the full text data).
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[frizzle]

Jerry Stuckle wrote:

frizzle wrote:

Jerry Stuckle wrote:

frizzle wrote:

pjSoni wrote:
>Hi
>
>Try to do like when admin enters article he should enter <SPLIT> tag

>from where you want break with article so when you read from db try to

>split from split tag.
>
>Regards,
>Jatin
Thanks all for the replies.
I understand what bobzimuta and pjSoni are saying with the intro thing,
but i need two different fields, so the intro in the fulltext can
differ from just te intro.

I think i'll go with Jerry's: SELECT COUNT(id) FROM texttable WHERE
id=1;
But the id of the fulltext would have to match the exact id of the
record with intro and other info?
Would it matter if i added LIMIT 1 to it, so the DB won't go on?

How careful should i be asigning extra indexes? I read somewhere, that
if i asign indexes to fields that aren't going te be updated a lot,
it's not bad, otherwise it would take a lot of time ...

And now i also have second thoughts about my field in the topic records
with the number of replies.
It goes against normalization, but i thought it would save a lot of
calculating time ...

Frizzle.
Yes, it has to match the key of the other table. It would also the
primary key of that entry. Being a primary key, it would be unique.

Also, if your use INNODB tables you can make it a foreign key
referencing the other table.

You shouldn't need to add LIMIT 1 to the query because you have a unique
index on the field. MySQL should be able to fetch everything it needs
from the index (which, unlike the table, is in order).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Why is this unlike the table in order? Since both are probably created
in the same instance. The Fulltext indexes have a bigger chance *not*
to be in order, since they could be added later ...

And what exactly do you mean with a foreign key ?

Frizzle.

(Thanks a lot for your effort to help me!)

First of all, check out foreign keys in the MySQL documentation:
http://dev.mysql.com/doc/refman/5.0/...eign-keys.html

No, the table wouldn't necessarily be in order (your original one
wouldn't either - SQL is by default unordered). But the index built on
the primary key is always ordered (so the engine can do a quick binary
search of the index). Therefore, MySQL can determine whether a record
exists or not by searching the index and doesn't need to load the file
itself in the query I showed you.

But the advantage is you wouldn't have to read the full text data every
time you need to access the summary information. You only get the full
text when you request it.

Remember - MySQL reads the entire row, even if you only ask for part of
the data, because everything's in one file.

Also, if you have a request which requires a full table scan, having the
full text in a separate table improves the table scan (unless, of
course, you're searching the full text data).
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Ok, thanks a lot, you convinced me on this. This of course is quite
important for more other things to achieve. I will for instance not
save the number of comments as an int() in the topics table, but will
perform a count!

Frizzle.

[frizzle]

I am btw not using INNODB, but ISAM.
(Neither ring a bell for me, but that's what the docs said.)

Frizzle.

[Jerry Stuckle]

frizzle wrote:

I am btw not using INNODB, but ISAM.
(Neither ring a bell for me, but that's what the docs said.)

Frizzle.

If you're going to use foreign keys, you need to change to innodb. It's
not hard - just call ALTER TABLE on each one.

Or use PHPMyAdmin - a great tool if you're not familiar with it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[frizzle]

Jerry Stuckle wrote:

frizzle wrote:

I am btw not using INNODB, but ISAM.
(Neither ring a bell for me, but that's what the docs said.)

Frizzle.

If you're going to use foreign keys, you need to change to innodb. It's
not hard - just call ALTER TABLE on each one.

Or use PHPMyAdmin - a great tool if you're not familiar with it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

I actually use PHPmyAdmin, but don't know where to set it to INNODB, or
my host disabled that, (i can choose form 3 other ones though ...)

Frizzle.

[Jerry Stuckle]

frizzle wrote:

I actually use PHPmyAdmin, but don't know where to set it to INNODB, or
my host disabled that, (i can choose form 3 other ones though ...)

Frizzle.

After you select the table, go to the Operations tab. One of the
entries is "Table type". Change this to INNODB.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[frizzle]

Jerry Stuckle wrote:

frizzle wrote:

I actually use PHPmyAdmin, but don't know where to set it to INNODB, or
my host disabled that, (i can choose form 3 other ones though ...)

Frizzle.

After you select the table, go to the Operations tab. One of the
entries is "Table type". Change this to INNODB.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

The only options i get there are:
- MyISAM (current)
- Heap
- ISAM
- Merge

So i guess it's not possible (yet). But i have things up and running
correct now, or do you think it's that important to go use foreign
keys?

Frizle.

[Jerry Stuckle]

frizzle wrote:

Jerry Stuckle wrote:

frizzle wrote:

I actually use PHPmyAdmin, but don't know where to set it to INNODB, or
my host disabled that, (i can choose form 3 other ones though ...)

Frizzle.

After you select the table, go to the Operations tab. One of the
entries is "Table type". Change this to INNODB.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

The only options i get there are:
- MyISAM (current)
- Heap
- ISAM
- Merge

So i guess it's not possible (yet). But i have things up and running
correct now, or do you think it's that important to go use foreign
keys?

Frizle.

OK, you're hosting company may have an older copy of MySQL, may not have
installed INNODB support or whatever.

INNODB will allow you to ensure the integrity of your foreign keys. For
instance, if you delete a row in your main table, it can automatically
delete the matching row(s) in your full text table (other options are
available also). Without it, you can't guarantee the integrity of the
database.

So it all depends on how important it is to you. You'll probably be ok
- this isn't like a bank, for instance, where you don't want to delete a
person's record while they still have open accounts. If you have some
extra rows in your full text table it might slow the server down a
couple of milliseconds is all.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[frizzle]

Jerry Stuckle wrote:

frizzle wrote:

Jerry Stuckle wrote:

frizzle wrote:

I actually use PHPmyAdmin, but don't know where to set it to INNODB, or
my host disabled that, (i can choose form 3 other ones though ...)

Frizzle.
After you select the table, go to the Operations tab. One of the
entries is "Table type". Change this to INNODB.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

The only options i get there are:
- MyISAM (current)
- Heap
- ISAM
- Merge

So i guess it's not possible (yet). But i have things up and running
correct now, or do you think it's that important to go use foreign
keys?

Frizle.

OK, you're hosting company may have an older copy of MySQL, may not have
installed INNODB support or whatever.

INNODB will allow you to ensure the integrity of your foreign keys. For
instance, if you delete a row in your main table, it can automatically
delete the matching row(s) in your full text table (other options are
available also). Without it, you can't guarantee the integrity of the
database.

So it all depends on how important it is to you. You'll probably be ok
- this isn't like a bank, for instance, where you don't want to delete a
person's record while they still have open accounts. If you have some
extra rows in your full text table it might slow the server down a
couple of milliseconds is all.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Ok, thanks a lot for all the effort. No more further questions ...
for now ;)

Again, thanks a lot for taking so much time!

Frizzle.

[Jim Michaels]

FULLTEXT() is a MySQL search function. so you might want to name your
column something else.

"Jerry Stuckle" <js*******@attglobal.net> wrote in message
news:Nf********************@comcast.com...

frizzle wrote:

Hi there,

I have a mySQL system with a news publishing part in it:
Admins can create new items with text in it, and they have an option to
create 'fulltexts', so you'd get "read more ..." on the front page,
click it and read the fulltext.

Is there a possibility for mySQL (query) to check if 'fulltext' is
empty or not, and only return true or false, so i don't have to put the
whole fulltext into the mysql_fetch_array() to decide wether or not to
show 'read_more', or should i create an extra boolean field in the DB
saying fulltext y/n ?

Greetings Frizzle.

Put it in a separate table with just the article's id and the text. If
the id exists in the second table, there is more text.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Jim Michaels]

"frizzle" <ph********@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Hi there,

I have a mySQL system with a news publishing part in it:
Admins can create new items with text in it, and they have an option to
create 'fulltexts', so you'd get "read more ..." on the front page,
click it and read the fulltext.

Is there a possibility for mySQL (query) to check if 'fulltext' is
empty or not, and only return true or false, so i don't have to put the
whole fulltext into the mysql_fetch_array() to decide wether or not to
show 'read_more', or should i create an extra boolean field in the DB
saying fulltext y/n ?

Greetings Frizzle.

if you create the column with type TEXT or LONGTEXT (or whatever) with
DEFAULT NULL, you can use MySQL's ISNULL() function to detect if it's empty
or not.
for instance,
SELECT ISNULL(fulltext1) FROM tablename ORDER BY itemtimestamp DESC

you can in your code with $row=mysql_fetch_array() see if $row['ISNULL']
(not sure if it should be lower case here) has an appropriate value. echo
it to find out.