20 Different Tables Querys Slowing Page Display
I have an issue with a very large PHP page that calls over 20 different MySql Statements. This page is loading somewhat slow and i want to speed things up. I have looked into sql caching applications like http://eaccelerator.net/ and http://www.danga.com/memcached/ which i may install later, but i feel they are more for pages that are called frequently, my page calls the same data less times but can call more than 2000 different versions of the data.
Being that my page is a little over 1000 lines, putting it all here is not gonna happen. I will try to shorten the code using examples... After writing the blow code.. it may be really confusing. I have stuck the file on my server http://www.empiresolutions.net/clien.../green_bar.zip in case anybody whats to get a better look. This file in it current state works fine, just is slow. Any and all help is so appreciated.
Another Question... Can anyone provide suggestions on how to bench-test my sql scripts to see exact improvment speeds?
Each indent means the following query is nested.
Query 1 (2 Join Tables)
Query 2 (2 Join Tables)
Query 3 (1 Table)
Query 4 (4 Join Tables, While Loop)
Query 5 (2 Join Tables)
Query 6 (1 Table)
Query 7 (4 Join Tables, While Loop)
Query 8 (2 Join Tables)
Query 9 (1 Table)
Query 10 (1 Table, While Loop)
Query 11 (2 Join Tables, Calls every 10 rows from Query 10)
Query 12 (2 Join Tables, Calls every 10 rows from Query 10, same as Query 11 basically)
Query 13 (2 Table, While Loop)
Query 14 (1 Table)
Query 15 (2 Join Tables, This query and subs are called 4 times in a FOR loop)
Query 16 (2 Join Tables)
Query 17 (2 Join Tables)
Query 18 (1 Table)
Query 19 (2 Join Tables)
Query 20 (1 Table)
View Complete Forum Thread with Replies
Related Forum Messages:
Display Data Per Page
I have a search form which displays data depending on the user input. User can perform search based on certain ocupation and certain gender. After performing the appropiate 'select' statement, if, for example, you know there are 100 rows retrieved. How would you display these data over 10 different pages? eg: clicking on the search button will display the first 10 rows of data, then clicking on the 'Next Page' button will display the next 10 and so on
View Replies !
Display Random Urls On Web Page
I would like to either go in one direction or the other. 1. Is there a way using php to do a search on the internet using a keyword, collect the top five results, title and description, then display them on my webpage? 2. If i use sql and pre-define 100 urls which are relevant to my keyword, is there a way i can query my database and return 5 random records from the result. I'm just looking for an easy way to keep good content on my pages. What do you guys think?
View Replies !
How To Display MYSQL Data On Index Page
I am totally new to web programming. I have a MYSQL database with fields date(date, field) Celebration (variable) and slogan(variable). It contains the data for which ecah day is celebrated . eg. Date Celebration Slogan 1st Dec. world AIDS day We Care 25th Dec Christmas Merry Christmas I want take put these 'day celebrations' on my index.html page, two days in advance eg. on the 23rd Dec., I should display "Merry Christmas" On the 26th, this should be removed.
View Replies !
Using LIKE Too Much, Slowing My Query Down, Help!
This query is taking too long to run. At about 0.5 seconds right now, and I suspect its because of all the "OR e.category_ids LIKE '%,1480,%'" lines. Does anyone know of a better way to write those? To clarify: e.categoryids holds values like ",1,43,656,12,34," etc. SELECT e.entrytitle, e.entry_id, e.entrydate, e.entrystub, j.journal_id, j.journalauthor, j.journalurl, j.journalname FROM vb_journal_entries e LEFT JOIN vb_journals jON j.journal_id = e.journal_id WHERE e.entry_active = 1 AND j.active = 1 AND j.journalurl != '' AND (1=0 OR e.category_ids LIKE '%,188,%' OR e.category_ids LIKE '%,1480,%' OR e.category_ids LIKE '%,1481,%' OR e.category_ids LIKE '%,1482,%' OR e.category_ids LIKE '%,1492,%' OR e.category_ids LIKE '%,1496,%' OR e.category_ids LIKE '%,1500,%' OR e.category_ids LIKE '%,1537,%' OR e.category_ids LIKE '%,1540,%' OR e.category_ids LIKE '%,1555,%' OR e.category_ids LIKE '%,1559,%' OR e.category_ids LIKE '%,1572,%' OR e.category_ids LIKE '%,1582,%' ) GROUP BY journal_id, entry_id DESC ORDER BY entry_id DESC LIMIT 5
View Replies !
PHP Page Displaying When First Hit, Page Not Found On Refresh.
I have installed MYSQL sucessfully and PHP. I usually code in ASP (I know, I know) & SQL Server (Yes OK). And have recently turned to the light! When I load a page for the first time (On IIS) it looks fine and new data change in the database is displayed and it looks great. When I click a link to another page with links to the DB then I get Page Cannot be found, if I go back to the URL for the site (thus index.php) Refresh and then click the link it works??!
View Replies !
Blank Page In Php Page With Mysql Query
I've installed PHP 5.1.2 manually, along with MySQL 4.1.18. Any php page that contains a mysql function displays a blank page . I read many article on mysql and php configuration and did not find any issue ... I copied the libmysql.dll to the system32 directory I set the extension_dir in php.ini to the extension directory of my PHP installation directory I enabled the MySQL extension in php.ini by uncommenting the line extension=php_mysqli.dll I tested the mysql connection from a dos prompt it worked , i can check ith a show database the list of the databse . But each time i put come synatc mysql in a php page the resultis a blank page . I read that we neee in php5 mysqli.dll but I found only mysql.dll do i have just to renme it..?
View Replies !
Two Querys In One
How can I make this both querys be only one recordset? select distinct grupo from sentient_campos where id_madre = 1 select distinct grupo_madre from sentien_campos where id_madre = 2
View Replies !
Screen Display Of Tables
Sometimes when I view a table using SELECT, the table columns are misaligned. For example, the | delimiter on the last column on the right appears to wrap. Is there a way to make a nice "pretty" tablular display on the screen?
View Replies !
Multiple Tables, One Display
Let's say I have three tables; personid1 class1 date1 personid2 class2 date2 personid3 class3 date 3 I need to display the classes and dates on a page, with the person id =PID in a URL.
View Replies !
Merging Two Querys
I have two query that I I would like to be one, but I can't figure out how to do it. (mysql 3.23) $sql = sprintf ("SELECT task,SUM(hours),login FROM activities WHERE login='%s' group by login",$usuario); $sql2 = sprintf ("SELECT SUM(hours) FROM activities WHERE login='%s' AND month='%s' group by login",$usuario,$getdate);
View Replies !
Multiple Querys
I'm working on a query where I'd like to be able to search one OR two attributes. I've come accorss a few postings that lead me to the code below. It works fine if I put something in both attributes but fails if I leave the second blanc. Can you help? SELECT * FROM books WHERE title LIKE '%$title%' AND title_sub LIKE '%$title_sub%' ORDER_BY print_id (p.s. I would lideally like to make the first attriubute optional. I may also want to add a few more attributes later)
View Replies !
Dates In Querys
I'm returning the month from a datatime stamp in a mysql query using the following: MONTH(theDate) AS tMonth Only problem is, if the month is before november, it doesnt not return the preceeding 0, so for example.. september I only get 9 not 09. Is there any way in the query to define this?
View Replies !
Multiple Querys Into One
How can I reduce multiple querys into 1 I have 1 main select PHP //get all the booking data from the tables, now I need to $cust_sql = mysql_query("SELECT DISTINCT customer.bid, customer.oid, customer.c_name, customer.pnumber, customer.mnumber, customer.party_names, customer.paid, booking.startdate, booking.enddate, booking.cid, booking.resort, booking.villa, booking.vtype, booking.adults, booking.children, booking.infno, booking.outfno, booking.atime, booking.cost FROM customer,booking WHERE customer.bid= '" . $_GET['id'] . "' AND booking.id='" . $_GET['id'] . "'"); if (!$cust_sql) { echo 'An error has occured with the db and is detailed below:<br> ' . mysql_error(); exit; } $row = mysql_fetch_row($cust_sql); I then run a another to return the country name using the booking.cid from the main result set. PHP $country_sql = mysql_query("SELECT DISTINCT country_name FROM country WHERE cid = '".$row[14]."'"); Is there a way to condense the 2 or more queries into the main one.
View Replies !
Version 4 Query Slowing Killing Me
I want to create a query that will select all the rows in table A that dont appear in table B based on a column of ints that appears in both tables. In other words table A has a column of memberIDs and table B has a column of memberIDs. I want to select all the memberIDs from table A that dont appear in table B.
View Replies !
Call Centre Database Slowing Down
I currently have a large database with over 40 tables, it includes a main table for storing referral info, booking tables, job notes and many others. Just recently the database as started to slow down, currently my table are all using the engine format myISAM. I have been reading up on this and it seems that myISAM is one of the quicker db engines, but also i have looked into using innoDB engine. I have a lot of updates going on at the same time from a call centre full of eager workers and i can't have them moaning that it is running slow. If i was to swap of to the innoDB engine i think it would run better than it currently is but wot do u think? also what sort of size to db's usually get up to before they start to slow down? i want to exclude the possibility that SQL statements are causing the problem.
View Replies !
SELECT Data From 3 Tables And Display It As Row
How can i select data for month 04, 05 and 06 from 3 separate tables and display it as three rows. I am trying this but it does not work. SELECT MONTH(click_date) as Month, COUNT(*) as 'count' FROM broadband_clicks where YEAR(click_date)=2007 AND MONTH(click_date)=04 GROUP BY MONTH(click_date) SELECT MONTH(click_date) as Month, COUNT(*) as 'count' FROM broadband_clicks where YEAR(click_date)=2007 AND MONTH(click_date)=05 GROUP BY MONTH(click_date) SELECT MONTH(click_date) as Month, COUNT(*) as 'count' FROM broadband_clicks where YEAR(click_date)=2007 AND MONTH(click_date)=06 GROUP BY MONTH(click_date) so basically i want something like this. Month | count(*) 04 | 1000 05 | 980 06 | 1100
View Replies !
Mysql Shuts Down During Rapid Execution Of Querys
I am using version 4.0.12-nt of MySQL and when I hit the enter key rapidly I can't connect to the database. The result is a message is returned to me from mysql that says I can't connect because the database is down. If I only hit the 'enter' key once (to execute the page that my queries are on(their are four queries on this particular web page)) my queries execute just fine. We have noticed that this only happens when we:
View Replies !
Slowing Brute Force Attack :: Sleep() Call After Failed Connection
Is there an option for mysqld to add a sleep() call after each failed connection request, before reporting to the client (to slow down brute force attack)? I glanced at the manual and there doesn't seem to be one. If there isn't, would it be a good idea? If yes, I could probably submit a patch. This feature should be trivial to implement, I think.
View Replies !
Queries Pr.page
I use php/mysql for website, I am on novice level, - meaning I do not write php classes and such. I keep it real simple, with a simple db, full of records. question is; how many queries is normally run on a complexed site? take DevShed and imagine it was run on mysql, how many queries aprox does it need to get loaded? I am currenlty doing about 10 different queries pr. page to bring out some user details and different records of the user using 3-4 different db-tables.. and I wonder if this is normal, or if it's extremly bad programming / db design..? it loads fast enough, but it just seems alot.
View Replies !
Web Admin Page?
is there a web admin interface that is included when installing MySQL? If so, what is the url (e.g. http://localhost/mysqladmin)?? I am getting into programming in PHP and want to use MySQL on my development machine (they will both be on the same box to run concurrently). It's a Windows machine running IIS 5.0
View Replies !
Front Page
I am currently working on a site with SMF boards, i have downloaded TP (tinyportal) and i am trying to do a php code so all the threads from a certain board will go on the front page,help me with this code?
View Replies !
Navigating On Same Page
I want to pass the value of a variable via a pop-up menu (JS-behavior in MX) to change the title and the content dynamically in two layoutcells on the same page. In MySQL I have a database called E_Interface with one table and three colums: txtID_int (prim. key), title_int, content_int. The query in the recordset is: SELECT * FROM e_interface WHERE txtID_int = colname. As a variable I have set: (NAME)colname (DEFAULT VALUE) 1 (RUN-TIME VALUE) $HTTP_GET_VARS['txtID_int']. When I test the query I get the right row out of the database. I can't get a working php-statement in the pop-up menu.
View Replies !
Page Numeration
I am looking for a script snippet that will write out a number of pages with prev and next. I already have a numeration function, but I have more than 150 pages in my database, and for this the page numbers are all shown : ex: <prev 1 2 3 4 5 6 7 8 9 10 12 ....etc etc you get the picture....until 149 150 next > I would like a snippet that only writes out 10 page numbers at a time ex: <prev 1 2 3 4 5 6 7 8 9 10 next> ..and after clicking 10 or next> the ten next numbers should be shown: <prev 11 12 13 14 15 16 17 18 19 20 next> Anybody out there who has an idea as how to do that? Code:
View Replies !
Multiple Page
1. How to display my record in multiple page?2. How to display sort by word? i means page 1 for record(name) starting with A, page 2 starting with B, and so on
View Replies !
Page Counter
i challenged myself to make a Page Counter (simple i know) but i got an error i can not figure out, it probably something simple like a quote in wrong place, but i cant get it working.... here is my problem: using the code below, i manually enter a number into my MySQL table, and when the page loads, i want the counter to increment, however the PHP program changes the data to "$counu" i think this is because i have set the length of the field to max at 6, and when it increments a string, it changes the t in counter to a u.. dunno... Code:
View Replies !
Use MySQL As Part Of A Web Page
I would be interested in hearing opinions on how easy or difficult it it to use MySQL as part of a Web page. I have no experience with MySQL, but I have experience with MS Access and VB. The project that I'd like to put together is an online ordering facility for videotapes, not too many concurrent users and relatively low volume as these things go. I am also interested in how one would go about integrating the MySQL database with a site constructed with MS FrontPage (2000 or 2002).
View Replies !
Link Keywords To A Page
so my company tasked me with writing an in house search engine. i have to index gigs and gigs of data (~100+) to do just simple keyword searches. the indexing is easy. my problem is i can't think of an efficient way to link the words to the page they appeared on and then store that relation in a database.
View Replies !
Mysql Error Page
Hey would anyone have a script that redirects traffic to a different page if there is a mysql error, or... know where to get one?
View Replies !
Login Page Creation
i am trying to create a login page on my site with user name and password i mainly work in dreamweiver on a mac platform. how can i use the mysql database to store the information and link dreamweiver to get the info i have no idea how mysql works so you have to be patient with me and guide me step by step.
View Replies !
Page Size Increased
I am trying to help search for a solution for the DB crashes for one of the application. So far all I know is that when you look in to the the error file it says " Page Size Increased". Being from a non DB backgroud I have checked the physical memory of the system and it does not seem to be an issue at this time. I am not sure what else could be the reasons for this message. If I can get some ideas I can start looking for the problems in the way the DB is setup.
View Replies !
Start Mysql From A Web Page
I built a script to start the MySql server and I have the script located in my cgi-bin directory. This is my script: /bin/sh -c "cd /u1/mydir/mysql ;./bin/safe_mysqld &" from the command prompt it starts the server but when I try it from my web page I get Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request. I can run other cgi's just fine, I have the permissions set to 755. I hope one of you expert know how to do get around this.
View Replies !
Multi Page Form
Currently I have a form where, when a user logs in, the user info is displayed. if it's a new user, the form is blank and a row in inserted into mysql upon submit. The user is then taken to a confirmation page, and the user can go back and edit the info. I'm guessing that to continue this trend, I need to create multiple php pages for the form: step1.php, step2.php, step3.php.... However, if I wanted to run these forms off of one php page, may be using arrays and a loop... say, if step 1 for ( i; i<lengthloop; i++) loop array [step1][i], I would have to create the row in the database upon login, rather than as an aftermath.
View Replies !
Redirecting A User To A Web Page
I am looking to setup user accounts so that when one particular user logs in, they go to one page, then when another user logs in they go to another page, and so forth. Is there a simple way to do this?
View Replies !
Several Queires On A Single Page
These days there are many websites ran entirely out of databases. Say someone has a table in a database, and each row of that table contains code that makes up a single web-page.(each row has the code for one web-page) Now let's say there is a single web-page that uses several queries to pull out each row of the database I mentioned above seperately. This way a single web-page can display many web-pages. Is this possible? Could a navigation be made to where when a link is clicked the row corresponding to that link could be displayed all by itself? I'm know I can use the ids that correspond to each row in the table, but how would I set the query up that pulls out the row? First how would I only display one row at a time, second how would I set up the query so it's pulls out a different row's id depending on which link in the navigation is clicked? And finaly how would I make a navigation that generates a new link whenever a new row / page is added to the database?
View Replies !
MySQL Page Crashing
I have a simple ASP page, which uses GetRows to display the value of a fieldname within a DB. This page works with a mySQL db I have, which is only 10 records. I have another database (same format), which contains 1.7 million records and doesn't work. The page sits there for a bit, then crashes, displaying a "page cannot be displayed" error. How can I find out what's going on? Where is the problem occuring, etc.
View Replies !
Redirecting Users To A Different Page
I have my tables all built and I have successfully built my first PHP/MySQL logon page. Works great. To start off, this is my test table structure: +----+--------+----------+----------+-------------------+ | id | logged | username | password | urlredirect | +----+--------+----------+----------+-------------------+ | 1 | NULL | user1 | user2 | page1.php | +----+--------+----------+----------+-------------------+ | 2 | NULL | user2 | user2 | page2.php | +----+--------+----------+----------+-------------------+ What I want to do is when user1 logs in, the get redirected to page1.php. When user 2 logs in, they get redirected to page2.php. Does anyone have any expample pages, or know how to do this? I am stuck on how to call the urlredirect column for the specific user in the php login page. I am using MySQL 5.0.17 and PHP 5.1.1 and Dreamweaver MX to develop.
View Replies !
Mysql/php Search Page
I need to make a database with following three items. 1) City Name 2) Country in which city is located in. 3) City's Zip Code That will be the MySQL part. On the php section. I want to make a search.php page in which you can type any of the above and it will return a small table with all of the values. For example. If in my database. there were: San Fransisco, California, 95001 San Jose, California, 95111 new york city, new york, 10111 and in the search.php's search box if I was to type 10111. The results will return me New York City, New Youk, 10111.
View Replies !
Update Page Not Updating
I have a form that appears to work the way it is suppose to until I hit the submit button on the form....then it won't update. I have a form where you key in an ID number, which then takes you to another form with all of the information. You can add info to a couple of fields, which are the ones I want to update to the database, but they are not being updated. If these fields don't have any info in them, should it be update, or should it be insert (or is insert only for new rows in a table) I have had to comment out the following code, or the form was automatically giving me the successfully updated comment. if (!isset($_POST['Submit'])){ I have also commented out the } at the other end of the code. here is the update code I am using. Purchase and Comment are both fields in the database, and are set to not null. These fields are not called to insert in the original form as they are not used at that point. Code:
View Replies !
How Many Is Too Many Queries On The Average Web Page?
I'm just curious... I'm trying to watch how many queries I run in any given page. But I find myself being too conservative sometimes, trying to think of better ways to do things... but then end up getting frustrated. Just looking for some average feedback. In your opinion or experience, how many queries to the DB using PHP is too many queries? I know less is better... i'm just trying to get an idea.
View Replies !
Page Results By Timestamp
I've been racking my brain on this for a couple of hours now, so i'm hoping that maybe someone can give me a hand. I want to show results by timestamp and give an individual page per date. All of the rows are given a timestamp of midnight of the date they were added. I just can't figure how I am to return the results of the query. The query i'm using for the content. PHP $query = "SELECT id,timestamp FROM content ORDER BY timestamp DESC LIMIT $limitvalue, $limit"; The limit and limit value are part of the paging script that I have.
View Replies !
How To Run A Query Without Reloading A Page (PHP)
Lets say I have a lot of toggle buttons (as checkboxes) that I want to automatically update in a table when checked or unchecked, but I don't want to reload the page for each one just to run the query. Is there a way to run the queries without reloading the page? I've been trying to output the PHP code using JavaScript but I'm not having much luck.
View Replies !
Simple Web Page Counter
Web host uses MySQL 4.0.25 I am looking to make a very simple webpage counter for my site. It must count, on a monthly basis, the number of times a page has been viewed. What is the best way of doing this? NB The queries have not been tested - just ideas at the moment OPTION 1 ======== Create a table that has just 1 field - a datetime CREATE TABLE page_counter page_access DATETIME , INDEX (page_access) then, when a page is accessed, run the query INSERT INTO page_counter (page_access) VALUES (NOW()) and to view the count of page views for a give month/year, run the query SELECT COUNT(*) FROM page_counter WHERE YEAR(page_access) = YEAR(NOW()) AND MONTH(page_access) = MONTH(NOW()) OPTION 2 ======== [code] CREATE TABLE page_counter page_access_year INT , page_access_month INT , INDEX (page_access_year, page_access_month) [code] then when a page is accessed, run the query INSERT INTO page_counter (page_access_year, page_access_month) VALUES (YEAR(NOW(), MONTH(NOW()) and to view the count of page views for a give month/year, run the query SELECT COUNT(*) FROM page_counter WHERE page_access_year = YEAR(NOW()) AND page_access_month = MONTH(NOW()) OPTION 3 ========
View Replies !
Building A 1 Page Site?
I have a couple of review site and I'm thinking of building a new one, but along the lines of a 1 page (well, maybe 2 or 3) site. I think the database is pretty basic and the queries dead simple (okay, except for the "I'm thinking about")*, but I'm concerned about 2 things. 1 - site simplicity and fast page loads 2 - some duplicate content The reviews will have 2 separate text fields. One will be a description written by me .. unique text. Another will be a review = customer comments supplied by an affiliate .. duplicate content. Each text field (description and review) will be up to 10k in size. The database records will also have fields for : name, image link, affiliate link, and a few more .. very brief. Probably a total of 10 fields. What Im wondering: 1 - what problems should I watch out for, if any, by adding this much text. *I'm thinking about making the first query show up to 10 or 20 items with just maybe the first 30 or 40 words of each text field plus a thumbnail; and then a link in each that would produce a single page with the full text and larger image. 2 - I'm a little concerned that I may get a duplicate content penalty. Will having it in a database help? I'm pretty sure I could put the 'echo' in an iframe and the iframe in a directory and exclude SE bots from that directory with robots.text .. does that make sense? Finally, there are hundreds of items so I plan on breaking them into categories to keep the menus a reasonable length .. and using javascript to to show an *items* menu when the *category* menu is selected. So should I go ahead and use AJAX to query the database?
View Replies !
Creating A Search Page
When creating a search page that searches all the tables in a database, the user should get back a list of records found. However, the tables have different structures. One table might have Article Title, Author, Article Text; another table will have Video Title, Video Filename, Video Format, Video Length. Some tables will have more columns of records, some less. So, when you create a search page that searches all the tables in a database, how do you display the info in the results in a consistent and meaningful way? (By meaningful, I mean, returning just the article title will not help the user to know if it's a title of a video or an article.)
View Replies !
|
TRACKER
