Re: AW: [PHP] How to argue with ASP people...

[Bob Ramsey]

Don't mean to start a discussion whatsoever, I
 

love php, but 
   

one thing i can't do in php is
 

Response.Redirect(page.asp) .
   

Apart from that no complains so far :)
 


Um, as I understand it this is simple to do in php.  Just use:
header(Location: $somestring);
Here's the ASP code I found when I looked up what response.redirect does:
%
u_location=request.form(u_location)
if u_location   then
response.redirect (u_location)
end if
%
form method=post action=redirect_varible.asp
select size=1 name=u_location
option value=http://www.goto.com;GoTo.com/option
option value=http://www.priceline.com;Priceline.com/option
option value=http://www.alladvantage.com;AllAdvantage.com/option
/select
input type=submit value=Submit
/form

Here's how I'd code the same thing is php:
?php
   if isdefined($_POST[u_location]
   {
   $newpage = $_POST[u_location];
   header(Location:  $newpage);
  }

form method=post action=redirect_varible.php
select size=1 name=u_location
option value=http://www.goto.com;GoTo.com/option
option value=http://www.priceline.com;Priceline.com/option
option value=http://www.alladvantage.com;AllAdvantage.com/option
/select
input type=submit value=Submit
/form
As I understand it, all response.redirect does is tell the browser to go
to another page.  That's all the header function does too.  I use this
all the time if people aren't authenticate to push them to the login
page or if they aren't using https to connect, to push them to the https
url.
Am I missing something here?
The only snage with the header function is that you must not print or
echo anything to the browser before you use it.  In other words you
can't do this:
html
body
pI moved you to another page./p
?php header(Location: http://someotherpage.com;); ?
/body
/html
Whoops.  Just realized that this was coming through the mysql lists, so 
it's OT.  Original poster can e-mail me off list with questions.

Bob

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

Re: detailed summary of data, average, min, max

[Bob Ramsey]

Thanks,  that was it exactly.
bob
At 02:25 PM 10/28/2004, Michael Stassen wrote:
Something like
  SELECT uid,
 AVG(number_grade) AS average_grade,
 SUM(IF(letter_grade = 'A',  1, 0)) AS A_count,
 SUM(IF(letter_grade = 'B+', 1, 0)) AS B+_count,
 SUM(IF(letter_grade = 'B',  1, 0)) AS B_count,
 SUM(IF(letter_grade = 'B-', 1, 0)) AS B-_count,
 SUM(IF(letter_grade = 'C',  1, 0)) AS C_count,
  FROM grades_table
  GROUP BY uid;
should do.
Michael
Bob Ramsey wrote:
Hi,
I have a table of grades like this:
title, section, instructor, letter_grade, number_grade, uid
With data that would look like this:
English, 1, Smith, B, 88, 1
English, 1, Smith, B, 86, 1
English, 1, Smith, B+, 89, 1
Math, 1, Jones, A, 95, 2
Math, 1, Jones, B, 85, 2
Math, 2, Smith, C, 75, 3
Math, 2, Smith, B-, 82, 3
I want a query that will give me something like this:
uid, average_grade, A_count, B+_count, B_count, B-_count, C_count
1, 87.67,  0,1,  2,0,   0
2, 90, 1,0,  1,0,   0
3, 78.50,0,  0,1,   1
I can do this is a separate query for each grade, but that makes a lot of 
little queries.  Is there a way to do this in one query?  Or am I just 
going to have to break the average out and do the counts in one query and 
the average in another?
Thanks,
bob
==
Bob Ramsey  SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III
MA, Management of Information Systems 2004
MA, English Literature, 1992
ph:  1(319)335-9956  187 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==

==
Bob Ramsey  SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III
MA, Management of Information Systems 2004
MA, English Literature, 1992
ph:  1(319)335-9956  187 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

detailed summary of data, average, min, max

[Bob Ramsey]

Hi,
I have a table of grades like this:
title, section, instructor, letter_grade, number_grade, uid
With data that would look like this:
English, 1, Smith, B, 88, 1
English, 1, Smith, B, 86, 1
English, 1, Smith, B+, 89, 1
Math, 1, Jones, A, 95, 2
Math, 1, Jones, B, 85, 2
Math, 2, Smith, C, 75, 3
Math, 2, Smith, B-, 82, 3
I want a query that will give me something like this:
uid, average_grade, A_count, B+_count, B_count, B-_count, C_count
1, 87.67,  0,1,  2,0,   0
2, 90, 1,0,  1,0,   0
3, 78.50,0,  0,1,   1
I can do this is a separate query for each grade, but that makes a lot of 
little queries.  Is there a way to do this in one query?  Or am I just 
going to have to break the average out and do the counts in one query and 
the average in another?

Thanks,
bob
==
Bob Ramsey  SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III
MA, Management of Information Systems 2004
MA, English Literature, 1992
ph:  1(319)335-9956  187 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: field that does not contain text between symbols - solved

[Bob Ramsey]

Thanks for the replies.  This appears to be the right answer:
where page_body regexp '.*img .*.*' and page_body not regexp '.*img .* 
alt= .*.*';

bob
==
Bob Ramsey  SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III
MA, Management of Information Systems 2004
MA, English Literature, 1992
ph:  1(319)335-9956  187 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

filed that does not contain text between symbols

[Bob Ramsey]

Hi,
I have some web pages in a database and I want to check to make sure 
that all of the images have alt tags in them.  So what I need to do is 
ask something like this in psuedocode:

select page_name from web_pages where page_body does not contain 'alt=' 
between 'img' and '';

But I just can't figure out the right syntax.  Any ideas?
Thanks,
bob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: filed that does not contain text between symbols

[Bob Ramsey]

Chris Blackwell wrote:
not sure you can do this just with mysql, I think your gunna need to select
the html from the db then send it to something like perl or php and use a
regex parser on it.
 

Yeah, that's what I was afraid of.  Now all I have to do is decide 
between PERL and PHP for the scripting language. ;)

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

different kind of nested selects

[Bob Ramsey]

Let's say I have two tables:
T1:
Name
---
apple
banana
cherry
T2:
value| name
-|--
1  | apple
2  | banana
3  | banana
4  | cherry
5  | apple
6  | apple
I want to get a result that looks like this:
name| all_values
apple| 1,5, 6
banana| 2,3
cherry| 4
In my head, the select statement looks like this:
select t1.name, (select t2.value from t2 where t2.name=t1.name) as 
all_values from t1;

Is there a way to do this with just one sql statement?
Thanks,
bob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Document Upload Facility

[Bob Ramsey]

Michael Mason wrote:
I'm new to MySQL and already very impressed with it's flexibility, 
speed and functionality. This in mind, I am looking for a way to allow 
users to upload documents to the server for later retrieval by an 
administrator.

 

Can this be done or will I have to find a nasty third party tool...?
You should be able to do this, but be aware that storing arbitrary 
binary data might have security implications.  Basically, what you end 
up doing is having a table something like this:

create table user_files(user_name varchar(255) not null, user_file blob);
Then you use your html code on a web page to let users upload a file.  
You take that file and insert it into the database.  I've done it before 
and it works ok.  Just make sure to check that if someone uploads an 
executable file you don't accidentally execute at some point.

I don't have code handy to share, but if you need some I could probably 
knock something out quickly.

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

Re: How do you deal with URL's?

[Bob Ramsey]

Personally, I'd split that into 2 fields.  I think that's a better way 
to model the data unless there's something I don't know.

Otherwise, try this:
mysql select * from url;
+---+
| url   |
+---+
| a href=http://www.alabama.gov;Alabama/a  |
| a href=http://access.wa.gov;Washington/a |
+---+
2 rows in set (0.00 sec)
mysql select substring(url,locate('',url)+2, 
char_length(url)-locate('',url
)-5) as state from url;
++
| state  |
++
| Alabama|
| Washington |
++
2 rows in set (0.00 sec)

mysql
What I had to to was to have mysql take the string:
a href=http://www.alabama.gov;Alabama/a
and give me the parts between  and /a.  First, I had to find the 
position of  and then add 2 to it.  The substring function in mysql 
takes the parameters string, starting_position, and length.  Using 
locate, I got the starting postion and added 2 to it.  For length, I had 
to use locate again; locating  gives me the position of the  in .  
Subtracting 5 gives me the right length after discounting the /a and 
the 2 positions I'm off from .

Someone more experienced that I can tell you if there's a more effecient 
way.  My inclination would be that for best results, you should split 
the field in two and build your webpage like this:

a href=$URL$STATE/a
Hope this helps.
bob
David Blomstrom wrote:
Suppose I have a field with the names of states,
linked to their home pages:
a href=http://www.alabama.gov/;Alabama/a
a href=http://access.wa.gov/;Washington/a
If I display this on a webpage, I'll get the names of
the states, linked to their home pages. But is there a
simple strategy that will let me to display the names
UNLINKED on another page, or do I have to create a
second field that lists simple state names, with no
URL's?
Thanks.
	
		
__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 



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

Re: pattern matching - but in reverse

[Bob Ramsey]

Oh, I think I know this one.
Copied from my console:
mysql select * from test;
++-+
| id | myvalue |
++-+
|  1 |  12 |
|  2 |  15 |
|  3 |   3 |
|  4 |  10 |
|  5 |  10 |
|  6 |  10 |
++-+
6 rows in set (0.04 sec)
mysql select * from test where myvalue = REPLACE('-1-2-','-','');
++-+
| id | myvalue |
++-+
|  1 |  12 |
++-+
1 row in set (0.06 sec)
In this example, the user did a search for '-1-2-' and I told mysql to 
give me all records that equaled the result of the replace function that 
replaced all '-' with nothing.  So it matched 12 from the user input -1-2-.

If that works for you, let me know.
bob

Luke Majewski wrote:
Hi everyone,
ok, so I know how to use RLIKE to match regular expressions. However, 
let's say I have an isbn number of:

0-06-430022-6
saved in the database but someone wants to search for it by entering:
0064300226
or even
006-430-0226

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

Re: pattern matching - but in reverse

[Bob Ramsey]

Whoops.  I was thinking about how I have my isbn table stored. ;)  I 
prefer to remove all formatting from numbers like this(isbn, phone 
numbers, social security numbers, etc) before storing them.  Anyway, 
here's something that should work.  I'm not sure if it is the most 
efficient way to do this, but it works:

