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_arr ay() 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_arr ay() 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*******@attgl obal.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_arr ay() 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*******@attgl obal.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_arr ay() 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*******@att global.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*******@attgl obal.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_arr ay() 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*******@att global.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*******@attgl obal.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_avail able' 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_arr ay() to decide wether or not to
>show 'read_more', or should i create an extra boolean field in the DB
>saying fulltext y/n ?
>
>Greeting s 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*******@a ttglobal.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*******@att global.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_avail able' 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*******@attgl obal.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_arr ay() to decide wether or not to
>>show 'read_more', or should i create an extra boolean field in the DB
>>saying fulltext y/n ?
>>
>>Greeting s 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*******@a ttglobal.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*******@att global.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_avail able' 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*******@attgl obal.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_arr ay() to decide wether or not to
>>>show 'read_more', or should i create an extra boolean field in the DB
>>>saying fulltext y/n ?
>>>
>>>Greeting s 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
processin g empty text fields in the first table.

--
=========== =======
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@a ttglobal.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_avail able' 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.

Additionall y, 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*******@att global.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*******@attgl obal.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_arr ay() to decide wether or not to
>>>show 'read_more', or should i create an extra boolean field in the DB
>>>saying fulltext y/n ?
>>>
>>>Greeting s 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
processin g empty text fields in the first table.

--
=========== =======
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@a ttglobal.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_avail able' 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.

Additionall y, 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*******@att global.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*******@attgl obal.net
=============== ===