Query Doesn't Work With Mysql4
Can someone tell me what's wrong with the mysql query below? It does work on mysql 5 but gives me an error with mysql 4.
SELECT rev_profile_indiv. *
FROM rev_profile_indiv, rev_profile_kids_indiv
WHERE 1
AND reviewing
IN ( 1, 0 )
AND active
IN ( 1 )
AND contract
IN ( 1 )
AND UNIX_TIMESTAMP( DATE( rev_profile_kids_indiv.birthdate ) ) >= UNIX_TIMESTAMP( ��-12-15' )
AND UNIX_TIMESTAMP( DATE( rev_profile_kids_indiv.birthdate ) ) <= UNIX_TIMESTAMP( ��-12-15' )
GROUP BY rev_profile_indiv.username
ORDER BY lastname
LIMIT 0 , 30;
View Complete Forum Thread with Replies
Related Forum Messages:
Function Doesnt Work
i tried write simple function: CREATE FUNCTION `getLastNumber`(some_guy INT(50), date DATE) RETURNS float BEGIN DECLARE a FLOAT; SET a = NULL; SELECT number INTO a FROM numbers p WHERE p.number_owner = some_guy AND p.date_created <= date ORDER BY p.date DESC LIMIT 1; RETURN a; END that would return last inserted number into table for some person. When some_guy has number in table, then is everything ok and it returns last number, but when some_guy does not have row in table it returns: Query OK, -1 rows affected. Pls why? and how to fix it?
View Replies !
GROUP BY Doesnt Work
i have a table that contains the folowing fields: some_table: company_number something_number price date_price_established for a certain company i need to get all the dates that prices were established. and i dont want any date_price_established to repeat themselfs. for example, for the following database: 1,1,15.5,1.1.2006 1,1,16,1.1.2006 1,1,17.5,1.1.2006 1,1,16,3.1.2006 the results will be : 1.1.2006, 3.1.2006. this is what i've tried: SELECT date_price_established FROM myDb.some_table WHERE company_number = 1 GROUP BY date_price_established";
View Replies !
Domain Name Now Doesnt Work
I have a software script I had installed on my server that works with a mysql database. It was working fine as far as pulling information from the database under my old domain name, however, I changed to a new domain name as primary on my server and now the database does not pull anything into the template. I know this is probably simple but not sure how to fix this.
View Replies !
Where Clause Doesnt Work
im creating a website with a mysql database. ive jus got started. im havin a few problems i have a product table and i execute a simple query but it doesnt seem to give me any results: heres the query: Select * from product where ProductName = 'Age of Empires III'; the above query doesnt work but this 1 does Select * from product where ProductName LIKE 'Age%';
View Replies !
Mysqldump Doesnt Work
i try to insert the followind data to mysql database mysqlimport -u root -p anaktisi2 < phpbb_db_backup.sql But this doesnt insert anything in the db.
View Replies !
Full Text Searsh Doesnt Work
I am trying to do a Full text search, but I have two problems. 1. If I enter a word that is found under multiple entries, I get no return. But it i search a word tha's only under one enetry, then it works. 2. This query works: PHP SELECT * FROM `site` WHERE MATCH (site_name,keywords) AGAINST ('mice') AND `approved`=Ƈ' , but when I add WITH QUERY EXPANSION, it doesnt work. PHP SELECT * FROM `site` WHERE MATCH (site_name,keywords) AGAINST ('mice' WITH QUERY EXPANSION) AND `approved`=Ƈ' I get this error: MySQL said: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH QUERY EXPANSION ) AND `approved` = Ƈ' LIMIT 0, 30' at
View Replies !
What Query Would Work For This?
I have two tables: say Yes and No. Yes(ID, YesEmail, EmailOwner); No(ID, NoEmail); Both YesEmail and NoEmail contain email addresses. What query can retrieve all the email addresses in Yes table that are not listed in No table? Thanks in advance.
View Replies !
Cannot Get MySQL Query To Work In .asp
I have a MySQL database and in the front-end software I am using to Run MySQL, I have created a query on the tables which runs in the front-end, similar to the MS Access query grid. I now wish to pull this query on-line using .asp, but I seem to have found out that I can only pull data from tables in MySQL? ....
View Replies !
Query Wont Work
PHP Code: if(mysql_query("UPDATE users SET referer_score=referer_score+1 WHERE userID='$frommysql_referer'")) { die(mysql_affected_rows()); im trying to figure out why refer_score isnt being iincremented as im expecting it to. i know that the value of $frommysql_referer is the right value that im expecting it to be. just it wont increment referer_score. and mysql_affected_rows() wont print anythnig to the screen
View Replies !
What Do I Need To Do To Make This Query Work?
I must've been working too long hours this week, as my flow of ideas has come to a complete stop. Its really quite simple: 2 tables - bookings , invoices These two counts gives me precisely what I want PHP $unpaid_bookings = mysql_result(mysql_query("SELECT COUNT(id) FROM bookings WHERE invoice_id = 0 AND client_id = $id AND status != Ɔ' AND finish <= '$time'"),0);$total_bookings = mysql_result(mysql_query("SELECT COUNT(id) FROM bookings WHERE client_id = $id AND status != Ɔ' AND finish <= '$time'"),0); so I present this in a ratio, $unpaid_bookings / $total_bookings... awesome now, when I try and flip the coin and show unpaid invoices over total invoices, it all falls apart once I have more than 1 booking per invoice. First i'll explain the table structure. Each booking has a column `invoice_id`, which is assigned to the primary `id` of the invoice table. So one invoice might be associated with x bookings. PHP $unpaid_invoices = @mysql_result(mysql_query("SELECT COUNT(i.id) FROM invoices i, bookings b WHERE i.id = b.invoice_id AND b.client_id = $id AND i.is_paid = Ɔ'"),0);$total_invoices = @mysql_result(mysql_query("SELECT COUNT(i.id) FROM invoices i, bookings b WHERE i.id = b.invoice_id AND b.client_id = $id"),0); Again so it works fine when 1 invoice is associated to 1 booking, but when 1 invoice is associated with say 2 bookings, the invoice count will return two instead of one. The worst part is, I know exactly why (because there are two bookings for the invoice), I just can't get around it! I've tried all sorts of joins, group by's, and i'm not having any luck. I'm trying not to be weak and add the client_id into the invoice table,
View Replies !
Query Does Not Work On Some Server Instances
query: Select * From Students Where C_Name = "XXXXXX" WHen I run this query on 2 of my computers, the result returns no rows. When I run this query on a remote machine hosted by 1and1, with a copy of the same database, the query returns the correct row. The syntax is identical.....
View Replies !
Cant Work Out Correct Select Query For ..
mysql> select * from parent_cat; +-------+----------+----------+ | catid | pname | psection | +-------+----------+----------+ | 1 | Trainers | Men | | 2 | Bags | Woman | | 4 | Boots | Woman | | 5 | Fragance | Men | +-------+----------+----------+ 4 rows in set (0.00 sec) mysql> select * from categories;; +-------+---------------------------+---------+------+ | catid | catname | section | pid | +-------+---------------------------+---------+------+ | 1 | Nile | Men | 1 | | 2 | Bags | Woman | 2 | | 4 | Boots | Woman | 4 | | 6 | Air Force I & II Trainers | Men | 1 | | 7 | Adidas Trainers | Men | 1 | | 8 | Timberland Trainers | Men | 1 | | 9 | Lacoste Trainers | Men | 1 | | 10 | Gucci & Hogen Trainers | Men | 1 | | 11 | D&G Trainers | Men | 1 | | 12 | Prada Trainers | Men | 1 | | 13 | Chanel Trainers | Men | 1 | +-------+---------------------------+---------+------+ and the following query: $pid = $_GET['catid']; $query = "SELECT categories.catid, categories.catname, categories.pid FROM categories, parent_cat WHERE categories.section = 'Men' AND parent_cat.catid=categories.pid AND categories.pid = '$pid'"; At the moment the query only returns all categories.catname when my categories.catid = 1, but returns nothing if it equals something else?
View Replies !
Degrading Sub Select Query To Work In MySQL 4
I have the following query working as intended in MySQL 5. However, I had not realised that the target server is running 4.0.2 and it's not possible to upgrade. SELECT records.player_id, records.record_id, records.game_id, records.updated, game_modes.mode_title, records.record_time, players.firstname, players.lastname, players.nickname, players.country, teams.team_title, classes.class_title, tracks.track_title, games.game_title, games.version, record_types.type_title FROM games, records, classes, teams, tracks, record_types, players, game_modes WHERE records.record_time = ( SELECT MIN(record_time) FROM records WHERE records.player_id = players.player_id AND records.game_id = 1 AND records.mode_id = 1 AND records.track_id = 1 AND records.class_id = 5 AND records.type_id = 1 GROUP BY records.player_id) AND records.track_id=tracks.track_id AND record...................
View Replies !
Query Which Gets Rows Based On A Radius, Lon And Lat Doesn't Work?
I have found this query which gets rows based on a radius. I need this for zip codes based on lon and lat's. $sql2 = "SELECT * FROM table as z WHERE (SQRT( (69.1 * (".$userLat." - z.lat)) * (69.1 * (".$userLat." - z.lat)) + (53.0 *(".$userLong." - z.lon)) * (53.0 *(".$userLong." - z.lon))) <= ".$userRadius." )"; return $sql2; $res = mysql_query($sql2, $connDB) or die(mysql_error()); $row = mysql_fetch_assoc($res); based on a test zip code gives a result like SELECT * FROM table as z WHERE (SQRT( (69.1 * (52.399834 - z.lat)) * (69.1 * (52.399834 - z.lat)) + (53.0 *(4.840762 - z.lon)) * (53.0 *(4.840762 - z.lon))) <= 100 ) the lat and lon of the test zip code are right. As you can see z.lat and z.lon don't get any value. And these would be every lat and lon in table. In my db table lon and lat are decimal(10,6) type with a default value of 0.000000
View Replies !
WHERE Doesnt Equal
I am trying to select something my DB where a clause does not equal something but for some reason I cant seem to make it work. Here is what I have tried. PHP Code: SELECT * FROM golf_groups WHERE name!="something" but this doesnt seem to work. i had done something like this before and thought that I had done it this way, but since this does not work,
View Replies !
It Doesnt Use My Index!
`player_1` mediumint(7) unsigned NOT NULL default Ɔ' `player_2` mediumint(7) unsigned NOT NULL default Ɔ' reffering to users' id-number in another table. I have two indexes: KEY `one` (`player_1`), KEY `two` (`player_2`) The cardinality of them is 6000 and 7000. My primary-key has cardinality 150 000. When i run this query: EXPLAIN SELECT * FROM `gamebase` WHERE player_1 =1 It uses the "one"-key (doh!). But now, that I run my next query (using OR), it tells me that one and two are possible keys, but it uses neither, but loops through my entire table "Using where". EXPLAIN SELECT * FROM `gamebase` WHERE player_1 =1 OR player_2 =1
View Replies !
Reformatting Email Address To Work On SELECT Query
This is probably basic, but so far I cant find any articles about this. I want to "SELECT * FROM dbase WHERE emailaddress = $email;" $email is a feed from PHP, and will contain a string like "some.one@someplace.com" Apparently with $email as a normal string, it gives an error saying: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '@someplace.com)' at line 1" I reckon it's the dots and the @s that terminate the syntax and making it not work. How can i get out of this problem? Is there any function I can call to solve this? Or probably a little bit of more programming?
View Replies !
Its Returning A Value That Doesnt Exist
Okay here is the deal. I am running a query to find out if there is an ID in my table that has a case number that matches the one being submitted. If there isnt once its should step into an if statement that would submit a number of records. Otherwise it should jump to the else statement. I the problem comes from: the query keeps returning "Record id#2" but there is no record matching the case number being submited from the form. if( isset($_POST['Submit'])){ //Begin If statement logic $resultcheck = mysql_query("SELECT * FROM submissions WHERE wfmcase = " . $_POST['wfmc']) or die(mysql_error()); //Query string to test if wfmcase was already submitted echo "Sucess, form varibles have been passed.<br />"; echo "Resultcheck query results: $resultcheck <br />"; if ($resultcheck == ""){ //Checks to see if the query was empty. echo "There are no duplicate case escalations pending. <br />"; I added the echo statement so that i could see how far it was getting through the script. It dies after printing the varible $resultcheck which gives "Record id#2". It never enters the 2nd if statement because for what ever reason the query has the "record #id2" in it... What is causing this? I have verfied that there is no case that matches the one coming from the form and i get the same error.
View Replies !
MySQL4.1 And PHP
I had to reformat my drive, so I got and installed Apache2, and installed PHP 4.3.11 and conf. both and all is fine... Then I install MySQL 4.1 and MySQL Administrator. Then PHP pages are PAGE NOT FOUND, and I can not add users in the MySQL Administrator, is keeps saying can not update user information.... Or does someone have a older version of MySQL 4.1.... The last one I had that was MySQL 4.1 but NEVER gave me a problem.... Until I dumped the old copy thinking I would get a freash copy.
View Replies !
Mysql4
Why does the cpu usage of mysqld stay at 98-99% when there is like no process running in showprocess very little traffic as well it used to fine; taking only 3% max cpu, but now it stays at near 100% constantly, why does this happen? I asked around many forums, but no one ever knows the answer to this similiar type of problems no process; light traffic; extreme high cpu usage dedicated dual xeon server with 1G Ram on freeBSD
View Replies !
MySQL4 And Windows XP
I am having problems running mysql 4 on windows xp. Mysql 3 works fine. However version 4 won't run. Even if I start the server with the admin panel it shuts down again after a couple of seconds. This has only been a problem since I wiped my hard drive and did a clean install of everything.
View Replies !
Back To MySQL4
I have built a website using PHP5 and MySQL5 only to find that I cannot find a host that uses MySQL 5 yet… I have now changed the site to access the database via PDO. Problem is, PDO does not currently work with MySQL5 (correct? If anyone could tell me how to get PDO to work with MySQL5 this would be brilliant!) I therefore need to load MySQL4 onto a machine that is already running MySQL5. Do I need to uninstall MySQL5 first?
View Replies !
Raid And MySQL4.1
I had a forum and all was fine I wanted to install a raid card, so I backed up the forum files and did a database backup with MySQL Administrator... Installed the card and got Windows XP up and running, reinstalled everything, and restores the forums and database backup. But now the forums are all messed up..... When you load the forum, it only loads half the page, then you refreash and it will load more sometimes less, but it is never the same..... Somethmes it will put the overall_header.tpl at the top and half if it at the botton.... It is werd.... Is there a problem with switching from a non-raid system over to a raid system with the MySQL and the way it handles the databases?
View Replies !
Standby Db In Mysql4
I have mysql 4 db running in RHEL4. We are planning to build a standby db for this in another server. Do mysql 4 support standby? If so, is there any documentation to implement standby db!!
View Replies !
Installing Mysql4.0.23
I need start mysql4.0.23 in Linux RedHat7.2, I was install mysql4.0.23 server successfull, but it dont start. I need configure it for my university project.
View Replies !
Convert Mysql5 Db To Mysql4
there are some syntax in mysql 5 thats not compatibe with mysql 4....so when i export a mysql 5 db is there an option to make it compatible? if i remember correctly, there was athe passowrd problem. is it safe to update from version 4 to version 5??? whats the tricky points??
View Replies !
Exporting From Mysql5 To Mysql4
i'm trying to export my database from my test machine (mysql5) to my production server (mysql4), the first problem is something related (i think) to differences in the sintax, to solve this i used phpmyadmin to generate the dump and set an option 'compatibility with sql export' to mysql40 or mysql323, that works but now characters that should be accented vowels á,é,í,ó,ú and ñ look very strange and i can't figure it out how to make them look normal again.
View Replies !
Information On Differences Between MySQL4 And 5
I'm trying to track down information that will tell me all the differences between MySQL4 and MySQL5, that includes things like depreciated commands/functions/etc and new commands/functions/etc. And any other changes that may affect a move from everything that runs on a MySQL4 database that will need to be re-written to work fine on MySQL5,
View Replies !
MySQL4 -> MySQL5 Problem
This query works in MySQL4: SELECT news.news_id, news.news_headline, news.news_content, news.news_date AS true_date, DATE_FORMAT(news_date, '%H:%i, %D %M %Y') AS news_date, news.news_author, news.source, news.sourceurl, news.headlineimage_id, users.forum_profile_url, news_images.headlineimage, news_images.width, news_images.height, news_images.alttext, news_images.companyurl, COUNT( news_comments.comments_id ) AS news_count FROM news JOIN users, news_images ON news.news_author = users.username AND news.headlineimage_id = news_images.headlineimage_id LEFT OUTER JOIN news_comments ON news.news_id = news_comments.news_id WHERE news_date >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY news.news_id ORDER BY true_date DESC; When I run this same query on the same database on MySQL5 it will not run and bails with: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON news.news_author = users.username AND news.headlineimage_id = news_images.hea' at line 1
View Replies !
SQL Fails On MySQL4 To MySQL5 Upgrade
I have some SQL that works perfectly in mysql4, but fails when it's run in mysql5, on exactly the same schema. I'm not an expert and can't work out what's wrong. Can anyone spot what's going on? The error message is Unknown column 'head.id' in 'on clause' The SQL is SELECT head.*, types.*, count(review.headphoneId) as reviews FROM headphone head, headphonetypes types LEFT JOIN review ON review.headphoneId = head.id WHERE types.headphoneTypeId = head.typeId GROUP BY head.id ORDER BY head.model .
View Replies !
MySQL4 Installed But Mysqladmin Prevents Me From Making Changes...
I've been using MySQL 3.23 for a number of months - I've got familiar with the everyday stuff (read/write/sort) but access or grant management still remains foreign. After a recent hack attempt on my server, I decided to be safe and re-install everything (its a LAMPhp server on a SuSE 8.1 box). Everything looks fine with the exception of my MySQL installation. I am installing it via an rpm - I've tried using both version 3 and version 4 but both give me the same problem. When I install the server and client, and then execute the following on the server # /usr/bin/mysqladmin -u root -h 192.168.1.3 password elephant I get the message error: 'Host 'stanley.mydomain.com' is not allowed to connect to this MySQL server. I have two network cards on the box (192.168.1.3 ad 192.168.1.2). I've tried using each of their IP addresses, and localhost, but none seem to work. I had a previous working version of MySQL Control Center on my WinMe box which I have also tried to use, but it has started to fail during bot with the message "Mysqlcc has caused an error in QT-MT312.DLL. Mysqlcc will now close." This error repeats even after a reboot or re-install. Can someone advise me as to how I can proceed as this is the only thing that is holding me up from continuing with my project work... Please share via newsgroup for all to learn as I note there seems to be frequent posts with regards to access problems... I'd prefer to stick with latest production version 4 (though I have no problems rolling back to 3.23 if need be).
View Replies !
Remove Duplicate Rows - MySQL4 JOIN
I am looking to remove duplicate rows from a table. I have limited knowledge of MySQL - so please bear with me. The following code correctly displays the duplicate rows: SELECT firmname, address1, custom_2, MIN( selector ) AS min_sel, count( * ) AS issimilar FROM my_table GROUP BY firmname, address1, custom_2 HAVING issimilar > 1; Now I try to run an inner join to show the rows, so that I can then delete them (delete is not included yet) select bad_rows.* from my_table as bad_rows inner join ( SELECT firmname,address1,custom_2,MIN(selector) AS min_sel,count(*) AS issimilar FROM my_table GROUP BY firmname,address1,custom_2 HAVING issimilar > 1 ) as good_rows on good_rows.firmname = bad_rows.firmname AND good_rows.address1 = bad_rows.address1 AND good_rows.custom_2 = bad_rows.custom_2 AND good_rows.min_sel <> bad_rows.selector; The problem is that I can't use a select inside a JOIN in MySQL4. I don't know enough about MySQL to rewite the above so that it will work in MySQL4 - I know its something like: SELECT t1.firmname, t2.firmname, count(*) AS issimilar FROM my_table AS t1 INNER JOIN pmd_listings AS t2 ON t1.firmname = t2.firmname GROUP BY t1.firmname, t2.firmname HAVING issimilar > 0 ORDER BY issimilar DESC; Hoping someone can help. Also have a question on comparing data from other tables - but I need the above to work first.
View Replies !
Errors Get When Try To Migrate Form Mysql4 To Mysql5 Using Mysql Migration Tool
I am running mysql server 4.0. I have installed the mysql5.0 in the same server. When I try to migrate form mysql4 to mysql5 in the same server I get java heap space error. I was looking the google the change the java heap space. But did not get any thing. I am running with win2k and mysql4 and mysql5 with JVM installed with 512 MB Ram. There for I tried some other way. Even I installed my sql 5 in some other server in LAN whitch has more RAM and then try to migrate form mysql4 to mysql5 though the LAN I get some strange error saying Code:
View Replies !
SELECT Doesnt Select Anything WHERE Doule
I am running the next script SELECT * FROM flcc_Tickets WHERE 'Match No'=1; but dont get any results although there are lots of resords with 'Match No'=1 in a table. 'Match No' is DOUBLE . if run it with 'Match No'=0 i get all the table.
View Replies !
Bug Work
I've got this bug http://bugs.mysql.com/bug.php?id=27861. I know they've made a fix and it's going to come out in the next version but is there any way I can fix this myself?
View Replies !
How Does Sql Work Exactly?
Well what exactly does sql do when you create a table for like a login (user) What does the tables and stuff do. Do they just create like a library to store that users information so they can log back in to site or w/e....
View Replies !
Subselect Does Not Work
i am trying to remove values from a list menu if the join table d= oesnt have keys when a key is selected for instance: locations locationID locations_join locationID shotlistID SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN lo= cations_join lj ON l.locationID =3D lj.locationID WHERE l.locationID NOT IN= (select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER B= Y l.location ASC. so when shotlistID is selected all the keys from the locations_join joined = to the shotlistID would be remove from the locations list please help, i'm = trying to do this in one query saving from getting all the keys into an arr= ay then checking if the values arent in the array when generating the list.
View Replies !
Date_add Does Not Work
I'm using mySQL 5.0, with a database of UNIX timestamps. Can someone explain this to me? This query works great: SELECT from_unixtime( 1132277574 ) But this one breaks: SELECT date_add(from_unixtime( 1132277574 ), interval 1 week) Telling me, oh-so-helpfully, that the error is near "'week)'". (It doesn't work when I use "1 weeks" either.)Does the from_unixtime simply not work with the date_add function, or is there some arcanity to this syntax that I'm missing?
View Replies !
Getting Log File To Work
I am putting log = "C:Program FilesMySQLMySQL Server 5.0sql.txt" into my .ini files and mysql wont start. I cant work out the syntax to tell mysql to ouput all the queries to a log file.
View Replies !
How Does Synchronizing Work?
I have a question; currently we want to run our central database from two different locations as opposed to one currently. I am trying to figure out how to synchronize the two locations. My question is: if two (or more) locations create new clients locally, each location will thus create a new client-numbers on their own active operational database. This opens the possibility that each locations will create identical client-numbers; of course being different clients altogether.
View Replies !
Mysql_connect Does Not Work
I have searched through the forum and tried the suggestions that have been made for this issue but I am about ready to give up! I have installed Apache 2.0.52, PHP 5.0.3, and MySQL 4.0.23 on Windows XP. I uncommented extension=php_mysql.dll in the php.ini file and copied libmysql.dll into both C:WindowsSystem32 and C:WindowsSystem. My phpinfo.php test works fine so I know PHP and Apache are working together. I do not see any MySQL references on that entire page. From previous posts I know I should. Needless to say, mysql_connect(...) does not work, it gives an undefined function error. I apologize if this should have been posted in the PHP forum instead. If it is a PHP issue, I will repost this there.
View Replies !
Work In MySql
This query works fine in SQL Server, will it work in MySql? SELECT a.total, b.browsertotal, b.browser, (b.browsertotal / a.total) * 100 AS browserpercent FROM (SELECT Count(id) As total FROM MyTable) a, (SELECT count(id) as browsertotal, browser FROM MyTable GROUP BY browser) b I dont have MySQL and I am trying to help someone out.
View Replies !
Getting PHP & MySQL To Work Together
I am trying to get PHP to write into MySQL database. Right now it does not seem that it is doing such, MySQL doesnt appear when I bring up the PHPInfo() page. I have removed semi-colons before the Mysql.dll extension in php.ini, and I have made sure that PHP is in the windows PATH. What else do I need to do to get this stuff running?
View Replies !
Sql Statement Doesn't Work
When i was using a previous version of mysql (3.something) this sql statement worked fine. strSQL = "SELECT COUNT(*) as COUNTER FROM prayerrequest where dateexpires <= '" & sDate & "'" sDate equals todays date. But after installing the new version (4.1) of mysql this statement no longer works. I went as far as stepping through all dates and comparing them and it wouldn't work. "1/11/2005" from the database did not equal sDate which was "1/11/2005".. The only way I could get it to work was to use the asp/vb function FormatDateTime on dateexpires and then compare it with sDate. Does mysql not return a different var type? Is there anyway I can convert dateexpires in the above sql statement so I can compare it with sDate?
View Replies !
Cannot Get 3 Where Statement To Work
I am having a problem with MySQL 5. I cannot get 3 where statements to work with my database. Any two of the three work fine and return records as they are supposed to but when I add a third clause (all clauses are AND) I get absolutely no records at all and no error message. I don't understand what could be causing this and can use any help offered. SELECT this, that, otherthing FROM myTable WHERE something = "this" AND somethingelse = "that" AND yetsomethingelse > 0 ORDER BY RAND() LIMIT 3;
View Replies !
Join Doesn't Seem Work
I have some issues with a simple query, but I just can't figure out why the query doesn't return expected results. Here is the situation: I have a table look like this: TRANSACTION(UNIQUE_ID(PK), CUSTOMER_ID, EMP_ID, TRANS_TYPE, TRANS_DATE, POSTED_DATE, AMOUNT_CHARGED, AMOUNT_PAID, TRANS_LOC, TRANS_ID_CHARGE, TRANS_ID_PAYMENT) Some rules for the data in the table: - The TRANS_TYPE indicates if it is a charge or payment(C charge, P for payment). For a payment, there is a unique TRANS_ID_PAYMENT, and for same transaction, the charge record holds same TRANS ID in the TRANS_ID_CHARGE field. - The TRANS_LOC is for location where the charge happened. But for a payment record, this field is null. which means, payment records and charge records can't be linked together through location. - TRANS_DATE is the date the transaction happened. For a payment record, this field is also null. - A charge record and a payment record for same transaction usually have different POSTED_DATE since they are posted most likely on different days. - AMOUNT_PAID could be less than AMOUNT_CHARGED for same transaction since customers can have an installment payment plan. Here is my query: SELECT DISTINCT A.CUSTOMER_ID, A.AMOUNT_PAID FROM TRANSACTION A WHERE A.TRANS_TYPE = 'C' AND A.TRANS_DATE BETWEEN '2007-01-01' AND '2007-09-30 AND A.LOC_ID = 1 UNION ALL SELECT DISTINCT A.CUSTOMER_ID, A.AMOUNT_PAID FROM TRANSACTION A, TRANSACTION B WHERE A.TRANS_TYPE = 'P' AND B.TRANS_TYPE = 'C' AND B.TRANS_DATE BETWEEN '2007-01-01' AND '2007-09-30 AND B.LOC_ID = 1 AND A.CUSTMER_ID = B.CUSTOMER_ID AND A.EMP_ID = B.EMP_ID AND A.TRANS_ID_PAYMENT = B.TRANS_ID_CHARGE The purpose of this query is to pair all the charge and payment records for the same transaction that happened between a certain period of time. But the results show that there are some dangling payment records that are not linked to any charge records.
View Replies !
|
TRACKER