mysql select * from t2;
+--+
| isbn |
+--+
| 12345|
| 123-45   |
| 123-4-5  |
| 123-4-56 |
| 123-4-57 |
| 123-4-58 |
| 123-3-58 |
| 123-3-58 |
+--+
8 rows in set (0.00 sec)
mysql select * from t2 where 
replace(isbn,'-','')=replace('1-2-3-4-5','-','');
+-+
| isbn|
+-+
| 12345   |
| 123-45  |
| 123-4-5 |
+-+
3 rows in set (0.00 sec)

mysql
Note that in my example, I had three entries with essentially the same 
isbn number, just formatted differently.  This is basically the same 
idea as before except now we are replacing the '-' in the data in both 
the table and the user input string. 

This seemed simpler than trying to first strip all of the '-' from the 
user string and then re-insert them in the right places.  You can do 
this, but it's a lot clunkier and I wouldn't recommend it.  If you are 
curious, the sql statement is:

mysql select * from t2 where isbn=
   -  concat(
   -  substring(
   -  replace('1-2-3-4-5','-',''),
   -  1,
   -  3),
   -  '-',
   -  substring(
   -  replace('1-2-3-4-5','-',''),
   -  4,
   -  5)
   -  );
++
| isbn   |
++
| 123-45 |
++
1 row in set (0.01 sec)
What I did here as to first strip all of the '-' out of the user string, 
since we weren't sure where or if they were there.  Then I rebuilt the 
string to match the pattern ###-## using concat and substring.  As I 
said, this just seems far too clunky to deal with even if it does work.

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

