MYSQL :: UPDATE Fails On Somewhat Large Data Sets

Large Data Sets
I'd like to store a large data set of the following form in MySQL: about 8000 replies (by unique institutions) to a questionnaire with 3000 fields at 120 different dates. Fields are either versions of int or char with lengths between 8 and 75. I need to do searches across all those dimensions (institutions, fields, dates).

I'm wondering what the best database design (in terms of query speed) would be (3Ghz Pentium, 1GB RAM, Linux 2.6, one hard disk).

Update Sql Fails For 4.0.26
I executed the following sql commands on 4.0.26(linux)

drop table gtest;

create table gtest(id int default 116, name varchar(20) default 'peter');

insert into gtest(id, name) values(100, default);

select * from gtest;

+------+-------+
| id | name |
+------+-------+
| 100 | peter |
+------+-------+

1 row in set (0.00 sec)

Then i tried the following

update gtest set id=default;
update gtest set name=default;

I get the following error

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'default' at line 1

I looked up the docs and its a SQL stmt parse error.

Update Fails
I'll put this very simply, will give my current code if necessary (first trying to explain it here I came to an conclusion I'd better erase it and only ask..)

I have a script, which by using mysql_affected rows, checks if UPDATE query was used to update already existing data. If that fails, INSERT query will create that data. However Mysql only returns affected rows as true, if the old data is different than the new one.

What I'm asking here is - Which do you think is the best way to update - or if nothing to update - insert new data to sql. I guess REPLACE wont work when it should be done by id fields?

ON UPDATE CASCADE Fails
I have a table which has a foreign key relationship with itself. I want and expect my updates to cascade (deletes definitely cascade as expected) but instead I just get error 1217: foriegn key error.

I have written example code to use in reproducing the problem: Code:

LOAD DATA Fails When Non-ascii Chars In Data
I am trying to load a cities database that is in CSV format. Some of the fields contain extended characters like the umlaut or accented chars. The load fails with: Code:

Selecting 2 Sets Of Data
I need to select two groups of data based on the time stamp.

Table 1

Card | Date | Time | #1 | #2 | #3
-----------------------------------------------------
1 | 2004-09-09 | 19:39:21 | 841630 | 251534 | 110
2 | 2004-09-09 | 19:39:21 | 895424 | 256112 | 86
3 | 2004-09-09 | 19:39:21 | 895527 | 256881 | 109
1 | 2004-09-09 | 19:49:51 | 841750 | 251545 | 112
2 | 2004-09-09 | 19:49:51 | 895634 | 256173 | 88
3 | 2004-09-09 | 19:49:51 | 895937 | 256885 | 113
1 | 2004-09-09 | 19:59:42 | 841934 | 251554 | 112
2 | 2004-09-09 | 19:59:42 | 895886 | 256182 | 89
3 | 2004-09-09 | 19:59:42 | 896222 | 256892 | 113

I need the latest set of data (time 19:59:42) for all three cards and
then I will need the next previous set of data (time 19:49:51) for all
three cards.

After I have the data, I'll do calculations to arrive at %, etc.

To select the max time, I've tried the following:

select table1.* from table1 left join table1 as t2 on table1.card =
t2.card and table1.time < t2.time where t2.card IS NULL order by
date,time,card;

I don't know how to determine the previous set of data.

Multiple Data Sets
How can I select which data set (result set) the stored procedure produce if
there are more than one select statement in the code. Ex.

[1] select petname from pets where kind="dog"
[2] select petname from pets where kind="cat"

can I select from either statement [1] or statement [2] for the output result set?

Cannot Delete Or Update A Parent Row: A Foreign Key Constraint Fails
I am trying to delete a record through a user interface, but when i try to delete the record i get the following message:

Cannot delete or update a parent row: a foreign key constraint fails

The parent table is a products table and has four child tables linked through a product id foreigh key. The table is an innoDB type.

Update Query Fails In Previous MySQL Version
The following query runs fine on my development server (v4.0.17):

"update user inner join events on user.username = events.username
set user.LastMessageDate = '$current_time'
where events.text_sent=0 and events.to_mobile >0 and events.GMT_event_send_time<='$send_time'"

but fails on my production server running v3.23.56.

Anyone any ideas why it doesn't run and what I need to do to fix it?

