Query Works With One Record
This query works great if there is one matching record in the Albums table. When there's more than one matching record it returns nothing.
Code:
SELECT Artists.Artist, Albums.Album_Name
FROM Artists, Albums
WHERE SOUNDEX('colname') = SOUNDEX(Artists.Artist) AND Albums.Artist_ID=Artists.Id
View Complete Forum Thread with Replies
Related Forum Messages:
Query Only Works Once
I'm using jsp with mysql and am querying the database for data. Everything works fine on the first try. If I click back on my browser and hit submit again on my form, I get no data. I've tried removing the close() commands but that doesn't seem to work.
View Replies !
SQL Query Works Only In Win32!
I use MySQL from my php page and from phpMyAdmin. In Windows Server, following query works fine. In Linux Server instead it returns error. The SQL Query is: SELECT DISTINCT a.id, a.container, b.container FROM folder AS a JOIN folder AS b ON a.container=b.id The PHP error is: You have an error in your SQL syntax near 'ON a.container=b.id' at line 1 So, I ask: If in Windows this query work fine, where is the error in the Linux version?
View Replies !
JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of student numbers for each school from two tables School,Student. select sc.name, Sum(if(student.SNO is not null, 1, 0)) nmbr, from school sc left outer join student on sc.school_id = student.school_id group by sc.school_id this runs just 0.30 sec with Mysql3.23 but 30 Sec with Mysql 4.0.13.
View Replies !
GROUP_CONCAT Problem In 5.0.24 (query Works In 5.0.18)
So on my production server they are running MySQL - 5.0.24-standard. Locally I'm only running 5.0.18 but didn't figure it would be much of a problem, until I noticed that my "results" from certain searches were different with the exact same data on each MySQL version. The query is this:
View Replies !
Query Help. This Works, But I Don't Think It Is Good Practice
comics: id date location comments: id comicid datetime header comment author Every comic can have multiple comments. Each comic will have a comment, but there is a period of time where the comic is posted but the first comment is not yet submitted. So for argument's sake we can't assume there is any comment at all for a given comic. I want the all the comic ids and dates before a certain date along with the most first comment entered relating to that comic. select comics.date , comics.id , comments.header from comics left outer join comments on comments.comicid = comics.id where (comics.date>=񟭇-03-01') group by comics.id I never defined WHICH header should be returned when many are grouped together. I just know that mysql will return the first one in this circumstance. I seem to recall reading somewhere that different databases handle this sort of thing differently, with some returning a null in that field? I keep trying to forumate a useful subquery but keep drawing blanks just as I think I have it figured out in my head. Like I forget how the string starts by the time I mentally formulate the end....
View Replies !
Query- Get Every X Record?
My app loads my database every 30 seconds with weather data from several different weather stations (2880 records per station per day). I need to extract every 10th record for a station (288 records per day) to build a PHP array for a graph.
View Replies !
Query Help: Get Oldest Record W/ Grouping?
I'm building a system that allows a maximum of two records per unit. When a new record is created for that unit, if there are already two records the oldest record is deleted. Thus, I'm trying to build a query that gets the oldest record for each unit, but also returns the number of records per unit. The trouble I'm having is in making sure the record I'm getting is the oldest one! I thought I could use the Order By clause to get the oldest record, but it doesn't work. In a database table where there are a total of four records: recordID unitID recordFile recordTime 10 1 someFile1.txt 1179778828 11 3 someFile5.txt 1179778828 12 3 someFile5.txt 1179778990 13 1 someFile5.txt 1179778956 The following query: SELECT unitID, COUNT(recordID) AS numRecords, recordID AS oldestID, recordFile, recordTime FROM records GROUP BY unitID ORDER BY recordTime DESC Returns the following result: unitID numRecords oldestID recordFile recordTime 3 2 12 someFile5.txt 1179778990 1 2 13 someFile5.txt 1179778956 These are the newest records, not the oldest. I'm not even sure that is a reliable case. I get the exact same result using the same query but with ASC instead of DESC!
View Replies !
Return Info On Last Record With One Query
Lets call the table: comics Lets call the fields I am looking for: ID filename I want to get the maximum ID from the table and the filename field corresponding to that maximum id. I can do it easy in two queries: select max(ID) as max_id from comics then in php send select filename from comics where ID=$id (where $id is the number returned from the first query) I would like to do ONE query like: Code: select max(ID) as max_id, and filename from comics where ID=max_id
View Replies !
Insert A Record Using MySQL Query Browser
I know I can probably type in the actual query, but I was wondering if there was a means of inserting a record without typing "insert into table_name (blah, blah2) values (blah, blah)". I'm looking for a method as easy as typing into an Excel spreadsheet or Access DB. (I know I'm gonna get bashed for mentioning Microsoft products.)
View Replies !
Query To Display A Record By Recent Time
Many users uploaded their files to my mysql table through php script, my table having the details of uploading time, file name, & uploader name. i need to find the recent uploaded file for all uploaders. i tried with this query, select file_name,uploded_by,MAX(date_time) from upload group by uploded_by; it is giving the recent time, but it is not giving the latest file, it is showing first uploaded file.
View Replies !
How To Write Query To Select The Max(version) For Each Unique File_name Record?
I am a MySQL newbie trying to write a query that selects file_name records possessing the highest numbered version for that unique file_name. I show sample data and two trial queries below. Logically I want to use max(version) as a constraint in a Where Clause. However, the max() function is not allowed directly in a where clause. I have contemplated a second table to track the max version for each file name. I would like to structure the data in an efficient manner for query performance when the data set grows to many thousands of unique file_name records with many hundreds of versions each........
View Replies !
Query To Retrieve 5 Records Immediately Before And After A Particular Record In Sorted Manner
Just consider a simple table with one integer column (however numbers are not inserted in sorted manner and some of them may be missing). Given a particular record, say 32, I would like to retrieve 5 records immediately before and after this record in ascending manner of sort. So, in this case I should get 25, 27, 28, 30, 31, 32, 33, 34, 37, 38, 39 (I did not have 26, 29 and 35 in the table) Why do I go about writing such query?
View Replies !
Works
I have this and it works just fine but I cant help but think there is a much better way of accomplishing the same thing in one sweep verses 6 PHP Code: mysql_query("update residential set bed='1' where bed like 'ONE%'"); mysql_query("update residential set bed='2' where bed like 'TWO%'"); mysql_query("update residential set bed='3' where bed like 'THREE%'"); mysql_query("update residential set bed='4' where bed like 'FOUR%'"); mysql_query("update residential set bed='5' where bed like 'FIVE%'"); mysql_query("update residential set bed='6' where bed like 'SIX%'");
View Replies !
UNION - Works In 4.0.16, Not In 4.1.8
I have a query that works in 4.0.16 on my web host, but not 4.1.8 on my dev. box. Web host is upgrading in 10 days. Notes: Server language is ColdFusion gamedate is type DATE SELECT DISTINCT gamedate FROM schedule WHERE gamedate >= now() UNION ( (SELECT DISTINCT gamedate FROM schedule WHERE gamedate < now() ORDER BY gamedate desc LIMIT 1) ORDER BY gamedate asc LIMIT 3 The goal is to select the most recent gamedate prior to "today" and the next two game dates including "today".
View Replies !
SSN Changes In Database But Still Works As Key?
I have a ssn field in a table but somthing happens to the data. I use an update to enter the data and set the value to 123-12-1234 for a test but when I look in the table or I get the record from a record set it come out like -1123. Why is this? But when I run a query and ask it to return all records which have a ssn of 123-12-1234 it returns the correct record. If this is correct then how can I display the ssn number in a java text field.
View Replies !
Works In MYSQL 4.1 But Not In 5.0
MySQL 1054 - Unknown COLUMN 'p.manufacturers_id' IN 'on clause' SELECT count(*) as total FROM products p, categories c, products_to_categories p2c LEFT JOIN manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE c.categories_status=1 AND p.products_id = p2c.products_id AND c.categories_id = p2c.categories_id AND p.products_status = Ƈ' AND p.products_id = pd.products_id AND pd.language_id = Ƈ' Here is the query in PHP PHP "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, p.manufacturers_id, m.manufacturers_name from " . TABLE_PRODUCTS . " p, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where c.categories_status=1 and p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and p.products_status = Ƈ' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";
View Replies !
How Exactly The LIKE Keyword Works?
I have a table named Inventory and have 3 items in it containing the work w069. I want to run a query to return the three items. My query: Select * from Inventory where Description Like 'w069' It doesn't seem to work, returning zero row.
View Replies !
Automatically Record Date Of Record Entry
I have my database table set up and I have an HTML form that is PHP driven that will add the information entered into the form into to my database table. I have a local buy-sell-trade Website. The way it has worked is that people fill out a form and the results emailed to me. I then take the information and enter it into a Web page. I only want the ads to be displayed for 30 days. I keep the ad for a total of 6 weeks (displays for 30 days and sits in limbo for 2 weeks afterwords) and if not renewed within that 2 week limbo period - I delete it. Entering all the ads and keeping up with the dates manually has become a burden. I've only recently began looking into databases. My hosting company provides me with phpmyadmin and mysql 5.0. I'm new to all of this but I have managed to set up a database table and a HTML form that is PHP driven that allows ads to be automatically added to the database table. There is a lot I need to do to make this ideal, but one step at a time. First, I need to know the date (March 02, 2006) the ad was created or added to the database table. I know that I need to add some piece of code to my php form to record this information, but what code and where do I put it? I know I will need to create an extra field in my database table to house the date - I can handle this. I've read the date and time information here http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html but there's a lot there and I don't know which is right for my needs. Plus, it doesn't tell me how or where to insert it into my php form (or does it?).
View Replies !
SHOW DATABASES Works In 4.0.14
I've been trying to solve this problem for a long time. Only thing I did when upgrading from 3.25 to 4.0.14 I took the default my.cnf. I did not convert the privilege tables with mysql_fix_privilege_tables. Suddenly, the mysql started to show all databases to all users (even though the user had access denied and could not do show tables on them). So I started to find help and tried the mysql_fix_privilege_tables. It didn't work properly (more than just access denied and errors, that are ok) so I did the changes manually conforming to source code of mysql_fix_privilege tables. PhpMyAdmin stopped its warning about privilege tables not conforming to current mysql version. Everything seemed ok except that SHOW DATABASES still worked. The users naturally all have Show_db_priv="N" in the tables. Also trying "safe_show_database", "skip_show_database" in my.cnf and "--skip-show-database" witch mysqld didn't help.
View Replies !
Exporting MS Works For MySQL
I have a MS Works database and I can't find anything useful on the PHP site for working with this type of database. I did a quick search and it seems that it can be exported for OoenOffice.org and other spreadsheet database programs but none of them give a method. Mostly selling books and MS Access. If there is a way to access the .MWD or .DBF Microsoft works files with PHP that would be great. If that's not possible, preferably not, I'd rther switch to MySQl easily if possible. I'm still looking but it's not looking good.
View Replies !
Mysql.exe Sort Of Works
I have just installed MySQL (not an easy thing to do). I also have installed PHP. I use Dreamweaver8 for 2 static sites, and eventually will figure out how to use that with PHP/MySQL. Everything seems to work fine, and I'm working my way through Janet Valade's book, "PHP and MySQL for Dummies." Everything I have tried in the book works perfectly except for the mysql.exe command. I am in the correct folder and type mysql.exe -u root -p and am prompted for my password. When I enter my password the cursor moves to the next line and just sits there. Nothing happens. Fortunately, the window is not frozen and I can just close it.
View Replies !
MySQL Delete: Works In Php 5 But Not Php 4?
I've developed a small login system using Xampp on my local machine. Everything is running great except when the admin tries to delete something. Apparently I have the wrong syntax for my deletion in mysql. Here's my original delete statement (from mysql5): DELETE at.* FROM access_table AS at WHERE at.client_id=2 And here's the error I get from mysql4 (I ran the command in phpmyadmin console): #1066 - Not unique table/alias: 'at' So I removed the aliases and re wrote my command as such: DELETE * FROM access_table WHERE at.client_id=2 And got the following error from php4: #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 '* FROM access_table WHERE at.client_id=2' at line 1
View Replies !
Count(id) Works Great But How To Add Another Field?
SELECT c.categorytype, c.name, COUNT(id) FROM categorytype AS c LEFT JOIN photo AS p ON p.categorytype = c.categorytype GROUP BY c.name but I am wanting to add thumbnail from the photo table. I have tried another select and inner join but get error. What is the correct way of doing this? I thought of adding a where clause but that is already the same as the current left join.
View Replies !
V. 4.1.7 + Php4 Works, + Php5 Problems
I am running: Mac OS X 10.3.7 apache 2.0.52 MySQL 4.1.7 php 4.9.3 & php 5.0.3 I have an html form that "POST"s data to a db. I have a "GET" script to repopulate form for editing. Everything works fine with php 4.9.3. With php5.0.3 the data is "POST"ed just fine (I used a different GUI app to check) When I try to "GET" said data, form is returned empty? I have all error reporting turned on = nothing. I have all backwards-compatibility directives in php.ini config file turned on.
View Replies !
Two Different Websites Connected To One Database But Only One Works...
I have been recently having problems transferring my website from one domain name to another. The first domain name was used whilst at uni, but the second is my own perosnal website. One the original website, the MySQL database allows the user to login and register new users without any problems. However, the second website does not allow me to do this. I would consider myself a newbie when its comes to MySQL so I was wondering if the community could help me out on this one. Both websites are hosted by the same company, and both websites use the same database but my limited knowledge cannot solve this problem. Are there certain permissions I need to set or something? Can anyone help me out on this one?
View Replies !
Page Times Out But Works After Refresh
I have been developing a page that displays survey results data. There are many SQL queries but has worked so far. Recently when I click the link to the page the page times out but if I refresh it displays the data correctly. Are the results of a query stored so the refresh of the page works quicker?
View Replies !
V. 5.0.18, NO_ZERO_IN_DATE, NO_ZERO_DATE Works With That Version?
this question may seem a little odd but I'm working with version 5.0.18 (I know its outdated but I must work with it) and stumbled across some behaviour I cannot explain. I created and emtpy table and a colum with a Date type, not null constraint and a default value with 0000-00-00. The problem is as follows, while trying to add a column I received an error: "ERROR 1067 (42000): Invalid default value for 'mycolumn'. After a little research I found that someone set the sql_mode NO_ZERO_DATE, NO_ZERO_IN_DATE. I was quite surprised because everyone was stating that the two flags were first added in version 5.0.20 and not earlier. How can that be?
View Replies !
My MySQL Back-end Works Very Slow
I have installed my MySQL Server in a Window XP platform running in a Pentium4 machine. When I access the database on the machine, it is working in a normal speed. But when I access the database from a client machine (still running in windows platform), it runs very slow.
View Replies !
Delete Using Multiple Table: Works On 4.1.20, But Not On 4.0.16
I've a simple delete query delete from joingrp as J using joingrp as J,msggrp as M where J.msgid=M.msgid and M.parent!=0 When I tried it on phpMyadmin at ixweb, having mysql-4.1.20, it works as desired. But when I tried it on phpMyadmin at iPowerweb, having 4.0.16, it says #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 'as J using joingrp as J,msggrp as M where J.msgid=M.msgid Can I solve this without using any server-side loops?
View Replies !
Can't Update Multiple Rows Via PHP, But Works In PhpMyAdmin
My php script will update 1 row fine, but fails on 2. However, the exact same query works when pasted into phpMyAdmin. THIS QUERY WORKS BOTH IN MY PHP AND PHPMYADMIN: $sql = "update table set color='red' where id='1';"; $sql_do = mysql_query($sql); THIS QUERY WORKS ONLY IN PHPMYADMIN: $sql = "update table set color='red' where id='1'; update table set color='green' where id='2';"; $sql_do = mysql_query($sql); I know the second query is correct, because when I copy it from my php code and paste it into phpMyAdmin, it works. But only the first query, with 1 row update, works from my php code.
View Replies !
JOIN Statement No Longer Works Under MySQL V5
I have the following query, which worked fine under v4.1 of MySQL... SELECT m.*,g.*,p.* FROM ibf_members m,ibf_groups g LEFT JOIN ibf_admin_permission_rows p ON ( m.id=p.row_member_id ) WHERE m.id=1 AND g.g_id=m.mgroup But now it appears as though my server provider has upgraded to v5, and now I get the following error, when I run the query... SQL error: Unknown column 'm.id' in 'on clause' What has changed between v4.1 & v5,
View Replies !
Quering Works Except Need To Sort Results By Other Table...
I have the following query: SELECT plugin_id FROM tc_plugin_lists WHERE (page_id='1' AND position='0') ORDER BY tc_plugins.sort_number ASC; This almost works, the problem is the ORDER BY clause. It should order the results by the sort_number field from another table called tc_plugins. Now it throws an error... SELECT items FROM other table ORDER BY other tables field values where field is same as in 'items' ASC ?
View Replies !
Want Only One Record Returned Per Post_id (was "Help With Query")
I can't figure out how to do this query. Help would be muchly appreciated! I want to get the DISTINCT(post.post_id) but not sure how to do this. Here is the query I have thus far but it's returning two records each with the same post_id but different cat_id's. SELECT post.post_id, auth_alias, pc.cat_id, post_heading, DATE_FORMAT(post_created, '%M %d, %Y'), post_body FROM posting post LEFT OUTER JOIN post_cats pc ON post.post_id = pc.post_id WHERE post_status = Ƈ' ORDER BY post.post_created DESC LIMIT 0,5
View Replies !
Getting Record Before Or After The Record That Meets The Criteria
In a query I want to get all the results and then order those results by last name, but then I want to filter those results down to only the record that comes before or after the record that has 'empno' = '1259'. I want to get all the results already ordered and then filter them down to one record either before or after (depending on what is needed) the record where 'empno' = '1259'.
View Replies !
How To Record The Current Record In A Table?
I'm looking at convert file formats to mysql. The original file format used an "ENTRY" conception to record the current record. But I cannot find a solution to emulate "ENTRY" conception in MySQL. After searching MySQL's tutorials, the AUTO-INCREMENT fields would only use "mysql_insert_id()" to obtain its value. But this value cannot identify the current record, Right? This value are only last record after inserting? Who can help me to find a better solution to emulate implementing what the "ENTRY" does. In other words, How to obtain the auto-increment value contains AUTO-INCREMENT columns in a record which generated by SELECT statement.
View Replies !
Record Creates Record
I have a table that holds inventory information...primary key'd with an asset#. I have another table that I hold certain information in that I don't want in the main inventory table. I would like the second table to key itself and automatically enter a record when a device is entered. For example, I enter a scanner in my main table, but I want the DB to then also enter that assetNumber into the second table for me.
View Replies !
|
TRACKER
