Delete or Update on Import

[Tim McDonough]

Once each day I have a PHP application that reads a legacy dBase
format file and updates a customer table in a MySQL database. The PHP
script reads each dBase record, searches for it in the table, updates
the field values if they have changed, or creates a new record in the
MySQL database if one does not exist. So far, so good.

My problem is that occasionally a customer will be deleted in the
dBase format system. In this case the "deleted" customer is still
present in the MySQL database. It's never updated but it still appears
in reports, etc. I could first bring all the dBase data into a
separate table and then compare the two tables deleting records in the
main table that have no match in the freshly imported data.

I believe this would cause me to examine all the records several times
between importing, checking for changes, deletions, etc. I would
appreciate any suggestions for a more elegant/efficient method. There
are about 20,000 records.

Thanks in advance,

Tim




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Writing to Local Files

[Tim McDonough]

On Tue, 07 Sep 2004 15:08:30 -0500, gerald_clark wrote:
>
> Tim McDonough wrote:
>
>> Is there a way to write the results of a query to an output file
>> on a client computer instead of the server? My reference book
>> says MySQL cannot write to a file on the client.
>>
>> Does MySQL not allow the LOCAL option for security reasons? You
>> can run the query from a command line and redirect the output,
>> it's just not nicely formatted.
>>
> Formatting with redirected output is no different than it is at the
> mysql client command prompt, so how would a 'LOCAL' option change
> anything?
>
> You can set the pager to a script that outputs to a file.

If there were a LOCAL option for OUTFILE I would be able to have the
fields separated by commas and enclosed in quotes and make a CSV file
that Excel or OpenOffice understands. MySQL on Win32, at least version
4.0.16, does not let me use the "enclosed by" and "separated by"
modifiers without OUTFILE.

Tim




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Writing to Local Files

[Tim McDonough]

Is there a way to write the results of a query to an output file on a
client computer instead of the server? My reference book says MySQL
cannot write to a file on the client.

I have a work around by using a PHP script on the user's machine that
does a MySQL query, formats the results, and writes them to a file but
that seems a little clunky.

Does MySQL not allow the LOCAL option for security reasons? You can
run the query from a command line and redirect the output, it's just
not nicely formatted.

Thanks in advance,

Tim



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Replication with Different OS

[Tim McDonough]

Our MySQL database server currently runs on a Win32 platform. If we
setup replication do the slaves have to also run on Win32 or can a
slave be a different platform such as Linux?

Tim


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Better Solution than Multiple Queries?

[Tim McDonough]

I have an application where I want to look for records that match
certain criteria and then for each item found do a second lookup for
additional information. Normally I would do a join. In this case
however I want to display each of the results from the first of the
two criteria whether or not the second part is found or not.

The solution I presently have does a query for the first criteria.
Then, I loop through the results of that query and do another query
for each returned row. This produces the desired results but requires
a lot of queries, i.e.-- if the first query returns 1000 customers
then I make 1000 additional queries to get the remaining info if it
exists.


result1 = first query
for each row in result1
{
  result2 = second query
  display result1
if result2 isn't empty then
{
display result2
}
}

Is there a more efficient way to query the database and always display
the information I presently get from the first query even if there are
no matches found on the second?

Thanks for any suggestions.

Tim


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to optimize ugly order by?

[Tim McDonough]

On Wed, 24 Mar 2004 15:47:46 +0100, Henrik Schröder wrote:

> I have a table of users which contains a lot of information, and
> also a column called membershiptype which can either be 0, 1, or 2.
> This table is used a lot in various searches, and all results
> should be ordered by membership type, but the order should not be 0-
> 1-2, but instead 1-2-0. Currently, this is achieved like this:
>
> SELECT ... ORDER BY (membershiptype <> 1) ASC, (membershiptype <>
> 2) ASC, login ASC
>
> ...which is rather ugly, and forces MySQL to create a temp table
> with the calculated expressions and then re-sort the result using
> these. Since this query is used a lot, it would be nice if I could
> get rid of this. I'm completely stumped. Any ideas?
>
> No, I did not choose the sort order. No, I can not change the
> values used. Yes, it has to be this sort order. :-)

There may be a cleaner method but could you add a field that's used
for sorting so that the sorted field has a value of 0 where membership
type is 1, 1 where it is 2, and 2 where it is 0? You could then ORDER
by this field but use the value from membershiptype for your other
purposes.

Tim


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Finding Most Recent Record for Sensor Problem Solved

[Tim McDonough]

On Tue, 09 Mar 2004 00:41:19 -0500, Michael Stassen wrote:
> Did anyone refer you to the following page in the manual?
>
>
> 

I admit to feeling a little foolish here. My "solution" came from
combining a couple of similar query formats that were sent to me as
sql code. I had not seen this in the manual but the people helping me
obviously had without suggesting the link as you did.

