Search Results

Search found 20931 results on 838 pages for 'mysql insert'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 180/838 | < Previous Page | 176 177 178 179 180 181 182 183 184 185 186 187  | Next Page >

  • Can this MySQL subquery be optimised?

    - by Dan
    I have two tables, news and news_views. Every time an article is viewed, the news id, IP address and date is recorded in news_views. I'm using a query with a subquery to fetch the most viewed titles from news, by getting the total count of views in the last 24 hours for each one. It works fine except that it takes between 5-10 seconds to run, presumably because there's hundreds of thousands of rows in news_views and it has to go through the entire table before it can finish. The query is as follows, is there any way at all it can be improved? SELECT n.title , nv.views FROM news n LEFT JOIN ( SELECT news_id , count( DISTINCT ip ) AS views FROM news_views WHERE datetime >= SUBDATE(now(), INTERVAL 24 HOUR) GROUP BY news_id ) AS nv ON nv.news_id = n.id ORDER BY views DESC LIMIT 15

    Read the article

  • MySql too many connections

    - by MichaelMcCabe
    I hate to bring up a question which is widely asked on the web, but I cant seem to solve it. I started a project a while back and after a month of testing, I hit a "Too many connections" error. I looked into it, and "Solved" it by increasing the max_connections. This then worked. Since then more and more people started to use it, and it hit again. When I am the only user on the site, i type "show processlist" and it comes up with about 50 connections which are still open (saying "Sleep" in the command). Now, I dont know enough to speculate why these are open, but in my code I tripple checked and every connection I open, I close. ie. public int getSiteIdFromName(String name, String company)throws DataAccessException,java.sql.SQLException{ Connection conn = this.getSession().connection(); Statement smt = conn.createStatement(); ResultSet rs=null; String query="SELECT id FROM site WHERE name='"+name+"' and company_id='"+company+"'"; rs=smt.executeQuery(query); rs.next(); int id=rs.getInt("id"); rs.close(); smt.close(); conn.close(); return id; } Every time I do something else on the site, another load of connections are opened and not closed. Is there something wrong with my code? and if not, what could be the problem?

    Read the article

  • MySQL query killing my server

    - by Webnet
    Looking at this query there's got to be something bogging it down that I'm not noticing. I ran it for 7 minutes and it only updated 2 rows. //set product count for makes $tru->query->run(array( 'name' => 'get-make-list', 'sql' => 'SELECT id, name FROM vehicle_make', 'connection' => 'core' )); while($tempMake = $tru->query->getArray('get-make-list')) { $tru->query->run(array( 'name' => 'update-product-count', 'sql' => 'UPDATE vehicle_make SET product_count = ( SELECT COUNT(product_id) FROM taxonomy_master WHERE v_id IN ( SELECT id FROM vehicle_catalog WHERE make_id = '.$tempMake['id'].' ) ) WHERE id = '.$tempMake['id'], 'connection' => 'core' )); } I'm sure this query can be optimized to perform better, but I can't think of how to do it. vehicle_make = 45 rows taxonomy_master = 11,223 rows vehicle_catalog = 5,108 rows All tables have appropriate indexes

    Read the article

  • MySQL & PHP Use of Undefined Constant

    - by Nik
    Alright, PHP is throwing this error (only in the logs): Error PHP Notice: Use of undefined constant department - assumed 'department' (line 5) PHP Notice: Use of undefined constant name - assumed 'name' (line 6) PHP Notice: Use of undefined constant email - assumed 'email' (line 7) PHP Notice: Use of undefined constant message - assumed 'message' (line 8) Lines 4-7 $department = mysql_real_escape_string($_POST[department]); $name = mysql_real_escape_string($_POST[name]); $email = mysql_real_escape_string($_POST[email]); $message = mysql_real_escape_string($_POST[message]); I think it has something to do with defining constants before defining them (how is this possible).

    Read the article

  • PHP mySQL - select unique value that not being used from dirrefent table

    - by apis17
    Updates : Please see below i have table: data +-----------------------+--------------+-----------+ | State | d_country | d_postcode| +-----------------------+--------------+-----------+ | State1 | Country1 | 1111 | | State2 | Country2 | 2222 | | State3 | Country3 | 3333 | | State4 | Country4 | 4444 | +-----------------------+--------------+-----------+ And another table: user +-----------------------+--------------+-----------+ | Name | u_country | u_postcode| +-----------------------+--------------+-----------+ | Name1 | Country3 | 3333 | | Name2 | Country5 | 5555 | | Name3 | | 6666 | | Name4 | Country6 | 6666 | | Name5 | Country6 | 6666 | +-----------------------+--------------+-----------+ What SQL should i use to: Determine the number (count) of country that are not listed on table data. For example u_postcode is not listed in d_postcode is 5555 and 6666. It will return 2. List down name and what country not available in table data yet. Updates I want to use grouping to filter postcode and make Name3 and Name4 as different rows. For example: +-----------------------+--------------+-----------+ | Name | u_country | u_postcode| +-----------------------+--------------+-----------+ | Name2 | Country5 | 5555 | | Name3 | | 6666 | | Name4 | Country6 | 6666 | +-----------------------+--------------+-----------+ Any possible idea?

    Read the article

  • Mysql Database Question about Large Columns

    - by murat
    Hi, I have a table that has 100.000 rows, and soon it will be doubled. The size of the database is currently 5 gb and most of them goes to one particular column, which is a text column for PDF files. We expect to have 20-30 GB or maybe 50 gb database after couple of month and this system will be used frequently. I have couple of questions regarding with this setup 1-) We are using innodb on every table, including users table etc. Is it better to use myisam on this table, where we store text version of the PDF files? (from memory usage /performance perspective) 2-) We use Sphinx for searching, however the data must be retrieved for highlighting. Highlighting is done via sphinx API but still we need to retrieve 10 rows in order to send it to Sphinx again. This 10 rows may allocate 50 mb memory, which is quite large. So I am planning to split these PDF files into chunks of 5 pages in the database, so these 100.000 rows will be around 3-4 million rows and couple of month later, instead of having 300.000-350.000 rows, we'll have 10 million rows to store text version of these PDF files. However, we will retrieve less pages, so again instead of retrieving 400 pages to send Sphinx for highlighting, we can retrieve 5 pages and it will have a big impact on the performance. Currently, when we search a term and retrieve PDF files that have more than 100 pages, the execution time is 0.3-0.35 seconds, however if we retrieve PDF files that have less than 5 pages, the execution time reduces to 0.06 seconds, and it also uses less memory. Do you think, this is a good trade-off? We will have million of rows instead of having 100k-200k rows but it will save memory and improve the performance. Is it a good approach to solve this problem and do you have any ideas how to overcome this problem? The text version of the data is used only for indexing and highlighting. So, we are very flexible. Thanks,

    Read the article

  • How to load the rest of the data in mysql in php

    - by user225269
    I have trouble figuring out how do I load the rest of the data in html. Here is my code: <?php $con = mysql_connect("localhost","root","nitoryolai123$%^"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("school", $con); $result = mysql_query("SELECT * FROM student WHERE IDNO='$_GET['id']'"); ?> <?php while ( $row = mysql_fetch_array($result) ) { ?> <table class="a" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#D3D3D3"> <tr> <form name="formcheck" method="post" action="updateact.php" onsubmit="return formCheck(this);"> <td> <table border="0" cellpadding="3" cellspacing="1" bgcolor=""> <tr> <td colspan="16" height="25" style="background:#5C915C; color:white; border:white 1px solid; text-align: left"><strong><font size="2">Update Students</td> <tr> <td width="30" height="35"><font size="2">*I D Number:</td> <td width="30"><input name="idnum" onkeypress="return isNumberKey(event)" type="text" maxlength="5" id='numbers'/ value="<?php echo $_GET['id']; ?>"></td> </tr> <tr> <td width="30" height="35"><font size="2">*Year:</td> <td width="30"><input name="yr" onkeypress="return isNumberKey(event)" type="text" maxlength="5" id='numbers'/ value="<?php echo $row["YEAR"]; ?>"></td> this is the part where I am getting the error: mysql_select_db("school", $con); $result = mysql_query("SELECT * FROM student WHERE IDNO='$_GET['id']'"); ? What do you recommend that I could do so that I can also load the ID Numbers corresponding data into the other forms?

    Read the article

  • MySql left join on several regs

    - by egidiocs
    Hi there! I have this table1 idproduct(PK) | date_to_go 1 2010-01-18 2 2010-02-01 3 2010-02-21 4 2010-02-03 and this other table2 that controls date_to_go updates id | idproduct(FK) | prev_date_to_go | date_to_go | update_date 1 1 2010-01-01 2010-01-05 2009-12-01 2 1 2010-01-05 2010-01-10 2009-12-20 3 1 2010-01-10 2010-01-18 2009-12-20 4 3 2010-01-20 2010-02-03 2010-01-05 So, in this example, for table1.idproduct #1 2010-01-18 is the actual date_to_go and 2010-01-01 (table2.prev_date_to_go, first reg) is the original date_to_go . using this query select v.idproduct, v.date_to_go, p.prev_date_to_go original_date_to_go from table1 v left join produto_datas p on p.idproduto = v.idproduto group by (v.idproduto) order by v.idproduto can I assume that original_date_to_go will be the first related reg of table2? idproduct | date_to_go | original_date_to_go 1 2010-01-18 2010-01-01 2 2010-02-01 NULL 3 2010-02-21 2010-01-20 4 2010-02-03 NULL

    Read the article

  • mysql - speedup regex

    - by Uwe
    I have a table: +--------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+----------------+ | idurl | int(11) | NO | PRI | NULL | auto_increment | | idsite | int(10) unsigned | NO | MUL | NULL | | | url | varchar(2048) | NO | | NULL | | +--------+------------------+------+-----+---------+----------------+ the select statement is: SELECT idurl, url FROM URL WHERE idsite = 34 AND url REGEXP '^https\\://www\\.domain\\.com/checkout/step_one\\.php.*' The query needs 5 seconds on a table with 1000000 rows. Can I achieve a speedup with indexes or something else?

    Read the article

  • How to match multiple field in mySQL

    - by Mint
    Im trying to match forum_id with several different forum_id's, something like forum_id = 5,7,12,43,63,78 I currently have this code: SELECT topic_title, topic_id, forum_id FROM $MYSQL_TOPIC WHERE topic_title LIKE '%%%s%%' AND forum_id = 5 LIMIT 50 (using mysql_real_escape_string and sprintf) I tried: forum_id = 5 OR 7 forum_id = 5|7 forum_id = 5 AND 7 forum_id = 5 & 7 But none of them will match them all and therefore search though them all.

    Read the article

  • Caching Mysql database for better performance

    - by kobey
    Hi, I'm using Amazon cloud and I've performance issue since the HDD is not located on my machine. My database is small (~500MB) and I can afford to keep it all in my RAM. I do not want to keep queries in my RAM, i need all the tables there. How can i do it? Thanks, Koby P.S. I'm using ubuntu server...

    Read the article

  • mysql whats wrong with this query?

    - by Hailwood
    I'm trying to write a query that selects from four tables campaignSentParent csp campaignSentEmail cse campaignSentFax csf campaignSentSms css Each of the cse, csf, and css tables are linked to the csp table by csp.id = (cse/csf/css).parentId The csp table has a column called campaignId, What I want to do is end up with rows that look like: | id | dateSent | emailsSent | faxsSent | smssSent | | 1 | 2011-02-04 | 139 | 129 | 140 | But instead I end up with a row that looks like: | 1 | 2011-02-03 | 2510340 | 2510340 | 2510340 | Here is the query I am trying SELECT csp.id id, csp.dateSent dateSent, COUNT(cse.parentId) emailsSent, COUNT(csf.parentId) faxsSent, COUNT(css.parentId) smsSent FROM campaignSentParent csp, campaignSentEmail cse, campaignSentFax csf, campaignSentSms css WHERE csp.campaignId = 1 AND csf.parentId = csp.id AND cse.parentId = csp.id AND css.parentId = csp.id; Adding GROUP BY did not help, so I am posting the create statements. csp CREATE TABLE `campaignsentparent` ( `id` int(11) NOT NULL AUTO_INCREMENT, `campaignId` int(11) NOT NULL, `dateSent` datetime NOT NULL, `account` int(11) NOT NULL, `status` varchar(15) NOT NULL DEFAULT 'Creating', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 cse/csf (same structure, different names) CREATE TABLE `campaignsentemail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parentId` int(11) NOT NULL, `contactId` int(11) NOT NULL, `content` text, `subject` text, `status` varchar(15) DEFAULT 'Pending', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=140 DEFAULT CHARSET=latin1 css CREATE TABLE `campaignsentsms` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parentId` int(11) NOT NULL, `contactId` int(11) NOT NULL, `content` text, `status` varchar(15) DEFAULT 'Pending', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=141 DEFAULT CHARSET=latin1

    Read the article

  • How to model parent to child pair in MySQL (SQL)

    - by mikeschuld
    I have a data model that includes element types Stage, Actor, and Form. Logically, Stages can be assigned pairs of ( Form <--- Actor ) which can be duplicated many times (i.e. same person and same form added to the same stage at a later date/time). Right now I am modeling this with these tables: Stage Form Actor Form_Actor _______________ |Id | |FormId | --> Id in Form |ActorId | --> Id in Actor Stage_FormActor __________________ |Id | |StageId | --> Id in Stage |FormActorId | --> Id in Form_Actor I am using CodeSmith to generate the data layer for this setup and none of the templates really know how to handle this type of relationship correctly when generating classes. Ideally, the ORM would have Stage.FormActors where FormActor would be the pair Form, Actor. Is this the correct way to model these relationships. I have tried using all three Ids in one table as well Stage_Form_Actor ______________ |Id | |StageId | --> Id in Stage |FormId | --> Id in Form |ActorId | --> Id in Actor This doesn't really get generated very well either. Ideas?

    Read the article

  • Specific query in Mysql

    - by Radhe
    I have two tables reports and holidays. reports: (username varchar(30),activity varchar(30),hours int(3),report_date date) holidays: (holiday_name varchar(30), holiday_date date) select * from reports gives +----------+-----------+---------+------------+ | username | activity | hours | date | +----------+-----------+---------+------------+ | prasoon | testing | 3 | 2009-01-01 | | prasoon | coding | 4 | 2009-01-03 | | prasoon | designing| 2 | 2009-01-04 | | prasoon | coding | 4 | 2009-01-06 | +----------+-----------+---------+------------+ select * from holidays gives +--------------+---------------+ | holiday_name | holiday_date | +--------------+---------------+ | Diwali | 2009-01-02 | | Holi | 2009-01-05 | +--------------+---------------+ Is there any way by which I can output the following? +-------------+-----------+---------+-------------------+ | date | activity | hours | holiday_name | +-------------+-----------+---------+-------------------+ | 2009-01-01 | testing | 3 | | | 2009-01-02 | | | Diwali | | 2009-01-03 | coding | 4 | | | 2009-01-04 | designing| 2 | | | 2009-01-05 | | | Holi | | 2009-01-06 | coding | 4 | | +-------------+-----------+---------+-------------------+

    Read the article

  • MySQL GROUP_CONCAT + IN() = missing data :-(

    - by Andrew Heath
    Example: Table: box boxID color 01 red 02 blue 03 green Table: boxHas boxID has 01 apple 01 pear 01 grapes 01 banana 02 lime 02 apple 02 pear 03 chihuahua 03 nachos 03 baby crocodile I want to query on the contents of each box, and return a table with each ID, color, and a column that concatenates the contents of each box, so I use: SELECT box.boxID, box.color, GROUP_CONCAT(DISTINCT boxHas.has SEPARATOR ", ") AS contents FROM box LEFT JOIN boxHas ON box.boxID=boxHas.boxID WHERE boxHas.has IN ('apple','pear') GROUP BY box.boxID ORDER BY box.boxID and I get the following table of results: boxID color contents 01 red apple, pear 02 blue apple, pear My question to you is: why isn't it listing ALL the has values in the contents column? Why is my WHERE statement also cropping my GROUP_CONCAT? The table I thought I was going to get is: boxID color contents 01 red apple, banana, grapes, pear 02 blue apple, lime, pear Although I want to limit my boxID results based upon the WHERE statement, I do not want to limit the contents field for valid boxes. :-/ Help?

    Read the article

  • mysql - joining three tables with HAVING

    - by Qiao
    I have table: id name type where "type" is 1 or 2 I need to join this table with two other. Rows with "type = 1" should be joined with first table, and =2 with second. Something like SELECT * FROM tbl INNER JOIN tbl_1 ON tbl.name = tbl_1.name HAVING tbl.type = 1 INNER JOIN tbl_2 ON tbl.name = tbl_2.name HAVING tbl.type = 2 But it does not working. How it can be implemented?

    Read the article

  • MySQL Select statement Where table1.id != table2.id

    - by Michael
    I have a table of data which has posts, then I have a separate table of data which has deleted posts. What happens when a post is deleted is that it's ID get's added to the deleted table rather than removing the post entry. What is a clean efficient way of selecting all the posts from the posts table without selecting the ones that have their ID in the deleted table

    Read the article

  • mysql left outer join

    - by tirso
    hi to all I have two tables employee and timecard, employee table has fields employee_id,firstname,middlename,lastname and timecard table has fields employee_id,time-in,time-out,tc_date_transaction. I want to select all employee records which have the same employee_id with timecard and date is equal with the current date. If there are no records equal with the current date then return also the records of employee even without time-in,timeout and tc_date_transaction. I have query like this SELECT * FROM employee LEFT OUTER JOIN timecard ON employee.employee_id = timecard.employee_id WHERE tc_date_transaction = "17/06/2010"; result should like this: employee_id,firstname, middlename, lastname,time-in,time-out,tc_date_transaction 1,john,t,cruz,08:00,05:00,17/06/2010 2,mary,j,von,null,null,null any help would greatly appreciated Thanks in advance

    Read the article

  • Select nth percentile from MySQL

    - by mazin k.
    I have a simple table of data, and I'd like to select the row that's at about the 40th percentile from the query. I can do this right now by first querying to find the number of rows and then running another query that sorts and selects the nth row: select count(*) as `total` from mydata; select * from mydata order by `field` asc limit 37,1; Can I combine these two queries into a single query?

    Read the article

  • Mysql select - improve performance

    - by realshadow
    Hey, I am working on an e-shop which sells products only via loans. I display 10 products per page in any category, each product has 3 different price tags - 3 different loan types. Everything went pretty well during testing time, query execution time was perfect, but today when transfered the changes to the production server, the site "collapsed" in about 2 minutes. The query that is used to select loan types sometimes hangs for ~10 seconds and it happens frequently and thus it cant keep up and its hella slow. The table that is used to store the data has approximately 2 milion records and each select looks like this: SELECT * FROM products_loans WHERE KOD IN("X17/Q30-10", "X17/12", "X17/5-24") AND 369.27 BETWEEN CENA_OD AND CENA_DO; 3 loan types and the price that needs to be in range between CENA_OD and CENA_DO, thus 3 rows are returned. But since I need to display 10 products per page, I need to run it trough a modified select using OR, since I didnt find any other solution to this. I have asked about it here, but got no answer. As mentioned in the referencing post, this has to be done separately since there is no column that could be used in a join (except of course price and code, but that ended very, very badly). Here is the show create table, kod and CENA_OD/CENA_DO very indexed via INDEX. CREATE TABLE `products_loans` ( `KOEF_ID` bigint(20) NOT NULL, `KOD` varchar(30) NOT NULL, `AKONTACIA` int(11) NOT NULL, `POCET_SPLATOK` int(11) NOT NULL, `koeficient` decimal(10,2) NOT NULL default '0.00', `CENA_OD` decimal(10,2) default NULL, `CENA_DO` decimal(10,2) default NULL, `PREDAJNA_CENA` decimal(10,2) default NULL, `AKONTACIA_SUMA` decimal(10,2) default NULL, `TYP_VYHODY` varchar(4) default NULL, `stage` smallint(6) NOT NULL default '1', PRIMARY KEY (`KOEF_ID`), KEY `CENA_OD` (`CENA_OD`), KEY `CENA_DO` (`CENA_DO`), KEY `KOD` (`KOD`), KEY `stage` (`stage`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 And also selecting all loan types and later filtering them trough php doesnt work good, since each type has over 50k records and the select takes too much time as well... Any ides about improving the speed are appreciated.

    Read the article

  • Import CSV to mysql

    - by 404error
    I have created a database and table. I have also created all the fields i will be needing. I have created 46 fields including 1 that is my ID for the row. The CSV doesn't contain the id field, nor does it contain the headers for the columns. I am new to all of this but have been trying to figure this out. I'm not on here being lazy asking for the answer, but looking for direction. I'm trying to figure out how to import the CSV but have it start importing data starting at the 2nd field, since I'm hoping the auto_increment will fill in the ID field, which is the first field i created. I tried these instructions with now luck. Can anyone offer some insight? your cvs file's column name must match your table column name browse your required .csv file select CSV using LOAD DATA options Check box 'ON' for Replace table data with file in Fields terminated by box type , in Fields enclosed by box " in Fields escaped by box \ in Lines terminated by box auto in Column names box type column name seperated by , like column1,column2,column3 10 check box ON for Use LOCAL keyword.

    Read the article

  • MYSQL join - reference external field from nested select?

    - by PHP thinker
    Is it allowed to reference external field from nested select? E.g. SELECT FROM ext1 LEFT JOIN (SELECT * FROM int2 WHERE int2.id = ext1.some_id ) as x ON 1=1 in this case, this is referencing ext1.some_id in nested select. I am getting errors in this case that field ext1.some_id is unknow. Is it possible? Is there some other way?

    Read the article

  • mysql to get depth of record, count parent and ancestor records

    - by Nate
    Hey All, Say I have a post table containing the fields post_id and parent_post_id. I want to return every record in the post table with a count of the "depth" of the post. By depth, I mean, how many parent and ancestor records exist. Take this data for example... post_id parent_post_id ------- -------------- 1 null 2 1 3 1 4 2 5 4 The data represents this hierarchy... 1 |_ 2 | |_ 4 | |_ 5 |_ 3 The result of the query should be... post_id depth ------- ----- 1 0 2 1 3 1 4 2 5 3 Thanks in advance!

    Read the article

  • mySQL Query JOIN in same table

    - by jeerose
    Table structure goes something like this: Table: Purchasers Columns: id | organization | city | state Table: Events Columns: id | purchaser_id My query: SELECT purchasers.*, events.id AS event_id FROM purchasers INNER JOIN events ON events.purchaser_id = purchasers.id WHERE purchasers.id = '$id' What I would like to do, is obviously to select entries by their id from the purchasers table and join from events. That's the easy part. I can also easily to another query to get other purchasers with the same organization, city and state (there are multiples) but I'd like to do it all in the same query. Is there a way I can do this? In short, grab purchasers by their ID but then also select other purchasers that have the same organization, city and state. Thanks.

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 176 177 178 179 180 181 182 183 184 185 186 187  | Next Page >