Search Results

Search found 58168 results on 2327 pages for 'mysql real escape string'.

Page 180/2327 | < Previous Page | 176 177 178 179 180 181 182 183 184 185 186 187  | Next Page >

• MySQL ON DUPLICATE KEY UPDATE issue

  - by user644347

  Hi could some one look at this and tell me where I am going wrong. I have an SQL statement that when I echo using php I get this to screen INSERT INTO 'moviedb'.'genre' SET 'GenreID' = '18' , 'GenreName' = 'Drama' ON DUPLICATE KEY UPDATE 'GenreName' = 'Drama' WHERE 'GenreID' = '18' INSERT INTO 'moviedb'.'genre' SET 'GenreID' = '16' , 'GenreName' = 'Animation' ON DUPLICATE KEY UPDATE 'GenreName' = 'Animation' WHERE 'GenreID' = '16' And here is the statement $sql="INSERT INTO 'moviedb'.'genre' SET 'GenreID' = '{$genresID[$i]}' , 'GenreName' = '{$genreName[$i]}' ON DUPLICATE KEY UPDATE 'GenreName' = '{$genreName[$i]}' WHERE 'GenreID' = '{$genresID[$i]}'"; This is the error I recieve: 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 ''moviedb'.'genre' SET 'GenreID' = '18' , 'GenreName' = 'Drama' ON DUPLICATE KEY ' at line 1 Any help would be greatly appreciated, thanks in advance.

  Read the article

• How to add indexes to MySQL tables?

  - by Michael

  I've got a very large MySQL table with about 150,000 rows of data. Currently, when I try and run SELECT * FROM table WHERE id = '1'; the code runs fine as the ID field is the primary index. However, recently for a development in the project, I have to search the database by another field. For example SELECT * FROM table WHERE product_id = '1'; This field was not previously indexed, however, I've added it as an index but when I try to run the above query, the results is very slow. An EXPLAIN query reveals that there is no index for the product_id field when I've already added one and as a result the query takes any where from 20 minutes to 30 minutes to return a single row. EDIT: My full EXPLAIN results are: | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+----------------------+------+---------+------+------+------------------+ | 1 | SIMPLE | table | ALL | NULL | NULL | NULL | NULL | 157211 | Using where | +----+-------------+-------+------+----------------------+------+---------+------+------+------------------+

  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

• 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

• 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 select where count = 0

  - by david parloir

  Hi, In my db, I have a "sales" table and a "sales_item". Sometimes, something goes wrong and the sale is recorded but not the sales item's. So I'm trying to get the salesID from my table "sales" that haven't got any rows in the sales_item table. Here's the mysql query I thought would work, but it doesn't: SELECT s.* FROM sales s NATURAL JOIN sales_item si WHERE s.date like '" . ((isset($_GET['date'])) ? $_GET['date'] : date("Y-m-d")) . "%' AND v.sales_id like '" . ((isset($_GET['shop'])) ? $_GET['shop'] : substr($_COOKIE['shop'], 0, 3)) ."%' HAVING count(si.sales_item_id) = 0; Any thoughts?

  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

• 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

• How do I delete in Django? (mysql transactions)

  - by alex

  If you are familiar with Django, you know that they have a Authentication system with User model. Of course, I have many other tables that have a Foreign Key to this User model. If I want to delete this user, how do I architect a script (or through mysql itself) to delete every table that is related to this user? My only worry is that I can do this manually...but if I add a table , but I forget to add that table to my DELETE operation...then I have a row that links to a deleted, non-existing User.

  Read the article

• Using AND/OR mysql commands with FROM_UNIXTIME

  - by scatteredbomb

  Trying to select a query in php/mysql to get "Upcoming Items" in a calendar. We store the dates in the DB as a unix time. Here's what my query looks like right now SELECT * FROM `calendar` WHERE (`eventDate` > '$yesterday') OR (FROM_UNIXTIME(eventDate, '%m') > '$current_month' AND `$yearly` = '1') ORDER BY `eventDate` LIMIT 4 This is giving me an error "Unknown column '' in 'where clause'". I'm sure it has to do with my use of parenthesis (which I've never used before in a query) and the FROM_UNIXTIME command. Can someone help me out and let me know how I've screwed this up? Thanks!

  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

• How do I implement real time front end updates using node.js+ + ror?

  - by ming yeow

  what is the best way to implement real time updates in ruby on rails using Node.JS? It would be great to hear either real examples or your thoughts on alternative solutions

  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

• 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

• 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 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

• [php + mysql] Save IP in database

  - by Knarf

  When a user logs inn I want to save their IP in the database. How would I do that? What type is best to use for the MySQL field? How would the PHP code to get IP look like? I'm thinking of using it as an extra security feature for the login/session stuff. I'm thinking of checking the IP the user logged in with from the DB with the IP the user have now as addition to checking the session. So that it first check the session, and then check if you have a valid IP. Is that an allright extra feature? And what other things can I do to make it more secure?

  Read the article

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

  - by Isern Palaus

  Hello, I've a table called sports that contains a list of list of sports, other called seasons that contains the seasons for a specific sport and competitions that have the competitions of a specific sport and season. I need one MySQL query to print the list of sports with how much seasons and competitions has each. My tables structure: sports +--------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(32) | NO | | NULL | | | slug | varchar(45) | NO | | NULL | | | description | varchar(128) | NO | | NULL | | +--------------------+------------------+------+-----+---------+----------------+ seasons +--------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | id_sport | int(10) unsigned | NO | MUL | NULL | | | name | varchar(32) | NO | | NULL | | | slug | varchar(32) | NO | | NULL | | +--------------------+------------------+------+-----+---------+----------------+ competitions +--------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | id_season | int(10) unsigned | NO | MUL | NULL | | | name | varchar(32) | NO | | NULL | | | slug | varchar(64) | NO | | NULL | | | description | varchar(128) | YES | | NULL | | +--------------------+------------------+------+-----+---------+----------------+ The result of my query needs to contain: sports.*, total_seasons (SUM of seasons where seasons.id_sport=sports.id) and total_competitions (SUM of competitions where competitions.id_season=seasons.id AND seasons.id_sport=sports.id). Thank you in advance!

  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

• 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

• 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

• 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

• 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

• MySQL Rating system (calculating average from two tables).

  - by MussuR

  I have two tables, videos and videos_ratings. The videos table has an int videoid field (and many others but those fields are not important I think) and many records. The videos_ratings table has 3 int fields: videoid, rating, rated_by which has many records (multiple records for each fields from the videos table) but not for all records from the videos table. Currently I have the following mysql query: SELECT `videos`.*, avg(`videos_ratings`.`vote`) FROM `videos`, `videos_ratings` WHERE `videos_ratings`.`videoid` = `videos`.`videoid` GROUP BY `videos_ratings`.`videoid` ORDER BY RAND() LIMIT 0, 12 It selects all the records from table videos that have a rating in table video_ratings and calculates the average correctly. But what I need is to select all records from the videos table, no matter if there is a rating for that record or not. And if there aren't any records in the videos_ratings table for that particular videos record, the average function should show 0. Hope someone could understand what I want... :) Thanks!

  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

< Previous Page | 176 177 178 179 180 181 182 183 184 185 186 187  | Next Page >