Error 1452 Cannot Add Or Update A Child Row: A Foreign Key Constraint Fails.
I am creating a contacts database using family information.

My tables in question look like this:
create table Household
( HouseholdID int(4) not null,
LastName varchar(100),
constraint pk_HouseholdID primary key (HouseholdID)
);

create table Personal
( PersonalID int (4)not null,
HouseholdID int (4) not null,
AddressID int (4) not null,
FirstName varchar (20),
LastName varchar (60),
constraint pk_PersonalID primary key (personalID)
);

create table ContactInfo
( AddressID int (4) not null,
PersonalID int (4) not null,
lastName varchar(50) not null,
mailingAddress varchar(60),
City varchar(25),
state varchar (20),
ZipCode varchar(5),
PhoneNumber int (10),
EmailAddress varchar (60),
constraint pk_AddressID primary key (AddressID)
);
** I have others, but these are the ones involoved in the issue**

Here is what I am trying to update with:

insert Personal
values (0100,0001, 1000, 'Gram & Gram', 'Holbrook');

THis is my error:
[mobile3] ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (`directory/personal`, CONSTRAINT `fk_AddressID` FOREIGN KEY (`AddressID`) REFERENCES `contactinfo` (`AddressID`))

Mysql Query Fails Due To A ' In Data.
I am having problems now with the following code.

An example of my topic.

"we'll meet again"

But when the following line searches for it i get an erro saying that the code is wrong.

I know that it is to do with the ' , but how do Iget round this problem i have. without having to remove all of them from the data.

$topics = mysql_query("SELECT * FROM `forum_topics` WHERE `topic` = '" . $topic . "'") or die(mysql_error());

Insering Data Into Bit Field Fails
I know there's an issue with mySQL not being able to display BIT values, but that's not the issue, it's just not inserting them.

Here's some info about server / table before i explain the problem.

mysql Ver 14.12 Distrib 5.0.24a, for pc-linux-gnu (i486) using readline 5.1

The field causing problems is bit(3)

Right, when I do...

INSERT INTO waypoints (lat_long,date,type,marker) VALUES('12313123132,1231321','1174415400','2','1');

I get the line inserted and I get a warning, as follows.

"Out of range value adjusted for column 'type' at row 1"

At first I assumed this was because I was entering a decimal number, but when I do an update it inserts the decimal number correctly.

Insert Data Into 2 Tables With 1 Query Fails
I found 2 old threads on how to insert data into 2 tables with 1 query. Both of them said that i should seperate the inserts with a ;. But i must be doing something wrong, because it comes up with an error like this ".....right syntax to use near ' INSERT INTO eiland_details"

What am i doing wrong? It's possible, right? To insert data into multiple tables with 1 query?

$query = "
INSERT
INTO sub_pages
(mainpage_id, intro_text)
VALUES
('$main_data->mainpage_id','$sub_intro');
INSERT
INTO eiland_details
(inwoners, oppervlakte)
VALUES
('$detail_inwoners',$detail_oppervlakte')";

Update Performance On Large Table
I have a table that is approaching 4 gigs. I have optimized as much as
I can with indexes so that select statements are ok, but updating
entries seems to be taking a bit of time. I have 2 questions:

1) I know about the EXPLAIN command for select queries, but is there
something like that for updates? I get an error when I try to do
"EXPLAIN UPDATE mytable..." Do updates use indexes the same way as
selects?

2) Would breaking the table up into seperate tables and then using a
merge table increase the performace? I've heard that key reads are
slower on merge tables - but since I could make 4 tables of a gig
each, wouldn't it be faster opening up 1 smaller file to make an
update instead of a huge one?

Insert A Large Of Data
how to insert a large of data in .sql file format (include CREATE TABLE and INSERT TABLE), because after I export data to backup from my website, data file aprox 50MB, and script always timeout when I restore data using phpMyadmin?

My Server use Linux OS and I have root account. May I use command line? Please help me and guide me step by step

Large Insert On Customized Data
We are doing some scripting to send out bulk email from a database.

The database has a table containing user email addresses along with other user information.

We want to select from the user table a list of users, and then insert these into the _mail_queue table so we can do batch processing of the emails.

