Posts by rch1231

thank, now i understand how to use the MAX() properly, but i still have a problem. The SQL statement you gave me will only selects the latest date for one of the "tables". What if i wanted to pick up the latest date for each of the "tables"

Ex:
Classroom2

Name: | Date:
Oliver | 10-10-10
Homer | 08-10-10

Classroom1

Name: | Date:
James | 20-10-10
Lars | 01-10-10

I want both of the greens to be displayed. MAX() will only get the 20-10-10 right? and i will not be able to select the other then...

Picky Picky Picky..

Try this:

(Select * 
from mytable 
where mytable.classroom = 'Classroom1' 
and mytable.date = (select max(date) 
from mytable where classroom = 'Classroom1')
) UNION (
Select * 
from mytable 
where mytable.classroom = 'Classroom2' 
and mytable.date = (select max(date) 
from mytable where classroom = 'Classroom2')
)

rch1231 thanks so much i think your help is great!!!

One thing if you dont mind how would i do my queries?

At moment i have been using dreamweaver with php/mysql and creating my recordsets in dreamweaver from the mysql database and displaying and using dreamweaver to do the inserts and display the data in a table.

I have been able to simple queries and have been managed to do a few join queries with help from other people along the way.

However i am not sure how i would do these queries to display by month, year and how much due??

Could you help please point me in the right direction or advise where i should do the queries, database or app??

Thank you again

You need a real query editor to create them with. You can get one for free from http://dev.mysql.com that will allow you to connect to the database and help you create the queries. I have a well worn copy of O'Reilly's MYSQL cookbook that has been great.

Classroom2

Name: | Date:
Oliver | 2012-10-10
Homer | 2012-10-10
Peter | 2011-11-10

Classroom1

Name: | Date:
James | 2012-10-10
Lars | 2012-10-10
John | 2011-10-10

This my table:
Classroom : Varchar
Date : datetime
Name : varchar

I'm looking for the select statement, not how to format the date...
the text in red i not to be selected

You need a sub query to find the max first then get the data based on the result. If should be something like this:

Select * 
from mytable 
where mytable.classroom = 'Classroom1' 
and mytable.date = (select max(date) 
from mytable where classroom = 'Classroom1')

Watch out for the datetime vs. date variable type if you are looking for all things on the same day.

I would drop the Commission field from the Policy table(s) and add a new table called Policy_Reps_Comm:

PRC_ID
Policy_ID
UserID
Date_Started
Date_Ended
Commission_Percent_Of_Total

On the last field it could also be commission_amount instead of a percent of the total.

Splitting the data out into a separate table you can handle things like:
Record 1 rep AAA gets 2.5% on policy 1234's Total_Premium
Record 2 rep BBB gets 2.5% on policy 1234's Total_Premium
Record 3 rep CCC gets 5% on policy 1234's Total_Premium because Rep AAA and BBB work for him.

By having a start and end date and creating a new record if the commission amount changes you have enough data to be able to recreate commissions. It gives a history of Rep AAA was paid r% from this date to this date and s% from this date to this date and t% since then. By having one rep per record you don't have to check for the other fields being filled in when doing your calculations and can make one pass through the table. It will make a lot of things easier in the long run, trust me.

You will be able to create queries that link all of the data together at the time you need it and hopefully very few holes to try and fill in later.

By the look of the data your date field does not have the variable type set as a "date". MySQL normally displays date in the format:

DATE 'YYYY-MM-DD'

see the following for more info:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html

Hello,

The answer to that generates a couple of new questions.
What is the maximum number of Reps that share a commission?
How is commission determined (flat rate, percentage of total, etc.)?

Normally in SQL when you have multiple values you spin off a new table and link it back with a record ID. If it is NEVER going to be more than 2 Reps you could create 2 sets of fields in the Policy table to hold the data but it is not the best method. You now have to code to check both fields and output for each. It is better to make a new table. You could decrease the number of tables by combining the Policy tables into one and adding a policy type flag.

Hi,

I was looking for some help with how i would go about setting up my commission table.

Basically i have currently:

Users Table
UserID
Username
Password
Email

Clients Table:
ClientId
Client_UserID
FirstName
LastName
Address
PostCode
Tel
Email

Insurance Table:
InsuranceID
Clients_ClientID
Provider
Commission
DatePaid

CarInsurance Table:
CarInsuranceID
Clients_ClientID
Provider
Commission
DatePaid

I want to setup a commission table to track commission due for a user and list the provider and client name.

