Search Results

Search found 14874 results on 595 pages for 'mysql connector'.

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 173/595 | < Previous Page | 169 170 171 172 173 174 175 176 177 178 179 180  | Next Page >

  • Row selection based on subtable data in MySQL

    - by Felthragar
    I've been struggling with this selection for a while now, I've been searching around StackOverflow and tried a bunch of stuff but nothing that helps me with my particular issue. Maybe I'm just missing something obvious. I have two tables: Measurements, MeasurementFlags "Measurements" contain measurements from a device, and each measurement can have properties/attributes attached to them (commonly known as "flags") to signify that the measurement in question is special in some way or another (for instance, one flag may signify a test or calibration measurement). Note: One record per flag! Right, so a record from the "Measurements" table can theoreticly have an unlimited amount of MeasurementFlags attached to it, or it can have none. Now, I need to select records from "Measurements", that have an attached "MeasurementFlag" valued "X", but it must also NOT have a flag valued "Y" attached to it. We're talking about a fairly large database with hundreds of millions of rows, which is why I'm trying to keep all of this logic within one query. Splitting it up would create too many queries, however if it's not possible to do in one query I guess I don't have a choise. Thanks in advance.

    Read the article

  • PHP - Retrieve Data From mySQL Server

    - by Kevin
    Hello, Does anyone know how to retrieve a piece of data and display the results in php file? A similar query that I would enter is something like this: SELECT 'email' FROM 'users' WHERE 'username' = 'bob' Thus, the result would be just the email. Thanks, Kevin

    Read the article

  • ORDER BY column_name help (via link in HTML table view) (PHP MySQL

    - by Derek
    My output for my table in HTML has several columns such as userid, name, age, dob. The table heading is simply the title of the column name, I want this to be a link, and when clicked, the selected column is sorted in order, ASC, and then DESC (on next click). I thought this was pretty straight forward but I'm having some difficulty. So far, I have produced this, and no output is taken, apart from the URL works by displaying 'users.php?orderby=userid' <?php if(isset($_GET['orderby'])){ $orderby = $_GET['orderby']; $query_sv = "SELECT * FROM users BY ".mysql_real_escape_string($orderby)." ASC"; } //default query else{ $query_sv = "SELECT * FROM users BY user_id DESC"; } ?> <tr> <th><a href="<?php echo $_SERVER['php_SELF']."?orderby=userid";?>">User ID</a></th> Hoefully if I get this working, I can sort the users by D.O.B. next also using the same principles. Does anyone have any ideas?

    Read the article

  • Membership with Mysql, EF 1 and ASP.NET 3.5

    - by sanfra1983
    Hi, I created a web application with asp.net 3.5 and ado.net entity framework WebForms 1, but have not yet succeeded in creating a memebrship and roles. When I go on ASP.NET Configuration and click the Security Tab I get the following error: Keyword not supported. Parameter name: metadata Someone has already created an application with these same features to help me understand where is the problem? P.S.: I'm going crazy Thanks to all

    Read the article

  • mysql data type confusion

    - by zen
    So this is more of a generalized question about MySQLs data types. I'd like to store a 5-digit US zip code (zip_code) properly in this example. A county has 10 different cities and 5 different zip codes. city | zip code -------+---------- city 0 | 33333 city 1 | 11111 city 2 | 22222 city 3 | 33333 city 4 | 44444 city 5 | 55555 city 6 | 33333 city 7 | 33333 city 8 | 44444 city 9 | 22222 I would typically structure a table like this as varchar(50), int(5) and not think twice about it. (1) If we wanted to ensure that this table had only one of 5 different zip codes we should use the enum data type, right? Now think of a similar scenario on a much larger scale. In a state, there are five-hundred cities with 418 different zip codes. (2) Should I store 418 zip codes as an enum data type OR as an int and create another table to reference?

    Read the article

  • Mysql - help me optimize this query (improved question)

    - by sandeepan-nath
    About the system: - There are tutors who create classes and packs - A tags based search approach is being followed.Tag relations are created when new tutors register and when tutors create packs (this makes tutors and packs searcheable). For details please check the section How tags work in this system? below. Following is the concerned query SELECT SUM(DISTINCT( t.tag LIKE "%Dictatorship%" )) AS key_1_total_matches, SUM(DISTINCT( t.tag LIKE "%democracy%" )) AS key_2_total_matches, COUNT(DISTINCT( od.id_od )) AS tutor_popularity, CASE WHEN ( IF(( wc.id_wc > 0 ), ( wc.wc_api_status = 1 AND wc.wc_type = 0 AND wc.class_date > '2010-06-01 22:00:56' AND wccp.status = 1 AND ( wccp.country_code = 'IE' OR wccp.country_code IN ( 'INT' ) ) ), 0) ) THEN 1 ELSE 0 END AS 'classes_published', CASE WHEN ( IF(( lp.id_lp > 0 ), ( lp.id_status = 1 AND lp.published = 1 AND lpcp.status = 1 AND ( lpcp.country_code = 'IE' OR lpcp.country_code IN ( 'INT' ) ) ), 0) ) THEN 1 ELSE 0 END AS 'packs_published', td . *, u . * FROM tutor_details AS td JOIN users AS u ON u.id_user = td.id_user LEFT JOIN learning_packs_tag_relations AS lptagrels ON td.id_tutor = lptagrels.id_tutor LEFT JOIN learning_packs AS lp ON lptagrels.id_lp = lp.id_lp LEFT JOIN learning_packs_categories AS lpc ON lpc.id_lp_cat = lp.id_lp_cat LEFT JOIN learning_packs_categories AS lpcp ON lpcp.id_lp_cat = lpc.id_parent LEFT JOIN learning_pack_content AS lpct ON ( lp.id_lp = lpct.id_lp ) LEFT JOIN webclasses_tag_relations AS wtagrels ON td.id_tutor = wtagrels.id_tutor LEFT JOIN webclasses AS wc ON wtagrels.id_wc = wc.id_wc LEFT JOIN learning_packs_categories AS wcc ON wcc.id_lp_cat = wc.id_wp_cat LEFT JOIN learning_packs_categories AS wccp ON wccp.id_lp_cat = wcc.id_parent LEFT JOIN order_details AS od ON td.id_tutor = od.id_author LEFT JOIN orders AS o ON od.id_order = o.id_order LEFT JOIN tutors_tag_relations AS ttagrels ON td.id_tutor = ttagrels.id_tutor JOIN tags AS t ON ( t.id_tag = ttagrels.id_tag ) OR ( t.id_tag = lptagrels.id_tag ) OR ( t.id_tag = wtagrels.id_tag ) WHERE ( u.country = 'IE' OR u.country IN ( 'INT' ) ) AND CASE WHEN ( ( t.id_tag = lptagrels.id_tag ) AND ( lp.id_lp 0 ) ) THEN lp.id_status = 1 AND lp.published = 1 AND lpcp.status = 1 AND ( lpcp.country_code = 'IE' OR lpcp.country_code IN ( 'INT' ) ) ELSE 1 END AND CASE WHEN ( ( t.id_tag = wtagrels.id_tag ) AND ( wc.id_wc 0 ) ) THEN wc.wc_api_status = 1 AND wc.wc_type = 0 AND wc.class_date '2010-06-01 22:00:56' AND wccp.status = 1 AND ( wccp.country_code = 'IE' OR wccp.country_code IN ( 'INT' ) ) ELSE 1 END AND CASE WHEN ( od.id_od 0 ) THEN od.id_author = td.id_tutor AND o.order_status = 'paid' AND CASE WHEN ( od.id_wc 0 ) THEN od.can_attend_class = 1 ELSE 1 END ELSE 1 END GROUP BY td.id_tutor HAVING key_1_total_matches = 1 AND key_2_total_matches = 1 ORDER BY tutor_popularity DESC, u.surname ASC, u.name ASC LIMIT 0, 20 The problem The results returned by the above query are correct (AND logic working as per expectation), but the time taken by the query rises alarmingly for heavier data and for the current data I have it is like 25 seconds as against normal query timings of the order of 0.005 - 0.0002 seconds, which makes it totally unusable. It is possible that some of the delay is being caused because all the possible fields have not yet been indexed. The tag field of tags table is indexed. Is there something faulty with the query? What can be the reason behind 20+ seconds of execution time? How tags work in this system? When a tutor registers, tags are entered and tag relations are created with respect to tutor's details like name, surname etc. When a Tutors create packs, again tags are entered and tag relations are created with respect to pack's details like pack name, description etc. tag relations for tutors stored in tutors_tag_relations and those for packs stored in learning_packs_tag_relations. All individual tags are stored in tags table. The explain query output:- Please see this screenshot - http://www.test.examvillage.com/Explain_query.jpg

    Read the article

  • MySQL: Column Contains Word From List of Words

    - by mellowsoon
    I have a list of words. Lets say they are 'Apple', 'Orange', and 'Pear'. I have rows in the database like this: ------------------------------------------------ |author_id | content | ------------------------------------------------ | 54 | I ate an apple for breakfast. | | 63 | Going to the store. | | 12 | Should I wear the orange shirt? | ------------------------------------------------ I'm looking for a query on an InnoDB table that will return the 1st and 3rd row, because the content column contains one or more words from my list. I know I could query the table once for each word in my list, and use LIKE and the % wildcard character, but I'm wondering if there is a single query method for such a thing?

    Read the article

  • MYSQL distinct query

    - by Patrick
    This is my example of my table: id | name | foreign_id | 1 a 100 2 b 100 3 c 100 4 a 101 5 b 101 6 c 101 I would like to get the distinct file with the latest foreign_id (bigger number). In this example, it would be row with id 4,5,6. Anyone has any idea? Thank you very much!

    Read the article

  • Database design MySQL using foreign keys

    - by dscher
    I'm having some a little trouble understanding how to handle the database end of a program I'm making. I'm using an ORM in Kohana, but am hoping that a generalized understanding of how to solve this issue will lead me to an answer with the ORM. I'm writing a program for users to manage their stock research information. My tables are basically like so: CREATE TABLE tags( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, tags VARCHAR(30), UNIQUE(tags) ) ENGINE=INNODB DEFAULT CHARSET=utf8; CREATE TABLE stock_tags( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, tag_id INT NOT NULL, stock_id INT NOT NULL, FOREIGN KEY (tag_id) REFERENCES tags(id), FOREIGN KEY(stock_id) REFERENCES stocks(id) ON DELETE CASCADE ) ENGINE=INNODB DEFAULT CHARSET=utf8; CREATE TABLE notes( id INT AUTO_INCREMENT NOT NULL, stock_id INT NOT NULL, notes TEXT NOT NULL, FOREIGN KEY (stock_id) REFERENCES stocks(id) ON DELETE CASCADE, PRIMARY KEY(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8; CREATE TABLE links( id INT AUTO_INCREMENT NOT NULL, stock_id INT NOT NULL, links VARCHAR(2083) NOT NULL, FOREIGN KEY (stock_id) REFERENCES stocks(id) ON DELETE CASCADE, PRIMARY KEY(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8; How would I get all the attributes of a single stock, including its links, notes, and tags? Do I have to add links, notes, and tags columns to the stocks table and then how do you call it? I know this differs using an ORM and I'd assume that I can use join tables in SQL. Thanks for any help, this will really help me understand the issue a lot better.

    Read the article

  • MySQL query optimization - distinct, order by and limit

    - by Manuel Darveau
    I am trying to optimize the following query: select distinct this_.id as y0_ from Rental this_ left outer join RentalRequest rentalrequ1_ on this_.id=rentalrequ1_.rental_id left outer join RentalSegment rentalsegm2_ on rentalrequ1_.id=rentalsegm2_.rentalRequest_id where this_.DTYPE='B' and this_.id<=1848978 and this_.billingStatus=1 and rentalsegm2_.endDate between 1273631699529 and 1274927699529 order by rentalsegm2_.id asc limit 0, 100; This query is done multiple time in a row for paginated processing of records (with a different limit each time). It returns the ids I need in the processing. My problem is that this query take more than 3 seconds. I have about 2 million rows in each of the three tables. Explain gives: +----+-------------+--------------+--------+-----------------------------------------------------+---------------+---------+--------------------------------------------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+--------+-----------------------------------------------------+---------------+---------+--------------------------------------------+--------+----------------------------------------------+ | 1 | SIMPLE | rentalsegm2_ | range | index_endDate,fk_rentalRequest_id_BikeRentalSegment | index_endDate | 9 | NULL | 449904 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | rentalrequ1_ | eq_ref | PRIMARY,fk_rental_id_BikeRentalRequest | PRIMARY | 8 | solscsm_main.rentalsegm2_.rentalRequest_id | 1 | Using where | | 1 | SIMPLE | this_ | eq_ref | PRIMARY,index_billingStatus | PRIMARY | 8 | solscsm_main.rentalrequ1_.rental_id | 1 | Using where | +----+-------------+--------------+--------+-----------------------------------------------------+---------------+---------+--------------------------------------------+--------+----------------------------------------------+ I tried to remove the distinct and the query ran three times faster. explain without the query gives: +----+-------------+--------------+--------+-----------------------------------------------------+---------------+---------+--------------------------------------------+--------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+--------+-----------------------------------------------------+---------------+---------+--------------------------------------------+--------+-----------------------------+ | 1 | SIMPLE | rentalsegm2_ | range | index_endDate,fk_rentalRequest_id_BikeRentalSegment | index_endDate | 9 | NULL | 451972 | Using where; Using filesort | | 1 | SIMPLE | rentalrequ1_ | eq_ref | PRIMARY,fk_rental_id_BikeRentalRequest | PRIMARY | 8 | solscsm_main.rentalsegm2_.rentalRequest_id | 1 | Using where | | 1 | SIMPLE | this_ | eq_ref | PRIMARY,index_billingStatus | PRIMARY | 8 | solscsm_main.rentalrequ1_.rental_id | 1 | Using where | +----+-------------+--------------+--------+-----------------------------------------------------+---------------+---------+--------------------------------------------+--------+-----------------------------+ As you can see, the Using temporary is added when using distinct. I already have an index on all fields used in the where clause. Is there anything I can do to optimize this query? Thank you very much!

    Read the article

  • Optimize a MySQL count each duplicate Query

    - by Onema
    I have the following query That gets the city name, city id, the region name, and a count of duplicate names for that record: SELECT Country_CA.City AS currentCity, Country_CA.CityID, globe_region.region_name, ( SELECT count(Country_CA.City) FROM Country_CA WHERE City LIKE currentCity ) as counter FROM Country_CA LEFT JOIN globe_region ON globe_region.region_id = Country_CA.RegionID AND globe_region.country_code = Country_CA.CountryCode ORDER BY City This example is for Canada, and the cities will be displayed on a dropdown list. There are a few towns in Canada, and in other countries, that have the same names. Therefore I want to know if there is more than one town with the same name region name will be appended to the town name. Region names are found in the globe_region table. Country_CA and globe_region look similar to this (I have changed a few things for visualization purposes) CREATE TABLE IF NOT EXISTS `Country_CA` ( `City` varchar(75) NOT NULL DEFAULT '', `RegionID` varchar(10) NOT NULL DEFAULT '', `CountryCode` varchar(10) NOT NULL DEFAULT '', `CityID` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`City`,`RegionID`), KEY `CityID` (`CityID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; AND CREATE TABLE IF NOT EXISTS `globe_region` ( `country_code` char(2) COLLATE utf8_unicode_ci NOT NULL, `region_code` char(2) COLLATE utf8_unicode_ci NOT NULL, `region_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`country_code`,`region_code`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; The query on the top does exactly what I want it to do, but It takes way too long to generate a list for 5000 records. I would like to know if there is a way to optimize the sub-query in order to obtain the same results faster. the results should look like this City CityID region_name counter sheraton 2349269 British Columbia 1 sherbrooke 2349270 Quebec 2 sherbrooke 2349271 Nova Scotia 2 shere 2349273 British Columbia 1 sherridon 2349274 Manitoba 1

    Read the article

  • MySQL cross table regular expression match

    - by Josef Sábl
    I have a web application and I am working on engine that analyzes referals. Now I have table with pageviews along with referes that looks something like this: pv_id referer ------------------------------------------------------------ 5531854534 http://www.google.com/search?ie=UTF-8... 8161876343 http://google.cn/search?search=human+rights 8468434831 http://search.yahoo.com/search;_... The second table contains sources definitions like: source regex ------------------------------------------------------------ Google ^https?:\/\/[^\/]*google\.([a-z]{2,4})(\/.*|)$ Yahoo ^https?:\/\/[^\/]*yahoo\.com(\/.*|)$ What I want is third table created by joinin these two: pv_id source ------------------------------------------------------------ 5531854534 Google 8161876343 Google 8468434831 Yahoo How to join these tables with regular expression?

    Read the article

  • mysql filtering result using left outer join

    - by user288178
    my query: SELECT content.*, activity_log.content_id FROM content LEFT JOIN activity_log ON content.id = activity_log.content_id AND sess_id = '$sess_id' WHERE activity_log.content_id IS NULL AND visibility = $visibility AND content.reported < ".REPORTED_LIMIT." AND content.file_ready = 1 LIMIT 1 The purpose of that query is to get 1 row from the content table that has not been viewed by the user (identified by session_id), but it still returns contents that have been viewed. What is wrong? ( I have checked the table making sure that the content_ids are there) Note: I think this is more efficient than using subqueries, thoughts?

    Read the article

  • MySQL get row closest to NOW()

    - by Christopher McCann
    I have a table with User data such as name, address etc and another table which has a paragraph of text about the user. The reason that they are separate is because we need to record all the old about data. So if the user changes their paragraph - the old one should still be stored. Each bit of about data has a primary key aboutMeID. What I want to do is have a join that pulls their name, address etc and the latest bit of aboutMe data from the other table. I am not sure though how I can order the join to only get the latest about me data. Can someone help?

    Read the article

  • get value from MySQL database with PHP

    - by Hristo
    $from = $_POST['from']; $to = $_POST['to']; $message = $_POST['message']; $query = "SELECT * FROM Users WHERE `user_name` = '$from' LIMIT 1"; $result = mysql_query($query); while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $fromID = $row['user_id']; } I'm trying to have $formID be the user_id for a user in my database. Each row in the Users table is like: user_id | user_name | user_type 1 | Hristo | Agent So I want $from = 1 but the above code isn't working. Any ideas why?

    Read the article

  • MySQL join problem

    - by David
    Table1 has u_name, Table2 has u_name, u_type and u_admin Table1.u_name is unique. But neither of the 3 fields in Table2 is unique. For any value of Table1.u_name, there are 0 to many entries in Table2 that Table2.u_name equals to that value. For any value of Table1.u_name, there are 0 to 1 entries in Table2 that Table2.u_name equals to that value AND Table2.u_type='S' What I want: Use Table1.u_name to get Table1.*, Table2.u_admin where Table1.u_name=Tabl2.u_name and Table2.u_type='S'. If there is no such entry in Table2 we still need to get Table1.* Please help give me some hints. Thank you so much!

    Read the article

  • mysql - union with creating demarcated field

    - by Qiao
    I need UNION two tables with creating new field, where 1 for first table, and 2 for second. I tried ( SELECT field, 1 AS tmp FROM table1 ) UNION ( SELECT field, 2 AS tmp FROM table2 ) But in result, tmp field was full of "1". How it can be implemented?

    Read the article

  • Deeply nested subqueries for traversing trees in MySQL

    - by nickf
    I have a table in my database where I store a tree structure using the hybrid Nested Set (MPTT) model (the one which has lft and rght values) and the Adjacency List model (storing parent_id on each node). my_table (id, parent_id, lft, rght, alias) This question doesn't relate to any of the MPTT aspects of the tree but I thought I'd leave it in in case anyone had a good idea about how to leverage that. I want to convert a path of aliases to a specific node. For example: "users.admins.nickf" would find the node with alias "nickf" which is a child of one with alias "admins" which is a child of "users" which is at the root. There is a unique index on (parent_id, alias). I started out by writing the function so it would split the path to its parts, then query the database one by one: SELECT `id` FROM `my_table` WHERE `parent_id` IS NULL AND `alias` = 'users';-- 1 SELECT `id` FROM `my_table` WHERE `parent_id` = 1 AND `alias` = 'admins'; -- 8 SELECT `id` FROM `my_table` WHERE `parent_id` = 8 AND `alias` = 'nickf'; -- 37 But then I realised I could do it with a single query, using a variable amount of nesting: SELECT `id` FROM `my_table` WHERE `parent_id` = ( SELECT `id` FROM `my_table` WHERE `parent_id` = ( SELECT `id` FROM `my_table` WHERE `parent_id` IS NULL AND `alias` = 'users' ) AND `alias` = 'admins' ) AND `alias` = 'nickf'; Since the number of sub-queries is dependent on the number of steps in the path, am I going to run into issues with having too many subqueries? (If there even is such a thing) Are there any better/smarter ways to perform this query?

    Read the article

  • Mysql regexp performance question

    - by Tim
    Rumour has it that this; SELECT * FROM lineage_string where lineage like '%179%' and lineage regexp '(^|/)179(/|$)' Would be faster than this; SELECT * FROM lineage_string where lineage regexp '(^|/)179(/|$)' Can anyone confirm ? Or know a decent way to test the speed of such queries. Thanks

    Read the article

  • Tree data in MySql database table

    - by Robert Koritnik
    I have a table that uses Adjacency list model for hierarchy storage. My most relevant columns in this table are therefore: ItemId // is auto_increment ParentId Level ParentTrail // in the form of "parentId/../parentId/itemId" then I created a before insert tigger, that populates columns Level and ParentTrail. Since the last column also includes current item's ID I had to use a trick in my trigger because auto_increment columns are not available in the before insert trigger. So I get that value from the information_schema.tables table. All works fine, until I try to write an update trigger, that would update my item and its descendants when the item changes its parent (ParentId has changed). But I can't make an update on my table inside the update trigger. All I can do is to change current record's values but not other's. I could use a separate table for hierarchy data, but that would mean that I would also have to create a view that would combine these two tables (1:1 relation) and I would like to avoid this is at all possible. Is there a way to have all these in the same table so that these fields (Level and ParetTrail) set/update themselves automagically using triggers?

    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 | 169 170 171 172 173 174 175 176 177 178 179 180  | Next Page >