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 >

  • Getting mysql row that doesn't conflict with another row

    - by user939951
    I have two tables that link together through an id one is "submit_moderate" and one is "submit_post" The "submit_moderate" table looks like this id moderated_by post 1 James 60 2 Alice 32 3 Tim 18 4 Michael 60 Im using a simple query to get data from the "submit_post" table according to the "submit_moderate" table. $get_posts = mysql_query("SELECT * FROM submit_moderate WHERE moderated_by!='$user'"); $user is the person who is signed in. Now my problem is when I run this query, with the user 'Michael' it will retrieve this 1 James 60 2 Alice 32 3 Tim 18 Now technically this is correct however I don't want to retrieve the first row because 60 is associated with Michael as well as James. Basically I don't want to retrieve that value '60'. I know why this is happening however I can't figure out how to do this. I appreciate any hints or advice I can get.

    Read the article

  • auto_increment in MySQL - can I omit it?

    - by kees-kist
    I've noticed that PHPmyAdmin creates the following SQL for table creation: CREATE TABLE something ( ... ) auto_increment=1; When I write a database creation script I don't use the auto_increment bit. From reading related questions here I understand that it determines the starting value for auto_increment values. But it is good practice to reset it to 1, or should I just leave it out of the SQL so that the default is used?

    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 - 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

  • 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

  • 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

  • rewritten mysql query returning unexpected results, trying to figure out why

    - by dq
    I created a messy query in a hurry a while ago to get a list of product codes. I am now trying to clean up my tables and my code. I recently tried to rewrite the query in order for it to be easier to use and understand. The original query works great, but it requires multiple search strings in order to do one search because it uses UNIONS, and it has a few other issues. My newly modified query is easier to understand, and only requires one search string, but is returning different results. Basically the new query is leaving records out, and I would like to understand why, and how to fix it. Here are the two queries (search strings are all null): Original Query: $query = 'SELECT product_code FROM bus_warehouse_lots WHERE status=\'2\''.$search_string_1 .' UNION SELECT product_code FROM bus_po WHERE status=\'0\''.$search_string_2 .' UNION SELECT bus_warehouse_entries.new_product_code AS product_code FROM (bus_warehouse_entries LEFT JOIN bus_warehouse_transfers ON bus_warehouse_entries.picking_ticket_num=bus_warehouse_transfers.pt_number) LEFT JOIN bus_warehouse_lots ON bus_warehouse_entries.ebooks_lot_id=bus_warehouse_lots.id WHERE bus_warehouse_entries.type=\'6\' AND bus_warehouse_transfers.status=\'0\''.$search_string_3 .' UNION SELECT bus_contracts.main_product AS product_code FROM bus_contracts LEFT JOIN bus_warehouse_lots ON bus_contracts.main_product=bus_warehouse_lots.product_code WHERE bus_contracts.status=\'0\''.$search_string_4 .' UNION SELECT prod_id AS product_code FROM bus_products WHERE last_usage > \''.date('Y-m-d', strtotime('-12 months')).'\''.$search_string_5 .' ORDER BY product_code'; New Query: $query = 'SELECT bus_products.prod_id FROM bus_products' .' LEFT JOIN (bus_warehouse_lots, bus_po, bus_warehouse_entries, bus_contracts) ON (' .'bus_products.prod_id = bus_warehouse_lots.product_code' .' AND bus_products.prod_id = bus_po.product_code' .' AND bus_products.prod_id = bus_warehouse_entries.new_product_code' .' AND bus_products.prod_id = bus_contracts.main_product)' .' LEFT JOIN bus_warehouse_transfers ON' .' bus_warehouse_entries.picking_ticket_num = bus_warehouse_transfers.pt_number' .' WHERE (bus_products.last_usage > \''.date('Y-m-d', strtotime('-12 months')).'\'' .' OR bus_warehouse_lots.status = \'2\'' .' OR bus_po.status = \'0\'' .' OR (bus_warehouse_entries.type = \'6\' AND bus_warehouse_transfers.status = \'0\')' .' OR bus_contracts.status = \'0\')' .$search_string_6 .' GROUP BY bus_products.prod_id' .' ORDER BY bus_products.prod_id';

    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

  • 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

  • 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

  • Strange error from mysql storage engine

    - by zerkms
    General error: 1030 Got error -1 from storage engine the used storage engine is innodb the query was runned when i got it today morning was: SELECT feeds.* FROM feeds ORDER BY RAND() LIMIT 1 i know rand() is bad but it's very small table (<500 records) and not loaded project this error i receive approximately once a day. cannot google anything relevant :-(

    Read the article

  • I'm trying to build a query to search against a fulltext index in mysql

    - by Rockinelle
    The table's schema is pretty simple. I have a child table that stores a customer's information like address and phone number. The columns are user_id, fieldname, fieldvalue and fieldname. So each row will hold one item like phone number, address or email. This is to allow an unlimited number of each type of information for each customer. The people on the phones need to look up these customers quickly as they call into our call center. I have experimented with using LIKE% and I'm working with a FULLTEXT index now. My queries work, but I want to make them more useful because if someone searches for a telephone area code like 805 that will bring up many people, and then they add the name Bill to narrow it down, '805 Bill'. It will show EVERY customer that has 805 OR Bill. I want it to do AND searches across multiple rows within each customer. Currently I'm using the query below to grab the user_ids and later I do another query to fetch all the details for each user to build their complete record. SELECT DISTINCT `user_id` FROM `user_details` WHERE MATCH (`fieldvalue`) AGAINST ('805 Bill') Again, I want to do the above query against groups of rows that belong to a single user, but those users have to match the search keywords. What should I do?

    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

  • mysql statement with nested SELECT - how to improve performance

    - by ernie
    This statement appears inefficient because only one one out of 10 records are selected and only 1 of 100 entries contain comments. What can I do to improve it ? $query = "SELECT A,B,C, (SELECT COUNT(*) FROM comments WHERE comments.nid = header_file.nid) as my_comment_count FROM header_file Where A = 'admin' " edit: I want header records even if no comments are found.

    Read the article

  • Data truncation when retrieving data from MySQL database with prepared statements

    - by KSiimson
    I have a script that retrieves multiple products using prepared statements. Like putting loops into loops, I have prepared statements in prepared statements - so there is a prepared statement for retrieving all products, a prepared statement to retrieve all images for that product, a prepared statement to get all attributes for that products, and so on. This does not work with one MySQLi instance, so I use multiple MySQLi objects that are opened and closed when needed. It usually works fine, but sometimes, especially when displaying multiple products, some data is truncated. For example - MicoLoans becomes MicoLoa. There was an actual spelling mistake here - now when I changed MicoLoans to MicroLoans, the same page displayed MicroLoa... So the same number of characters was truncated from the end. It is sort of consistent where it appears - for example there can be descriptions for 8 products, and description of 1 product is heavily truncated. When I add 9th product, the short description is still truncated for that same product as before. Any ideas?

    Read the article

  • Single Column Multiple Filter In Mysql Intersection

    - by Jeebus
    Here is a table CarID| Attribute | Value 1 | Color | Red 2 | Color | Blue 3 | Color | Red 1 | Type | Coupe 2 | Type | Hatch Back 3 | Type | Coupe 3 | Make | Honda 2 | Make | Toyota 1 | Make | Ford Now I would like to run a filter Like Select * From Cars WHERE (Attribute = Color AND Value = Red) AND (Attribute = Make AND Value = Honda).... and Hope to get the CarID as 3 ! This is simple case of Intersection of 2 queries but I don't know how to get it done in a single query. Any help appriciated.

    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 Inner Join two table over two keys

    - by bertsisterwanda
    I am doing a query to return all users shopping carts, stored in the sb_carts table. The product information stored in sb_carts is referenced over two keys product_sku and school_id. It needs to reference both to return a unique product with unique stock levels etc. When I execute the following query it returns one row, I am expecting 3 rows. I have tried breaking the inner join into two separate joins but this still returns only 1 result. joining only on one key has the desired result, but may be retuning the wrong product. A left join returns 3 rows but some data is missing product specific Here is a simplified example of what I am doing SELECT sb_carts.product_sku FROM sb_carts INNER JOIN sb_products ON sb_products.sku = sb_carts.product_sku AND sb_products.school_id = sb_carts.school_id WHERE sb_carts.order_id = 0 AND sb_carts.user_id = 2 GROUP BY sb_carts.cart_id

    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

  • Mysql stored procedure where clause

    - by Mneva skoko
    I am having a problem with this stored procedure: Delimiter // Create procedure(in varchar(50)) Begin Select * from employees where email = eml; End// Delimiter ; I don't get errors when I run this procedure but when i call it in my php script it returns nothing.

    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

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 >