mysql select db - Page 187

what mysql table structure is better

- by Sergey

I have very complicated search algorithm on my site, so i decided to make a table with cache or maybe all possible results. I wanna ask what structure would be better, or maybe not the one of them? (mySQL) 1) word VARCHAR, results TEXT or BLOB where i'll store ids of found objects (for example 6 chars for each id) 2) word VARCHAR, result INT, but words are not unique now i think i'll have about 200 000 rows in 1) with 1000-10000 ids each row or 200 000 000+ rows in 2) First way takes more storage memory but i think it would be much faster to find 1 unique row among 200 000, than 1000 rows among 200 mln non unique rows i think about index on word column and no sphinx. So that do YOU think? p.s. as always, sorry for my english if it's not very good.

Read the article

MySQL UpdateXML with automatic node inserting?

- by JimPsr

I am writing an application that supports custom fields. Currently I store all custom fields in a XML formated text field ( e.g. '<root><field1>val1</field1><field2>val2</field2></root>' in cust_field) I am able to to use updateXML(cust_field, '/root/field1', '<field1>new value</field1') to update those values, however if I use updateXML(cust_field, '/root/field3', '<field3>new value</field3>') then it does not work since field3 is not in the old value. Is there a way to let MySQL automatically insert the new field3 node and its value into cust_field? I am thinking about stored procedure or even stored function but not familiar with both, can anyone point me to the right direction?

Read the article

viewing data using php and mysql

- by circle

hello, i'm circle.very new in php and mysql.i need to develop a website include with database.but i have a problem with this. i'm using php and sql to develop it.my task is: i have two table, investor and commission. investor have three level which is coordinator, consultant and participant.what should i do is, only coordinator can add consultant and participant in their profile.and only coordinator can add participant in their profile. so here, when investor was login, and they go to view commission, they only can see their owned coordinator and participant. but i'm stuck when they want to view the commission.maybe it only a simple thing, but i cannot solved it. help me..:(

Read the article

mysql - check if data exists across multiple tables

- by Dd Daym

I am currently running this query inside MySQL to check if the specified values exists within the table associated with them. SELECT COUNT(artist.artist_id), COUNT(album.album_id), COUNT(tracks.track_id) FROM artist, album, tracks WHERE artist.artist_id = 320295 OR album.album_id = 1234 OR tracks.track_id = 809 The result I get from running this query is all 1, meaning that all the statements after the WHERE clause is true. To further check the query's reliability, I changed the tracks.track_ = 809 to 802, which I know does not match. However the results displayed are still all 1, meaning that they were all successfully matched even when I purposefully inserted a value which would not have matched. How do I get it to show 1 for a match and 0 for no matches within the same query? EDIT: I have inserted an image of the query running

Read the article

Converting delimited string to multiple values in mysql

- by epo

I have a mysql legacy table which contains an client identifier and a list of items, the latter as a comma-delimited string. E.g. "xyz001", "foo,bar,baz". This is legacy stuff and the user insists on being able to edit a comma delimited string. They now have a requirement for a report table with the above broken into separate rows, e.g. "xyz001", "foo" "xyz001", "bar" "xyz001", "baz" Breaking the string into substrings is easily doable and I have written a procedure to do this by creating a separate table, but that requires triggers to deal with deletes, updates and inserts. This query is required rarely (say once a month) but has to be absolutely up to date when it is run, so e.g. the overhead of triggers is not warranted and scheduled tasks to create the table might not be timely enough. Is there any way to write a function to return a table or a set so that I can join the identifier with the individual items on demand?

Read the article

mysql syntax how to add a third table to $query

- by IberoMedia

I have code: $query = "SELECT a.*, c.name as categoryname, c.id as categoryid". " FROM #__table_one as a". " LEFT JOIN #__table_two c ON c.id = a.catid"; $query .= " WHERE a.published = 1" ." AND a.access <= {$aid}" ." AND a.trash = 0" ." AND c.published = 1" ." AND c.access <= {$aid}" ." AND c.trash = 0" ; I would like to add a third table ('__some_table') for the parts of the query where a.publish, a.access and a.trash. In other words, I want these fields to be retrieved from another table, not "#__table_one", but I do not know how to incorporate the #__some_table into the current query I imagine the JOIN command can help me, but I do not know how to code mysql Thank you,

