Help Required Improving Query
the following query has been giving me problems. My users are reporting that credit is going down automatically and download counts seem to be overwritten with other downloads. I'm thinking using JOINS would be better, but I'm not sure which how best to structure it.
MySQL
UPDATE tb_members s, tb_userfield f,tb_downloads dl SET s.credit_available = credit_available - 1, s.credit_used = s.credit_used + 1, f.field24 = f.field24 - 1, dl.info_downloads_web = dl.info_downloads_web + 1, dl.info_downloads_today = dl.info_downloads_today + 1, dl.info_downloads_30days = dl.info_downloads_30days + 1, dl.info_last_download_time = NOW() WHERE s.userid = '".$ses_userid."' AND f.userid = '".$ses_userid."' AND dl.key='".$download_key."'"
View Complete Forum Thread with Replies
Related Forum Messages:
Improving LIMIT X,Y Query
I have only 1m records in my database running on a laptop of speed 1.6GHz, memory 512MB ram, and Toshiba MK8032GAX hard disk. I use 'LIMIT x,10' for the query to utilise record paging. When the value of x is nearer to 0, the query speed is fast. Presently, 'LIMIT 0,10', 'LIMIT 50000,10' and 'LIMIT 100000,10' takes about 0sec, 11secs and 4mins respectively. 1) Is there anything I should do, in terms of sql statement or database design, to obtain the same speed for cases where x is in the middle or nearer to the end? 2) How fast is a simple select statement (not select count(*)) on a system like mine? 3) Suppose I left my original select .. limit query as is, what is the minimum hardware that I need to improve the speed?
View Replies !
Need Some Help In Improving Performance On A Query (was: Hi People)
I need some help in improving performance on a query which is taking too long to execute. I have 3 tables: table 'photo' stores information on photos and it has 2 important fields: - photo_id - title table 'item' stores information on generic items in the system (photos is an example). It has 3 important fields: - item_id - item_type (like 'photos') - submitter_id table 'user' stores information on users in the system. It has 2 important fields: - user_id - name I would like to get the number of photos that either have a title that contains 'a' or the submitter's name contains 'a' I tried the following: SELECT count(DISTINCT photo.photo_id) NumItems FROM (photo, item, user) WHERE (item.item_id = photo.photo_id AND item.item_type = 'photos' AND (photo.title LIKE '%a%' OR (user.name LIKE '%a%' AND user.user_id = item.submitter_id))); This query is taking way too long (sometimes up to 10 seconds Note that I have over 2,000,000 records in the 'user' table (I suspect the problem is there) I only have 2 recods in the 'photo' table I only have 2 records in the 'item' table
View Replies !
Any Tips For Improving This Query Time?
Is there anything I can do to improve execution time for this query? mysql> SELECT webpageUrl, webpageName, COUNT(*) AS `count` FROM _1_log GROUP BY webpageUrl ORDER BY `count` DESC LIMIT 5; +---------------------------------------------------------+---------------------------------------------------------------+-------+ | webpageUrl | webpageName | count | +---------------------------------------------------------+---------------------------------------------------------------+-------+ | http://www.w3counter.com/ | W3Counter - Free Web Counter, Web Stats, Live Analytics | 2490 | | http://www.w3counter.com/stats/visitors/4071/day/1000/0 | Visitors Overview - www.pitbullmortgageschool.com/ | 1496 | | http://www.w3counter.com/stats/4071/v_daily | W3Counter - Visitors by Date - www.pitbullmortgageschool.com/ | 1130 | | http://www.w3counter.com/stats/ | Your Websites | 1124 | | http://www.w3counter.com/stats/websites | W3Counter - Websites | 800 | +---------------------------------------------------------+---------------------------------------------------------------+-------+ 5 rows in set (0.88 sec) mysql> explain SELECT webpageUrl, webpageName, COUNT(*) AS `count` FROM _1_log GROUP BY webpageUrl ORDER BY `count` DESC LIMIT 5; +----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+ | 1 | SIMPLE | _1_log | ALL | NULL | NULL | NULL | NULL | 29422 | Using temporary; Using filesort | +----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+ 1 row in set (0.00 sec) It averages 0.5-3 seconds throughout the day (it's run often enough that it shows up in my slow query log multiple times an hour for exceeding 1 second). Adding a partial index on webpageUrl doesn't affect anything, and URLs can be quite long. Is there anything I can do to speed up that query? The table is updated too often to maintain separate url/count tables -- can't spare the resources.
View Replies !
Query Required
Can anyone help me in building a query in which the table contains a field called name varchar and stdate date.Given this can anyone give me a query to get the total number of names of the present month.The month has to be taken automatically say using curdate().
View Replies !
Help Required In This Query....!!!!
I want to replace a character "/" with ⁄ in my database, is there any option to do that. Its like : "this/is/a test line" and after replacement it should looks like that : "this⁄is⁄a test line".
View Replies !
SELECT Search Query - Table Join Required? Help Please!! (PHP + MySQL)
I have a search form that has: - drop down with states (nsw, vic etc) - drop down with all business categories (retail, commercial etc) - keyword / postcode field (2000, or 'builders') The user gets results returned from the business table filtered by state (mandatory), which category is selected (mandatory) and by keyword (optional) or postcode (optional). If keyword / search phrase is given then it will do search of the keywords fields of the business table (has already been indexed) in the selected category only. If postcode it will return all businesses in that category in order of distance from the given postcode. I have 5 tables (additional fields ommitted): 'state' state_id, name 'businesses' business_id, keywords, name, postcode, state 'postcodes' fromPostcode, toPostcode, distanceKMS 'categories' category_id, name 'business2category' business_id, category_id Please dont thing i'm just pawning off my work here!! Basically, i've got this working already, but only just, and in a very long and convulted format. Its far too long to post here, but since i'm not fully versed in table joins, i've been searching individual tables (e.g. SELECT *,MATCH AGAINST etc), building arrays, searching arrays again, and then building results at the end to fit into the paginator. Now the search is taking too long to perform, and i need a leaner alternative. not to mention theres way too many lines of code, and i just know theres a better way. There must be a very simple way to achieve the following searches using table joins, can anybody please help me with 3 search examples below so i can try to understand joins better? Search 1: State + Category only Search 2: State + Category + Keyword Search 2: State + Category + Postcode If there is no postcode, the others still need to display the data filtered by distance from a default postcode of 2000. I'd really appreciate if anybody has a few minutes free to help out here, and hopefully teach me something about effective table joins and searching. I've omitted the extra fields and tables from the real structure, and just left the relevant ones above - if theres anyhing missing or not making sense please let me know and i'll fix up asap.
View Replies !
Improving The Performance
Serve Spec: AMD 2000 RAM 1.5 GB Fedora 10 How can I modify my.cnf for a better performance. Current file looks like [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # To allow mysqld to connect to a MySQL Cluster management daemon, uncomment # these lines and adjust the connectstring as needed. #ndbcluster #ndb-connectstring="nodeid=4;host=localhost:1186" [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [ndbd] # If you are running a MySQL Cluster storage daemon (ndbd) on this machine, # adjust its connection to the management daemon here. # Note: ndbd init script requires this to include nodeid! connect-string="nodeid=2;host=localhost:1186" [ndb_mgm] # connection string for MySQL Cluster management tool connect-string="host=localhost:1186"
View Replies !
Tips For Improving The Performance Of Mysql
I really hope I can get some general advice and suggestions about how I can improve the performance of our mysql server. We have a dedicated webhost running a number of quite highly used websites. They all use mysql quite heavily at times. Recently we've had complete mysql gridlock and had to restart the server. What are the steps I can take to improve performance? This is what i've done so far. 1. Optimise queries - i've been checking the queries we are running and trying to cache the results in a session rather than generating all the time. I've also installed something called mytop which is basically the same as the standard unix top command, except for mysql processlist and shows you the queries currently running and the time elapsed. 2. I have run explain on my queries and put in indexes all over the place to try and make lookups quicker. Things i'm considering doing 1. Reducing the amount of data in the database and perhaps rotating the tables slightly, our biggest tables are approaching 2 million records and linking these together is i'm sure causing some of the problem, especially when we have to run a SUM query on them, even with good indexes. 2. Moving the database off the webserver and onto its own dedicated mysql server. 3. Load balancing the database and somehow clustering more than one database server, but this would cause serious headaches so not seriously considering unless it was thought the best solution. 4. Upgrading mysql... We are currently running version 4.0.27, should we upgrade to 5? Will this give us performance boost? 5. Re-compiling... After a google search I discovered that potentially we could recompile mysql with a better options set and perhaps improve performance. 6. Buying a book on mysql optimisation Any suggestions?
View Replies !
Required Fields
Just so you know I am using Delph 2007 along with MySQL, because of the many benefits of this database. How do you deal with fields that are required in MySQL? I have read "MySQL in 24 hours". I don't believe it discussed much about required fields. If there is a tutorial on this, please let me know. Thanks! :) P.S. Is there anything I would have to deal with on the coding side, concerning required fields?
View Replies !
Locking Required?
If a PHP script uses MySQL MyISAM tables for a table that may have concurrent updates/inserting do we manually have to lock/unlock the table using queries in the PHP code or will MySQL internal locking take care of this for us? I assumed that if a PHP script has many users using it at once, it will be sending many queries to MySQL at the same time, but MySQL should on its own queue these queries and perform them without conflict correct? I assumed table locking queries were if we needed to lock/unlock tables for specific situations?
View Replies !
MySQL Interface Required
I hope this question is appropriate for this group. My server provides a MySQL database. Do I need a third-party interface to add tables and records to it, or is their another way?
View Replies !
Locking Required During Update
if a update query is executed with concurrent access to the database is table locking always recommended? i am thinking specifically about updates that do not take into account the value that is already stored in the DB. let me give an example to explain. a person wants to update the salary of someone we could write: Update table1 Set salary = "30" Where personid = 5; in that case the user is not reading the salary value eg they are not doing salary = salary + 100 so is there a need to do any locking if multiple users do the same thing say using a thin client? i cant see the possibility of a lost update error occurring.
View Replies !
Time Required By Database
Is there a way for me to print out the sum of all the time required by the mysql database? In most cases, I connect to the database once on a page view and then utilize multiple select, insert, etc. statements before disconnecting. Before disconnecting, can I select the total time required by the database (and not perl) in that session? Even getting the time required by one select statement would be useful, too.
View Replies !
Necessary Files Required To Run Mysql DB
When installing the application into clients machine i want to install mysql 5.0 also. But i don't want to install full mysql5.0 onto clients machine.Is it possible to copy some dll files and register it and able to connect to the MySql from java application.
View Replies !
Makeing A Field Required
This is a very basic and simple question but very difficult to find an answer to. I have a table, and within that table there are 3 fields which i need to make required. I tried google for the past hour but it is so broad that it is aggrivating me. Somone please help, i'm getting so frustrated. How do I make it so that an existing field needs to be required?
View Replies !
MySQL Help Required In VBulletin
i run a forum....my problem is when my host was moving to another server some problem occured and my databased were showing some error as they were of 1KB .... though thats not the prob..the main prob is i had the previous day's backup with me...and when i try to restore it ..it shows Database Error in the site...and database doesnt upload properly...please any MySQL guru help me cos without that i wud be losing 1000 posts if possible add me on msn - ronak@holy-s hit.com remove space between s & h
View Replies !
Password Required To Unzip Connector/J
I just installed MySQL on WinXP and immediately tried installing Connector/J. While the downloaded file is unzipping, the process stops and says File Callable Statement Regressio... is password protected. Please enter password in the box below. What passowrd do they want? Its not the password I just gave while installing MySQL. That one doesn't work.
View Replies !
Different Syntax Required For Older Mysql?
I have a statement which works on one Mysql database but not another. The one which works is running version 4.1.18, the one that is not is running 4.0.24. This is the query: SELECT DISTINCT MONTH (col1), YEAR (col1) FROM (SELECT * FROM test order by col1) SORTEDDATES_TAB Is there a differnet syntax required for the different versions?
View Replies !
Mysql/phpmyadmin Installation Method Required
I just got anew laptop and I am currently trying to install all of my web applications. I can remember the last time i installed mysql and phpmyadmin it took me ages, som I was wondering if there is any wizard available that I could use to find a smooth way to install this.
View Replies !
Microsoft VBScript Runtime (0x800A01A8) Object Required: 'oConn'
I'm kind of stuck on a few changes I'm making to my coding. I'm putting connection strings into a sub in a file called functions.asp. however, when I try to send a query, the object does not seem to exist. strange thing is it works when I comment out Call ConnectDB("Members") in test.asp and uncomment Set oConn = Server.CreateObject("ADODB.Connection") oConn.Open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=Users;OPTION=3" please take a look at my code...
View Replies !
Simple Sql Question: Using A Query Result As A Query Variable
EDIT: it works now, I had an error in my code, not my method. I have a very simple question. I have 2 tables: 'users' and 'posts' with the following structure: users: id, username, email_address posts: id, user_id, post_title, post_text in a my own mind's mysql, I would like to: SELECT posts.id, posts.user_id, posts.post_title, posts.post_text users.username FROM users, posts WHERE posts.user_id = users.id I usually do one query for the post data, and then, based on the use_id record, do another of the users table, but today, I'm being forced to do them in one swoop.
View Replies !
How To Create Efficient MySQL Query From A Pseudo Query
I'm trying to build a webapplication where users can search for a person having a particular preference for color and material. To store this information I use the following structure (a MySQL dump can be found at the end of this post): *table person with fields: -persid: autoincrement id -name: name of the person *table material with fields: -materialid: autoincrement id -material: name of the material eg "wood" *table color with fields: -colorid: autoincrement id -color: name of the color eg "green" *table persmaterial with fields: -persmatid: autoincrement id -persid: link to table person -materialid: link to table material *table perscolor with fields: -perscolorid: autoincrement id -persid: link to table person -colorid: link to table color In the webapplication the search can be entered by the users as a kind of pseudo query: (color=red OR color=blue) AND color=green AND material=iron My question is: how can I automatically transform this pseudo query into an efficient MySQL query? I have tried out some different options: Option 1: (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) UNION (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) Remarks: *I do not see how to turn a general pseudo query into a query like the one in option 1, except for turning the pseudo query into a sum of products form where the sulms would correspond to the UNIONs. IS there a clever way to obtain such a sum of products form from an arbitrary pseudo query? Option 2: SELECT persid FROM person p WHERE (EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=1 AND p.persid=pc.persid) OR EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=3 AND p.persid=pc.persid)) AND EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=2 AND p.persid=pc.persid) AND EXISTS(SELECT * FROM persmaterial pm WHERE pm.materialid=2 AND p.persid=pm.persid) Remarks: *very easy to get from pseudo query to MySQL query but what about performance? Option 3: SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING sum(case when pc.colorid in (Ƈ',Ɖ') then 1 else 0 end) >= 1 AND sum(case when pc.colorid=ƈ' then 1 else 0 end)>=1 AND sum(case when pm.materialid=ƈ' then 1 else 0 end)>=1 Remarks: *this option requires the pseudo query to be turned into a product of sums form; again is their a clever way to obtain such a form; Option 4 SELECT DISTINCT pc1.persid FROM perscolor pc1 INNER JOIN perscolor pc2 ON pc1.persid=pc2.persid AND pc2.colorid=2 INNER JOIN persmaterial pm1 ON pc1.persid=pm1.persid AND pm1.materialid=2 LEFT OUTER JOIN perscolor pc3 ON pc1.persid=pc3.persid AND pc3.colorid=1 LEFT OUTER JOIN perscolor pc4 ON pc1.persid=pc4.persid AND pc4.colorid=3 WHERE COALESCE(pc3.persid,pc4.persid) IS NOT NULL Remarks: *this option requires the pseudo query to be turned into a product of sums form Option 5: SELECT p.persid FROM person p, persmaterial pm,perscolor pc1,perscolor pc2,perscolor pc3 WHERE p.persid=pm.persid AND p.persid=pc1.persid AND p.persid=pc2.persid AND p.persid=pc3.persid AND (pc1.colorid=1 OR pc2.colorid=3) AND pc3.colorid=2 AND pm.materialid=2 GROUP BY p.persid Remarks: *very easy to get from pseudo query to MySQL query but what about performance? -- phpMyAdmin SQL Dump -- version 2.6.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 19, 2006 at 01:13 PM -- Server version: 4.1.9 -- PHP Version: 4.3.10 -- -- Database: `aston` -- -- -------------------------------------------------------- -- -- Table structure for table `color` -- CREATE TABLE `color` ( `colorid` int(11) NOT NULL auto_increment, `color` varchar(30) NOT NULL default '', PRIMARY KEY (`colorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `color` -- INSERT INTO `color` VALUES (1, 'red'); INSERT INTO `color` VALUES (2, 'green'); INSERT INTO `color` VALUES (3, 'blue'); INSERT INTO `color` VALUES (4, 'yellow'); -- -------------------------------------------------------- -- -- Table structure for table `material` -- CREATE TABLE `material` ( `materialid` int(11) NOT NULL auto_increment, `material` varchar(30) NOT NULL default '', PRIMARY KEY (`materialid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `material` -- INSERT INTO `material` VALUES (1, 'wood'); INSERT INTO `material` VALUES (2, 'iron'); -- -------------------------------------------------------- -- -- Table structure for table `perscolor` -- CREATE TABLE `perscolor` ( `perscolorid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `colorid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`perscolorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; -- -- Dumping data for table `perscolor` -- INSERT INTO `perscolor` VALUES (1, 1, 1); INSERT INTO `perscolor` VALUES (2, 1, 2); INSERT INTO `perscolor` VALUES (3, 2, 1); INSERT INTO `perscolor` VALUES (5, 3, 3); INSERT INTO `perscolor` VALUES (6, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `persmaterial` -- CREATE TABLE `persmaterial` ( `persmatid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `materialid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`persmatid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -- Dumping data for table `persmaterial` -- INSERT INTO `persmaterial` VALUES (1, 1, 1); INSERT INTO `persmaterial` VALUES (2, 1, 2); INSERT INTO `persmaterial` VALUES (3, 2, 1); INSERT INTO `persmaterial` VALUES (5, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `person` -- CREATE TABLE `person` ( `persid` int(11) NOT NULL auto_increment, `name` varchar(30) NOT NULL default '', PRIMARY KEY (`persid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; -- -- Dumping data for table `person` -- INSERT INTO `person` VALUES (1, 'john'); INSERT INTO `person` VALUES (2, 'emily'); INSERT INTO `person` VALUES (3, 'liz');
View Replies !
Slow Query->Efficient Query
I have a query that is running really slow !!!! I have joined on Key fields and indexed the tables fully but it is still solw. -------------------------------------------- select d.id, a.signed, u.Forename, u.Surname, d.paid, p.date, d.payment, p.amount, d.acctual from details d join poten a on a.id = d.id left join recieved p on d.id = p.id left join users u on a.signed = u.userid where d.paid > '01-Dec-2005' and d.authorrceived is not null and d.authorrefused is null and ((d.payment starting 'E' or d.payment starting 'e') or (d.payment starting 'Q' or d.payment starting 'q' and p.target = '500')) order by d.paid, a.signed, d.id
View Replies !
Reusing A Query Output In The Same Query
I am guessing a basic question but not one I can find an obvious answer to. If I create a calculated or modified column in a query (such as a modified text string), and then want to reuse that in the same query as I need to do three or four operations on it in sequence, how do I do it in mySQL 4.1? Do I need to create a new column to store the interim result in an existing table (and then clear or alter it each time I run the query), or create a temporary table, or is there an easy way to reuse the query output in the same query (does the query have a name like a table name)? If it requires a new column or table, are there particular disciplines to ensure it is robust and self maintaining?
View Replies !
Pagination W/1 Query + How To Use Query With Indexes
i'm asking 2 questions in 1 thread because i don't wanna take up too much room, hopefully no one will mind. i have mysql 4.1.10 1) i want to find all the rows that were edited this month. the query i currently have ( MONTH(CURDATE()) = MONTH(date) ) doesn't use indexes. how can i manipulate it so i can take advantage of indexes. 2) this is something i've always wondered, but usually just assumed was not possible. if i am listing some results, say 20 per page, how can i get both the total number of results as well as the 20 items required for that specific page. say there are 2 million total results, so grabbing them all and showing just 20 is not an option. if this is not possible what is the most efficient way of making both queries?
View Replies !
Create Single Query From Queries On Two Tables (was "Help With Query...")
I read from other thread that query inside loop is not good idea. May I ask some help how can I create a single query to the following code which I use loop. $sql = "SELECT * FROM mytable order by points desc limit 10"; $rec = mysql_query($sql) or die(mysql_error()); $datas = mysql_fetch_array($rec); do{ $sq = "Select * from secondtable where linkid = '$datas[id]'"; $rst = mysql_query($sq) or die(mysql_error()); $rows = mysql_fetch_array($rst); echo "$rows[somefield]"; }while($datas= mysql_fetch_array($rec)); This works perfectly but I want the second query to be out of the loop if there is a way and how.
View Replies !
Grab 'title' From The Table 'forum' Within The Same Query (was "Help With Query")
I have the following query for my vBulletin database: PHP $get_stats_newthreads = $db->query_read(" SELECT thread.forumid, thread.postuserid, thread.postusername, thread.threadid, thread.title, thread.lastpost, thread.forumid, thread.replycount, thread.lastposter, thread.dateline, thread.iconid, thread.views, IF(views<=replycount, replycount+1, views) AS views, thread.visible FROM " . TABLE_PREFIX . "thread AS thread WHERE NOT ISNULL(thread.threadid) AND $weekold<lastpost AND thread.visible!=0 AND (forumid=34 OR forumid=7 OR forumid=8 OR forumid=11 OR forumid=10) ORDER BY rand() DESC LIMIT 5"); and would like to grab 'title' from the table 'forum' where forum.forumid=thread.forumid
View Replies !
Run A Query On The Results Of A Query?
Lets say I have a query that searches for people living in Colorado. That results in a list on a "results.php" page. Now I want to query that result and search further for people who use Linux. I know I can do this from one query, but I would like to create several checkboxes on my results.php page where I do a further secondary query. Possible? I guess I want to query a query.
View Replies !
Is It Possible To Run A Query On The Results Of A Query?
I have been trying to figure this out but no luck. Lets say I have a query that searches for people living in Colorado. That results in a list on a "results.php" page. Now I want to query that result and search further for people who use Linux. I know I can do this from one query, but I would like to create several checkboxes on my results.php page where I do a further secondary query. Possible? I guess I want to query a query.
View Replies !
Query From Query Results
I have a report I'm working on that is sort of like google adsense, where it tracks ad clicks and views. I need to provide the option for the user to narrow down the results by date. I have my query worked out, but would it be better for me to run the query again and add the date information to the query, or should I cache the results and then query them? If I were to cache the results and then run queries off that, what would be the advantages or disadvantages? I also have no idea how to go about doing that. Should I create temporary tables to hold the queried information or is there another way?
View Replies !
Query 2 Tables For Query
I have 3 tables, products, order_log, and groups. Products is a list of products available, groups are groups that products are put in, and order_log is a log of the current products in an order. I need to sort the order_log by the group the products are in. order_log does not have a group_id in it, however products does. So: SELECT * FROM order_log WHERE product's group_id = 1.
View Replies !
Sub Query And Count Query
i have a database with the following structure id | MoveDate | ItemId | SiteID (a new entry is entered when an item is moved from 1 site to another) and i am trying to forumlate a query so that i can count how many days each item was at a specific location so lets assumes i have the following data 1 | 01/01/2007| 1 | 1 2 | 03/01/2007| 1 | 2 how can i run a query that will tell me that between the dates 01/01/2007 and 08/01/2007 item 1 was at site 1 for 2 days and site 2 for 5
View Replies !
Using A Query Result In Another Query
Can I use the results of a SELECT query as a "table" in another query? I want to let my user pick a subset of the data, then refine it further. So ... do I have to repeat all the selection criteria at each step, or can I just do refer to the last query result? If so, what's the PHP syntax for this?
View Replies !
Rewriting A Query Without A Sub Query
I've recently changed hosts and found that some of my code broke. The new host is using mysql version 4.0.25 which does not support sub-queries (and they won't upgrade). I'm trying to figure out how to rewrite the following query so it will work on 4.0.25 but not getting anywhere.....
View Replies !
Query Inside Query?
I would like to know if its practical to Insert a Select statement inside a previous select statements (array). With that said, the 'inner' select having a WHERE statement thats dependant upon the array results...
View Replies !
How To Use Previous Select Query Results In New Select Query?
I have tested everything and it works OK in its current form. However, what I need help with is the part of the query highlighted in red. As it currently stands, the PHP while statement loops through the rows and echos out the html. The issue I have is that it echos out the same speaker name and subject type for each iteration. Now I know this is happening because I have set subject.event_id = 1, so what I need is to use the event_id of the current iteration as the clause and that is what I need help with....
View Replies !
Can I Use An Either/or Query?
I am using the following query to generate a web page. Searchterm is derived from a search form. $query="select * from jobs, items where jobs.JobNumber like '$Searchterm' and items.JobNumber like '$Searchterm'"; The data from the jobs table goes in a general form describing a specific job. The data from the items table goes in a separate form which lists all of the items associated with that job. This works fine if there is data for the selected job number in both tables but in some cases, the data only exists in the jobs table - there is no corresponding data in the items table. What I would like in that case is to output the jobs table data and simply leave the items output blank, but since there are no records meeting the criteria of the query, nothing is selected. Is there a way to create a query so that it will do what I want. If not, can it be done with an either/or statement?
View Replies !
Query Within Query
I have a query that I've gotten to work fine in SQL Server, but when I tried to run the same query on an indentially structured database in MySQL, I get a syntax error. This is the query: select user_id, score, (select count(*) + 1 from ericTest where score > A.score) as rank from ericTest A Essentially, it's a table of user_id, score, and the user's rank, which is calculated by the inner query. Thoughts on what's wrong?
View Replies !
1 Big Query
I have a question about MySQL in general. I'm working with a PHP page that has a couple of nested foreach loops that access the DB. It works out to be a little over 8,500 small queries. This page takes minutes to display. However, when I select basically the same data all at once and then sort through it using PHP arrays and such it takes a couple of seconds.
View Replies !
AVG Query
I have a sql problem I need your help with. I have two tables, one called the user_table having columns user_id and user_rating and another called user_ratings having columns seller_id and rating. The two tables are joined as user_id=seller_id. I wish that every time a new entry is made in the user_ratings table, the user_rating column in user_table be updated with the average rating for that user.
View Replies !
Fix My Query
All right I've sat here for a few hours... still can't get this to work: Code: SET @spot1 = LOCATE('.net/',wp_posts.post_content); SET @spot2 = LOCATE('target="_blank">Download',wp_posts.post_content); SET @spot3 = @spot2 - @spot1; IF @spot1 > 0 THEN UPDATE wp_posts SET post_content = REPLACE(post_content,@spot1,@spot3); It has a problem saying wp_post table does not exists but it does.also it has a problem with my if statement.
View Replies !
For A Query
I have a table feedtagrelation with two fields. FeedID and TagID. FeedID TagID 1 1 2 1 1 2 3 2 4 5 2 2 5 4 i want a query such that it gives all the feeds that are present in more than one tagid (can go up to n level) example :- a query to find feed ids such that they have tagid both 1 and 2 .
View Replies !
IN For A Query
Stuck on a query which i think uses a IN clause but I'm not sure. I have queried a table for a list of reference ids for objects and a list of ids of users who created these objects. With both of these i then created an array $tempreferenceid (for the reference ids for objects) and $tempuserid (for the ids of users). I now need one select query for another two tables 'object' and 'adminsession' for the details of each object using the object id and the details of each user using the user id. this is what I have... SELECT object.url, object.file_url, object.icon_url, object.title, object.text, object.description, object.keywords, object.type, adminsession.useralias FROM object, adminsession WHERE object.id IN '$tempreferenceid' AND adminsession.userid IN '$tempuserid'";
View Replies !
|
TRACKER