I am not sure if i should have a UserID column in my insurance tables for this to work?

Could anyone advise on what my structure should be?

I need to query it by dates.

So in the website the user needs to be able to filter the results based on month and year.

So show how much they have made in 2010 or show how much they have made in just the month of April.

Also if possible show how much is due to come in.

So if the datepaid column is null then show the total commission due in, then once it is paid it will change to show paid.

Hope someone can help me with this, appreciate your help.

Many thanks

Hello,

This may not be all you need and is based on what I would do. Remember there is more than one way to solve the issue so review this …

Hello,

Try bringing up task manager and minimizing it to the task bar then let the screen saver kick and see what is taking up all of your CPU time. If nothing else download Maleware Bytes, update it and let it scan your system (may take an hour or two). There is a free version just make sure you are getting it from their site.

hi guys, does anyone can recommend a freeware or any tools to recover a raw hdd drive. The system was suddenly corrupted, hdd can still be detected and shown on my computer but upon clicking on the icon it's asking me to "Reformat my hdd".

Hello,

If this is a one time thing and I suggest downloading UBCD for windows. It is a bootable CD that has partition and file recovery tools available from the system after it boots.

If the data is critical look into photo-rec and testdisk which are free or purchase and R-Studio and their applications.

Hi
I tried the example code and the script still manages to display incorrectly.

The modified code looks like this.

 for( n=21; n<=26; n++ )
         { 
          printf "%-8s %-10s %6s\t %10.2f\n", lname[n]", ", fname[n],
    position[n], TotalSales[n] | "sort -n -k 3"
           } 

The output is the following:

Lazy Acres, Inc.
2009 Sales Associates Ranking
Name                Position    Sales Amount
============================================

Buck,    Fast       Stock Boy       2630.78
Doe,     John       Clerk            134.01
Lindon,  Rosemary   Producer          31.00
Miller,  Dennis     Commedian          9.90
Rush,    George     Salesman        1049.79
Worker,  Susan      Manager          360.00

end quote.

By default sort uses white space (any space or tab) to separate fields so -k 3 would sort Stock, Clerk, Producer, etc. Is it possible to move the column you want to sort to the front of the list?

The other thing I just noticed is that you are running the sort on each individual line. AWK is parsing the file one record at a time and by piping inside awk you are only sorting the current record. Your could try sorting the output before you pass it to awk or put the sort outside the for loop.

I have a desktop i am trying to get it connected with wireless adapter. iam able to configure wireless connection settings. once it connects it keeps dropping the wireless connection and connects back in few mins , this happens for ever.

i have other laptops that are connected to internet through the same router. i am able to connect to internet thru wired on the same router for that desktop.

after connecting (with connections dropping) wirelessly the desktop does not have a ip address , not sure what is the issue.....any help will be big help

Hello,

I have seen this type of activity before when the wireless is getting interference from other devices or even the adapter it's self. If you have a cordless phone move it away from the computer having problems or unplug it and see if the problem goes away. The other thing is that the wireless signal is a very high frequency and if the system is close to the ground floor (cement foundation) it can cause interference with it's self by reflecting off the floor.

If the router is near by then use the wire instead of wireless. Wire max speed with good signal is 54Mb and wire is 100Mb and has no interference. Just a suggestion.

A new site that I have to work with requires me to use putty to connect to the site if I want to work remotely as opposed to logging in to their phpMyAdmin to manipulate my data..
I am unfamiliar; please read that has having no knowledge of how to do this.
So far, from my reading, I have collected the tools to do this, but I do not know how to integrate the tools to get this to work and that is the reason for this email.
I have Putty and HeidiSQL and apparently HeidiSQL has Plink which appears to be of value, but once again I am unclear how to use this.
Given all of the above, can someone point me to a tutorial, an existing series of notes or give me notes on how to run these applications so I can see and manipulate my data on my new hosting service?
Thanks!
WBR

Attached is a doc I worked up on how to use ssh tunnel to connect to mysql with putty..

Hello,

The problem is you are not telling sort which column to sort off of. By default is uses the first column. The -k flag in sort tells it what field to use for the sort and the = t flag tells it the separator. You could try:

sort -n  -t - -k 3

You may have trouble with the - as the separator so you could substitute : to separate the info and then replace it with sed afterward.

Hello,

Attached is a table of netmask based on the network selected.

Download the free version of Mal-ware Bytes and install and run it in safe mode till you get not more infections showing up.