Re: Is 255 Characters Really the Limit?

[Bob Ramsey]

Use the blob/text, mediumblob/mediumtext or  longblob/longtext types.  
Since you are just storing text, use the text versions.

It sounds like when you say text what you really mean is the varchar() 
type.  If you declare a field as a text type, you can store 2^16 
characters, or 65,536 characters.

From http://dev.mysql.com/doc/mysql/en/Storage_requirements.html the 
text types are:

tinytext = 2^8 = 256 characters (same as the max for varchar())
text=2^16=65,536 characters = 65 kilobytes (the old maximum size 
document Notepad was capable of opening, pre-windows 2000)
mediumtext=2^24=16,777,216 characters=16 megabytes
longtext=2^32=4,294,967,296 characters= 4 gigabytes of data

If you wanted to store pictures or mp3's or other binary data in the 
database, you'd use blob types instead of text with the same storage 
capabilities.

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

Re: urban myth?

[Bob Ramsey]

Ah, but the ordering is not random.  As your example has it, the results 
are in the order that the entries were inserted into the table.  There is 
an explanation for the order of the returned data.

bob
At 12:55 PM 5/3/2004, Garth Webb wrote:
On Mon, 2004-05-03 at 10:39, Boyd E. Hemphill wrote:
 My boss says that if you do a select statement against a table the
 result set always comes back in the same order.  I say that this is a
 myth and that the result is random, except when some ordering is
 specified in the SQL statement.

 Who is right?  Is this behavior specified by ANSI or ISO?
