Making The Right Subquery
I've been trying to wrap my head around this one for a while, and while it would be pretty easy to do using a loop in PHP I'd like to keep it in a single SQL statement if possible.
I have a table of comments, very much like you would store comments in for a blog. It goes something like this:
idCOMMENT | FK_LIST_ID | COM_Posted | COM_Comment
autoincrement| foreign key of post | Timestamp | Text for Comment
I only want to keep the most recent 30 or so comments on each listing so I need to create a statement that will delete any excess comments.
I could loop a statement like this in PHP:
DELETE FROM COMMENT WHERE idCOMMENT IN (SELECT idCOMMENT FROM COMMENT WHERE FK_LIST_ID=(variable from PHP) ORDER BY COM_Posted Desc LIMIT 30, 10^10)
But there will be thousands of listings, (it's and that's going to be rough on the server to make thousands of calls to it each night with the maintenance script. I think there must be a way to do this in pure SQL, just send one SQL statement to the server and that's it, but can't figure it out. Can you point me in the right direction?
View Complete Forum Thread with Replies
Related Forum Messages:
SUBQUERY Vs DEPENDENT SUBQUERY
I have a table called `accounts`, that contains columns: account_name VARCHAR (128) account_no VARCHAR (16) account_hash VARCHAR (32) balance DECIMAL(10,2) There is an INDEX on account_hash and there's currently 128 rows in the table. I have a table called `activity` that contains columns:....
View Replies !
Subquery Or Correlated Subquery Help
I need to develop a sql that uses the results from the first Query to find data in the second Query. Then the results of the second query to find the final results of the third Query. I’m also wondering if I should try to just link all these tables together instead of Subqueries or Correlated Query. First Query select ACCOUNT_ID, ACCOUNT_TYPE_C, PAT_ID from PAT_ACCT_CVG where ACCOUNT_TYPE_C in (120103,120104,120101) Second Query SELECT PAT_CVG_FILE_ORDER.PAT_ID, PAT_CVG_FILE_ORDER.LINE, COVERAGE.COVERAGE_ID, COVERAGE.CVG_EFF_DT, COVERAGE.CVG_TERM_DT FROMPAT_CVG_FILE_ORDER LEFT OUTER JOIN COVERAGE ONCOVERAGE.COVERAGE_ID = PAT_CVG_FILE_ORDER.COVERAGE_ID Where coverage.payor_id = ?' Third Query select TRAN.ORIG_SERVICE_DATE TRAN.TRAN_TYPE, TRAN.INSURANCE_AMOUNT from Tran where TRAN.TRAN_TYPE = 1 and TRAN.INSURANCE_AMOUNT > 0 and TRAN.proc_ID in 1008,1009 (now I need to compare the dates on this query to make sure that the TRAN.ORIG_SERVICE_DATE is within the COVERAGE.CVG_EFF_DT, COVERAGE.CVG_TERM_DT ( dates of the second query)
View Replies !
Sum() Is Making
i have a table called bill, in which there r three fields named bill,paid,exempted. when i when i want to deduct the due of each customer the following query does not work: ..........
View Replies !
Making A Db
I just need to make a db with a password... but I do not know any commands nor where to find the ones I want.
View Replies !
Making A Column
ihave some Access background. I've created a table for which I intended the email column to be the primary key, but didn't specify it at creation. Now, I'm trying to figure out how to specify it post facto.
View Replies !
Making Archive
i need to be able to archive old data, that also can be viewed later. is there an archiving feature in mysql that lets you do that? of course i can always put the data into a separate table and call it an ArchiveTbl, but thats not really saving space per say.
View Replies !
Making Auto_increment
I did some searching and didn't come up with anything. Is there any way to make auto_increment go up more then 1 every time. Possibly make it use an equation and generate random stuff? Probably not but it's worth asking.
View Replies !
Making New Table
I'm a new to mysql, so exuse me if this is already asked (i just don't know what exactly i'm looking for) This is what i would like to do: i'm creating new table and inserting new information to it, but i want to take one col from another table (searched by WHERE statment), how would i do it
View Replies !
Making Querry
I was wondering are there techniques to optmize your queery? Like someone told me if you do search on certian fields make them index keys. And in your SQL do not join too many tables etc.
View Replies !
Order Making
ORDER BY case when foto is not null THEN usuarios.ID_usuario END DESC, ID_usuario could ordering results by case.., then etc.. be making queries slow? Our db is really slow and we are trying to figure out why..
View Replies !
Making Connection
The situation is this. I have a web site. On the web site is a phpbb forum. The database is on my hosts remote server. It is not on my own computer. Recently i have been trying to set up an application to allow me to create a newsletter and send out to the members on my database. The application i have talks about an ODBC connection. Can I connect to the database on the remote server using this connection? Or should i have the database on my computer too? I have mysql on my computer but have never used it. I have learnt the basics of SQL and use this on the remote server.
View Replies !
Making Database
how do i make a database i cant make one i am trying to make a phpbb forum so i need help. all i have done is the install and the service config. but after the config shouldn't there be a mysql icon on the window control panel that creates db for you i dont have that.
View Replies !
Making Only One Instance Appear
I'm creating a drop-down box where the visitor may choose a state, and the server will pull all shops found in a row where the column State = their choice. I populate the drop-down box by the table so only states with shops in the table will show up and not all states. How do I form the query so it returns only one instance of the state? Here's the pertinent code: ....
View Replies !
Making ODBC Work
When trying to set up programs (dbQwikSite, Adobe Designer) to work with ODBC databases, I encounter the following during setup of the programs: "it connects, but if I set the initial catalog to any database it says: "Test connection failed because of an error initializing provider. Catastrophic Failure." But when I connect with the initial catalog field empty it connects successfully.How do I fix this problem?
View Replies !
Making All Tables MyISAM
Is there a way to specify MyISAM for all tables in a given file rather than having to specify it at the end of each table declaration. I think this would make porting databases back and forth to MySQL somewhat easier since including MyISAM as part of the table declaration is not compatible with standard SQL syntax, so I would like to keep MySQL-specific stuff separate from the rest of the SQL DDL file.
View Replies !
Making A Copy Of The Database
I need to create a copy of mySQL database on my dev machine (for development purposes), i use phpMyAdmin for db admin. the live db(from which i want to make a copy) uses phpMyAdmin also and i have access to it. I tried to use phpMyAdmin feature that runs and populates the DB from .sql file, however the max file size is 2MB and mine is 17MB so that hasnt worked, surely someone had to do it???? my db on my dev machine is mySQL as well, so it should be easy, right?
View Replies !
Making Values Null
Is it possible and safe to make a value into null in my database? My table contains rows with data and some collumns should be null but instead are like the following below ID | Make 1 null 2 honda 3 4 null 5 Ford So 3 should be null. I'm not sure why it is not showing up as not being null. Is it easy to change this across many rows and collumns
View Replies !
Making Columns In Mysql
I was wondering if there is a way to display more columns from one variable ? For example take this quick database: - Upload (the database), - uplooad_files(the table), - ID(the variable in the upload_files table) - files(another variable) Is there a way to display multiple entries in the files column in more columns on a web site ?
View Replies !
Making A Login Database
I've got mysql on a server and am trying to make a database to enable logins from a web page. I managed to build a database, but I can't connect to it. Is there some settings you have to know first? I just want a basic database with one username and one password that can be accessed and verified from an html form.
View Replies !
Making Values Unique
i have a table in which i stored the ID of the user and the ID of the friend of the user like this userid | friendID 1 | 5 1 | 7 now i want to keep the combination of these two values unique... like i dont want the userid 7 have the friendid 1...like userid | friendID 1 | 5 1 | 7 7 | 1
View Replies !
MySQL Not Making MYD Or MYI Files
I installed MySQL server 5.0 on my computer and have a problem. I create a database and create a table within it, then populate the table. At this point (on my old computer) I would copy the new database files to our webserver. However, on the fresh install, the folder only contains the .frm file, not the .MYD or .MYI files. I'm looking in C:Program FilesMySQLMySQL Server 5.0data emp just for reference.
View Replies !
Making MySQL Connection
I'm using DreamWearverMX, and try to establish the MySQL connection, but I'm confuse when it's asking for the connection name, MySQL server info, user name, password, and database. Where I can get these information to make the MySQL connection. I already build the "reservation" database, which contained fours tables, booking, clients, room and users.
View Replies !
Making Secure Database
how would i go about making my database as secure as possible so users information is kept as safe as possible.... at moment i am using md5 encryption for their passwords... does any1 recommend anything else ?? and also in general to keep my data as safe as possible..... will web hosts deal with keeping mysql database secure or should i do sumting against it?
View Replies !
Function For Making Url Friendly Strings
I'm using MySql on a web server. Iv''e got at table with diffrent catgories, where some of the category has non-url-supported charcters such as &, : åäö, etc. I'd like function that turns all non-url-supported-characters into a '-'. For instance: Me & My Friends -> me-my-friends. I know that I can make another table with "url-name" but that's not a good solution for med. I would also like to avoid using the REPLACE function for every non-supported chacter.
View Replies !
Making Table Headers Sortable
I have a table with various fields including the following: barcode firstname lastname status etc I query the database (mysql) and it displays the result sorted by barcode. im trying to make it so taht everytime the result is displayed on the html page, the table headers become a link and users can click on them so it gets sorted accordingly. for example if i ran a query that displays all the barcodes in the database, it'd look like this: barcode firstname lastname status 123 test test available 234 john doe available 343 ted ted not available but if i wanted to resort the results dynamically, i can't do that because the headers aren't clickable. so i was wondering if anyone knows a way of making the headers clickable so that when the user clicks on lets say firstname, it sorts the result using firstname field in the database.
View Replies !
Timestamp - Making It Stamp The Time
This is wrong: ALTER TABLE members CHANGE COLUMN startday DEFAULT NOW(); But it may convey what I want. I created a table with a column called startday which I made a data type of timestamp which I had hoped would fill the field when a record is added. But no. How do I alter the table column so that it will fill wiit the trimestamp of when the record is added?
View Replies !
Making A Vertical Table Into A Horizontal One
I have a tabel that stores data like this Name type value a a85 1 a a89 1 a a101 1 b a85 1 b a89 0 b a101 1 and so ON... so its a vertical table with a VarChar for names, another VarChar for type and a int for 1 or 0 value. my problem is that i need a query that when i send i NAME get me a result like this name a85 a89 a101 a 1 1 1 b 1 0 1 how can i do that ?
View Replies !
Making Random Call But Exclude Something
I did made up a box where it querys 1 random content from the table. It looks like this: SELECT * FROM e107_pcontent WHERE content_heading AND content_icon AND content_id ORDER BY RAND() LIMIT 1 It works perfect but my problem is my contents also do have categorys and those are stored inside e107_pcontent too, just like an casual content. In order to avoid displaying categorys when it outputs a random content I wanted to exclude my categorys. How do I do it? I believe there are ways to do since they share the same information like normal contents, so I was thinking maybe I could make something up like an order "if content_id is number 1 skip the call and show next entry, if content_id is number 4 skip the call and show next entry" you know?
View Replies !
Making Large Test Database
I was using mysqldump to handle my backups. I had a script that ran nightly and would dump my tables and copy them over to a directory, which then got backed up. This worked great when our DB was only 400mb. We have since grown past 2Gb and my mysqldumps keep losing the connecton the mysql server. I'm not sure if this is due to the size of the db, mysql, or scripts timing out. To test, I'd like to create varying sizes of dbs from 2gb to 10gb to test backups with my forseen growth. How do I create a db with erronous data that isn't customer information to test? We aren't using innodb exclusively, so we needsomething that can make a mexed up db.
View Replies !
Making A Public MySQL Database?
I have a GAME SERVER that generates Status into MySql, and I need to have ODBC and MDAC in my PC in order to work, I want to know how to set MYSQL to work externally, like, i´m running the database in my pc but I want to run the PHP page in a webserver, so, what would I have to put in SERVER instead of "localhost" (because the database is not in LOCALHOST it is in my pc)
View Replies !
Making A .bat File For The SOURCE Command
i have a sql script file that i normally run from the mysql prompt using the command SOURCE so i first i do mysql> use tms; then i do mysql> source /path/to/file/script.sql; how can i create a batch file for this process my database username is root and no password
View Replies !
Making My Relational Tables Relate
I have created a mysql database and it is normalised to 3rd form. I can successfully join some of my tables to give the results I am looking for. However other tables that I join give the wrong data. I created the table data in a spreadsheet and simply filled in the numbers where table_id foreign key corresponded to table_id primary key in the primary table. This is probably why some tables relate correctly and others (where the data is repetitive and a reference table was created) do not. Obviously it appears this is not the way to create relational tables?
View Replies !
Making Mysql Username And Password
im setting up phpAdsNew and they ask for my database (mysql) username and password... i go to google and search on how to create usernames and passwords and i choose the link on mysql.com... it tells me to do this: GRANT ALL PRIVILEGES ON database TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION; i do that and all goes well...i go back to the installing of phpAdsNew and put in the username and password i made... it still doesnt work... so could someone tell me if thats how to make a username and password in mysql or if there is something wrong with phpAdsNew... and i'm using the mysql command line client to put the code in...just incase you need to know that.
View Replies !
Making An Array Of The First Letters In A Column
im trying to get an array of the 1st letters of a column in a table. i have a table that stores information about films and their directors. i want to know what letters are present in the table, i only want each letter once. example: if i had this in my table greg brant james field pete tongue jeff kapes i would get a result set like this 1: g 2: j 3: p so im trying this "SELECT DISTINCT SUBSTRING(director, 0, 1) AS letter FROM film_information" where diorector is the column and film_information is the table. It doesnt seem to like the substring(). Can you use a column in the substring function?
View Replies !
Making A Two Tables MySQL Query...
I'll try to explain this the best I can. Hopefully there are some people out there that can help me with this one since I am completely lost. :confused: First some information. I have two tables with this structure:
View Replies !
Making A Real-time Imagecopy Of Your Db
Hi here is the scenario: Lets say I have 2 shared hosting accounts , one is my primary host the other is backup. Initially, Both have identical php files and database data. The primary one is then launched to the world and the database quickly grows. How do I make it so that whatever changes are done to the primary database is also done to the backup database?
View Replies !
Making Fulltext Match More Accurate
First off, here's my query PHP Code: $searchnews = mysql_query("         SELECT news.title, news.link, news.description, news.date, related_sites.site_name,             MATCH(news.title, news.description) AGAINST ('$actorname') AS score         FROM news         WHERE MATCH(news.title, news.description) AGAINST('$actorname') >5 ORDER BY news.news_id DESC LIMIT $min,10     ",$dbh)         or die(mysql_error()); Ok. So I basically say grab everything that scores higher than 5. This works very well in most cases. But some searches come back odd. As you can see, I'm searching through news for a particular actor name.
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 !
|
TRACKER
