planet mysql - Page 168

Get list of duplicate rows in MySql

- by user347033

Hi, i have a table like this ID nachname vorname 1 john doe 2 john doe 3 jim doe 4 Michael Knight I need a query that will return all the fields (select *) from the records that have the same nachname and vorname (in this case, records 1 and 2). Can anyone help me with this? Thanks

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

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 2 primary key onone table

- by Bharanikumar

CREATE TABLE Orders -> ( -> ID SMALLINT UNSIGNED NOT NULL, -> ModelID SMALLINT UNSIGNED NOT NULL, -> Descrip VARCHAR(40), -> PRIMARY KEY (ID, ModelID) -> ); Basically May i know ... Shall we create the two primary key on one table... Is it correct... Bcoz as per sql law,,, We can create N number of unque key in one table, and only one primary key only is the LAW know... Then how can my system allowing to create multiple primary key ? Please advise .... what is the general rule

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

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

MySql left join on several regs

- by egidiocs

Hi there! I have this table1 idproduct(PK) | date_to_go 1 2010-01-18 2 2010-02-01 3 2010-02-21 4 2010-02-03 and this other table2 that controls date_to_go updates id | idproduct(FK) | prev_date_to_go | date_to_go | update_date 1 1 2010-01-01 2010-01-05 2009-12-01 2 1 2010-01-05 2010-01-10 2009-12-20 3 1 2010-01-10 2010-01-18 2009-12-20 4 3 2010-01-20 2010-02-03 2010-01-05 So, in this example, for table1.idproduct #1 2010-01-18 is the actual date_to_go and 2010-01-01 (table2.prev_date_to_go, first reg) is the original date_to_go . using this query select v.idproduct, v.date_to_go, p.prev_date_to_go original_date_to_go from table1 v left join produto_datas p on p.idproduto = v.idproduto group by (v.idproduto) order by v.idproduto can I assume that original_date_to_go will be the first related reg of table2? idproduct | date_to_go | original_date_to_go 1 2010-01-18 2010-01-01 2 2010-02-01 NULL 3 2010-02-21 2010-01-20 4 2010-02-03 NULL

Read the article

mysql check if numbers are in a comma separated list

- by Tillebeck

Hi I have a table like this: UID(int) NUMBERS(blob) ---------------------- 1 1,13,15,20 2 3,10,15,20 3 3,15 And I would like to test if 3 and 15 are in the blob called NUMBERS. And can see the LIKE %% cannot be used Only row with ID 2 and three scoulb be selected...

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

Validating Login / Changing User settings / Php Mysql

- by Marcelo

Hi everyone, my questions are about login, and changing already saved data. (Q1) 'Till now I've only saved input in the tables of the database (registration steps), now I need to check if the input (login steps), are the same of my table in database, in fact I have 3 types of users, then I'll have to check 3 kind of tables. Then if the input data matches with one of those 3 tables I will redirect the user to his specific area. I'm thinking about saved the submitted data $login=$_REQUEST['login']; and $password=$_REQUEST['password']; and compare with the login column in the database. Then if the login matches, I'll compare the password submitted with the one in the row, not in the column. But I don't know how to do this search and comparison,neither what to use. Then if both matches I'll redirect the user. Else I'll send an login error message. (this I know how to do) (Q2) What if need to change an already saved user ? For example to change an email address. My changing user's data web page is exactly the same like the registration user web page. Can I load the already saved options and values of registration (table user for example). Then the user will change whatever he thinks it's necessary, and then when he submits the new information, they would not create a new row in my table, but just be overwritten the old information? How can I do this? Sorry for any mistake in English, and Thanks for the attention.

Read the article

MYSQL get the name from another table that is associated with the first table

- by Juan Gonzales

I can't figure out why this statement is not working SELECT myChurches.id AS id, myChurches.church_name AS church_name FROM myChurches INNER JOIN church_staff ON church_staff.church_id=myChurches.id WHERE church_staff.mem_id='$logOptions_id' ORDER BY myChurches.church_name ASC Basically I need to find the person's that are staff members of a church from one table and want to get the 'name' of that church FROM the 'myChurches' table. Hopefully that makes sense. Thanks in advance

