Search Results

Search found 40581 results on 1624 pages for 'mysql select db'.

Page 126/1624 | < Previous Page | 122 123 124 125 126 127 128 129 130 131 132 133  | Next Page >

• Convert search from SQL Server to MySQL

  - by HAJJAJ

  hi, everyone. i need to convert this one from SQL Server into MySQL IF IsNull(@SearchText, '') <> '' BEGIN SET @SearchText = '%' + @SearchText + '%' SELECT NewsID,DeptID,DeptName,Title,Details ,NewsDate,img FROM @tbSearchtextTb WHERE IsNull(Title,'')+IsNull(Details,'') LIKE @SearchText END this code will search fro my search word in this columns: Title, Details. i tried to convert this line but i had lots of errors: these are my unsuccessful attempts IF ISNULL(SearchText,'') <> '' THEN SELECT CatID,CatTitle,CatDescription,CatTitleAr,CatDescriptionAr,PictureID,Published,DisplayOrder,CreatedOn FROM tmp WHERE CatTitle + CatDescription + CatTitleAr + CatDescriptionAr LIKE $SearchText; and this one IF $SearchText IS NOT NULL THEN SELECT CatID,CatTitle,CatDescription,CatTitleAr,CatDescriptionAr,PictureID,Published,DisplayOrder,CreatedOn FROM tmp WHERE ISNULL(CatTitle,'') +ISNULL(CatDescription ,'') +ISNULL(CatTitleAr ,'') +ISNULL(CatDescriptionAr,'') LIKE $SearchText; and many many other ways but i could not find any. so if you know please let me know, thanks and best regards.

  Read the article

• MySQL : delete from table that is used in the where clause

  - by Eric

  I am writing a small script to synchronize 2 MySQL tables ( t1 to be 'mirrored' to t2 ) In a step I would like to delete rows inside t2 that has been delete in t1 with the same id. I tried this query : delete from t2 where t2.id in ( select t2.id left join t1 on (t1.id=t2.id) where t1.id is null ) But Mysql forbid me to use t2 in the same time in the delete and in the select (sound logical by the way) Of course, I can split the query into 2 queries : first select IDs, then delete rows with these IDs. My question : do you have a cleaner way to delete row from t2 that does not exist anymore in t1 ? with one query only ?

  Read the article

• mysql query that has array

  - by Xainee Khan

  //get all id's of ur friend that has installed your application $friend_pics=$facebook->api( array( 'method' => 'fql.query', 'query' => "SELECT uid FROM user WHERE uid IN(SELECT uid2 from friend WHERE uid1='$user') AND is_app_user = 1" ) ); // this query work fine //your top10 friends in app $result="SELECT * FROM fb_user WHERE user_id IN($friend_pics) ORDER BY oldscore DESC LIMIT 0,10"; db_execute($result); i want to retrive ten top scorer from my database stored in oldscore but in my second query the array name $friend_pics is not working i guess,plz help me thanks

  Read the article

• Ordering by multiple columns in mysql with subquery

  - by Scarface

  Hey guys I have a query that selects data and organizes but not in the correct order. What I want to do is select all the comments for a user in that week and sort it by each topic, then sort the cluster by the latest timestamp of each comment in their respective cluster. My current query selects the right data, but in seemingly random order. Does anyone have any ideas? select * from ( SELECT topic.topic_title, topic.topic_id FROM comments JOIN topic ON topic.topic_id=comments.topic_id WHERE comments.user='$user' AND comments.timestamp>$week order by comments.timestamp desc) derived_table group by topic_id

  Read the article

• PHP MYSQL query result "RANKING"

  - by fkessler

  Hi, I need to get a list of users Ranking by points and from my command line (MySQL) is was able to generate the necessary code: SET @rank=0; SELECT rank, iduser, pontos FROM ( SELECT @rank:=@rank+1 AS rank, SUM(points.points) AS pontos, points.iduser, users.name, users.idade FROM points INNER JOIN users ON (points.iduser = users.id) WHERE (users.idade >= %s) AND (users.idade <= %s) GROUP BY points.iduser ORDER BY pontos DESC) AS totals WHERE iduser = %s The problem is that I need this to run on AMFPHP and I´ve tested it in a test PHP file and seems that I can´t use the SET and SELECT in the same "mysql_query". I´ve looked and some used to mysql_query to do this (I´ve tested it and it works), but can I trust this to be effective and error free? Does it work like in MySQL transactions or setting the @rank in a seperated query may cause unexpected results?

  Read the article

