Search Results

Search found 68715 results on 2749 pages for 'mysql data'.

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 199/2749 | < Previous Page | 195 196 197 198 199 200 201 202 203 204 205 206  | Next Page >

  • mysql query: SELECT DISTINCT column1, GROUP BY column2

    - by Adam
    Right now I have the following query: SELECT name, COUNT(name), time, price, ip, SUM(price) FROM tablename WHERE time >= $yesterday AND time <$today GROUP BY name And what I'd like to do is add a DISTINCT by column 'ip', i.e. SELECT DISTINCT ip FROM tablename So my final output would be all the columns, from all the rows that where time is today, grouped by name (with name count for each repeating name) and no duplicate ip addresses. What should my query look like? (or alternatively, how can I add the missing filter to the output with php)? Thanks in advance.

    Read the article

  • How Can I truncate Multiple Tables in MySql?

    - by Luiscencio
    I need to clear all my inventory tables. I've tryed SELECT 'TRUNCATE TABLE ' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'inventory%' but I get this error: Truncated incorrect DOUBLE value: 'TRUNCATE TABLE ' Error Code 1292 if this is the correct way, then what am I doing wrong?

    Read the article

  • MYSQL: Simplify this Query for better performance

    - by Treby
    How can i simplify this code. coz this uses subquerying SELECT ub.id_product as c_pid,DATE(ub.datetime_prchs)AS datePurchased,cb.bookname, (SELECT GROUP_CONCAT(c.userid ORDER BY c.userid ASC SEPARATOR ', ') FROM user_books ub INNER JOIN campus_bookinfo cb ON ub.id_product=cb.idx_campus_bookinfo LEFT JOIN customer c ON ub.id_customer=c.id_customer WHERE ub.id_product = c_pid )as buyer, cb.iAmount FROM user_books ub INNER JOIN campus_bookinfo cb ON ub.id_product=cb.idx_campus_bookinfo LEFT JOIN customer c ON ub.id_customer=c.id_customer WHERE ub.id_customer = 29 GROUP BY bookname ORDER BY ub.datetime_prchs I need a better code for the same output.. Thanks in advance

    Read the article

  • Limiting selected rows count with a stored procedure parameter in MySQL

    - by ssobczak
    I have a procedure SelectProc wich contains SELECT statement. I want to add a procedure param LimitRowsCount and use it as following: CREATE PROCEDURE SelectProc (IN LimitRowsCount INTEGER UNSIGNED) BEGIN SELECT (...) LIMIT LimitRowsCount; END but this approach doesn't work. The SELECT itself contains nested subqueries so I can't create view from it. Is there a way more propper then dynamic SQL (prepared statements) ?

    Read the article

  • MySQL Cursor Issue

    - by James Inman
    I've got the following code - this is the first time I've really attempted using cursors. DELIMITER $$ DROP PROCEDURE IF EXISTS demo$$ DROP TABLE IF EXISTS temp$$ CREATE TEMPORARY TABLE temp( id INTEGER NOT NULL AUTO_INCREMENT, start DATETIME NOT NULL, end DATETIME NOT NULL, PRIMARY KEY(id) ) $$ CREATE PROCEDURE demo() BEGIN DECLARE done INT DEFAULT 0; DECLARE a, b DATETIME; DECLARE cur1 CURSOR FOR SELECT MAX(end) AS end FROM ( SELECT id, start, end, @r := @r + (start > @edate) AS num, @edate := GREATEST(@edate, end) FROM ( SELECT @r := 0, @edate := '0001-01-01' ) vars, student_lectures WHERE ( student_id = 1 AND start >= '2010-04-26 00:00:00' AND end <= '2010-04-30 23:59:59' ) ORDER BY start ) q GROUP BY num; DECLARE cur2 CURSOR FOR SELECT MIN(start) AS start FROM ( SELECT id, start, end, @r := @r + (start > @edate) AS num, @edate := GREATEST(@edate, end) FROM ( SELECT @r := 0, @edate := '0001-01-01' ) vars, student_lectures WHERE ( student_id = 1 AND start >= '2010-04-26 00:00:00' AND end <= '2010-04-30 23:59:59' ) ORDER BY start ) q GROUP BY num LIMIT 1, 18446744073709551615; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; OPEN cur2; REPEAT FETCH cur1 INTO a; FETCH cur2 INTO b; IF NOT done THEN INSERT INTO temp(start, end) VALUES(a,b); END IF; UNTIL done END REPEAT; CLOSE cur1; CLOSE cur2; END $$ SELECT * FROM temp; I'm not getting anything outputted into the temp table. Running the following query gives me output, so I know there's rows it should be matching - but I imagine I've made some obvious mistake. SELECT MAX(end) AS end FROM ( SELECT id, start, end, @r := @r + (start > @edate) AS num, @edate := GREATEST(@edate, end) FROM ( SELECT @r := 0, @edate := '0001-01-01' ) vars, student_lectures WHERE ( student_id = 1 AND start >= '2010-04-26 00:00:00' AND end <= '2010-04-30 23:59:59' ) ORDER BY start ) q GROUP BY num; The output this query returns: +---------------------+ | end | +---------------------+ | 2010-04-26 13:00:00 | | 2010-04-26 18:15:00 | | 2010-04-27 11:00:00 | | 2010-04-27 13:00:00 | | 2010-04-27 18:15:00 | | 2010-04-28 13:00:00 | | 2010-04-29 13:00:00 | | 2010-04-29 18:15:00 | | 2010-04-30 13:00:00 | | 2010-04-30 15:15:00 | | 2010-04-30 17:15:00 | +---------------------+ 11 rows in set (0.02 sec)

    Read the article

  • Mysql Left Join Null Result

    - by Ozzy
    I have this query SELECT articles.*, users.username AS `user` FROM `articles` LEFT JOIN `users` ON articles.user_id = users.id ORDER BY articles.timestamp Basically it returns the list of articles and the username that the article is associated to. Now if there is no entry in the users table for a particular user id, the users var is NULL. Is there anyway to make it that if its null it returns something like "User Not Found"? or would i have to do this using php?

    Read the article

  • mysql - count rows by field

    - by Qiao
    all rows in table have type field. It is either 0 or 1. I need to count rows with 0 and with 1 in one query. So that result is something like: type0 type1 1234 4211 How it can be implemented?

    Read the article

  • problems with mysql "or"

    - by Simon
    i have a problem with "and" "or" syntax!!! here is my query. . . SELECT `act1`.`name`, `act1`.`surname`, `act2`.`name`, `act2`.`surname`, `act3`.`name`, `act3`.`surname` FROM videos, actors AS act1, actors AS act2, actors AS act3 WHERE videos.ident = 'somethink' AND ( act1.id = videos.id_actor1 OR act2.id = videos.id_actor2 OR act3.id = videos.id_actor3 ) it returns me all variations of {name, surname}, but why!!! i want the name and surname of first axactly, second and thirth, if they exist!!! halp me please

    Read the article

  • Incorrect table name, php/mysql

    - by user296516
    Hi guys, I've got this code mysqli_query ( $userdatabase, 'CREATE TABLE `user_'.$emailreg.'` ( ID int NOT NULL AUTO_INCREMENT PRIMARY KEY, IP varchar(10), FLD1 varchar(20), FLD2 varchar(40), FLD3 varchar(25), FLD4 varchar(25), FLD5 varchar(25) )' ); echo ( mysqli_error ($userdatabase) ); that works fine on my localhost, but when I upload it to the server, it starts giving me a "Incorrect table name '[email protected]'" error. any idea? Thanks!

    Read the article

  • MySQL: Transactions across multiple threads

    - by Zombies
    Preliminary: I have an application which maintains a thread pool of about 100 threads. Each thread can last about 1-30 seconds before a new task replaces it. When a thread end, that thread almost always will result in inserting 1-3 records into a table, this table is used by all of the threads. Right now, no transactional support exists, but I am trying to add that now. So... Goal I want to implement a transaction for this. The rules for whether or not this transaction commits or rollback reside in the main thread. Basically there is a simple function that will return a boolean. Can I implement a transaction across multiple connections? If not, can multiple threads share the same connection? (Note: there are a LOT of inserts going on here, and that is a requirement).

    Read the article

  • Grouping timestamps by interval between timestamps, then calculating from group MySQL

    - by Emile
    To put this question into context, I'm trying to calculate "time in app" based on an event log. Assume the following table: user_id event_time 2 2012-05-09 07:03:38 3 2012-05-09 07:03:42 4 2012-05-09 07:03:43 2 2012-05-09 07:03:44 2 2012-05-09 07:03:45 4 2012-05-09 07:03:52 2 2012-05-09 07:06:30 I'd like to get the difference between the highest and lowest event_time from a set of timestamps that are within 2 minutes of eachother (and grouped by user). If a timestamp is outside of a 2 minute interval from the set, it should be considered a part of another set. Desired output: user_id seconds_interval 2 7 (because 07:03:45 - 07:03:38 is 7 seconds) 3 0 (because 07:03:42) 4 9 (because 07:03:52 - 2012-05-09 07:03:43) 2 0 (because 07:06:30 is outside 2 min interval of 1st user_id=2 set) This is what I've tried, although I can't group on seconds_interval (even if I could, I'm not sure this is the right direction): SELECT (max(tr.event_time)-min(tr.event_time)) as seconds_interval FROM some_table tr INNER JOIN TrackingRaw tr2 ON (tr.event_time BETWEEN tr2.event_time - INTERVAL 2 MINUTE AND tr2.event_time + INTERVAL 2 MINUTE) GROUP BY seconds_interval

    Read the article

  • Select between dates with wildcard mysql

    - by Michael
    Hi, I am trying to select between two dates, but in the table they have timestamps after them. Example: calldate is 2010-03-16 22:43:28 How would I select this using calldate between '$date1' AND '$date2' to wildcard the timestamp after the date? I tried using like but it didn't work I just want to do between '$date1%' AND '$date2%' Anyone know how to do this? Thank you in advance!

    Read the article

  • Mysql storing quotes as &#39;

    - by Click Upvote
    I have some PHP code which stores whatever is typed in a textbox in the databse. If I type in bob's apples, it gets stored in the database as bob&#39;s apples. What can be the problem? The table storing this has the collation of latin1_swedish_ci.

    Read the article

  • MySQL DUMP as CSV

    - by swt83
    I've looked around and nothing seems to work: $file = '/path/to/file.csv'; $cmd = 'mysqldump DATABASE TABLE > '.$file.' --host=localhost --user=USER --password=PASS'; $cmd .= ' --lock-tables=false --no-create-info --tab=/tmp --fields-terminated-by=\',\''; exec($cmd); Everything I try creates an empty CSV file. Any ideas? Thanks much.

    Read the article

  • Sorting MySQL results within a resultset

    - by InnateDev
    I have a resultset of lets say 10 results. 3 of them have a type 'Pears', and the next 3 have a type 'Apples' and the next three have a type of 'Bananas'. The last record has a type of 'Squeezing Equipment' - unrelated to the fruits. How do I return this set of results (for pagination too) in a GROUPED order that I specify WITHOUT using any inherent sort factor like ALPHABETA or ID etc? I have the all types at my disposal before running the code so they can be specified. i.e. ID | Bananas ID | Bananas ID | Bananas ID | Apples ID | Apples ID | Apples ID | Pears ID | Pears ID | Pears ID | Squeezing Equipment

    Read the article

  • MySQL Select problems

    - by John Nuñez
    Table #1: qa_returns_items Table #2: qa_returns_residues I have a long time trying to get this Result: item_code - item_quantity 2 - 1 3 - 2 IF qa_returns_items.item_code = qa_returns_residues.item_code AND status_code = 11 THEN item_quantity = qa_returns_items.item_quantity - qa_returns_residues.item_quantity ELSEIF qa_returns_items.item_code = qa_returns_residues.item_code AND status_code = 12 THEN item_quantity = qa_returns_items.item_quantity + qa_returns_residues.item_quantity ELSE show diferendes END IF I tried this Query: select SubQueryAlias.item_code, total_ecuation, SubQueryAlias.item_unitprice, SubQueryAlias.item_unitprice * total_ecuation as item_subtotal, item_discount, (SubQueryAlias.item_unitprice * total_ecuation) - item_discount as item_total from ( select ri.item_code , case status_code when 11 then ri.item_quantity - rr.item_quantity when 12 then ri.item_quantity + rr.item_quantity end as total_ecuation , rr.item_unitprice , rr.item_quantity , rr.item_discount * rr.item_quantity as item_discount from qa_returns_residues rr left join qa_returns_items ri on ri.item_code = rr.item_code WHERE ri.returnlog_code = 1 ) as SubQueryAlias where total_ecuation > 0 GROUP BY (item_code); The query returns this result: item_code - item_quantity 1 - 2 2 - 2

    Read the article

  • Mysql CASE and UPDATE

    - by Rosengusta Garrett
    I asked yesterday how I could update only the first column that was empty. I got this of a answer: UPDATE `names` SET `name_1` = CASE WHEN `name_1` = '' then 'Jimmy' else `name_1` end, `name_2` = CASE WHEN `name_1` != '' and `name_2` = '' then 'Jimmy' else `name_2` end I tried it and it ended up updating every column with 'Jimmy' what's wrong with this? I can't find anything. It could possibly be the structure of the database. So here is what each name_* column is setup like: # Name Type Collation Attributes Null Default Extra 1 name_1 varchar(255) latin1_swedish_ci No None

    Read the article

  • Mysql eliminate user based on conditions

    - by Dustin
    I asked this last week over the weekend and it got buried in the archives before anyone could answer. So forgive me if you've already seen this. I teach classes and want to be able to select those students who have taken one class, but not another class. I have two tables: lessons_slots which is the table for every class such as: -------------------- -ID name slots- -1 basics 10 - -2 advanced 10 - -3 basics 10 - --------------------- The other table is class_roll, which holds enrollment info, such as: -------------------- -sID classid firstname lastname- -1 1 Jo Schmo -2 1 Person Two ... -13 2 Jo Schmo --------------------- What I want to do, I select everyone who has not had the advanced class (for example). I've tried doing SELECT * FROM lessons_slots LEFT JOIN class_roll ON lessons_slots.ID = class_roll.classid WHERE lessons_slots.name != 'advanced' But that doesn't work...All it does is eliminate that row, without eliminating the user. I want Jo Schmo, for example, to not show up in the results. Any ideas?

    Read the article

  • mysql select update

    - by Tillebeck
    Got this: Table a ID RelatedBs 1 NULL 2 NULL Table b AID ID 1 1 1 2 1 3 2 4 2 5 2 6 Need Table a to have a comma separated list as given in table b. And then table b will become obsolete: Table a ID RelatedBs 1 1,2,3 2 4,5,6 This does not rund through all records, but just ad one 'b' to 'table a' UPDATE a, b SET relatedbs = CONCAT(relatedbs,',',b.id) WHERE a.id = b.aid

    Read the article

  • PHP MySQL join table

    - by Jordan Pagaduan
    $sql = "SELECT logs.full_name, logout.status FROM logs, logout WHERE logs.employee_id = logout.employee_id"; tables -- logs logout I'm having error on this. I search join tables in google. And that's what I got. What is wrong with this code?

    Read the article

  • MySQL where condition but not limited by it

    - by Manny Calavera
    Hello. I would like to run a query on my database like this: SELECT SUM( t1.value ) AS total1, SUM( t2.value ) AS total2, SUM( t3.value ) AS total3, SUM( t4.value ) AS total4 FROM pay1 t1, pay2 t2, pay3 t3, pay4 t4 WHERE t1.date = '2010-04-29' AND t2.date = '2010-04-29' AND t3.date = '2010-04-29' AND t4.date = '2010-04-29' I am generating a report on payments and I would like to see a total of payments from each table based on the matching date. The problem is that some of the tables would not meet the condition of date and I want them to show up with 0 value if not. Currently, if any of the tables does not match the date, I get 0 results. I want to display value of 0 anywhere the date is not met and other fields should appear with the found values. The perfect operand for me would be "ANDOR" so that it won't be limited by any date that doesn't math in any table. Unfortunately, ANDOR does not exist as I am aware of so what should I do ? Can anyone help ? Thanks.

    Read the article

  • Show last 4 table entries mysql php

    - by user272899
    I have a movie database Kind of like a blog and I want to display the last 4 created entries. I have a column in my table for timestamp called 'dateadded'. Using this code how would I only display the 4 most recent entries to table <?php //connect to database mysql_connect($mysql_hostname,$mysql_user,$mysql_password); @mysql_select_db($mysql_database) or die("<b>Unable to connect to specified database</b>"); //query databae $query = "SELECT * FROM movielist"; $result=mysql_query($query) or die('Error, insert query failed'); $row=0; $numrows=mysql_num_rows($result); while($row<$numrows) { $id=mysql_result($result,$row,"id"); $imgurl=mysql_result($result,$row,"imgurl"); $imdburl=mysql_result($result,$row,"imdburl"); ?> <div class="moviebox rounded"><a href="http://<?php echo $domain; ?>/viewmovie?movieid=<?php echo $id; ?>" rel="facebox"> <img src="<?php echo $imgurl; ?>" /> <form method="get" action=""> <input type="text" name="link" class="link" style="display:none" value="http://us.imdb.com/Title?<?php echo $imdburl; ?>"/> </form> </a></div> <?php $row++; } ?>

    Read the article

  • How to simplify my country select menu PHP/mysql

    - by user342391
    I have a select menu that displays countries. It looks at the DB and judging by the value in the db shows the option as selected. Is there a simpler way off doing this than: if ($country == 'AG') {echo '<option value="AG" selected="selected">Antigua</option>';} else {echo '<option value="AG">Antigua</option>';}; if ($country == 'AR') {echo '<option value="AR" selected="selected">Argentina</option>';} else {echo '<option value="AR">Argentina</option>';}; if ($country == 'AM') {echo '<option value="AM" selected="selected">Armenia</option>';} else {echo '<option value="AM">Armenia</option>';}; if ($country == 'AW') {echo '<option value="AW" selected="selected">Aruba</option>';} else {echo '<option value="AW">Aruba</option>';}; if ($country == 'AU') {echo '<option value="AU" selected="selected">Australia</option>';} else {echo '<option value="AU">Australia</option >';}; if ($country == 'AT') {echo '<option value="AT" selected="selected">Austria</option>';} else {echo '<option value="AT">Austria</option>';}; if ($country == 'AZ') {echo '<option value="AZ" selected="selected">Azerbaijan</option>';} else {echo '<option value="AZ">Azerbaijan</option>';}; if ($country == 'BS') {echo '<option value="BS" selected="selected">Bahamas</option>';} else {echo '<option value="BS">Bahamas</option>';}; if ($country == 'BH') {echo '<option value="BH" selected="selected">Bahrain</option>';} else {echo '<option value="BH">Bahrain</option>';}; There are a lot of countries and doing this would be madness wouldn't it????

    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 | 195 196 197 198 199 200 201 202 203 204 205 206  | Next Page >