You are correct.  Ordering takes time.  Why choose a random column on
which to order the results and take additional time when the user didn't
ask for it.  Here's the proof:
create temporary table foo (num int(10));
insert into foo values (1), (2), (3), (4), (5);
select * from foo;
+--+
| num  |
+--+
|1 |
|2 |
|3 |
|4 |
|5 |
+--+
5 rows in set (0.00 sec)
delete from foo where num = 3;
insert into foo values (6);
insert into foo values (3);
delete from foo where num = 6;
select * from foo;
+--+
| num  |
+--+
|1 |
|2 |
|4 |
|5 |
|3 |
+--+
5 rows in set (0.00 sec)
Garth

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


==
Bob Ramsey  SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III
ph:  1(319)335-9956  187 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Reports

[Bob Ramsey]

[EMAIL PROTECTED] wrote:

hi,
   Is there a way to do report writing from Mysql databases? I want to 
transfer all the records from Mysql to a file. i used the Into OUTFILE but it 
doesnt display properly.I want to diplay it properly like records or reports. 

is there a way??

Thanks,
Liz
 

You are going to want to add formatting with something like PERL or 
PHP.  Alternately, you can use an odbc connection to have MS Access 
connect to your mysql server and make the reports for you.  Let me know 
if you need help setting it up.

Bob

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

Re: How to load fixed-format data?

[Bob Ramsey]

Roy Smith wrote:

I've got a bunch of files which contain packed fixed-field-width 
records.  The records are pretty long; depending on the file, as long 
as 1000 characters and 10's of fields.  Fields are a mix of numeric 
and alphanumeric types.

What's the best way to load these into mysql?  Mysqlimport looks like 
it only handles delimited data.


I think I'd use either perl or php to process the file.  If you aren't 
into programming, import the fixed width file into access (which does 
handle fixed width fields) then export to a comma separated value file.

bob



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

Re: Migrating Access databases to MySQL

[Bob Ramsey]

Arjun Subramanian wrote:

Do you have any
specific sites or utilities in mind ?
 

Try the code posted in this newsgroup posting:

http://groups.google.com/groups?q=macro+access+export+mysqlhl=enlr=ie=UTF-8oe=UTF-8selm=tchs8rl5phqib7%40corp.supernews.comrnum=3

It's a little long to post here.  It is for Access 97, so it may need 
some tweaking

There's also this project on freshmeat:

http://freshmeat.net/projects/exportsql/

Another project called exportsql here:

http://www.cynergi.net/exportsql/

I don't know if those two are the same or just have similar names.

Another macro here:

http://elmo.engineering.tech.nhl.nl/config/two/tools/access_to_mysql.txt

It's been a long time since I used one of these macros and I can't 
remember which one it was that I used.  You will probably have to do a 
little bit of tweaking if you have Access XP, but I don't know for sure.

Bob

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

Re: WHERE clauses across rows...

[Bob Ramsey]

If you use php to access your data through web pages you can move the 
pointer in the query results around.  I've got some code I could show you 
if it would be helpful.  Email me off list.

bob

At 10:55 AM 2/27/2004,  Eric B. wrote:
Not sure how you determine what the order of your rows are, but assuming you
have a column called rownumber, or soemthing to that extent, which is a
sequential numbering of the rows in your table, you can probably do it with
a join on itself.  You might have to play with the join syntax a little (in
the where clause), b/c this is totally off the top of my head.
ie:
SELECT unique_key_field FROM table_name as t1, table_name as t2 where
t1.rownumber = t2.rownumber+1
and
(
(t1.col1='strt' and t1.col2='word')
OR (t1.col2='strt' and t1.col3='word')
OR (t1.col3='strt' and t1.col4='word')
OR (t1.col4='strt' and t2.col1='word')
)
Good luck.

Eric

Joshua J. Kugler [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
 1) This is mostly an SQL question, although MySQL may have some trick up