• [MySQL, InnoDb] Rating place

  - by Pavel

  I'm trying to generate rating place table using following receipt http://stackoverflow.com/questions/1776821/assign-places-in-the-rating-mysql-php but my database is high loaded. I tried not to create table, but use MEMORY TABLE and update it using following SQL query insert into tops (uid) select uid from users order by exp desc; but got the following MySQL error Deadlock found when trying to get lock; try restarting transaction because there are too many queries until SQL select is being executed. How to solve this problem? P.S. CREATE TABLE tops as SELECT work almost fine except high server load... up to load average: 50 if tops are non-memory table. My table users has near 4.5 millions of rows. Thanks for any advices.

  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

• Show parts of the result of an SQL statement using PHP

  - by mouthpiec

  I have an SQL query which returns a set of data (around 40-50 tuples). I would like to display the results 5 at a time on an HTML page using PHP. I already managed to have the right SELECT statement, but i am having problems to display the results 5 by 5 using a "more" button. Can you please help? Note that every time i call the query, the data is being randomized, so it is not possible to set limits and call the query again. I have to find the method to store the results somewhere, and then show them 5 by 5.

  Read the article

• MySQL "IS IN" equivalent?

  - by nute

  A while ago I worked on a MS-SQL project and I remember a "IS IN" thing. I tried it on a MySQL project and it did not work. Is there an equivalent? Workaround? Here is the full query I am trying to run: SELECT * FROM product_product, product_viewhistory, product_xref 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.id IS IN (SELECT DISTINCT pvh.productId FROM product_viewhistory AS pvh WHERE pvh.cookieId = :cookieId ORDER BY pvh.viewTime DESC LIMIT 10) AND product_viewhistory.cookieId = :cookieId AND product_product.outofstock='N' ORDER BY product_xref.hits DESC LIMIT 10 It's pretty big ... but the part I am interested in is: AND product_product.id IS IN (SELECT DISTINCT pvh.productId FROM product_viewhistory AS pvh WHERE pvh.cookieId = :cookieId ORDER BY pvh.viewTime DESC LIMIT 10) Which basically says I want the products to be in the "top 10" of that sub-query. How would you achieve that with MySQL (while trying to be efficient)?

  Read the article

• Union - Same table, excluding previous results MySQL

  - by user82302124

  I'm trying to write a query that will: Run a query, give me (x) number of rows (limit 4) If that query didn't give me the 4 I need, run a second query limit 4-(x) and exclude the ids from the first query A third query that acts like the second I have this: (SELECT *, 1 as SORY_QUERY1 FROM xbamZ where state = 'Minnesota' and industry = 'Miscellaneous' and id != '229' limit 4) UNION (SELECT *, 2 FROM xbamZ where state = 'Minnesota' limit 2) UNION (SELECT *, 3 FROM xbamZ where industry = 'Miscellaneous' limit 1) How (or is?) do I do that? Am I close? This query gives me duplicates

  Read the article

• convert mysql code to codeigniter

  - by Jethro Tamares Doble

  How can i convert this code into an acceptable codeigniter code: mysql_select_db($database_connection_ched, $connection_ched); $query_Institutions = "SELECT * FROM tb_institutional_profile ORDER BY tb_institutional_profile.institution_name ASC"; $Institutions = mysql_query($query_Institutions, $connection_ched) or die(mysql_error()); $row_Institutions = mysql_fetch_assoc($Institutions); $totalRows_Institutions = mysql_num_rows($Institutions); <td width="192"><select name="institution_id"> <?php do { <option value="<?php echo $row_Institutions['institution_id']?>" ><?php echo $row_Institutions['institution_name']?></option> <?php } while ($row_Institutions = mysql_fetch_assoc($Institutions)); ?> </select></td>

  Read the article

• MYSQL: COUNT with GROUP BY, LEFT JOIN and WHERE clause doesn't return zero values

  - by Paul Norman

  Hi guys, thanks in advance for any help on this topic! I'm sure this has a very simply answer, but I can't seem to find it (not sure what to search on!). A standard count / group by query may look like this: SELECT COUNT(`t2`.`name`) FROM `table_1` `t1` LEFT JOIN `table_2` `t2` ON `t1`.`key_id` = `t2`.`key_id` GROUP BY `t1`.`any_col` and this works as expected, returning 0 if no rows are found. So does: SELECT COUNT(`t2`.`name`) FROM `table_1` `t1` LEFT JOIN `table_2` `t2` ON `t1`.`key_id` = `t2`.`key_id` WHERE `t1`.`another_column` = 123 However: SELECT COUNT(`t2`.`name`) FROM `table_1` `t1` LEFT JOIN `table_2` `t2` ON `t1`.`key_id` = `t2`.`key_id` WHERE `t1`.`another_column` = 123 GROUP BY `t1`.`any_col` only works if there is at least one row in table_1 and fails miserably returning an empty result set if there are zero rows. I would really like this to return 0! Anyone enlighten me on this? Beer can be provided in exchange if you are in London ;-)

  Read the article