Read the article

Nested mysql select statements

- by Jimmy Kamau

I have a query as below: $sult = mysql_query("select * from stories where `categ` = 'businessnews' and `stryid`='".mysql_query("SELECT * FROM comments WHERE `comto`='".mysql_query("select * from stories where `categ` ='businessnews'")." ORDER BY COUNT(comto) DESC")."' LIMIT 3") or die(mysql_error()); while($ow=mysql_fetch_array($sult)){ The code above should return the top 3 'stories' with the most comments {count(comto)}. The comments are stored in a different table from the stories. The code above does not return any values and doesn't show any errors. Could someone please help?

Read the article

MySQL & PHP Use of Undefined Constant

- by Nik

Alright, PHP is throwing this error (only in the logs): Error PHP Notice: Use of undefined constant department - assumed 'department' (line 5) PHP Notice: Use of undefined constant name - assumed 'name' (line 6) PHP Notice: Use of undefined constant email - assumed 'email' (line 7) PHP Notice: Use of undefined constant message - assumed 'message' (line 8) Lines 4-7 $department = mysql_real_escape_string($_POST[department]); $name = mysql_real_escape_string($_POST[name]); $email = mysql_real_escape_string($_POST[email]); $message = mysql_real_escape_string($_POST[message]); I think it has something to do with defining constants before defining them (how is this possible).

Read the article

PHP Moving mySQL Tree Node

- by TK

I am having trouble trying to move sub nodes or parent nodes up or down... not that good at math. CREATE TABLE IF NOT EXISTS `pages` ( page-id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, page-left mediumint(8) unsigned NOT NULL, page-right smallint(8) unsigned NOT NULL, page-title text NOT NULL, page-content text NOT NULL, page-time int(11) unsigned NOT NULL, page-slug text NOT NULL, page-template text NOT NULL, page-parent mediumint(8) unsigned NOT NULL, page-type text NOT NULL, PRIMARY KEY (page-id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; INSERT INTO pages (page-id, page-left, page-right, page-title, page-content, page-time, page-slug, page-template, page-parent, page-type) VALUES (17, 1, 6, '1', '', 0, 'PARENT', '', 0, ''), (18, 2, 5, '2', '', 0, 'SUB', '', 17, ''), (19, 3, 4, '3', '', 0, 'SUB-SUB', '', 18, ''), (20, 7, 8, '5', '', 0, 'TEST', '', 0, ''); As example how would I move TEST up above PARENT and say move SUB down below SUB-SUB by playing with the page-left/page-right IDs? Code is not required just help with the SQL concept or math for it, would help me understand how to move it better...

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

MySql too many connections

- by MichaelMcCabe

I hate to bring up a question which is widely asked on the web, but I cant seem to solve it. I started a project a while back and after a month of testing, I hit a "Too many connections" error. I looked into it, and "Solved" it by increasing the max_connections. This then worked. Since then more and more people started to use it, and it hit again. When I am the only user on the site, i type "show processlist" and it comes up with about 50 connections which are still open (saying "Sleep" in the command). Now, I dont know enough to speculate why these are open, but in my code I tripple checked and every connection I open, I close. ie. public int getSiteIdFromName(String name, String company)throws DataAccessException,java.sql.SQLException{ Connection conn = this.getSession().connection(); Statement smt = conn.createStatement(); ResultSet rs=null; String query="SELECT id FROM site WHERE name='"+name+"' and company_id='"+company+"'"; rs=smt.executeQuery(query); rs.next(); int id=rs.getInt("id"); rs.close(); smt.close(); conn.close(); return id; } Every time I do something else on the site, another load of connections are opened and not closed. Is there something wrong with my code? and if not, what could be the problem?

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 trigger to block a row delete

- by rtacconi

I wuold like to define a trigger to block the deletion of the row with ID 2 of the configuration table, you might guess why, I am trying something like that: CREATE TRIGGER do_not_delete_configuration_1 BEFORE DELETE ON configuration FOR EACH ROW BEGIN IF (OLD.configurationid != 1) THEN DELETE FROM configuration WHERE configuration.configuration=OLD.configurationid; END IF; END; | without a positive result.

Read the article

MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

- by user198729

SELECT u.id AS u__id, u.username AS u__username, p.id AS p__id, p.phonenumber AS p__phonenumber, p.user_id AS p__user_id FROM user u INNER JOIN phonenumber p ON u.id = p.user_id WHERE u.id IN (SELECT DISTINCT u2.id FROM user u2 INNER JOIN phonenumber p2 ON u2.id = p2.user_id LIMIT 20) This query is from here: http://www.doctrine-project.org/documentation/manual/1_2/en/dql-doctrine-query-language It's supposed to work on all DBMS ,but not in fact

Read the article

Modify MySQL INSERT statement to omit the insertion of certain rows

- by dave

I'm trying to expand a little on a statement that I received help with last week. As you can see, I'm setting up a temporary table and inserting rows of student data from a recently administered test for a few dozen schools. When the rows are inserted, they are sorted by the score (totpct_stu, high to low) and the row_number is added, with 1 representing the highest score, etc. I've learned that there were some problems at school #9999 in SMITH's class (every student made a perfect score and they were the only students in the district to do so). So, I do not want to import SMITH's class. As you can see, I DELETED SMITH's class, but this messed up the row numbering for the remainder of student at the school (e.g., high score row_number is now 20, not 1). How can I modify the INSERT statement so as to not insert this class? Thanks! DROP TEMPORARY TABLE IF EXISTS avgpct ; CREATE TEMPORARY TABLE avgpct_1 ( sch_code VARCHAR(3), schabbrev VARCHAR(75), teachername VARCHAR(75), totpct_stu DECIMAL(5,1), row_number SMALLINT, dummy VARCHAR(75) ); -- ---------------------------------------- INSERT INTO avgpct SELECT sch_code , schabbrev , teachername , totpct_stu , @num := IF( @GROUP = schabbrev, @num + 1, 1 ) AS row_number , @GROUP := schabbrev AS dummy FROM sci_rpt WHERE grade = '05' AND totpct_stu >= 1 -- has a valid score ORDER BY sch_code, totpct_stu DESC ; -- --------------------------------------- -- select * from avgpct ; -- --------------------------------------- DELETE FROM avgpct_1 WHERE sch_code = '9999' AND teachername = 'SMITH' ;

Read the article

php, mySQL & AJAX: Unable to use sessions across the scripts in the same domain

- by Devner

Hi all, I have the following pages: page1.php, page2.php and page3.php. Code in each of them is as below CODE: page1.php <script type="text/javascript"> $(function(){ $('#imgID').upload({ submit_to_url: "page2.php", file_name: 'myfile1', description : "Image", limit : 1, file_types : "*.jpg", }) }); </script> <body> <form action="page3.php" method="post" enctype="multipart/form-data" name="frm1" id="frm1"> //Some other text fields <input type="submit" name="submit" id="submit" value="Submit" /> </form> </body> page2.php <?php session_start(); $a = $_SESSION['a']; $b = $_SESSION['b']; $c = $_SESSION['c']; $res = mysql_query("SELECT col FROM table WHERE col1 = $a AND col2 = $b AND col3 = $c LIMIT 1"); $num_rows = mysql_num_rows($res); echo $num_rows; //echos 0 when in fact it should have been 1 because the data in the Session exists. //Ok let's proceed further //... Do some stuff... //Store some more values and create new session variables (and assume that page1.php is going to be able to use it) $_SESSION['d'] = 'd'; $_SESSION['e'] = 'e'; $_SESSION['f'] = 'f'; if (move_uploaded_file($_FILES['file']['tmp_name'], $file)) { echo "success"; } else { echo "error ".$_FILES['file']['error']; } ?> page3.php <?php session_start(); if( isset($_POST['submit']) ) { //These sessions are non-existent although the AJAX request //to page2.php may have created them when called via AJAX from within page1.php echo $_SESSION['d'].$_SESSION['e'].$_SESSION['f']; ?> } ?> As the code says it I am posting some info via AJAX call from page1.php to page2.php. page2.php is supposed to be able to use the session values from page1.php i.e. $_SESSION['a'], $_SESSION['b'] and $_SESSION['c'] but it does not. Why? How can I fix this? page2.php is creating some more sessions after some processing is done and a response is sent back to page1.php. The submit button of the form on page1.php is hit and the page gets POST'ed to page3.php. But when the SESSION info that gets created in page2.php is echoed, it's blank signifying that SESSIONS from page2.php are not used. How can I fix this? I looked over a lot of information and have spent about 50 hours trying to do different things with my scripts before arriving at the above conclusions. My app. is custom made using function (not OOPS) and does not use any PHP frameworks & I am not even about to use any as my knowledge of OOP concepts is limited any many frameworks are object oriented. I came across race conditions, but the solutions provided don't help too much. One more solution of using DB to hold sessions and seek and retrieve from DB is the last thing on my mind and I really want to avoid creating table, coding and maintaining code for a task as simple as just keeping sessions across pages in the same domain. So my request is: Is there a way that I can solve the above problem(s) via simple coding in present conditions? Any help is appreciated. Thank you.

Read the article

Single Column Multiple Filter In Mysql Intersection

- by Jeebus

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

What does this MySQL statement do?

- by user198729

INSERT IGNORE INTO `PREFIX_tab_lang` (`id_tab`, `id_lang`, `name`) (SELECT `id_tab`, id_lang, (SELECT tl.`name` FROM `PREFIX_tab_lang` tl WHERE tl.`id_lang` = (SELECT c.`value` FROM `PREFIX_configuration` c WHERE c.`name` = 'PS_LANG_DEFAULT' LIMIT 1) AND tl.`id_tab`=`PREFIX_tab`.`id_tab`) FROM `PREFIX_lang` CROSS JOIN `PREFIX_tab`); It's from an opensource project,and no documentation available. Especially,what does cross-join mean? I've only used join/left join .

Read the article

mysql left outer join

- by tirso

hi to all I have two tables employee and timecard, employee table has fields employee_id,firstname,middlename,lastname and timecard table has fields employee_id,time-in,time-out,tc_date_transaction. I want to select all employee records which have the same employee_id with timecard and date is equal with the current date. If there are no records equal with the current date then return also the records of employee even without time-in,timeout and tc_date_transaction. I have query like this SELECT * FROM employee LEFT OUTER JOIN timecard ON employee.employee_id = timecard.employee_id WHERE tc_date_transaction = "17/06/2010"; result should like this: employee_id,firstname, middlename, lastname,time-in,time-out,tc_date_transaction 1,john,t,cruz,08:00,05:00,17/06/2010 2,mary,j,von,null,null,null any help would greatly appreciated Thanks in advance

Search Results

Search found 14348 results on 574 pages for 'planet mysql'.

Page 168/574 | < Previous Page | 164 165 166 167 168 169 170 171 172 173 174 175 | Next Page >

- by user347033

- by dscher

- by Rockinelle

- by Bharanikumar

- by 404error

- by mazin k.

- by egidiocs

- by Tillebeck

- by PHP thinker

- by Marcelo

- by Juan Gonzales

- by Jimmy Kamau

- by Nik

- by TK

- by KSiimson

- by MichaelMcCabe

- by nickf

- by rtacconi

- by user198729

- by dave

- by Devner

- by Jeebus

- by Mint

- by user198729

- by tirso

< Previous Page | 164 165 166 167 168 169 170 171 172 173 174 175 | Next Page >