Read the article

How to find root cause for "too many connections" error in MySQL/PHP

- by Nir

I'm running a web service which runs algorithms that serve millions of calls daily and run some background processing as well. Every now and than I see "Too many connections" error in attempts to connect to the MySQL box" for a few seconds. However this is not necessarily attributed to high traffic times or anything I can put my finger on. I want to find the bottleneck causing it. Other than in the specific times this happens the server isn't too loaded in terms of CPU and Memory, and has 2-3 connections (threads) open and everything works smoothly. (I use Zabbix for monitoring) Any creative ideas on how to trace it?

Read the article

manipulating 15+ million records in mysql with php?

- by Nithish

Hey, I got a user table containing 15+ million records and while doing the registration function i wish to check whether the username already exist. I did indexing for username column and when i run the query "select count(uid) from users where username='webdev'" ,. hmmm, its keep on loading blank screen finally hanged up. I'm doing this in my localhost with php 5 & mysql 5. So suggest me some technique to handle this situation. Is that mongodb is good alternative for handling this process in our local machine? Thanks, Nithish.

Read the article

Streaming large result sets with MySQL

- by configurator

I'm developing a spring application that uses large MySQL tables. When loading large tables, I get an OutOfMemoryException, since the driver tries to load the entire table into application memory. I tried using statement.setFetchSize(Integer.MIN_VALUE); but then every ResultSet I open hangs on close(); looking online I found that that happens because it tries loading any unread rows before closing the ResultSet, but that is not the case since I do this: ResultSet existingRecords = getTableData(tablename); try { while (existingRecords.next()) { // ... } } finally { existingRecords.close(); // this line is hanging, and there was no exception in the try clause } The hangs happen for small tables (3 rows) as well, and if I don't close the RecordSet (which happened in one method) then connection.close() hangs.

Read the article

Error 1005 when adding a foreign key constraint on mysql table

- by luc

Hello, I have a problem when upgrading a django and mysql app with south. I've tried to make a sql-based upgrade with the code generated by the django sqlall command and I have a similar problem. Here is the sql code: CREATE TABLE `programmations_basissupport` ( `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `value` numeric(6, 0) NOT NULL ) ALTER TABLE `programmations_concert` ADD `basis_support_id` integer AFTER program_status_id; ALTER TABLE `programmations_concert` ADD CONSTRAINT `basis_support_id_refs_id_1e4ed8d7` FOREIGN KEY (`basis_support_id`) REFERENCES `programmations_basissupport` (`id`); An error is raised when adding the FK constraint: ERROR 1005 (HY000): Can't create table 'apidev_mnl.#sql-106e_632b00a' (errno: 150) Does anybody have an idea? Update: DEFAULT values where missing but even if I add the default='' in the django model, the creation of foreign keys fails. Thanks for your help

Read the article

Mysql maven jpa skeleton

- by coubeatczech

Hi, is there a skeleton for a project using mysql, some eclipse/top link with RESOURCE_LOCAL as connection type? Preferably using maven. I'm searching for it for hours and can't get running even the sipmlest exaple. So if you had it ready and running, please, post :-). Even something as simple as these two classes only. @Entity public class Message implements Serializable{ public Message() {} public Message(String s){ this.s = s; } @Id String s; public String getS(){ return s; } } public class App { static private EntityManagerFactory emf; static private EntityManager em; public static void main( String[] args ) { emf = Persistence.createEntityManagerFactory("persistence"); em = emf.createEntityManager(); Message m = new Message("abc"); em.persist(m); } }

Read the article

php codeigniter MySQL search query

- by kalafun

I want to create a search query on MySQL database that will consist of 5 different strings typed in from user. I want to query 5 different table columns with these strings. When I for example have input fields like: first name, last name, address, post number, city. How should I query the database that I dont always get all the rows. My query is something like this: SELECT user_id, username from users where a like %?% AND b like %?% AND c like %?% AND d like %?% AND e like %?%; When I exchange the AND for OR I always get all the results which makes sense, and when I use AND I get only the exact matches... Is there any function or statement that would help me with this?

Read the article