• MySQL Check if table exists error

  - by Max van Heiningen

  I'm trying to check if a table already exists, however I can't get this working. IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype='u' AND name='tablename') SELECT 'table already exists.' ELSE BEGIN CREATE TABLE Week_( id INT(10)AUTO_INCREMENT PRIMARY KEY (id), ... ...) END; My error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype='u' AND name' at line 1 Can someone help me with this? Thanks in advance

  Read the article

• MySQL -- How to do this better?

  - by Andrew

  $activeQuery = mysql_query("SELECT count(`status`) AS `active` FROM `assignments` WHERE `user` = $user_id AND `status` = 0"); $active = mysql_fetch_assoc($activeQuery); $failedQuery = mysql_query("SELECT count(`status`) AS `failed` FROM `assignments` WHERE `user` = $user_id AND `status` = 1"); $failed = mysql_fetch_assoc($failedQuery); $completedQuery = mysql_query("SELECT count(`status`) AS `completed` FROM `assignments` WHERE `user` = $user_id AND `status` = 2"); $completed = mysql_fetch_assoc($completedQuery); There has to be a better way to do that, right? I don't know how much I need to elaborate as you can see what I'm trying to do, but is there any way to do all of that in one query? I need to be able to output the active, failed, and completed assignments, preferably in one query.

  Read the article

• MySQL column names and aliases

  - by user329820

  hi, I have read that after select we use column-names but I have found a statement that was like this: SELECT 'A' FROM T WHERE A = NULL; would you lease help me? thanks (A is a column- name here?) my DBMS is MySQL EDITED : the exact question is this that: Will the above statement produce a row (select all that apply)? Notice that ANSI_NULLS is OFF. I want to know that the above statement will work? because some of you said that we should write IS NULL instead of =null

  Read the article

• Confusing alias mySQL

  - by Taylor

  I keep getting the same number outputted for the Total Sales, Minimum Sale, Largest Sale and Average Sale. The Total Invoices is working perfectly, but I cant seem to figure out how to fix the other ones. Here's the query: SELECT SUM( b.`Number of Invoices`) AS `Total Invoices`, SUM( b.`Total Customer Purchases`) AS `Total Sales`, MIN( b.`Total Customer Purchases`) AS `Minimum Sale`, MAX( b.`Total Customer Purchases`) AS `Largest Sale`, AVG( b.`Total Customer Purchases`) AS `Average Sale` FROM (SELECT a.CUS_CODE, COUNT(a.`Number of Invoices`) AS `Number of Invoices`, SUM(a.`Invoice Total`) AS `Total Customer Purchases` FROM ( SELECT CUS_CODE, LINE.INV_NUMBER AS `Number of Invoices`, SUM(LINE.LINE_UNITS * LINE.LINE_PRICE) AS `Invoice Total` FROM `ttriggs`.`INVOICE`, `ttriggs`.`LINE` WHERE INVOICE.INV_NUMBER = LINE.INV_NUMBER GROUP BY CUS_CODE, LINE.INV_NUMBER ) a ) b GROUP BY b.CUS_CODE; Heres the database diagram https://www.dropbox.com/s/b8cy5l29jwh8lyv/1_edit.jpg Subquery generates: CUS_CODE 10011 Number of Invoices 8 Total Customer Purchases 1119.03 Any help is greatly appreciated, Thanks!

  Read the article

• mysql result for pagination

  - by Reteras Remus

  The query is: SELECT * FROM `news` ORDER BY `id` LIMIT ($curr_page * 5), ( ($curr_page * 5) + 5 ) Where $curr_page is a php variable which is getting a value from $_GET['page'] I want to make a pagination (5 news on each page), but I don't know why the mysql is returning me extra values. On the first page the result ok: $curr_page = 0 The query would be: SELECT * FROM `news` ORDER BY `id` LIMIT 0, 5 But on the second page, the result from the query is adding extra news, 10 instead of 5. The query on the second page: SELECT * FROM `news` ORDER BY `id` LIMIT 5, 10 Whats wrong? Why the result has 10 values instead of 5? Thank you!

  Read the article

• Prioritize SQL WHERE clause

  - by JaTochNietDan

  Basically I want to do this: SELECT * FROM `table` WHERE x = 'hello' OR x = 'bye' LIMIT 1'; I want it to return 1 value, but to prioritize results from the 1st where clause. So if there exists a row where column x's value is "hello", it will not return the result from the 'bye' value. If the "hello" value doesn't exist though, it will return the result from the 'bye' value. Can't figure out a way to do it even though it seems fairly trivial. Any ideas?

  Read the article

