Very Slow MySQL SELECT Query After 11 Records
I have a very weird problem
I moved my database to new IIS 6.0 + PHP 4.3 + MySQL 4.0 setup. I tried to check with myphpadmin if everything works fine, but I couldn't browse my table (125 records). I used SELECT option and found out that SELECT query up to 11 records is ok, but after that, even if I choose to limit query to 12 records, it just hangs.
I tried selecting from the beginning of the table and from the middle - same thing, but when I run query localy on mysql console, everything works fine
View Complete Forum Thread with Replies
Related Forum Messages:
Select Query To Get Only Todays Date Records
I want to use the following query to show only users who registered today with todays date.format(m/d/y)field name is: a.date_registration select a.id, a.fname, a.sname, a.status, a.login, a.login_count, b.lang_1 as gender, a.email from pro_user a left join pro_reference_lang_spr b on b.table_key=཈' and b.id_reference=a.gender where a.date_registration = (Todays Date m/d/y) order by a.login limit 0, 10 0, 10
View Replies !
Create A Sql Query That Will Select Records By Selecting A Month
I am trying to create a sql query that will select records by selecting a month. So for example I have to create a query rhat will select all entries between 1st jun and 1st july (1117609200 and 1120201200) which should not be that hard. Is there a function that will generate what the timestamps should be automatically instead of me having to calculate them manually.
View Replies !
Select Multiple Dates' Records From MySQL DB
I need to select multiple records from a MySQL DB based on one date value that I get from a form. When I get the date from the form I need to get the values for that date and for the six days before that to display the full week's data. Code:
View Replies !
Mysql Query - Select Any
I have the following sql statemant to search a mysql database that gets if values from a form with combo box's in. SELECT * FROM hottubs, manufacturers WHERE manufacturers.manid = hottubs.manid AND hottubs.type = '%s' AND hottubs.dimlength <= '%s' AND hottubs.dimwidth <= '%s' AND hottubs.dimhight <= '%s' AND hottubs.seatsto <= '%s' AND hottubs.shape = '%s' ORDER BY $thesearchtype_search. Everything works fine except I want to add a select "any" from the shape combo box. I really need a way of cutting out the last " AND hottubs.shape = '%s' " if the $_GET['shape'] = 'any' Will this work using a variable as shown below?? IF ($_GET['shape'] != 'any' ) { $shape = AND hottubs.shape = '%s' } SELECT * FROM hottubs, manufacturers WHERE manufacturers.manid = hottubs.manid AND hottubs.type = '%s' AND hottubs.dimlength <= '%s' AND hottubs.dimwidth <= '%s' AND hottubs.dimhight <= '%s' AND hottubs.seatsto <= '%s' $shape ORDER BY $thesearchtype_search I have only been doing php about a month so go gentle!
View Replies !
Mysql Select Query With Php
I want to create a sql query but don't know if this is possible with mysql. The Query should do the following: Select all db entries from table in which entry a is smaller than the number (count) of all db entries with criteria x from another table.
View Replies !
Mysql Select Query
PHP Code: <?php include 'common.php' include 'db.php' if(!isset($_POST['register'])) { header("Location=register.php"); } else { dbconnect('rishaorg_members'); } if($_POST['newid']=='' or $_POST['firstname']=='' or $_POST['lastname']=='' or $_POST['address']=='' or $_POST['city']=='' or $_POST['state']=='' or $_POST['country']=='' or $_POST['birthcity']=='' or $_POST['birthstate']=='')........
View Replies !
MySql Query With SELECT
The following query used to work on older PHP. I have upgraded to PHP 4.3.2 and now it stopped working. No problems connecting to db can even print the variables used for search. But it seems as if the variables aren't presented to the query: $result = mysql_query("SELECT username, name, surname, email FROM forums_auth WHERE username ='$_POST[pnr]' OR name LIKE '$_POST[fnavn]' OR surname LIKE '$_POST[enavn]' ORDER BY surname, name ASC") OR DIE ( mysql_error() );
View Replies !
How To Corret This Mysql Select Query?
I convert some code from access to mysql. And I have a InboxMessage table which has From and To field. So, the query is like: select * from InboxMessage where To=12 12 is user id. This query works fine in access. But does not work in Mysql. I think the problem is the field To, which must be a keyword in mysql. So, how to fix this? I don't want to change the field, becuase this will need to change a lot of code.
View Replies !
Is There A MySQL Function Within The SELECT Query....
Is there a mySQL function within the SELECT query that can add all of the data within a field. For example if I have a table called "profiles" and a field within that table called "referals" (which is an integer) and I wanted to add all of the results within that field together... I was trying to think of a loop to write this code in and may still do it but was wondering if there is a function within the SELECT statement that can ADD all of them together.
View Replies !
Slow Query
i have this query that will only return members that user "1162828" hasnt already voted for. the reason behind this is so user "1162828" does vote on the same member more then once. my problem is that it is slow and gets recored to my slow logs. anyone know why this might be slow? Code:
View Replies !
Drop Down Menu Where The Items Are From A MySQL Select Query
I am just starting programming in PHP and wonder if it is possible to have a drop down menu where the items are from a MySQL select query. I am registering people for a weekend seminar and want to have a list of valid weekends to choose from on the form. Any pointers to scripts or PHP references that will help would be appreciated. I am using the book "Build your Own Database Driven Website Using PHP & MySQL" to get me started, but also need recommendations for a general primer to php.
View Replies !
Mysql SELECT Query That Selects The Latest Items
I am building a website that has a software download category, and has news on the main page. Now I have two tables, named "news" and "software". On the main page I want to select the latest 40 updated items, so whether it is a newspost from table "news" or an extra entry in table "software", or an updated version of an entry in "software". They are all INT types holding a time() string. what I want is a mysql SELECT query that selects the 40 latest items.
View Replies !
Combining 2 Fields In A Single Mysql Select Query
i want to combine 2 fields in a single select query fields. pageheader name values. abcd <div id="d"></div> i want to merge/combine the pageheader and name in a single select query so that it can retrieve the pageheader with some styles which i have stored in another table.
View Replies !
SLOW Query Trouble - Puzzler
I have carbon coppies of a crm_db running on 3 different servers 1. Desktop Development/Admin server, and 2. Tablet PCs in the field. All are running same WAMP5 Stack on XP. Apache version :Apache/2.0.55 (Win32) PHP version :5.1.2 Loaded extensions : bcmath, calendar, com_dotnet, ctype, date, ftp, iconv, odbc, pcre, Reflection, session, libxml, standard, tokenizer, zlib, SimpleXML, dom, SPL, wddx, xml, xmlreader, xmlwriter, apache2handler, mbstring, curl, gd, imap, mysql, mysqli, pdf MySQL version :5.0.18-nt Code:
View Replies !
MySQL Query: Select Only Numeric Substring From Varchar Field?
I would like to write a MySQL query that will limit the selected records to a specific pattern that end in digits rather than non-numeric characters. Say the field (TESTPHASE) has values like this A000 APM APM1 A015 A010 A045 HPM1 GPM F020 E045 I want the SELECT query to return only A000 A015 A010 A045 F020 E045 I suppose it would be possible to filter these after the fact with some fancy php code, but I'd MUCH rather do it up front with the MySQL query. Oh, and of course I am stuck with MySQL v3.23.45
View Replies !
Multiple Select From Listbox And Execute A Sql Query Select Statement
Supposing i have a html form with 2 listbox and i name it status and resolution. So it goes <select name='status[]'>... <select name='resolution[]'> Then i have a another php page. $status = $_POST['status']; $resolution = $_POST['resolution']; Now my question is, how do i do the sql query statement? mysql_query("SELECT * from bugs where status=$status and resolution=$resolution"); The problem is, i select 3 options form the status listbox eg. new, closed, duplicate. And from the resolution, i chose fixed, invalid.
View Replies !
Select ALL Records Between $1,000 & $25,000
I have a field called "price" in one of my MySQL database tables. I want to select ALL records between $1,000 & $25,000 - How can I do this? Also, what format would the numbers have to be in for this to work? Are commas or money signs allowed?
View Replies !
How Do You Select All Records In The Month You Are In?
I have records in the database - one of the fields is a date field. I want to select all records in the database that are in the month I am in. For example, if it is the 12th September 2002 today and I go to the page then it selects all records from September 2002. If it is the 4th June it selects all records in June etc.
View Replies !
Select Multiple Records
I have an application that uses mysql database. Several records are retrived from the database. To enable users to select multiple records, I've used checkboxes by the side of each record. However, the checkbox does not retrieve their value from the database, neither do they update the database. I just thought that by setting the value of the checkbox, I could determine the selected checkboxes and insert the corresponding records into the database. The selected records whose checboxes have been set are then inserted into another table. Or updated as I may also need to update some. My problem,however, is how to determine the selected records and then do multiple inserts into the database table?
View Replies !
Randomly Select Records
I'de like to provide a "lasts articles" table. By this, I'll show last articles inserted. But I won't always the same articles at any refresh. Question 1: how to get a "random" selection from the database, giving more priority to the last inserted (the ones with higher articleID) Question 2: I'd like to provide one article by client. I won't show 3 articles from the same client only because he has been the last one to insert his articles. So I must randomly select by choosing between last articleID, but only one per clientID. simplified table is: articleID clientID details price.
View Replies !
Select Two Weeks Of Records
ok - so i've got some entries with dates in my db. I thought i had it but its not quite there yet. If today is 20061017 and i want to look 3 weeks into the future its not as simple as addings 21 days. How can i get the month to change automatically after it reaches the last day of the month? Code:
View Replies !
Awfully Slow Mysql Script
Afternoon all. I wrote a simple script to grab some information from a database. It goes like this: - usual connection code - Order the database by the 'desc' column: $query="SELECT * FROM images ORDER BY `desc` DESC"; - get result, set num_rows etc. - $i=0; while ($i < $num_rows) { $desc=mysql_result($result,$i,"desc"); if( $desc == $lastdesc ) { } else { $albumid=mysql_result($result,$i,"albumid"); $filename=mysql_result($result,$i,"filename"); $title=mysql_result($result,$i,"title"); echo "<div class='imagethumb'> <a href='/fanart/artist.php?a=$desc' class='image'> <img src='/fanart/$albumtitle/image/thumb/$filename'> </a> <center><i>$desc</i></center> </div>"; $lastdesc = $desc; } $i++; } What this does is first simplyoutput the row's data into a nice image thumb. On that first run through, it sets $lastdesc to be the same as $desc. Then it moves on to the next row - and using $lastdesc, checks whether it has the same 'desc' as the last row, in which case it won't display as I only want one row to show for each desc. Then it moves on to the next row and checks again. If the desc is still the same, it'll skip through until it finds a new 'desc' and output that in the image thumb. After that it's off again to repeat. It works perfectly fine, but it's terribly slow - takes minutes just to get through 200 rows. Any ideas on optimisation? Have I gone about this in totally the wrong way?
View Replies !
MySQL Is Extremely Slow... Sometimes
I have some scripts that make 50+ queries to my SQL database. The speed is not very fast, as expected... but sometimes it takes forever, or cannot complete without a page timeout. It is strange, sometimes it goes fast... other times it cannot run at all for days. I have recently switched my webhost to iPower.com, and I seem to have more of these problems then before. My question is, can this be helped by switching to another provider or by getting a different class of webhost service? I have made the assumption that it is not my script that is the problem, because it has not changed. I figure it must be the webhost.
View Replies !
Mysql Running Slow
I'm a php guy and db system issues are not my thing. My server (linux) is running mysql 4.0.15, and running SLOW, very slow. I did a search for the slow query log file that the docs mention but could not find it. I have started changing the file types to myisam from innoDB. I'm wondering if the system is screwed up becuase the size of ibdata1 (where innodb data is stored) is 1344274432 bytes (about 1.1 GB), but as I convert tables from innodb to myisam (lots of them with lots of data), the file size of ibdata1 is not going down.
View Replies !
Copy Select Records To New Table
In the table Step I have multiple records for each given id. I want to save the current list of records for an id to a new Step_Old table so I can look back at what I had at a given point in time, since the records for an id in the Step table will change over time. Now the question... does anyone have a slick way to do this, or do I need to Select all the records I want to save and then save them one by one with a while loop into the Step_Old table?
View Replies !
How To SELECT Records WHERE Field Contains A Particular String?
I need to SELECT records WHERE a string matches the content of a field... eg. I have field values like: Phone namePhone name pinkPhone name bluePhone name polka-dot What I want is to have a MySQL query which selects ALL records which match the search term, eg. 'Phone name' returns all of the above, but not 'Phone': Currently my query looks like this: SELECT * FROM [table_name] WHERE [field_name] LIKE '%s' ORDER BY [value] ASC
View Replies !
Execute An Update Query Based On The Results Of A Select Query
I'm trying to execute an update query based on the results of a select query... This doesn't work - any ideas? This is the code I'm trying to get to run (it updates all products which are set to arrive by a certain date if that date has arrived or passed - and there are products that should and should not be updated in the db this is being tested with...): Code:
View Replies !
Slow Response Times (php + MySQL)
so my site is finally done but the main page takes about 4-5 seconds to load. It's about 50KB and I have a 1.5 MB DSL so it's not the loading time. Right now it searches through about 1500 records and returns about 50 of them with name, URL, image and a few other options. The site is not public yet and I notice that mysql take 60-90+% of the CPU but barely 3% of the memory when I call that page. Tried it plenty of times to make sure that's why it spiked. What's scaring is that the records will increase to at least 10,000. I have eAccelerator and I have no problems with a php-nuke site with way more records. It loads really fast, but I have removed a lot of the bells and whistles; here I need them. The server: Fedora II, a 2.4 P4, 1 GB Ram, MySQL 3.58, server load almost never goes above 0.50, even during the "delays" and when mysql taking 98%. After an OS /Apache 2.0 upgrade, I've noticed that I only have one mysql proccess running, before I had dozens. Is this better or worse? [root@********etc]# more my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid I have no problems with individual pages at all in this site. What do you guys suggest? Is my .cnf the problem? Unfortunately the program is finished, too late for database changes, if that's the problem. Can I "cache" the index page with an additional program and update it manually or every hour or so? Eventually I will upgrade to MySQL 4.* but right now I am swamped with updating and testing the new site and can't risk downtime. Any help would be greatly appreciated. some more mysql data, in case it makes sense to someone ;): ------------------------ system locking is not in use Possible variables for option --set-variable (-O) are: back_log current value: 50 bdb_cache_size current value: 8388600 bdb_log_buffer_size current value: 0 bdb_max_lock current value: 10000 bdb_lock_max current value: 10000 binlog_cache_size current value: 32768 connect_timeout current value: 5 delayed_insert_timeout current value: 300 delayed_insert_limit current value: 100 delayed_queue_size current value: 1000 flush_time current value: 0 innodb_mirrored_log_groups current value: 1 innodb_log_files_in_group current value: 2 innodb_log_file_size current value: 5242880 innodb_log_buffer_size current value: 1048576 innodb_buffer_pool_size current value: 8388608 innodb_additional_mem_pool_size current value: 1048576 innodb_file_io_threads current value: 4 innodb_lock_wait_timeout current value: 50 innodb_thread_concurrency current value: 8 innodb_force_recovery current value: 0 interactive_timeout current value: 28800 join_buffer_size current value: 131072 key_buffer_size current value: 8388600 long_query_time current value: 10 lower_case_table_names current value: 0 max_allowed_packet current value: 1048576 max_binlog_cache_size current value: 4294967295 max_binlog_size current value: 1073741824 max_connections current value: 100 max_connect_errors current value: 10 max_delayed_threads current value: 20 max_heap_table_size current value: 16777216 max_join_size current value: 4294967295 max_sort_length current value: 1024 max_tmp_tables current value: 32 max_user_connections current value: 0 max_write_lock_count current value: 4294967295 myisam_max_extra_sort_file_size current value: 256 myisam_max_sort_file_size current value: 2047 myisam_sort_buffer_size current value: 8388608 net_buffer_length current value: 16384 net_retry_count current value: 10 net_read_timeout current value: 30 net_write_timeout current value: 60 open_files_limit current value: 0 query_buffer_size current value: 0 record_buffer current value: 131072 record_rnd_buffer current value: 0 slave_net_timeout current value: 3600 slow_launch_time current value: 2 sort_buffer current value: 2097144 table_cache current value: 64 thread_concurrency current value: 10 thread_cache_size current value: 0 tmp_table_size current value: 33554432 thread_stack current value: 65536 wait_timeout current value: 28800
View Replies !
SLOW MYSQL Remote Connections
I was having a major issue connecting to a MySql Database from other machines on my network. localhost connections were just fine. But I wanted to connect to the DB remotely through PHP and the response was terrible. Connecting to Mysql Remotely using GUI's like MySql Front was terrible as well. I am hosting the MySql DB on a Windows XP Service Pack 2 machine, loaded to the teeth with processing and memory. But the result were terrible. Code:
View Replies !
Turn Select Query Result Into Hyperlink To Other Query
I have a query which gives results of selecting coursenames from a table called trainingtopics but this does so in a continuous bulk of text without any breaks between each record. my question how do I create line breaks between each record and also how can I force each query result to become a hyperlink which when clicked runs another query that gives details of that course. <?php $user = "root"; $host = "localhost"; $password = ""; $connection = mysql_connect($host, $root, $password) or die ("Couldn't connect to server."); $database = "courses"; $db = mysql_select_db($database) or die ("Couldn't select database."); $sql = "SELECT coursename FROM trainingtopics"; echo $sql."=sql<br>"; $result = mysql_query($sql) or die(mysql_error()); echo $result."=result<br>"; while($row=mysql_fetch_array($result)) { // NOTE this one ABOVE the echo echo "result found!"; echo $row[0]; } ?>
View Replies !
Select Distinct Records Within 2 Separate Tables
I have 2 tables - 1) - paid counties 2) - free counties and I want to search both to find a distinct list of the counties used. Is there a way to search the first list (save the data into an array), then add ONLY the distinct counties from the free table into the array. Then read all the data out of the array sorted by CountyName,CountyID.
View Replies !
Loop To Select And Update Records Using Classes
I am using a group of classes to manipulate MySQL tables. I would like to be able to go through a table one line at a time to convert fields from all upper case to word upper case. The problem I have, I think anyway, is that my result set from the first query is abandoned by the second query. I am wondering if there is an easy way around this? Code:
View Replies !
Select All The Records In A Table That Were Input Last Month
I am trying to select all the records in a table that were input last month, example:if month today is October select all record with month of September The query I am using is PHP Code: SELECT payments.*, members.* FROM payments, members WHERE payments.coupon_id = 餞C554360' AND payments.member_id = members.member_id AND payments.begin_date < NOW() - INTERVAL(1 MONTH) This display an error: 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 'MONTH)' at line 1 I am not sure if my query is written correctly.
View Replies !
Select Records By The Year From A Mktime() Stamp In The Database
I'm trying to select records by the year from a mktime() stamp in the database. I thought I could do it this way: PHP Code: $new_date = "2006"; $query = "select date, DATE_FORMAT(date,'%Y') AS year from articles where year = $new_date"; But I'm doing something wrong and haven't been able to figure out what it is. 2 hours of reading documentation and I'm missing something.
View Replies !
Sql Query For Find All Records Have Same Id
Lets suppose I have a table like this one idid_1id_2 date_time 110110002006-07-04 11:25:43 21021001 2006-07-04 11:26:43 310110052006-07-04 11:27:43 410310002006-07-04 11:25:43 I want to find all records have same id_1, but different id_2 and have difference in time less than 5 minutes. In this case this is record 1 and record 3. How can I do this ?
View Replies !
Database Records Query
Can anyone help me with the following mySQL code. Probably a simply solution but i cant find the answer anywhere. I have a table called news full of articles each one has an associated news_id. What i need to do is query the database and pull out the last article. Then i want to pull out the second to last article and finally the third to last article.
View Replies !
Query For Records Older Than A Date?
I need to write a query for a table that has records that are up to 5 years old. I need the query to return records that are older than 6 months only. There is a date field in the table. Using date_sub and interval I know how to get records between today's date and an interval, but not records older than that interval. select * from tables where date_checked BETWEEN DATE_SUB(CURRENT_DATE(),INTERVAL n DAY) AND CURRENT_DATE();
View Replies !
Sql Query For Count Records Of Current Week
I have a registration form that saves the registration date on a datetime column. I would like make a query to know how many records are in the table that where for the current week. For example, I'm on week 25 and I want to know how many records I have for this week without the need of specifying the from and to dates. Is there a way to do this?
View Replies !
Query Problem: Counting Records From Another Table Without Using A Subquery
I'm working on a rather complex SQL query, which retrieves a list of clients that match certain criteria. One of the criteria I need to test is the number of business days that have elapsed since the client's enrollment. To facilitate this, I've created and populated a table called business_days - containing one record for every business day (non-weekends and non-holidays) for the next ten years. Using this, I can just count the number of business_days between two dates, like this: Code:
View Replies !
View The Query Results One Record At A Time And Cycle (step) Through The Records
I need to know how to use PHP to step (one by one) through the records of MySQL database query result. I am able to connect to the DB and perform a query. I am able to print the results of the query for the ENTIRE query result. What I need now is to be able to view the query results one record at a time and cycle (step) through the records by using a control (button or link) on the page itself. (The first record of the query result displayed on page entry, a "next" link/button to see the next row of information).
View Replies !
Select Query With And / Or
Trying to get a search feature running. A table has 2 field 'itemname' and 'itemdescription'. I would like the search to use a keyword on both of these fields but i'm unsure of how to seperate it from the main query. $query = "SELECT " .sellerid . "," . itemname . "," . itemtype . "," . itemstate . "," . itemprice . "," . pic1 . "," . itemid . "," . itemdescription . " FROM item where " . itemtype . "= '$ctype' AND " . itemstate . " like '$stype' AND " . itemprice . " >= '$lprice' AND " . itemprice . " <= '$hprice' AND " . itemdescription . " like '%$kwords%' OR " . itemname . " like '%$kwords%' ORDER BY `itemprice` DESC LIMIT $offset, $rowsPerPage"; The OR seems to be working on the whole query not just the last little bit like i would wish. " . itemdescription . " like '%$kwords%' OR " . itemname . " like '%$kwords%' Something like this but contained in the main query? Does this make sense?
View Replies !
Select * From Query
Im trying to run a simply Select * from query but the result is a blank page. here is my code. when I connect to the db using the same code but inserting data I get no problems. so Im having no server problems. <?php $user = "root"; $host = "localhost"; $password = ""; $connection = @mysql_connect($host, $root, $password) or die ("Couldn't connect to server."); $database = "courses"; $db = @mysql_select_db($database, $connection) or die ("Couldn't select database."); $sql = "SELECT * FROM trainingtopics"; or die (mysql_error()); ............................
View Replies !
Query SELECT * FROM
here is my query to my database $result_pi = mysql_query("SELECT * FROM players WHERE teamid = '".$gm_team2."' AND position != 'G' and status = 'Healthy' AND registered = Ƈ' ORDER BY lname"); this works fine, but i am trying to add in as well status = 'Rookie' When i try and add this, both the status things work against each other. How can I get this query to bring up both of them.
View Replies !
Select Query
However when I echo mysql_num_rows it gives 0 results even though the same date is in the db table. class_date is set as date type in mysql db. PHP Code: $query = "SELECT * ". "FROM class ". "WHERE class_date = $class_date";
View Replies !
|
TRACKER