http://www.malwarebytes.org/

IT Prof. please help me..! I have encountered a problem regarding my ANTI-VIRUS...
My Anti-virus is "AVIRA", and it keeps detecting a Virus that is said to be a "BACKDOOR"...
It infected the explorer.exe.
Whenever I open any folder, it will appear...And also if I will Move it to quarantine, it will not be removed...
Please help me!

This is the actual message of avira when it detects the said virus...

http://www.mediafire.com/i/?hzmbdibgnij

Best Regards....

Hello,

I checked out the link you provided and the first think that I noticed was the path for the file was "c:\WINDOWS.0\EXPLORER.EXE
" instead of "C:\WINDOWS\EXPLORER.EXE". Is your copy of windows installed in C:\WINDOWS.0 ? It could be and if so then try booting to "safe mode with command prompt" and deleting the explorer.exe file and replace it with a copy from your install CD.

Opps...

cwarn23 is correct about it being the grave (`) and not the single quote ('). I was looking at the code I have and did not pay enough attention to what I was posting..... The example cwarn23 provided is the correct syntax.

The table name and fields that have spaces in the name must be inclosed in single quotes:

INSERT INTO 'employee record'('Employee ID', 'Employee type', 'First Name', 'Last name', 'Farm location', 'Farm type') VALUES('34', 'caretaker', 'Michael', 'Hipolito', 'lara', 'nursery' )

Hello,

Ok if you are running the trigger from another table (such as location) then you still need a field to hold the season and then the trigger you have will work with the addition of a where clause to tell mysql what record in category to update. If you add the season field to location table the following trigger used as an after insert trigger on location will update the first record in category every time a location is added.

Does that make sense?

thnx for your interest in my question, my main goal is to when a hotel is inserted automatically the (that's why the trigger) the category.num_summer or category.num_winter is updated and added one,
so i'll know how many hotels are used in Summer and how many on Winter.
A trigger that count hotels based on the season when a hotel is inserted on table location.
I hope i made clear what i want this to do. thnx again...

OK. Then my suggestion would be this:

Add a field to the location table for season so that when the row is added to location they post if it in summer or winter. Then use the query below to find out how amny in summber and winter...

SELECT location.season as Season,
count(location.season) as 'Number per Season'
FROM
location
GROUP BY
location.season
ORDER BY
location.season ASC

and you should get a accurate count.

i have 2 tables: category, location.
i create the tables:

create database tourist;
grant all on tourist.* to dbuser;
use tourist;
create table category(id integer NOT NULL AUTO_INCREMENT,season varchar(20),num_summer integer,num_winter integer,PRIMARY KEY(id));
create table location(code integer NOT NULL AUTO_INCREMENT,town varchar(20),hotel varchar(20),price integer,star integer,media varchar(70),PRIMARY KEY(code));

i want now to create a trigger on category that when is inserted in field <season> a season , if it is inserted before to count how many for that season.
Inserted winter and then again winter the num_winter should be 2.

i created this but doesn't work:

create trigger count_hotels
after insert on category
for each row
begin
if season='Summer' then
update category 
set categoty.num_summer = category.num_summer + 1
else if season='Winter' then
update category 
set categoty.num_winter = category.num_winter + 1
end if;
end;

If anyone could help i would appreciate it.

Hello,

OK I see a couple of things but want to make sure I have the right idea. If I am reading the code correctly you are running this trigger from the category table to update the record in category that you just entered....

One problem is you can't add to a field with a value of NULL so you need to change your default for the fields to be:

create table category(id integer NOT NULL AUTO_INCREMENT,season varchar(20),
num_summer integer default '0',
num_winter integer default '0' ,
PRIMARY KEY(id));

Based on that the row would always be 1 in either category.num_summer or category.num_winter in every record you add. In …

Did you reset the local modem or gateway. Check for link light on network port and on modem/gateway. If no link light check or replace cable. IF there is another computer find out what it's settings are and mimic them adding 1 to the ip address it has (i.e. if the other computer is 192.168.1.14 use 192.168.1.15 for the address on this computer) and the same gateway and netmask.

First I would check the BIOS setup and see what it is seeing. If the BIOS setup (CMOS) is seeing the CD ROM and other items then look for an option to "Reset Configuration data" in the section that has the PCI IRQ settings and set it to YES. When you reboot it will return to NO after a clean boo to the OS. Let me know what happens.

I would suggest removing and re-seating the ram. When it crashes do you get any type of error message.