• Why am I getting a MySQL error?

  - by John Hoffman

  Here is my query. Its intention is allow access to properties of the animals that constitute a match of two animals. The match table contains columns for animal1ID and animal2ID to store which animals constitute the match. SELECT id, (SELECT * FROM animals WHERE animals.id=matches.animal1ID) AS animal1, (SELECT * FROM users WHERE animals.id=matches.animalID) AS animal2 FROM matches WHERE id=5 However, MySQl returns this error: Operand should contain 1 column(s). Why? Is there an alternative way to do this, perhaps with a JOIN statement?

  Read the article

• Show the specific field on mysql table based on active date

  - by mrjimoy_05

  Suppose that I have 3 tables: A) Table UsrHeader ----------------- UsrID | UsrName ----------------- 1 | Abc 2 | Bcd B) Table UsrDetail ------------------------------- UsrID | UsrLoc | Date ------------------------------- 1 | LocA | 10 Aug 2012 1 | LocB | 15 Aug 2012 2 | LocA | 10 Aug 2012 C) Table Trx ----------------------------- TrxID | TrxDate | UsrID ----------------------------- 1 | 10 Aug 2012 | 1 2 | 16 Aug 2012 | 1 3 | 11 Aug 2012 | 2 What I want to do is to show the table like: --------------------------------------- TrxID | TrxDate | UsrID | UsrLoc --------------------------------------- 1 | 10 Aug 2012 | 1 | LocA 2 | 16 Aug 2012 | 1 | LocB 3 | 11 Aug 2012 | 2 | LocA Notice that there is one user but different location. That's based on the UsrDetail table that the user on a specified date has moved to another location. So, it should be show the user specific location on that date on every transaction. I have try this code but it is no luck: SELECT trx.TrxID, trx.TrxDate, trx.UsrID, User.UsrName, User.UsrLoc FROM trx INNER JOIN ( SELECT UsrHeader.UsrID, UsrHeader.UsrName, UserDetail.UsrLoc FROM UsrHeader INNER JOIN ( SELECT UsrDetail.UsrID, UsrDetail.UsrLoc, UsrDetail.Date FROM UsrDetail ) AS UserDetail ON UserDetail.UsrID = UsrHeader.UsrID ) AS User ON User.UsrID = trx.UsrID AND trx.TrxDate >= User.Date How to do that? Thanks..

  Read the article

• Good strategy for copying a "sliding window" of data from a table?

  - by chiborg

  I have a MySQL table from a third-party application that has millions of rows and only one index - the timestamp of each entry. Now I want to do some heavy self-joins and queries on the data using fields other than the timestamp. Doing the query on the original table would bring the database to a crawl, adding indexes to the table is not an option. Additionally, I only need entries that are newer than one week. My current strategy for doing the queries efficiently is to use a separate table (aux_table) that has the necessary indexes. My questions are: Is there another way to do the queries? and if not, How do I update the data in the indexed table efficiently? So far I have found two approaches for updating aux_table: Truncate aux_table and insert the desired data from the original table. Not very efficient because all the indexes must be re-crated. Check for the biggest timestamp in aux_table and insert all entries with a greater or equal timestamp from the original table. Occasionally drop older entries. Only copying entries with greater timestamp leads to dropped entries (because of entries with same timestamp that were inserted into the original table after the last update).

  Read the article

• MySQL - Select all as one string

  - by poru

  How could I select all as one string seperated with a ,? Example table: Table Stringtest Examplestring2 Anotherstring Otherstring And the selected result should be Stringtest,Examplestring2,Anotherstring,Otherstring.

  Read the article

• Why is the ( ) mandatory in the SQL statement select * from gifts INNER JOIN sentgifts using (giftID

  - by Jian Lin

  Why is the ( ) mandatory in the SQL statement select * from gifts INNER JOIN sentgifts using (giftID); ? The ( ) usually is for specifying grouping of something. But in this case, are we supposed to be able to use 2 or more field names... in the example above, it can be all clear that it is 1 field, is it just that the parser is not made to bypass the ( ) when it is all clear? (such as in the language Ruby).

  Read the article

• How to select the last two records for each topic_id in MySQL

  - by Luca Romagnoli

  Hi. I have to select the last two records for every topic. ex: table: msg id | topic_id ------------ 1 | 1 2 | 1 3 | 1 4 | 1 5 | 2 6 | 2 7 | 2 8 | 3 9 | 3 10 | 3 I want to obtain these rows: 3 1 4 1 6 2 7 2 9 3 10 3 How can I do this? thanks

  Read the article

• How to select the record contains MAX(some_field) within GROUP(group by)

  - by lovespring

  SELECT MAX(some_field) FROM table_A GROUP BY another_field. this only get the max value of the 'some_field', I wanna get the whole record which contains the MAX(some_field). thanks.

  Read the article

< Previous Page | 122 123 124 125 126 127 128 129 130 131 132 133  | Next Page >