Regular Expression for exclude something that has specific word inside bracked (MySQL)

- by bn

This Regular expression if for MySQL query: I want to exclude this row because it has 'something' in side the bracket "bla bla bla bla bla bla (bla bla bla something)" However I want to include this row, because it does not have 'something' inside the bracket "bla bla bla (bla bla bla)" I tried this query but it didnt work. SELECT * FROM table WHERE field NOT REGEXP '((%something%))'; I think this is wrong, I just did trial and error, I like to use regular expression, but never understand it completely. is there any good tutorial/books/links for learning the detail of regular expression? Thank You

Read the article

INSERT 0..n records into table 'A' based on content of table 'B' in MySql 5

- by Robert Gowland

Using MySql 5, I have a task where I need to update one table based on the contents of another table. For example, I need to add 'A1' to table 'A' if table 'B' contains 'B1'. I need to add 'A2a' and 'A2b' to table 'A' if table 'B' contains 'B2', etc.. In our case, the value in table 'B' we're interested is an enum. Right now I have a stored procedure containing a series of statements like: INSERT INTO A SELECT 'A1' FROM B WHERE B.Value = 'B1'; --Repeat for 'B2' -> 'A2a'; 'B2' -> 'A2b'; 'B3' -> 'A3', etc... Is there a nicer more DRY way of accomplishing this? Edit: There may be values in table 'B' that have no equivalent value for table 'A'.

Read the article

Using MySQL as a job queue

- by user237815

I'd like to use MySQL as a job queue. Multiple machines will be producing and consuming jobs. Jobs need to be scheduled; some may run every hour, some every day, etc. It seems fairly straightforward: for each job, have a "nextFireTime" column, and have worker machines search for the job with the nextFireTime, change the status of the record to "inProcess", and then update the nextFireTime when the job ends. The problem comes in when a worker dies silently. It won't be able to update the nextFireTime or set the status back to "idle". Unfortunately, jobs can be long-running, so a reaper thread that looks for jobs that have been inProcess too long isn't an option. There's no timeout value that would work. Can anyone suggest a design pattern that would properly handle unreliable worker machines?

Read the article

advanced search with mysql

- by Arsenal

I'm creating a search function for my website where the user can put in anything he likes in a textfield. It get's matched against anything (name, title, job, car brand, ... you name it) I initially wrote the query with an INNER JOIN on every table that needed to be searched. SELECT column1, column2, ... FROM person INNER JOIN person_car ON ... INNER JOIN car ... This ended up in a query with 6 or 8 INNER JOINs, and a whole lot WHERE ... LIKE '%searchvalue%' Now this query seems to cause a time'out in MySql, and I even got a warning from my hosting provider that the queries just taking up too many resources. Now obviously I'm doing this very wrong, but I was wondering how the correct approach to these kind of search functions is. Thanks!

Read the article

MySQL query with 2 COUNT() of other tables with where conditions

- by Isern Palaus

Read the article

MySQL get content from web page (or other network resource)

- by Rescommunes

Is it possible to open a curl like object in MySQL? What I would like to do is create procedure which would check to see if a certain value is returned from a specific URL like http://example.com/inschedule?id=200&time=20m. The returned result would be a simple string like 1 or 0. I know it is better to have a script do this by putting a entry into a table etc. However, it would be much less complex for me to be able to do it this way. Thanks

Read the article

PHP and MySQL - Printing rows matching a column value

- by Michael

