Search Results

Search found 18661 results on 747 pages for 'linq to mysql'.

Page 251/747 | < Previous Page | 247 248 249 250 251 252 253 254 255 256 257 258  | Next Page >

  • MySQL Join Comma Separated Field

    - by neeraj
    I have two tables. First Table is a batch table that contain comma separated student id in field "batch" batch -------------- id batch -------------- 1 1,2 2 3,4 Second Table is marks marks ---------------------- id studentid subject marks 1 1 English 50 2 2 English 40 3 3 English 70 4 1 Math 65 5 4 English 66 6 5 English 75 7 2 Math 55 How we can find those students of first batch id =1 who have scored more than 45 marks in English without using sub query. Problem i found to get this done using a single query is that we can not use IN as an association operator in JOIN statement What changes are required in below query to make it work? SELECT * FROM batch INNER JOIN marks ON marks.studentid IN(batch.batch) where batch.id = 1

    Read the article

  • mysql join on two indexes takes long time!!

    - by Alaa
    Hi All I have a custom query in dripal, this query is: select count(distinct B.src) from node A, url_alias B where concat('node/',A.nid)= B.src; now, nid in node is primary key and i have made src as an index in url_alias table. after waiting for more than a minute i got this: +-----------------------+ | count(distinct B.src) | +-----------------------+ | 325715 | +-----------------------+ 1 row in set (1 min 24.37 sec) now my question is: why did this query take this long, and how to optimize it?? Thanks for your help

    Read the article

  • MySQL join problem

    - by snaken
    Whats wrong with this SQL? It should return results but returns nothing SELECT `pid` FROM `products` LEFT JOIN `prods_to_features` ON (`ptf_pid` = `pid`) WHERE (`ptf_id` = '66' OR `ptf_id` = '67') AND (`ptf_id` = '76') Is it not possible to have the 2nd where clause for the table that has been used in the left join?

    Read the article

  • MySQL whats wrong with my foreign keys?

    - by Skiy
    Hello, what is wrong with the two foreign keys which I have marked with comments? create database db; use db; create table Flug( Flugbez varchar(20), FDatum Date, Ziel varchar(20), Flugzeit int, Entfernung int, Primary Key (Flugbez, FDatum)); create table Flugzeugtyp( Typ varchar(20), Hersteller varchar(20), SitzAnzahl int, Reisegeschw int, primary key (Typ) ); create table flugzeug( Typ varchar(20), SerienNr int, AnschDatum Date, FlugStd int, primary key(Typ,SerienNr), foreign key(Typ) references Flugzeugtyp(Typ)); create table Abflug( Flugbez varchar(20), FDatum Date, Typ varchar(20), Seriennr int, Kaptaen varchar(20), Primary key(Flugbez,FDatum,Typ,SerienNr), Foreign key(Flugbez) references Flug(Flugbez), -- Foreign key(FDatum) references Flug(FDatum), Foreign key(Typ) references Flugzeugtyp(Typ) -- ,Foreign key(SerienNr) references Flugzeug(SerienNr) ); When I uncomment these, I get: ERROR 1005 (HY000): Can't create table 'db.abflug' (errno: 150)

    Read the article

  • Merging tables in MySQL - sum up columns

    - by Alan Williamson
    I have an interesting problem, that i am sure has a simple answer, but i can't seem to find it in the docs. I have two separate database tables, on different servers. They are both identical table schema with the same primary keys. I want to merge the tables together on one server. But, if the row on Server1.Table1 exists in Server2.Table2 then sum up the totals in the columns i specify. Table1{ column_pk, counter }; "test1", 3 "test2", 4 Table2{ column_pk, counter }; "test1", 5 "test2", 6 So after i merge i want: "test1",8 "test2",10 Basically i need to do a mysqldump but instead of it kicking out raw INSERT statements, i need to do a INSERT..ON DUPLICATE KEY UPDATE statements. What are my options? Appreciate any input, thank you

    Read the article

  • select command in mysql doesnot return any row

    - by jeyshree
    i created a database using the command CREATE TABLE login_table2(user_name VARCHAR(32), first_name VARCHAR(32), last_name VARCHAR(32), password VARCHAR(64)); then i inserted a data using command INSERT INTO login_table2(user_name ,first_name , last_name , password ) VALUES('ramya', 'ramya', 'muthu', 'India'); the data got inserted into the table. then i inserted another set of data using command INSERT INTO login_table2(user_name ,first_name , last_name , password ) VALUES('jeyshree', 'jey', 'shree', 'India'); the data got inserted into the table too. then i gave the command SELECT first_name FROM login_table2; the command displayed all the first_ name in the table. however when i gave the command SELECT password FROM login_table2 WHERE user_name = 'ramya'; it does not fetch anything though the entry exist in the table.mention where i am going wrong.awaiting your reply.

    Read the article

  • How to query multiple tables with multiple selects MySQL

    - by brybam
    I'm trying to write a php function that gets all the basic food data(this part works fine in my code) Then grabs all the ingredients related to the ids of the selected items from the query before. Anyone have have any idea why my second array keeps coming back as false? I should be getting 1 array with the list of foods (this works) Then, the second one should be an array with all the ingredients for all the foods that were previously selected...then in my code im planning to work with the array and sort them with the proper foods based on the ids. function getFood($start, $limit) { $one = mysql_query("SELECT a.id, a.name, a.type, AVG(b.r) AS fra, COUNT(b.id) as tvotes FROM `foods` a LEFT JOIN `foods_ratings` b ON a.id = b.id GROUP BY a.id ORDER BY fra DESC, tvotes DESC LIMIT $start, $limit;"); $row = mysql_fetch_array($one); $qry = ""; foreach ($row as &$value) { $fid = $value['id']; $qry = $qry . "SELECT ing, amount FROM foods_ing WHERE fid='$fid';"; } $two = mysql_query($qry); return array ($one, $two); }

    Read the article

  • MySQL: Select pages that are not tagged?

    - by lauthiamkok
    Hi, I have a db with two tables like these below, page table pg_id title 1 a 2 b 3 c 4 d tagged table tagged_id pg_id 1 1 2 4 I want to select the pages which are tagged, I tried with this query below but doesn't work, SELECT * FROM root_pages LEFT JOIN root_tagged ON ( root_tagged.pg_id = root_pages.pg_id ) WHERE root_pages.pg_id != root_tagged.pg_id It returns zero - Showing rows 0 - 1 (2 total, Query took 0.0021 sec) But I want it to return pg_id title 2 b 3 c My query must have been wrong?? How can I return the pages which are not tagged correctly? Thanks.

    Read the article

  • Generate MySQL data dump in SQL from PHP

    - by Álvaro G. Vicario
    I'm writing a PHP script to generate SQL dumps from my database for version control purposes. It already dumps the data structure by means of running the appropriate SHOW CREATE .... query. Now I want to dump data itself but I'm unsure about the best method. My requirements are: I need a record per row Rows must be sorted by primary key SQL must be valid and exact no matter the data type (integers, strings, binary data...) Dumps should be identical when data has not changed I can detect and run mysqldump as external command but that adds an extra system requirement and I need to parse the output in order to remove headers and footers with dump information I don't need (such as server version or dump date). I'd love to keep my script as simple as I can so it can be hold in an standalone file. What are my alternatives?

    Read the article

  • formatting mysql data for ouptut into a table

    - by bsandrabr
    Following on from a question earlier today this answer was given to read the data into an array and separate it to print vehicle type and then some data for each vehicle. <?php $sql = "SELECT * FROM apparatus ORDER BY vehicleType"; $getSQL = mysql_query($sql); // transform the result set: $data = array(); while ($row = mysql_fetch_assoc($getSQL)) { $data[$row['vehicleType']][] = $row; } ?> <?php foreach ($data as $type => $rows): ?> <h2><?php echo $type?></h2> <ul> <?php foreach ($rows as $vehicleData):?> <li><?php echo $vehicleData['name'];?></li> <?php endforeach ?> </ul> <?php endforeach ?> This is almost perfect for what I want to do but I need to print out two columns from the database ie ford and mondeo before going into the second foreach loop. I've tried print $rows['model'] and all the other combinations I can think of but that doesn't work. Any help much appreciated

    Read the article

  • Realtime MySQL search results on an advanced search page

    - by Andrew Heath
    I'm a hobbyist, and started learning PHP last September solely to build a hobby website that I had always wished and dreamed another more competent person might make. I enjoy programming, but I have little free time and enjoy a wide range of other interests and activities. I feel learning PHP alone can probably allow me to create 98% of the desired features for my site, but that last 2% is awfully appealing: The most powerful tool of the site is an advanced search page that picks through a 1000+ record game scenario database. Users can data-mine to tremendous depths - this advanced page has upwards of 50 different potential variables. It's designed to allow the hardcore user to search on almost any possible combination of data in our database and it works well. Those who aren't interested in wading through the sea of options may use the Basic Search, which is comprised of the most popular parts of the Advanced search. Because the advanced search is so comprehensive, and because the database is rather small (less than 1,200 potential hits maximum), with each variable you choose to include the likelihood of getting any qualifying results at all drops dramatically. In my fantasy land where I can wield AJAX as if it were Excalibur, my users would have a realtime Total Results counter in the corner of their screen as they used this page, which would automatically update its query structure and report how many results will be displayed with the addition of each variable. In this way it would be effortless to know just how many variables are enough, and when you've gone and added one that zeroes out the results set. A somewhat similar implementation, at least visually, would be the Subtotal sidebar when building a new custom computer on IBuyPower.com For those of you actually still reading this, my question is really rather simple: Given the time & ability constraints outlined above, would I be able to learn just enough AJAX (or whatever) needed to pull this one feature off without too much trouble? would I be able to more or less drop-in a pre-written code snippet and tweak to fit? or should I consider opening my code up to a trusted & capable individual in the future for this implementation? (assuming I can find one...) Thank you.

    Read the article

  • mysql select update

    - by Tillebeck
    Hi I have read quite a few selcet+update questions in here but cannot understand how to do it. So will have to ask from the beginning. I would like to update a table based on data in another table. Setup is like this: - TABLE a ( int ; string ) ID WORD 1 banana 2 orange 3 apple - TABLE b ( "comma separated" string ; string ) WORDS TEXTAREA 0 banana -> 0,1 0 orange apple apple -> BEST:0,2,3 ELSE 0,2,3,3 0 banana orange apple -> 0,1,2,3 Now I would like to for each word in TABLE a append ",a.ID" to b.WORDS like: SELECT id, word FROM a (for each) -> UPDATE b SET words = CONCAT(words, ',', a.id) WHERE b.textarea like %a.word% Or even better: replace the word found in b.textarea with ",a.id" so it is the b.textarea that ends up beeing a comma separeted string of id's... But I do not know if that is possible.

    Read the article

  • MYSQL: How to limit inner join?

    - by Sergii Rechmp
    I need some help with my query. I have 2 tables: all: art|serie sootv: name|art|foo I need to get result like name|serie. My query is: SELECT t2.NAME, t1.serie FROM ( SELECT * FROM `all` WHERE `serie` LIKE '$serie' ) t1 INNER JOIN sootv t2 ON t1.art = t2.art; it works, but sootv table contains data like name|art|foo abc | 1 | 5 abc | 1 | 6 i get 2 same results. Its not what i need. Help me please - how i can get only one result: abc|1 Thanks.

    Read the article

  • Writing an optimised and efficient search engine with mySQL and ColdFusion

    - by Mel
    I have a search page with the following scenarios listed below. I was told there was a better way to do it, but not how, and that I am using too many if statements, and that it's prone to causing an error through url manipulation: Search.cfm will processes a search made from a search bar present on all pages, with one search input (titleName). If search.cfm is accessed manually (through URL not through using the simple search bar on all pages) it displays an advanced search form with three inputs (titleName, genreID, platformID) or it evaluates searchResponse variable and decides what to do. If simple search query is blank, has no results, or less than 3 characters it displays an error If advanced search query is blank, has no results, or less than 3 characters it displays an error If any successful search returns results, they come back normally. The top-of-page logic is as follows: <!---SET DEFAULT VARIABLE---> <cfparam name="variables.searchResponse" default=""> <!---CHECK TO SEE IF SIMPLE SEARCH A FORM WAS SUBMITTED AND EXECUTE SEARCH IF IT WAS---> <cfif IsDefined("Form.simpleSearch") AND Len(Trim(Form.titleName)) LTE 2> <cfset variables.searchResponse = "invalidString"> <cfelseif IsDefined("Form.simpleSearch") AND Len(Trim(Form.titleName)) GTE 3> <!---EXECUTE METHOD AND GET DATA---> <cfinvoke component="myComponent" method="simpleSearch" searchString="#Form.titleName#" returnvariable="simpleSearchResult"> <cfset variables.searchResponse = "simpleSearchResult"> </cfif> <!---CHECK IF ANY RECORDS WERE FOUND---> <cfif IsDefined("variables.simpleSearchResult") AND simpleSearchResult.RecordCount IS 0> <cfset variables.searchResponse = "noResult"> </cfif> <!---CHECK IF ADVANCED SEARCH FORM WAS SUBMITTED---> <cfif IsDefined("Form.AdvancedSearch") AND Len(Trim(Form.titleName)) LTE 2> <cfset variables.searchResponse = "invalidString"> <cfelseif IsDefined("Form.advancedSearch") AND Len(Trim(Form.titleName)) GTE 2> <!---EXECUTE METHOD AND GET DATA---> <cfinvoke component="myComponent" method="advancedSearch" returnvariable="advancedSearchResult" titleName="#Form.titleName#" genreID="#Form.genreID#" platformID="#Form.platformID#"> <cfset variables.searchResponse = "advancedSearchResult"> </cfif> <!---CHECK IF ANY RECORDS WERE FOUND---> <cfif IsDefined("variables.advancedSearchResult") AND advancedSearchResult.RecordCount IS 0> <cfset variables.searchResponse = "noResult"> </cfif> I'm using the searchResponse variable to decide what the the page displays, based on the following scenarios: <!---ALWAYS DISPLAY SIMPLE SEARCH BAR AS IT'S PART OF THE HEADER---> <form name="simpleSearch" action="search.cfm" method="post"> <input type="hidden" name="simpleSearch" /> <input type="text" name="titleName" /> <input type="button" value="Search" onclick="form.submit()" /> </form> <!---IF NO SEARCH WAS SUBMITTED DISPLAY DEFAULT FORM---> <cfif searchResponse IS ""> <h1>Advanced Search</h1> <!---DISPLAY FORM---> <form name="advancedSearch" action="search.cfm" method="post"> <input type="hidden" name="advancedSearch" /> <input type="text" name="titleName" /> <input type="text" name="genreID" /> <input type="text" name="platformID" /> <input type="button" value="Search" onclick="form.submit()" /> </form> </cfif> <!---IF SEARCH IS BLANK OR LESS THAN 3 CHARACTERS DISPLAY ERROR MESSAGE---> <cfif searchResponse IS "invalidString"> <cfoutput> <h1>INVALID SEARCH</h1> </cfoutput> </cfif> <!---IF SEARCH WAS MADE BUT NO RESULTS WERE FOUND---> <cfif searchResponse IS "noResult"> <cfoutput> <h1>NO RESULT FOUND</h1> </cfoutput> </cfif> <!---IF SIMPLE SEARCH WAS MADE A RESULT WAS FOUND---> <cfif searchResponse IS "simpleSearchResult"> <cfoutput> <h1>Search Results</h1> </cfoutput> <cfoutput query="simpleSearchResult"> <!---DISPLAY QUERY DATA---> </cfoutput> </cfif> <!---IF ADVANCED SEARCH WAS MADE A RESULT WAS FOUND---> <cfif searchResponse IS "advancedSearchResult"> <cfoutput> <h1>Search Results</h1> <p>Your search for "#Form.titleName#" returned #advancedSearchResult.RecordCount# result(s).</p> </cfoutput> <cfoutput query="advancedSearchResult"> <!---DISPLAY QUERY DATA---> </cfoutput> </cfif> Is my logic a) not efficient because my if statements/is there a better way to do this? And b) Can you see any scenarios where my code can break? I've tested it but I have not been able to find any issues with it. And I have no way of measuring performance. Any thoughts and ideas would be greatly appreciated. Many thanks

    Read the article

  • MySQL query paralyzes site

    - by nute
    Once in a while, at random intervals, our website gets completely paralyzed. Looking at SHOW FULL PROCESSLIST;, I've noticed that when this happens, there is a specific query that is "Copying to tmp table" for a loooong time (sometimes 350 seconds), and almost all the other queries are "Locked". The part I don't understand is that 90% of the time, this query runs fine. I see it going through in the process list and it finishes pretty quickly most of the time. This query is being called by an ajax call on our homepage to display product recommendations based your browsing history (a la amazon). Just sometimes, randomly (but too often), it gets stuck at "copying to tmp table". Here is a caught instance of the query that was up 109 seconds when I looked: SELECT DISTINCT product_product.id, product_product.name, product_product.retailprice, product_product.imageurl, product_product.thumbnailurl, product_product.msrp FROM product_product, product_xref, product_viewhistory WHERE ( (product_viewhistory.productId = product_xref.product_id_1 AND product_xref.product_id_2 = product_product.id) OR (product_viewhistory.productId = product_xref.product_id_2 AND product_xref.product_id_1 = product_product.id) ) AND product_product.outofstock='N' AND product_viewhistory.cookieId = '188af1efad392c2adf82' AND product_viewhistory.productId IN (24976, 25873, 26067, 26073, 44949, 16209, 70528, 69784, 75171, 75172) ORDER BY product_xref.hits DESC LIMIT 10 Of course the "cookieId" and the list of "productId" changes dynamically depending on the request. I use php with PDO.

    Read the article

  • [php] mysql and encoding

    - by user339865
    I moved my php application to the new server. i use mysql5 db. When i'm Updating or Inserting something to db, every " and - sign changed to ?. I use SET NAMES UTF8 and SET CHARACTER SET but it don't work. Any ideas?

    Read the article

  • MySQL> Selecting from more tables (with same columns) without UNION

    - by Petr
    Hi, It is probably pretty simple but I cannot figure it out: Say I have tables A and B both with the same columns. I need to do SELECT * FROM A,B without having results merged into one row. I.e. when each table has 2 rows, I need the result to have 4 rows. EDIT: I know about JOIN but dont know how to join the tables without predicate. I need merge them. Thanks

    Read the article

  • incrementing a table column's data by one || mySql

    - by Praveen Prasad
    iam having a table with columns like id || counter if i do something (some event) i want the counter's value(at a particular id) to increase by one , currently iam doing this : //get current value current_value = select counter from myTable where id='someValue' // increase value current_value++ //update table with current value update myTable set counter=current_value where id='someValue'; currently iam running 2 queries for this, please suggest me some way do it in one step.

    Read the article

  • MySQL inner join different results

    - by Darryl at NetHosted
    I am trying to work out why the following two queries return different results: SELECT DISTINCT i.id, i.date FROM `tblinvoices` i INNER JOIN `tblinvoiceitems` it ON it.userid=i.userid INNER JOIN `tblcustomfieldsvalues` cf ON it.relid=cf.relid WHERE i.`tax` = 0 AND i.`date` BETWEEN '2012-07-01' AND '2012-09-31' and SELECT DISTINCT i.id, i.date FROM `tblinvoices` i WHERE i.`tax` = 0 AND i.`date` BETWEEN '2012-07-01' AND '2012-09-31' Obviously the difference is the inner join here, but I don't understand why the one with the inner join is returning less results than the one without it, I would have thought since I didn't do any cross table references they should return the same results. The final query I am working towards is SELECT DISTINCT i.id, i.date FROM `tblinvoices` i INNER JOIN `tblinvoiceitems` it ON it.userid=i.userid INNER JOIN `tblcustomfieldsvalues` cf ON it.relid=cf.relid WHERE cf.`fieldid` =5 AND cf.`value` REGEXP '[A-Za-z]' AND i.`tax` = 0 AND i.`date` BETWEEN '2012-07-01' AND '2012-09-31' But because of the different results that seem incorrect when I add the inner join (it removes some results that should be valid) it's not working at present, thanks.

    Read the article

< Previous Page | 247 248 249 250 251 252 253 254 255 256 257 258  | Next Page >