The problem is lets say we need to send to 10,000 users. We would have to do 10,000 insert queries? I understand we could do a nested select, but some of the data entered into mail_queue is going to be customized first. (Example: Dear User's First Name,)

Managing A Large Piece Of Data
I have a table set up for all of my pages of content. There are columns that store certain small, various information about the page. Then, there is the main content column that holds the page content (actual article). One of my staff members that writes content for my site is complaining that the content gets cut off. I imagine it's because it's bigger than 65,000 bytes, which is all that the "TEXT" type of field will allow (that's what type of field my main content is... TEXT)

My question is what type of field should I change my main content column to an order to fit a larger piece of data? I've never had to this before, so I don't really know anything other than TEXT. Sixty-five thousand bytes isn't cutting it though.

Very Large Data-files - Performance?
I have to handle a large amount of Data (about 5GB) in one table. I want to extract the essence out of this table which should be something like 1MB and save it to a new table.
Mostly used commands are GROUP, WHERE, BETWEEN, IN and LEFT JOIN.

I use 3-4 temporary Tables. The script is ready and tested on a file of 60MB (750.000 datasets) and works fine (needs about 90seconds).

What I want to know is: Can I expect the query to work through my big data within some ours, or will I fail with a standard PC? What can I do to optimize (Hardware/Code)? There is only one query to work through at one time.

Large Data, Should It Be Split Into A Separate Table?
I'm designing a database and need some opinions.

I'm doing a table for blogs, and in it are things such as:
- author ID
- post ID
- date
- votes
- icon
- content

Now, the content is huge since it contains lots of text. Someone suggested that I split off the content into a separate table to save space and increase speed for listing for blogs and processing.

How true is this? I'm thinking it's rather unnecessary, unless the speed gains are true.

MYSQL Suitability For Large Data Processing
I am setting up a PHP driven reporting tool and am trying to select my backend.

I will be receiving data from multiple sources, probably about a million transactions a day to start, maybe increasing in the future. The data will be quite minimal, maybe 20 fields of data - the style will be something like web log records.

I plan to add these to a single table, so by the end year 1 I expect 365million records, maybe nearer 600million. This will grow exponentially as years go on.

Can MYSQL handle this?
How fast will it process a query on that data?

Importing Large Amount Of Data Does Not Work
I am importing large amounts of data (over 50 Meg) into a new mysql db
that I set up. I use[color=blue]
>mysql dbname < importfile.txt[/color]
But I keep getting timeouts and errors due to the data being too
large. I know that since if I break the imported data into multiple
chuncks (by importing a few tables at a time) then everything works.

Insert Large Amount Of Data Quickly
I have a large amount of data to add to a table but when I use SQL to insert one row by one row it is very slow. Is there a faster way to load data into a MySQL table?

MySQL Table Upload Large Data
Via MyAdmin I have inserted data in a MySQL table. But the number of inserting at ones is limited. How do I need to insert data from a ms access large table in one step in a new or existing MySQL table ?

Inserting Large Amounts Of Data Into Mysql
I have a whole bunch of numbers and state, county, city, etc. data that i'm trying to load into a database for a demographics website. The data is all in excel currently so I could easily copy and paste it to a text file or another medium if necessary but I'm wondering what the quickest and most efficient way to get it all entered into mysql is.

Inserting Large Groups Of Data As A Range
I am currently building a database for reports and records and would like to be able to add in large amounts of data by specifying a range. Example:

I want to insert data into unit1, unit2, unit3....all the way to unit100.

I would like to be able to do something like:

=========================================================
$unit11=$_POST['unit1'];
$unit21=$_POST['unit2'];
$unit31=$_POST['unit3'];
....
$unit1001=$_POST['unit100'];

mysql_query("INSERT INTO provisioning (unit1-unit100) VALUES ('$unit11'-'$unit1001')
=========================================================

*just to make myself clear, I want to do a range like (unit1-unit100) instead of oing (unit1, unit2, unit3....)

Importing Data Containing Large Text Fields
I'm in the middle of importing a very large database in MDB format into MySQL.

The data consists of a fairly disgusting list of redundant columns, and one of those columns contains plain text, including line feeds and carriage returns.

I've been bashing my brains out trying to import this data into MySQL WITHOUT resorting to using a BLOB (binary or otherwise) field to stick all that text data into. Varchar fields are fine, but there's a problem importing that data.

The problem is that since the textual data in that field contains linefeeds and carriage returns, exporting it as quote-delimited text is fine, except that there's no way for MySQL's LOAD DATA statement to see the linefeeds in that text as anything except newlines - and since most data export tools I have only provide simple CR/LF (linefeeds and carriage returns) as row delimiters, MySQL kept complaining about missing columns when it got to a field containing that raw text.

The fix was to append a text column to the raw table, containing an identical string in every row - I used XYZ, but almost anything goes. Then I re-exported the whole table using " string delimiters and CR/LF line terminators as usual. Code:

How Speed Up Data Retrieve From Large Table
I try to retrieve duplicate data from database but it take a lot of time to display and very slow.Even using index also same .Here is my sql statement :

$sqlselect2 = "SELECT DISTINCT p1.resume_id, p1.user_id, p1.email, p1.name, p1.gender, p1.house_tel, p1.office_tel, p1.hand_tel, p1.new_ic, p1.old_ic, p1.address1, p1.address2, p1.city, p1.dob FROM resume_personal p1 ,resume_personal p2 where p1.$searchString=p2.$searchString And p1.resume_id!=p2.resume_id Order by p1.$sort $order ";

$result2 = mysql_query($sqlselect2,$conn) or die(mysql_error());

Anyones got the better solution to retrieve duplicate data or to improve the speed?

Importing Large Amount Of Data Does Not Work
I am importing large amounts of data (over 50 Meg) into a new mysql db
that I set up. I use[color=blue]
>mysql dbname < importfile.txt[/color]

But I keep getting timeouts and errors due to the data being too large. I know that since if I break the imported data into multiple chuncks (by importing a few tables at a time) then everything works.

Most Efficient Way To Import Large Data Dump From Phpmyadmin?
I have a large data dump (export) from phpmyadmin my client just gave me - 200k - .

What's the most efficient way to import that into a new database? The new db has not defined tables since that is part of the phpmyadmin export.

Storing A Large Data Struct/object In MYSQL
I have the following structure.

typedef __nogc struct RemoteDisplayInfoType{
int HistData [512][512];
int channelNdx;
double xVar[2];
double yVar[2];
double paAngle[2];

}RemoteDisplayInfoType;

I want to store this in MySQl database and retreive it back. I am using ADO.Net with managed c++.

Writing Large Chunks Of Binary Data To MySQL With ODBC
I'm having a lot of trouble writing large chunks of binary data (tests
are in the range of 16-512K, but we need support for large longblobs) to
MySQL using ODBC. Database is local on a W2K system, but I have to
support all modern Windows systems, and a variety of ODBC configurations.
(I'll be testing against multiple ODBC databases soon - but development
is against MySQL)

I've been able to adapt some example code that executes a statement
(SQLExecute) and uses SQLBindParameter to mark the longblob field as
SQL_DATA_AT_EXEC. That works.

However I really need to use a result set (opened for update), and to
add new rows using SQLBulkOperation( hStmt, SQL_ADD)

This works for the normally bound fields, but fails if I bind a variable
using something like: Code:

With Sets A And B, How To Get A Minus B
I can generate two lists A and B, but I'm having trouble generating the list A minus B. (I'm helping to recruit umpires for a rowing regatta.)

-- Set A: People who got a recruitment letter

select concat('"',pe.first_name,' ',pe.last_name,'" <',pe.email1,'> ')
from person pe, communication c
where
c.fk_mailing = 4
and pe.id = c.fk_person
order by
pe.last_name, pe.first_name;

-- Set B: People who got the recruitment letter and registered
-- therefore we don't need or want to send a 2nd request to these people

select concat('"',pe.first_name,' ',pe.last_name,'" <',pe.email1,'>')
from person pe, communication c, participation pa
where
c.fk_mailing = 4
and pe.id = c.fk_person
and pe.id = pa.fk_person
and pa.year = 2007
order by
pe.last_name, pe.first_name;

-- How do I get Set C: A minus B ?

Character Sets
All I know is that there are 8 bit numbers from 0 to 255 mapped to
characters like A, B, C, D and some strange looking ones (like the ones
used to make boxes in old PC text modes) all these being called ASCII
characters.So, what in a nutshell does "character sets" mean and how are they
helpful in displaying multiple languages ?

Character Sets
I would like to know which is the difference between utf8_general_ci & utf8_unicode_ci. Which is more extended?

Update Data In A Row
I have a database with 1400 rows and we have a column called descript. But each content of the row in "descript" have different text. This text have words with a "?" sign.

How can rename each word that have a ? sign with another word?

for example:

Descript >> hello we are t?king a lot...

We need to replace the word "t?king" of each row with "taking"

Update Data
i get an excel report each month,
There are two columns (person`s name and Salary)
in this month report there was 99 persons.
my question is , if in next month excel report there are less persons...say 75, and also 23 persons raised their salary..
how can i updatae this changes in a mysql table that i have this mysql table has the exact same two columns and the exact same information i have in excel...
is there a way to do this?

Foreign Character Sets
My experience of MySQL revealed some issues with languages like Czech, Hungarian, Finnish and Polish. There was also a question as to whether it could support Chinese.

We run version 4.0.15 of MySQL (the very latest production version is
4.0.16).

I would like to know:

1. Can it support Russian, Chinese (Reduced Character Set &
Traditional) and Spanish (not expecting a problem with Spanish)? What
standards have to be met, if any?

I saw from the official web site manual it does support these
languages. But trying to execute the command SHOW CHARACTER SET;
returns an error saying there is something wrong with the syntax.

http://www.mysql.com/doc/en/Charset-charsets.html

2. If the current implementation cannot support Chinese and Russian,
are MySQL modules available to support these languages?

3. If modules are available, what would be the cost and timescales to
have them installed? Has anyone done something similar before? As
it's open source are there any costs? Also, what would the impact be
on the implementation to support English and Arabic i.e. can they
easily be installed, would there have to be a major retesting effort?

Character Sets And Collations
I've just installed MySQL 4.1.7 on my personal computer which i do all my web work on. I've put content into my database through phpMyAdmin.

Now when I import my sql file to my server Mysql 4.0.22 i get an error message 1064: Character Sets is wrong. & the website using the database to get content from, doesn't display it anymore.

Does anyone who how I can export my MySQL4.1.7 to a format which MySQL4.0.22 can read?

or can sometell me how to change the Character Sets and Collations to a set which works fine in both MySQL versions?

How To Get Few Result Sets Out Of One SQL Statement?
I want to get statistics out of my server. Now I have a recordset that takes day=1 and I count the records. I use ASP to go from 1 to 31 days but it is not fast as I have to use 31 queries loop. Isn't there a way in mySQL to get the 31 days results out of the single statement?

Show Empty Sets
I am wondering if there is a possibility to show empty data sets.
Assuming I am having a table with 80 records having uncontinious IDs between 0 and 100 I would like to generate a list showing 100 records with empty fields (just the ID) where there is an empty set. Is there any way to do this?

Foreign Character Sets
Im very new to mysql - My experience of MySQL revealed some issues
with languages like Czech, Hungarian, Finnish and Polish. There was
also a question as to whether it could support Chinese.

We run version 4.0.15 of MySQL (the very latest production version is
4.0.16).

I would like to know:

1. Can it support Russian, Chinese (Reduced Character Set &
Traditional) and Spanish (not expecting a problem with Spanish)? What
standards have to be met, if any?

I saw from the official web site manual it does support these
languages. But trying to execute the command SHOW CHARACTER SET;
returns an error saying there is something wrong with the syntax.

http://www.mysql.com/doc/en/Charset-charsets.html

2. If the current implementation cannot support Chinese and Russian,
are MySQL modules available to support these languages?

3. If modules are available, what would be the cost and timescales to
have them installed? Has anyone done something similar before? As
it's open source are there any costs? Also, what would the impact be
on the implementation to support English and Arabic i.e. can they
easily be installed, would there have to be a major retesting effort?

CHAR SETS And COLLATE
I get the following error when using COLLATE and CHARACTER SET clauses. I am using mysql Ver 12.22 Distrib 4.0.23, for pc-linux (i686).

---error i am getting---------
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE latin1_german1_ci
) DEFAULT CHARACTER SET latin2 COLLAT
--------------------------------------

Following is the command I used. I got it from the mysql documentations. Please, any one help me out and let me know how can I effectively use different charsets in mysql tables.

CREATE TABLE t1
(
c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci
) DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;

Merge Two Sets Of Rows
I have 2 sets of rows retrieved from 2 different tables. Is there any function in MySQL or PHP so that I can merge those 2 sets of rows and let each row mixed with another for 2 sets?I mean the 1st row in the new set is from set 1 and the 2nd row is from set 2, and the 3nd row is from set 1, and the 4th one is from set 4, and so on. If one set has more rows than another, then simply append the rest to the end.

UNIONs And Intersection Sets
I would like to use MySQL to find out what the intersection set is of a UNION of SELECTs. Generally, I use "SELECT * from names INNER JOIN A ON(B) WHERE C = D UNION SELECT * from names INNER JOIN E ON(F) WHERE G = H [etc};" to create the UNION set. Now I would like to see what the intersection is of the UNION set with all the smaller SELECT sets, while excluding all NULL results, to get the lowest common denominator. I've been trying IFNULL, but I haven't got it to work. Does anyone have a generic command for such?

Returning Result Sets
Hi, I need a stored procedure that returns a result set so I can fill a DataGrid in VB 6. I'm using mySQL 5 and I'm having big problems making this work. What I did at first was to stick to the basic structure of the procedure:

BEGIN
SELECT * FROM table;
END;

When I entered it using the mySQL client, it worked fine. However, when i try to use it in my VB application and get that result into a ADODB.Recordset object (so I can fill the DataGrid control), I get an error message : "SELECT in an stored procedure must have INTO". Does anyone know what's going wrong? I'd really like to use stored procedures instead of just sending the SELECT from the client for performance reasons,

ResultSet Is From UPDATE. No Data.
I have a probleme executing a query using a PreparedStatement, it
results in a SQLException with the message "ResultSet is from UPDATE.
No Data.". Below I added the profiler log which indicates that the
statement-id changed between [PREPARE] and [QUERY]. Shouldn't it be
[PREPARE] and [EXECUTE] anyway?

INFO: Profiler Event: [PREPARE] at
org.jboss.resource.adapter.jdbc.BaseWrapperManaged Connection.prepareStatement(BaseWrapperManagedConn ection.java:360)
duration: 62 ms, connection-id: 0, statement-id: 43, resultset-id: -1,
message: SELECT name,value FROM Attribute WHERE foreignuuid=?
2006-09-18 10:47:30,176 INFO [STDOUT] (Timer-4) 18.09.2006 10:47:30 N/A
N/A

INFO: Profiler Event: [QUERY] at
org.jboss.resource.adapter.jdbc.WrappedPreparedSta tement.executeQuery(WrappedPreparedStatement.java: 296)
duration: 32 ms, connection-id: 0, statement-id: 44, resultset-id: 0,
message: SELECT name,value FROM Attribute WHERE
foreignuuid='c01b997f0a0dfd8e018a4aeffbfeb45f'

I'm using JDK 1.5.0_06, mysql-connector-java-3.1.13-bin.jar with JBoss
4.0.2 and mysql 4.1.10 on W2K. I'm using JNDI to get a DataSource and
retrieve a Connection object.

Can anybody think of reason why the statement-id changes? Is this
change the reason for the SQLException mentioned above?

Update Auto_increment Data
i need to re sort auto_increment value in mysql database.ex: there is 5 record - and i have to delete record no 3. while i'm using increment ID as row number.so it will display 1,2,4,5 . i need it to auto re-sort update on this fields.

Update Heavy Data
We have an os commerce application with a database of 7mb is working fine on our local server but when we are trying to update the data from local to online server via phpmyadmin we are unable to upload it there.
Is any other way or idea to upload the file to server?
"we can do it by root access of server but we dont have that acess in our hosting"

How To Join Two Result Sets In Version 3.23?
I have two queries that are identical except for the joined table - all retrieved fields are the same.

SELECT r.tbl_ID, r.date, r.field
FROM table1 r
INNER JOIN table2 t2
ON r.tbl2_ID = t2.tbl_ID
WHERE t.field3 = 'Y'

and

SELECT r.tbl_ID, r.date, r.field
FROM table1 r
INNER JOIN table3 t3
ON r.tbl2_ID = t3.tbl_ID
WHERE t.field3 = 'Y'

You can't use UNION in version 3.23 so I am left with guessing you output to an array then add to that array.