Hello, I need to write a PHP script that will print out results from a MySQL database. For example, say I have 9 fields. Field 1 is an auto increasing number, field two is a three digit number. I need to be able to have a script read, find the matching number (it'll be from a POST), and then display all matching three digit results, and the 7 other fields as well. I am already logged in to the database in this script. I guess I'm really at a loss of where to begin. How would one start something like this? Thank you.

Read the article

MySQL storage engine dilemma

- by burntblark

There are two MySQL database features that I want to use in my application. The first is FULL-TEXT-SEARCH and TRANSACTIONS. Now, the dilemma here is that I cannot get this feature in one storage engine. It's either I use MyIsam (which has the FULL-TEXT-SEARCH feature) or I use InnoDB (which supports the TRANSACTION feature). I can't have both. My question is, is there anyway I can have both features in my application before I am forced to make a choice between the two storage engines.

Read the article

Getting mysql syntax error and cant find source

- by eGGzy

I have function that updates log table. function wslog($userID, $log, $where) { safe_query("INSERT INTO ".PREFIX."log ( time, userID, log, where ) values( '".time()."', '".$userID."', '".$log."', '".$where."' ) "); } And I have this php code: wslog($userID, 'server|'.mysql_insert_id().'', 'servers'); But I keep getting syntax error: Query failed: errorno=1064 error=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 'where ) values( '1269208030', '1', 'server|14', 'servers' )' at line 1 query=INSERT INTO ws_DII_log ( time, userID, log, where ) values( '1269208030', '1', 'server|14', 'servers' )

Read the article

MySQL COUNT help

- by space

I'm trying to count all the ids form table3 that are related to the first two tables but I think my SQL code is wrong can some one help me fix it? Here is the code: $dbc = mysqli_query($mysqli,"SELECT table1.*, table2.*, COUNT(id) as num, table3.id FROM table1 INNER JOIN table2 ON table1.id = table2.id INNER JOIN table3 ON table2.id = table3.id WHERE table2.id = '$id'"); Here is the error message. Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 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 '' at line 4

Read the article

MySQL query problem

- by FinalDestiny

I have 2 mysql tables 1. questions: with the following columns: id, title, answer1, answer2, answer3, answer4, answer5, nranswers. and 2. answers with the following columns: id, questionid, userid, answer Every question has maximum 5 answers( it can have between 2 and 5 answers). My problem is that I want to select from my database, for a given question, how many times was every option selected. For example, let's suppose I have the question with the id 46, with 4 answers, and 48 users voted for the option #2, 37 users for the option #1 and 39 for the option #4. I want a query that selects that and write these things: 1 37 2 48 3 0 4 39 P.S. VERY IMPORTANT! IT MUST COUNT ONLY NRANSWERS ANSWERS, AND IT MUST ECHO THE ONES THAT WEREN'T VOTED BEFORE.

Read the article

Storing dates i Train schedule MYSQL

- by App_beginner

Hi I have created a train schedule database in MYSQL. There are several thousand routes for each day. But with a few exceptions most of the routes are similar for every working day, but differ on weekends. At this time I basically update my SQL tables at midnight each day, to get the departures for the next 24 hours. This is however very inconvenient. So I need a way to store dates in my tables so I don't have to do this every day. I tried to create a separate table where I stored dates for each routenumber (routenumbers are resetted each day), but this made my query so slow that it was impossible to use. Does this mean I would have to store my departure and arrival times as datetimes? In that case the main table containing routes would have several million entries. Or is there another way? My routetable looks like this: StnCode (referenced in seperate Station table) DepTime ArrTime Routenumber legNumber

Read the article

MySQL look for missing ratings

- by Dustin

Hopefully I can ask this without being confusing. I am a photographer and I am having some of our clients rate pictures that we have taken. We have hundreds of pictures in our portfolio that they may be rating. What I want to do is ask our clients to rate pictures again, but only show them the pictures they haven't yet rated. I currently have three tables: one that stores the actual ratings, one that stores the pictures (or location of each picture), and one that stores the information about the rater. I'm using codeigniter for my db management, if that helps at all. What I have so far is this: "SELECT * FROM ratings LEFT JOIN portfolio ON ratings.portfolioid = portfolio.portfolioid" This will give me a row for each rating, but won't show me where a rating is missing for a picture. Thanks in advance!

Search Results

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

Page 187/1624 | < Previous Page | 183 184 185 186 187 188 189 190 191 192 193 194 | Next Page >

- by Sergey

- by JimPsr

- by circle

- by Dd Daym

- by epo

- by IberoMedia

- by Nir

- by Nithish

- by configurator

- by luc

- by coubeatczech

- by kalafun

- by bn

- by Robert Gowland

- by user237815

- by Arsenal

- by Isern Palaus

- by Rescommunes

- by Michael

- by burntblark

- by eGGzy

- by space

- by FinalDestiny

- by App_beginner

- by Dustin

< Previous Page | 183 184 185 186 187 188 189 190 191 192 193 194 | Next Page >