Thanks for pointing this out. I still had a tough time finding this in
the manual copy I have by searching.

Tim


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Finding Most Recent Record for Sensor Problem Solved

[Tim McDonough]

The other day I was looking for a way to find only the most recent
piece of data stored in a historical database of 40 sensors.
Thanks for all the helpful suggestions. While no one suggested a
fully working solution the suggestions got me pointed in the right
direction. In case anyone might be lurking with a similar problem
here is the gist of what I was needing to do and the solution I
developed.

We have a system that collects data from 40 different sensors. Each
reading is automatically stored along with the time and date of the
measurement in the MySQL database. For monitoring purposes we wanted
a web page on our Intranet that would show the most recent reading
from each sensor, its name, and timestamp. The database currently
holds about 20,000 records.

The solution below uses a temporary table since sub-queries aren't
available in the current version of MySQL that we use. A version of
this query is actually a part of a PHP script on the internal
webserver.

I don't claim it's particularly elegant or even the best way to do
it but I've been wrestling with this off and on for several days now
and this seems to be working well.

--- start sql file ---

-- T. McDonough
-- OFR, Inc.
-- 08-March-2004
-- MySql Version 4.0.16 Win32
-- Finds the most recent sensor report for each unique
-- sensor.

-- Make a temporary table to hold name and date

create temporary table tmp (
unitnum varchar(100),
posdt datetime
);

-- lock the main table in preparation for the queries... we don't
want
-- the data to change between the insert and the select.

lock tables sensor read;

-- extract the unit names with the most recent time and dates and
load
-- that data into the temporary table.

insert into tmp select unitnum, max(posdt) as posdt
from sensor
where unitnum like 'OFR-%'
group by unitnum;

-- extract data from the main table only where it matches what's in
the
-- temporary table.

select sensor.unitnum,
  sensor.posdt,
  sensor.value,
  tmp.unitnum,
  tmp.posdt
from sensor, tmp
where sensor.unitnum = tmp.unitnum and sensor.posdt = tmp.posdt;

-- unlock the tables so so other processes can use them

unlock tables;

-- get rid of the temporary table.

drop table tmp;


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Re: Selectinmg most recent dates from multiple table items

[Tim McDonough]

 --- Original Message ---
From: "Joshua J. Kugler" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sun, 07 Mar 2004 04:57:30 -0900
Subject: Re: Selectinmg most recent dates from multiple table items

> SELECT DISTINCT sensor_id, other_fields FROM table_name
> ORDER BY time_stamp_field DESC LIMIT 40
>
>
> Not sure if that will work, but does it point you in the right
> direction?

When I use this I end up getting the most recent 40 sensor readings
from the same sensor. I'm trying to figure out the proper query to
get the most recent sensor reading from each of the 40 sensors.

> On Saturday 06 March 2004 07:22 pm, Tim McDonough wrote:

> This seems similar to something I'm working on which I haven't
> sorted out to my satisfaction yet.
>
> We have a system that collects and stores data that is time
> stamped in a mysql database. There is data from 40 sensors and it
> does not arrive at exactly the same time so each sensor reading
> and it's time stamp are stored. The table contains a date/time,
> the sensor ID, and the value.
>
> One request is to have a web page that will display each sensor
> and it's most recent measurement so the most recent is always
> shown. regardless of which sensor it came from. How do I create a
> query that will extract the most recent data (latest date) from
> the database for each of the sensors and have it sorted it by the
> sensor ID?
>
> This isn't obvious to me from reading the documentation on
> queries and I've been searching and reading through messages
> without success yet. I suspect a big part of the problem is I'm
> fairly new to sql queries and am not exactly sure what terms to
> search for.
>
> Tim


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Selectinmg most recent dates from multiple table items

[Tim McDonough]

On Sat, 06 Mar 2004 20:40:24 -0600, Paul DuBois wrote:

>
> You can use your LIMIT clause as well, as long as by that you don't
> mean "5 most recent from *each* table".
>

This seems similar to something I'm working on which I haven't sorted
out to my satisfaction yet.

We have a system that collects and stores data that is time stamped in
a mysql database. There is data from 40 sensors and it does not arrive
at exactly the same time so each sensor reading and it's time stamp
are stored. The table contains a date/time, the sensor ID, and the
value.

One request is to have a web page that will display each sensor and
it's most recent measurement so the most recent is always shown.
regardless of which sensor it came from. How do I create a query that
will extract the most recent data (latest date) from the database for
each of the sensors and have it sorted it by the sensor ID?

This isn't obvious to me from reading the documentation on queries and
I've been searching and reading through messages without success yet.
I suspect a big part of the problem is I'm fairly new to sql queries
and am not exactly sure what terms to search for.

Tim


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]