its
 sleeve that would help me.
 2) I've searched the archives, and google
 3) I've been using SQL for a long time, but can't think of a way to solve
this
 4) This may not be possible. :)

 I am dealing with serial data that is being put into a table, and I have
to
 search through that data to find certain start words.  That is, data
that
 indicates the start of a new packet of data.  This start word, since this
is
 asynchronous serial data, could be split over rows.

 For purposes of example, let us assume we have a table of four columns,
and
 that my start indicator is strt in one column and word in the next
 column.  Now I want to find the next start word.  The first three cases
are
 easy, I just do something like WHERE col1 = 'strt' AND col2 = 'word',
etc..

 But, what I need to be able to do is something like this:

 SELECT unique_key_field FROM table_name
 WHERE (col1='strt' AND col2='word')
 OR (col2='strt' AND col3='word')
 OR (col3='strt' AND col4='word')
 OR (col4='strt' AND col1_in_the_next_row='word')

 Is this even possible?  I'd hate to issue hundreds of queries to check if
 strt word is split across rows.

 Should I investigate setting variables equal to the col4, and on a failed
 search, use that variable in the next query to see if the old col4 pairs
 with anything in col1?

 Or am I better off searching for the good case, and on failure, go and
look
 for 'strt' in col4, then when I get a row, see if 'word' is in col1 on the
 next row (via another query)?

 Ideas? Tips? Suggestions?

 Thanks much!

 j- k-

 --
 Joshua J. Kugler
 Fairbanks, Alaska
 Computer Consultant--Systems Designer
 .--- --- ...  ..- .--.- ..- --. .-.. . .-.
 [EMAIL PROTECTED]
 ICQ#:13706295
 Every knee shall bow, and every tongue confess, in heaven, on earth, and
under
 the earth, that Jesus Christ is LORD -- Count on it!


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




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


==
Bob Ramsey   Applications Development  Support II
ph:  1(319)335-9956  187 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query help - add results then divide by

[Bob Ramsey]

I think that you can just do this:

select sum(ads.col)*1.191*sum(ads.depth)/131.77 where date ='2004-02-26' 
AND editionID = '13' AND ads.page = '16';

because of the disttributive property of multiplication.

(2 * 1.191) +(6*1.91) +(4*1.91)/131.77 = 12 *1.91/131.77 = 
(12*1.91)/131.77 = 12*(1.91/131.77)

Test it to make sure I understand what you're asking, but it worked for 
my in my tests.

bob
Rogers, Dennis wrote:
	Good afternoon,

How can I take the 3 results below add them together then divide by
131.77?
	Can it all be done in one SQL statement?

	Thanks in advance.

mysql describe ads;

+---+---+--+-+++
| Field | Type  | Null | Key | Default| Extra
|

+---+---+--+-+++
| adID  | int(11)   |  | PRI | NULL   |
auto_increment |
| page  | int(11)   |  | | 0  |
|
| adnum | varchar(20)   |  | ||
|
| date  | date  |  | | -00-00 |
|
| depth | decimal(3,2)  | YES  | | 0.00   |
|
| timestamp | timestamp(14) | YES  | | NULL   |
|
| col   | int(11)   | YES  | | 0  |
|
| acc   | varchar(50)   |  | ||
|
| editionID | int(11)   |  | | 0  |
|

+---+---+--+-+++
9 rows in set (0.00 sec)
mysql SELECT ((ads.col * 1.91)  * ads.depth)  FROM ads Where date =
'2004-02-26' AND editionID = '13' AND ads.page = '16';
+-+
| ((ads.col * 1.91)  * ads.depth) |
+-+
|7.64 |
|   34.38 |
|7.64 |
+-+
3 rows in set (0.01 sec)
 

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

update or replace with select statement

[Bob Ramsey]

Hi,

I know I can do

insert into mytable select * from some_other_table;

but I can't find the right syntax to use with either an update or 
replace.  Ideally I'd like to do something like this:

update mytable set mytable.a=(select other_table.some_column from 
other_table where other_table.some_column=mytable.a+1) where mytable.b=6;

The goal is to set a column in a to a value based on a column in another 
table when b in the same record has a specific attribute.

Can I do this?

Thanks,

bob



==
Bob Ramsey   Applications Development  Support II
ph:  1(319)335-9956  187 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

wildcards the field

