Re: Returning where COUNT 5
With a join and group by I think you have HAVING. Hi all how to do this in MySQL? Returning only records with COUNT 5? SELECT `groups`.`groupsDescr`, `roles`.`roles_Agroup`, `roles`.`rolesDescr`, COUNT(`roles`.`rolesDescr`) AS TOTAL FROM `roles` INNER JOIN `groups_roles` ON (`roles`.`rolesID` = `groups_roles`.`fkrolesID`) INNER JOIN `groups` ON (`groups_roles`.`fkgroupsID` = `groups`.`groupsID`) GROUP BY `roles`.`rolesDescr` WHERE COUNT(`roles`.`rolesDescr`) GT 5 MySQL 4.0.12 give an error... maybe because i'm using WHERE COUNT? Thanx for your time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Installation Problem
After the phpmyadmin login I got the same error. In the phpmyadmin config files I had to set the local IP address that phpmyadmin was on. Dear Michael, I want to use PHP, Apache, mySQL and phpMyAdmin for evaluation but I m not able to get this rite. I am trying to instal MySQL with phpMyAdmin and i get the error when view from : http://localhost/phpMyAdmin/ #2003 - Can't connect to MySQL server on 'localhost' (10061) OS - WinXP Pro Apache phpMyAdmin 2.5.7 I tried reinstalling but not able to get. I checked Apache and php they work fine except mySQL. Any help will be appreciated. Feel free to email or contact me on phone # below. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update problem
echo $updatequery to screen and see what the sql looks like.
I am trying to update a table from results generated from a select query,
but can't seem to get the queries running right. The select query works
fine, but when I try to use the results for an update it error out. Here
is
my query:
db_connect();
$query = SELECT cst_SiteID FROM customers WHERE cst_Offer = 1;
echo $query;
echo br/;
$result = mysql_query($query);
$numofrows = mysql_num_rows($result);
$numofcols = mysql_num_fields($result);
echo table\n;
for ( $r = 0; $r $numofrows; $r++)
{
$row = mysql_fetch_array($result);
echo td$row[0]/td;
$updatequery = UPDATE assets .
SET asset_Managed = '2' .
WHERE asset_SiteID = $row[0];
$result = mysql_query($updatequery) or die (Query Failed:
$updatequery);
$numofrows = mysql_num_rows($result);
for ($i = 0; $i $numofrows; $i++)
{
$row = mysql_fetch_array($result);
echo td$row[0]/td;
}
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Erro on Query
Try back ticks around it. `DESC' Dear Friends. when Creating the follow table CREATE TABLE CONHEC_DBF (CON CHAR (7) , SER CHAR (2) , NUMNF CHAR (8) , SERNF CHAR (2) , LOJA CHAR (2) , PAIDF CHAR (12) , QUANT REAL (6,0) , NOP CHAR (10) , TRAN CHAR (4) , DTC DATE, DTE DATE, ESPEC CHAR (10) , PESO REAL (8,2) , VALORNF REAL (12,2) , FRPESO REAL (12,2) , FREVAL REAL (12,2) , VALKILO REAL (12,2) , PERVFRE REAL (12,2) , PERTFRE REAL (12,2) , OUTROS REAL (12,2) , PREST REAL (12,2) , BCFRE REAL (12,2) , ALIICM REAL (6,2) , ICMS REAL (12,2) , TOTFRE REAL (12,2) , VALOREM REAL (12,2) , CIFFOB CHAR (1) , TIPOFAT CHAR (1) , TIPTRAN CHAR (1) , FATURA CHAR (10) , DATAFAT DATE, DESC REAL (12,2) , IMPEXP CHAR (1) , SR_RECNO BIGINT (15) NOT NULL AUTO_INCREMENT , SR_DELETED CHAR (1) NOT NULL , PRIMARY KEY (SR_RECNO) ) Type=InnoDb I got an Erro when defining Column DESC(same error even if DESC is between ) So how can i Create this Column, or how to resolve this problem mysql server version 4.1.1 Regards Luiz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL vs Oracle
10 seconds !?Unless that query is huge and without indexes, it should run MUCH quicker. I'm using Oracle 8.0.5 and MySQL 4.1.1a I'm not optimizing Oracle nor MySQL, because I don't want future users messing with optimizations. I access through JDBC drivers. Oracle usually needs 10 seconds in every query I execute. It is very odd. Thank you again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Learning PHP/MySQL
I like the web site www.phpbuilder.com ... lots of great articles. Hi All, Can someone recommend a good book for learning PHP/MySQL? TIA, Kay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Notifying User when DB is Updated via browser
How about an HTML meta tag refresh set at 20 minutes. This may be slightly off-topic, apologies if so but I was wondering if anyone could point me in the right direction. I'm currently storing applications from a web site into our mysql DB, our client has a web-based backoffice. He has to refresh his screen every 20 minutes to see the latest applications. Is there any way of using some sort client-server technology to alert the user, via the browser that a new entry has appeared in the DB - I'm already sending an email but this is still not the best solution. Thanks in advance, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Asterisks in Integer Columns
One simple option would be to add a footnote column. And add in your code, if footnote column is NOT NULL then add a * on to population and show footnote at the bottom. population footnote 100 null 200*yada yada Suppose I have several columns of numberals - area, popoulation, etc. - and I want to include asterisks and footnotes, as in below: 200 4200 258* 234 24 258superscript1/superscsript 2400 What are some good strategies for doing this? You really aren't supposed to include asterisks in integer columns, right? It would also be nice to have a strategy that would give you the option of masking asterisks and footnotes when you don't want them displayed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Completely Stumped - phpMyAdmin config
I remember something setting mine up. The phpMyAdmin.ini (I think) had to have the correct IP address of the machine/database it was running on. My machines internal address was 192.168.2.100, but the INI setting had to be the outside resolved IP 24.16.xx.xx if running it from outside. Hello: After an all nighter, and reading and trying as much as I can, I'm completely stumped. I'm running RedHat 9, Apache 2.0, SSL, mySQL 3.23, PHP-4.2.2 I can create and insert tables on the server command line. I can run php coded pages residing on the server from a remote browser. I can open phpMyAdmin from a remote browser using http access. I cannot access tables using phpMyAdmin from a remote browser. There is no mention of php in the httpd.conf, but I have php.conf in the http dir. Can anyone please help? Ken -- 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]
Re: RV: Select data from two different databases
Yes Local. SELECT databasename.tablename.columnname Hi, two questions: Is it possible to select data from different Databases on the same server? Is it possible to select data from different Databases on Different servers across the network? I searched for information on how to do it, but i haven't found any. thanks in advance Alejandro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where do exported SQL files go?
Using phpmyadmin, when I select Export, and click the checkbox Save as File, it prompts where for where and what file name. I'm trying to figure out how to get my MySQL databases online. Someone told me to EXPORT my databases as SQL files, then go into my online websites's phpMyAdmin program and import it, and everything else would fall into place. At first, I was confused and exported just a single table. I was able to import it online. Then I went back and exported my entire database, but I couldn't import it, because I couldn't find it. I exported it again under a distinctive name - zorex - then did a Windows search for it. No trace. The table I saved was on my desktop, but there's no trace of a database SQL on my desktop, nor have I found it in my MySQL or phpMyAdmin folders. Any tips? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing Queries (specifically with LIMIT)
I found it interesting. Thanks Eric. (reconstructed from archives i accidentally deleted the copy in my mailbox Daniel Clark writes: I don't see how LIMIT would make a difference. LAST_INSERT_ID() only returns one record. But it's worth trying in a big loop to get timing numbers. Well, I decided to test this all out and see what happened. From what my little test was able to determine, there is no difference in speed from doing: SELECT LAST_INSERT_ID(); vs SELECT LAST_INSERT_ID() LIMIT 1; I used 100,000 selects as my benchmark number (which on my box here took about 40 seconds per run to complete). Times were measured using unix time, and I averaged the user times and the difference was so small as to be negligable, then i decided to measure the same thing but with SELECT LAST_INSERT_ID() FROM table; and it was approximately 25% slower (but im assuming as the table grew in length so would the gap in speed. Conclusion: it makes no difference positive or negative including the LIMIT, but as Michael pointed out including the FROM clause causes a big penalty. I hope someone else finds this slightly interesting. For completeness im including the program i wrote to test this. Your mileage may vary, it needs a little configuring for your particular setup before you can run tests. (database name, user/password etc) you have to manually change the one line inside the loop to call whichever function you want to test. And its also handy to change the one print statement before the loop. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing Queries (specifically with LIMIT)
I don't see how LIMIT would make a difference. LAST_INSERT_ID() only returns one record. But it's worth trying in a big loop to get timing numbers. I have a rather simple straightforward question that I was not able to find in any of the documentation. I am in the process of writing an application, and I was wonder whether there is any benefit (or penalty) in LIMIT(ing) a LAST_INSERT_ID() query? i.e. SELECT LAST_INSERT_ID() FROM foo vs. SELECT LAST_INSERT_ID() FROM foo LIMIT 1 Thanks in advance, Eric Absgarten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE pk1 AND pk2 returns 0 records
Possibly two rows? Can you change the where clause? WHERE CartID=999 OR ProdID=333 I have a simple query: SELECT * FROM cart WHERE CartID=999 AND ProdID=333 The primary key is made of both CartID and ProdID, both integers. The query will not return the record in question This returns the row: SELECT * FROM cart WHERE CartID=999 And this returns the same row: SELECT * FROM cart WHERE ProdID=333 But this returns zero records: SELECT * FROM cart WHERE CartID=999 AND ProdID=333 Any ideas? Brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with a Select Query
SELECT user_id, rep_nurse_1, rep_nurse_2 FROM Users WHERE User_ID = 'x' I have a problem with a SELECT query. I have a users table and each user can be a Rep, Nurse or Administrator defined by User_Type. Each Rep has two Nurses allocated to them and the User_ID of the nurse is held in column Rep_Nurse_1 and Rep_Nurse_2. I am trying to perform a query that returns the two nurses details based on the User_ID of the Rep: SELECT U1.*, U2.* FROM Users U1, Users U2, Users R WHERE R.User_ID = 'x' AND U1.User_ID = R.Rep_Nurse_1 AND U2.User_ID = R.Rep_Nurse_2 However this returns just the first nurse allocated to that rep. I have listed my User table defination below and would be most grateful for anyones advice here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Range query on datetime with index - any optimization?
I wonder if mysql isn't trying to process where GMTBase DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY) What about doing this date subtracting in PHP and adding the result to the SQL statement. Hello folks, I'm usinng MySQL 4.0.17. I have a table something like: RuleID int, GMTBase datetime, Credited bigint, ... I have an index built on GMTBase. I have rougly 8 million rows. GMTBase stores a datetime for the top of the hour on a given date. I want to build a summary of the last 2 days without scanning every record. It appears that there is no way to get MySQL to use the index on GMTBase to avoid scanning all 8 million rows. I estimate it should only scan about 267K rows. Explain mentions the GMTBase index but says it will examine about a million rows. That seems to roughly match my estimate of the number of distinct GMTBase values. The query I want to run is: select RuleID, GMTBase, sum(Credited) from RuleHistograms where GMTBase DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY) group by GMTBase Have I done something wrong or is there simply no way to avoid scanning all of those records? Thanks, _M -- 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]
Re: Use of AVG in where clause
I believe you have to add your AVG() to your GROUP BY clause. I have a sql query as follows: ** SELECT DB_ESTABLISHMENT_NAME, AVG(DB_GRADE) FROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ON ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID WHERE AVG(DB_GRADE) 2 GROUP BY ES.DB_ESTABLISHMENT_ID ; ** and I get the following error: ERROR : Invalid use of group function Can anyone tell me how to use a function in the where clause? Thanks in advance, Julien. -- 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]
Re: AVG function in order by clause
In Oracle you can reference the second field AVG() by the field #. ORDER BY 2 Thanks a lot for the replies. I have changed the query as follows: ** SELECT DB_ESTABLISHMENT_NAME, AVG(DB_GRADE) FROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ON ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID GROUP BY ES.DB_ESTABLISHMENT_ID HAVING AVG(DB_GRADE) 2 ORDER BY AVG(GRADE) ** Now I am having a problem with the order by clause. How can I have the AVG function in the ORDER BY clause or how can I sort by average grade? Thanks in advance, Julien Martin. -- 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]
Re: INSERT INTO dropping slashes from strings
I think you want chr(39) (single quote). Every time I think I've got it, I am reminded thatI don't got it. Hello all, once again! Can anyone tell me why the following takes place? In my VB app I am adding records to the table Jobs with this code (trimmed way down - my actual INSERT statement populates about 20 fields): sqlstr = INSERT INTO Jobs VALUES( _ Chr(34) txtSceneFile Chr(34) ) adocn.execute sqlstr Now, let's say that my txtSceneFile contains C:\Data\test\foo\bar.lws. My Jobs table will show the following for the applicable data field: C: Data. This, my frields, is a new one to me, to be sure! Any ideas? Many thanks, Steve -- 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]
Re: INSERT INTO dropping slashes from strings
Check the table sturcture for required fields and EXACT field names.
mysql is case sensitive.
I tried that to no avail (I had actually had the code set initially to
use single quotes thusly:
sqlstr = INSERT INTO Jobs VALUES(' _
txtSceneFile ')
but it generated the same erroneous string. Using the ascii-code
equivalent (Chr(34) does the same.
It's just soodd!!
Daniel Clark wrote:
I think you want chr(39) (single quote).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT INTO dropping slashes from strings
How about: (I reversed on set of quotes)
sqlstr = INSERT INTO Jobs VALUES(' txtSceneFile ')
Err...what I meant to say here was ascii code equivalent (chr(39) (I
tried single *and* double quotes). Sorry about that.
Steve Pugh wrote:
Hi Daniel,
I tried that to no avail (I had actually had the code set initially to
use single quotes thusly:
sqlstr = INSERT INTO Jobs VALUES(' _
txtSceneFile ')
but it generated the same erroneous string. Using the ascii-code
equivalent (Chr(34) does the same.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT INTO dropping slashes from strings
Hum. I've never tried inserting without the field names. Serves me right to type this stuff instead of copying/pasting - my previous post was indeed wrong but also wasn't the exact code I used (yours was, with the single quote embedded in the doubles). It's been a very dyslexic week for me. On your previous response regarding field names and case sensitivity, I'm not actually referencing any field names in my INSERT INTO query - I'm just populating all of the fields in order, and the order is correct from what I can tell. The other fields are solid (once I got past a poorly formatted date field, that is) but these strings with the backslashes stripping out and parts lopping off, that's the last rub. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
Yes it is a myth. The records will come back in the same order IF there have been not inserts and deletes. Depends on the database product to. 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? Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- 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]
Re: Need correct 'order by' syntax where field does not contain NULL
select * from navigation WHERE id = '.$category.' AND active='y' AND order IS NOT null ORDER BY order, title Hi. I had a MySQL DB set up and recently added a field 'order' to allow for exceptions in a web site menu heirarchy. Fields should be ordered by 'order' field first where it does not contain 'NULL', and then by field 'title'. I had this previously: select * from navigation WHERE id = '.$category.' AND active='y' ORDER BY title ...and now, when I put in: select * from navigation WHERE id = '.$category.' AND active='y' ORDER BY order, title ...my menu shows nothing. How can I write my statement to allow for ORDER by order where 'order' does not contain 'NULL', and then title? Thanks, Eve -- 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]
Re: Database structure
I used to program for a medical tester. I used method 2: 2 - a table with 60 rows for one assessment : results(#assessment_nr, labtest_nr, p, d) where p and d are my two results. The BIG advantage was changes to the tests, adding new ones, or deleting fields. Made it much more flexible. I would like an advise on the following problem : I have a table of patients. Each patient can make different biological assessments. Each assessment is always decomposed into different laboratory tests. A laboratory test is made of a test number and two values coming from analysers. The schema is : Patients(#patient_nr,name,etc...) Assessment(#assessment_nr, #patient_nr, assessment_type, other usefull values). Assessment_types(assessment_type, labtest_nr) An assessment is composed of different tests, let's say assessment type 1 is composed of lab test 1,2,3,5 and assessment type 2 of lab test number 10 to 70. I have an assessment with 60 different lab tests (always the same). I have two ways for storing the values : 1 - a table with 120 columns for the two values. results(#assessment_nr, p10,d10, p11,d11, .,p70,d70). where 10 to 70 represents the lab test number. 2 - a table with 60 rows for one assessment : results(#assessment_nr, labtest_nr, p, d) where p and d are my two results. Here comes my question. Which of the two would you choose? The firsrt solution has the advantage of returning one single row for one complete assessment. If I have to make statistics, it is easy. But, if I have to modify the composition of an assessment (which occurs very rarely), I shall have to use an alter table instruction. As I have 4 different assessment types, I have to create five different tables, one per assessment type. The second solution is normalized and more elegant. But I am preoccupied by the size of the table. For one assessment, I'll store 60 rows with only two useful integers in it. And you must add the size of the index. With 25.000 assessments a year, it makes 1.500.000 rows with only 4 columns amoung them 2 only for the results and 2 for identification. I would like to store 10 years online, so 15.000.000 rows. What about the size of index ? Any advise ? I thank you in advance. Alain Reymond (I hope that it is clear enough with my bad English). -- 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]
Re: Order by price?
I think when adding the $ the number gets convert to a string. And then
the query orders it by alpha numberic.
I'm having trouble with this query:
SELECT
CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2))
as price FROM table ORDER BY price
A note first. The column establishments is an int(10), as you see I am
taking this number through a formula, and I want the result of this
formula
to be rounded to two decimal places, and to stick a $ sign in front of
it
so I can easily display it on a table.
However here is the problem, in ASC order I get these results:
$110.46
$173.86
$208.02
$22.62
$22.62
And in DESC order I get these results:
$90.44
$79.94
$50.10
$48.33
(with the max result 208 for instance at the 3rd from very bottom, 110 is
at
very bottom)
Any help in solving this would be greatly appreciated.
Thanks,
Yoed
--
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]
Re: Last Record Pulling my hair out :D
Think you want: Select max(job_id) from job_log_2004 I'm trying to select the last record (row) in a db. I'm trying with a select: Select * from job_log_2004 where job_log_2004.JobID = (select max(job_log_2004.JobID) from job_log_2004) and it fails. The part about select max works and returns a number. What on earth am I doing wrong? Or is there an easier way to return the last record? Thanks Thanks, James -- 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]
Re: Randomly selecting from table
Guess you could use the rand() function and look for a matching row id!?! Is it possible to randomly select from all entries in a table, but have it be 2 different ones each time? If so, what documentation should I be looking at? I am using PHP and MySQL together, if this helps. Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error ?
Sounds to me like it doesn't the the alias name T2 but might need the full table name. Hi, Can anyone give me a hint to what this error message is trying to say. Thanks, Justin Palmer -Original Message- From: Justin Palmer [mailto:[EMAIL PROTECTED] Sent: Thursday, April 15, 2004 1:38 PM To: [EMAIL PROTECTED] Subject: FW: Error ? The query that generated this error was: SELECT t1. * FROM `wat_student_profile` AS t1, `wat_student_job` AS t2 LEFT JOIN t2 ON t1.student_id = t2.student_id WHERE t1.cv != '' AND t1.sevis_id = '' AND t2.student_id = NULL LIMIT 0 , 30 Regards, Justin Palmer -Original Message- From: Justin Palmer [mailto:[EMAIL PROTECTED] Sent: Thursday, April 15, 2004 1:28 PM To: [EMAIL PROTECTED] Subject: Error ? Hi, I get this error from a query: #1066 - Not unique table/alias: 't2' What does it mean? Can anyone point me to a place where I can look up what these codes actually mean? Any help would be great. Regards, Justin Palmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using PHP to copy tables
Sound like a great idea! Is there an easy way to create an HTML page that will copy selected tables to backup copies on the same server? I want to create an administration page for my client to be able to backup their database whenever they see fit. But, I can't give them direct access to the MySQL server and don't want them backing up to their hard drive. I prefer to simply copy the tables to backup versions on the server so that if problems arise, I can log into the server and simply copy the backups to the originals. Thanx in advance. -- Robb Kerr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing the primary key
You can ALTER TABLE http://www.mysql.com/doc/en/ALTER_TABLE.html I´'m new with mysql and have following question: I have a table, that has a primary key with two columns and want to add a third column to this primary key. Is this possible and when yes: Do I have to delete all tables, that reference to this table? Best regards Michael R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problems
Do you have separate indexes on: Table1.ID Table2.ID Table1.Field1 Table2.Field1 Table1.Field1 Table1.Field2 Select* FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla We have tried to set up indexes and the query still takes 8 minutes to run. It only returns 6,000 records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problems
I know Oracle likes the indexes separatly, but mySQL might like combinations. No, we tried individual indexes and then one big grouped index but not individual indexes on each of the fields. Adding the index actually added a few seconds to the query so we weren't sure if that was the way to go. I'll try this, though. Eric At 10:36 AM 2/25/2004 -0800, Daniel Clark wrote: Do you have separate indexes on: Table1.ID Table2.ID Table1.Field1 Table2.Field1 Table1.Field1 Table1.Field2 Select* FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla We have tried to set up indexes and the query still takes 8 minutes to run. It only returns 6,000 records. -- 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]
Re: Need help constructing query ...
Hi, I have a table full of logged urls and ip addresses. The following query returns all the urls and the number of requests. How would I modify it to return unique requests based on distinct ip addresses? select url, count(*) as pageviews from table group by url order by pageviews desc How about: SELECT ip_address, url, count(*) FROM tablename GROUP BY ip_adress, url -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple query help
select value from tableName where date in (select max(date) from tableName where id = 4); But, it doesn't work with mysql 4.0. Any ideas? Does anybody had this problem before? What about: SELECT value, date FROM tablename WHERE id = 4 ORDER BY date ASC Just pick the first row. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
seeing I cannot send the whole file...
I think I have stuffed up the installation of the MySQL thing. I am trying to get it installed onto a Debian box and am not having alot of luck :o| http://www.scanners.8m.com/config.html Any help would be appreciated, Cheers Dan Clark Information Systems System Support HealthCare Otago 5046 8450 (025) 336 296 ## Attention: This e-mail is privileged and confidential. If you are not the intended recipient please delete the message and notify the sender. Any views or opinions presented are solely those of the author. ## - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