[Bob Ramsey]

Hi,

I'd like to use a mysql database as a lookup to authorize people to edit a 
webpage.  The idea is that people would go to a main login page for 
authentication and then when they visit a page they are authorized to edit, 
they see an edit this page link.

What I'm envisioning is a really basic table structure like this:

id varchar (10)
path varchar (255)
id is a foreign key that links back to another table I have with more user 
information, but that isn't really important.

Entries might look like this, with explanation in brackets []:

'ramsey', '/' [I am the webmaster, I can edit all files in all directories]
'jones', '/data1' [jones can edit all files in the data1 directory]
'jones', '/data2/jonesdoc.php' [jones can also edit this one file in data2]
'smith', '/data2' [smith can edit all files in data2]
'smith', '/data2' [smith can also edit all files in data3]
People will generally have access to an entire directory, but not 
necessarily.  Most people will have access to more than one directory.

So if jones goes to http://www.mysite.com/data1/index.php, the php code on 
the page sees that the file is /data1/index.php.  It then needs to find out 
if jones can edit that file.

I'd like to be able to do a single mysql query.

select * from table where path = '/data1/index.php' and user 
='jones';[obviously won't work]

I can use php and get take the current page and turn it into '/data1' and 
then do:

select * from table where (path ='/data1/index.php' or path='/data1') and 
user ='jones';

but I'm curious if there's a way to have '/data1/index.php' match '/data1' 
using wildcards or something like that.

Thanks,

bob



==
Bob Ramsey   Applications Development  Support II
ph:  1(319)335-9956  187 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Certification Test Questions

[Bob Ramsey]

So how reflective of the real test is the sample test at mysql.com? 
Normally the little sample tests are easier than the real thing, but I'm 
curious.  I got 8 out of 10.

bob

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

Re: wildcards in the field

[Bob Ramsey]

At 03:52 AM 1/13/2004,  Harald Fuchs wrote:

SELECT *
FROM tbl
WHERE user = 'jones'
  AND '/data1/index.php' LIKE concat(path, '%');


Thanks for the tip.

Bob



==
Bob Ramsey   Applications Development  Support II
ph:  1(319)335-9956  187 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

wildcards the field

[Bob Ramsey]

Hi,

I'd like to use a mysql database as a lookup to authorize people to edit a 
webpage.  The idea is that people would go to a main login page for 
authentication and then when they visit a page they are authorized to edit, 
they see an edit this page link.

What I'm envisioning is a really basic table structure like this:

id varchar (10)
path varchar (255)
id is a foreign key that links back to another table I have with more user 
information, but that isn't really important.

Entries might look like this, with explanation in brackets []:

'ramsey', '/' [I am the webmaster, I can edit all files in all directories]
'jones', '/data1' [jones can edit all files in the data1 directory]
'jones', '/data2/jonesdoc.php' [jones can also edit this one file in data2]
'smith', '/data2' [smith can edit all files in data2]
'smith', '/data2' [smith can also edit all files in data3]
People will generally have access to an entire directory, but not 
necessarily.  Most people will have access to more than one directory.

So if jones goes to http://www.mysite.com/data1/index.php, the php code on 
the page sees that the file is /data1/index.php.  It then needs to find out 
if jones can edit that file.

I'd like to be able to do a single mysql query.

select * from table where path = '/data1/index.php' and user 
='jones';[obviously won't work]

I can use php and get take the current page and turn it into '/data1' and 
then do:

select * from table where (path ='/data1/index.php' or path='/data1') and 
user ='jones';

but I'm curious if there's a way to have '/data1/index.php' match '/data1' 
using wildcards or something like that.

Thanks,

bob



==
Bob Ramsey   Applications Development  Support II
ph:  1(319)335-9956  187 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

corrupt odbc connection in MS Access

[Bob Ramsey]

Has anyone seen something like this before:

We have an Access database (about 9 megs in size) with linked tables in 
it.  The machine DSN shows that the information for the connection is 
correct.  But when you try to open the table in Access, a dialog pops up 
to connect to the mysql server and the information is wrong.  It has the 
wrong database name.  We correct the information in the odbc dialog box 
so that it matches the DSN information and the table opens.

We have triple checked the odbc connections for windows; they are all 
correct.  There's nothing in Access that references the wrong database.  
There's nothing in the registry.  Deleting the linked tables and 
re-linking them and repairing the access database appears to fix the 
problem.

Has any one else experienced this or know what might be causing it?  My 
only guess so far was that the mdb itself had become corrupt, which was 
why deleting and relinking the tables then repairing the database fixed 
the problem.

I'd appreciate any information you have.

Thanks,

bob

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

Re: importing Access databases

[Bob Ramsey]

I saw a macro for access that will produce a  script that will recreate 
your tables and the data in them.  But it only worked in older versions 
of access.

Sorry I can't be more help.

bob

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

Re: between A and B with another condition?

[Bob Ramsey]

Like this?

mysql select * from t2;
+-++
| name| number |
+-++
| bob |  3 |
| bob |  2 |
| bob |  1 |
| al  |  1 |
| al  |  2 |
| al  |  3 |
| al  |  4 |
| adam|  4 |
| adam|  3 |
| adam|  2 |
| charlie |  2 |
+-++
11 rows in set (0.00 sec)
mysql select * from t2 where (number 3) and (name between 'a' and 'c');
+--++
| name | number |
+--++
| bob  |  2 |
| bob  |  1 |
| al   |  1 |
| al   |  2 |
| adam |  2 |
+--++
5 rows in set (0.00 sec)
Is that what you mean?



==
Bob Ramsey   Applications Development  Support II
ph:  1(319)335-9956  216 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

update and order by in 3.23.51

[Bob Ramsey]

Hi,

I'm using 3.23.51 and I'd like to update a field in a certain order.  I've 
got a table of images for a slide show and the format of the table is:

image varchar(128) not null
order_number integer not null primary key
So we might have something like:

duck.jpg 1
cat.jpg 2
horse.jpg 3
I want the user to be able to insert a new picture anywhere and 
automatically up date the order numbers of the other items.  Apparently 
this is easy in version 4, because you can just do:

update slideshow set order_number=order_number +1 where order_number 1 
order by order_number desc

Because you have to change 3-4, 2-3 so that you can insert a new number 2.

Is there a good workaround for this in version 3?

Thanks,

Bob



==
Bob Ramsey   Applications Development  Support II
ph:  1(319)335-9956  216 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: wrong time in m$office

[Bob Ramsey]

At 04:10 PM 3/11/2003,  [EMAIL PROTECTED] wrote:
m$office (office 2000 - win xp) can not handle the time correct.
openoffice (win-xp or redhat-phoebe3)  has no problems.


I believe that's a known problem with Microsoft products.  They only appear 
to be able to handle datetime objects, not date or time.  You might also 
look up the #DELETED# error on the web for similar problems.

Bob

PS.  Cool.  This message initially bounced back to me from the mysql list 
server because it didn't contain one of the following 
words:  sql,query,queries,smallin. Your message cannot be posted because 
it appears to be either spam or simply off topic to our filter.

Now it should get through.



==
Bob Ramsey   Applications Development  Support II
ph:  1(319)335-9956  216 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date query.

[Bob Ramsey]

At 09:36 AM 3/10/2003,  Anil Garg wrote:
The date field in my database table looks as below:
07th of March 2003 10:14:29 PM
Weird.  All of my datetime variables look like '2003-03-07 22:14:29'.  Are 
these fields datetime field types or are they text that looks like a date?

If they are datetime, you can just do this:

select * from mytable where (mydate = date_sub(now(), interval 14 day) and 
(mydate  now());

This should get you all dates that are more recent than 14 days ago but are 
not in the future.  I'm not 100% sure of the syntax though.  Check out 
http://www.mysql.com/doc/en/Date_and_time_functions.html for more info.

If that is just a string that looks like a date, I'm not sure what you can 
do using mysql alone.  If you were using a scripting language like php or 
perl, you could do some pre-processing.

Bob





==
Bob Ramsey   Applications Development  Support II
ph:  1(319)335-9956  216 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

help with join syntax

[Bob Ramsey]

Hi,

I have the following tables:

mysql describe lawfac_pub;
++--+--+-+-+---+
| Field  | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| hawkid | varchar(16)  |  | PRI | |   |
| emailalias | varchar(128) | YES  | | NULL|   |
| first_name | varchar(64)  | YES  | | NULL|   |
| last_name  | varchar(64)  | YES  | | NULL|   |
| title  | varchar(128) | YES  | | NULL|   |
| building   | varchar(128) | YES  | | BLB |   |
| phone  | varchar(64)  | YES  | | NULL|   |
| room   | varchar(255) | YES  | | NULL|   |
| notes  | varchar(255) | YES  | | NULL|   |
++--+--+-+-+---+
mysql describe fac_stud_lunch;
+---++--+-+-+---+
| Field | Type   | Null | Key | 
Default | Extra |
+---++--+-+-+---+
| instructor_hawkid | varchar(16)|  | PRI 
| |   |
| meal_time | datetime   |  | PRI | -00-00 
00:00:00 |   |
| meal_type | enum('lunch','supper') |  | | 
lunch   |   |
| location  | 
varchar(128)   |  | | |   |
| num_students  | int(11)| YES  | | 
NULL|   |
+---++--+-+-+---+

mysql describe fac_stud_lunch_join;
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| instructor_hawkid | varchar(16) |  | PRI | |   |
| student_hawkid| varchar(16) |  | PRI | |   |
| meal_time | datetime|  | PRI | -00-00 00:00:00 |   |
+---+-+--+-+-+---+


And this is my query I run from php:

SELECT DISTINCT lawfac_pub.first_name, lawfac_pub.last_name, 
date_format(fac_stud_lunch.meal_time, %W, %M %D, %Y) as formatted_date, 
fac_stud_lunch.meal_time AS fac_stud_lunch_meal_time, 
fac_stud_lunch.instructor_hawkid, fac_stud_lunch.meal_type, 
fac_stud_lunch.location, fac_stud_lunch.num_students
FROM lawfac_pub INNER JOIN (fac_stud_lunch INNER JOIN fac_stud_lunch_join 
ON fac_stud_lunch.instructor_hawkid = 
fac_stud_lunch_join.instructor_hawkid) ON lawfac_pub.hawkid = 
fac_stud_lunch_join.instructor_hawkid
WHERE (((fac_stud_lunch_join.student_hawkid) Not Like 's1')) order by 
fac_stud_lunch.meal_time;

And I keep getting an error on the inner join.  I've usually been able to 
get the syntax for joining 3 or 4 tables by using Access, but it seems to 
have failed me this time.

Any ideas why this select statement is failing?

Thanks,

Bob



==
Bob Ramsey   Applications Development  Support II
ph:  1(319)335-9956  216 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: help with join syntax

[Bob Ramsey]

At 03:57 PM 3/4/2003, Martin Ostlund wrote:

I usually use phpMyAdmin


Thanks for the tip.  Unfortunately it isn't my server and they haven't 
finished installing phpMyAdmin on it yet.  That's why I usually make odbc 
connections in access and build the queries graphically.  But it just keeps 
choking on the join statement.

You have an error in your SQL syntax near '(fac_stud_lunch INNER JOIN 
fac_stud_lunch_join ON fac_stud_lunch.instructor_hawk' at line 2

Thanks,

Bob





==
Bob Ramsey   Applications Development  Support II
ph:  1(319)335-9956  216 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

newbie sql statement help

[Bob Ramsey]

The people who run our mysql server are using version 3.23.51, so I don't 
have access to some of version 4's features like sub selectes.

I have a table with data like this:

firm	firm_rank	time	time_rank	student
A	1		10	1		jones
A	1		10	1		smith
A	1		10	1		alvin
A	2		10	1		bob
A	2		10	2		charlie
B	2		10	1		jones
B	2		10	1		smith

and so forth.  Each student can rank a firm 1-10 and an interview time 
1-10.  There are usually about 30 firms and about 200 time slots.

My goal is to pick a random student from the highest firm rank and then 
from the highest time rank for each time.  So someone who ranks the firm as 
2 and the time as 1 loses to someone who ranks the firm as 1 and the time 
as 3.  If Jones is picked for Firm A at 10, she is ineligible for any more 
Firm A slots and all  10 o'clock slots regardless of firm.

I can almost see a way to use the unique, random, and order by features to 
make one really complex select statement that just pulls everything out, 
but I can't do it.  If it can't be done, I'm willing to loop through doing 
individual firms and just selecting out a random high ranking person for 
each time, but I can't see that either.

I'm using mysql and php.  Any ideas?  I'd hate to just keep looping through 
and making lot's of small select statements, which I know I can do.  It 
seems like there's a better way.

Thanks,

bob




==
Bob Ramsey   Applications Development  Support II
ph:  1(319)335-9956  216 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php