Search Results

Search found 49789 results on 1992 pages for 'mysql insert id'.

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

• MySQL Query involving column names containing math operators

  - by devil_fingers

  I'm a MySQL scrub, and I have asked around and checked around the internet for what I'm sure will turn out to be something obvious, but I'm very frustrated with what I thought would be a very, very simple query not working. So here goes. Please be gentle. Basically, in a large database, some of the column names contain mathematical operators like "/" and "+." (Don't ask, it's not my database, I can't do anything about it). Here is the "essence" of my query (I took out the irrelevant stuff for the sake of this question): SELECT PlayerId, Season, WPA/LI AS WPALI FROM tht.stats_batting_master WHERE Season = "2010" AND teamid > 0 AND PA >= 502 GROUP BY playerid ORDER BY WPALI DESC When I run this, it returns "Unknown column 'LI' in 'field list'," I assume because it sees the "/" in WPA/LI as a division sign. Like I said, I'm sure this is easy enough to work around (it must be given how much this database is used), but I haven't' been able to figure out how. Thanks in advance for any help.

  Read the article

• mysql - offset problem

  - by Phil Jackson

  Hi, I recently posted a question about getting last 3 results in table in the correct order. I now want the get all comments apart from the last 3 in the correct order. Here is my syntax; SELECT * FROM (SELECT * FROM $table ORDER BY ID DESC OFFSET 3) AS T ORDER BY TIME_STAMP the error i am receiving is; 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 'OFFSET, 3) AS T ORDER BY TIME_STAMP' at line 1 I can't seem to get it to work. Any help much appreciated.

  Read the article

• MySQL order by error in PHP

  - by theflyinghaiwian

  Hi, I'm making a simple cms system for a site I'm making for non-tech users to edit... So far so good but when I try and run this code I keep getting: 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 ''pages' ORDER BY 'pages'.'pageOrder' ASC LIMIT 0 , 30' at line 1 By the error it looks like a problem with the order by section and indeed it works without it... $sql = "SELECT * FROM 'pages' ORDER BY 'pages'.'pageOrder' ASC LIMIT 0 , 30"; $result = mysql_query($sql) or die(mysql_error()); Now I know there is nothing wrong with the code because originally I wrote my own SQL but then after it failed I robbed some from phpmyadmin and it still gives the error but it works in phpmyadmin... I'm really at my wits end with this, help is very much appreciated thank you...

  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

• 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 Full Text Search Boolean Mode Partial Match

  - by Rob

  I've found boolean mode of MySQL full text search useful, however there are a couple of things I can't seem to figure out how to achieve. For instance imagine I have a full text column containing the words "Steve's Javascript Tutorial - Part One". I would like to match this for each of the following searches: "tutorials", "javascript tutorials", "java", "java script", "script" Imagine that each of those searches is simply assigned to a variable in whatever language may be being used (I always use PHP). How could I modify this to make sure that Steve's article is returned on each of those searches? MATCH (article_title) AGAINST ('"+$variable+"*' IN BOOLEAN MODE)

  Read the article

• MySQL Import Database Error because of Extended Inserts

  - by Castgame

  Hello all, I'm importing a 400MB(uncompressed) MySQL database. I'm using BIGDUMP, and I am getting this error: Stopped at the line 387. At this place the current query includes more than 300 dump lines. That can happen if your dump file was created by some tool which doesn't place a semicolon followed by a linebreak at the end of each query, or if your dump contains extended inserts. Please read the BigDump FAQs for more infos. I believe the file does contain Extended Inserts, however I have no way to regenerate the database as it has been deleted from the old server. How can I import this database or convert it to be imported? Thanks for any help. Best Nick EDIT: It appears the only viable answer is to separate the extended inserts, but I still need help figuring out how to split the file as the answer below suggests. Please help. Thank you.

  Read the article

• Cannot output json from MySQL - getting null value

  - by sky

  I'm using following code but cannot return data from MySQL. This is the output: <script type="text/javascript"> var somethings= [null,null,null]; </script> It does have three post, but I couldn't get the title(message) output. EDIT: this is the code I'm using: <?php $session = mysql_connect('localhost','name','pass'); mysql_select_db('dbname', $session); $result= mysql_query('SELECT * FROM posts', $session); $somethings= array(); while ($row= mysql_fetch_assoc($result)) { $somethings[]= $row['something']; } ?> <script type="text/javascript"> var somethings= <?php echo json_encode($somethings); ?>; </script>

  Read the article

• MySQL Prepared Statements vs Stored Procedures Performance

  - by amardilo

  Hi there, I have an old MySQL 4.1 database with a table that has a few millions rows and an old Java application that connects to this database and returns several thousand rows from this this table on a frequent basis via a simple SQL query (i.e. SELECT * FROM people WHERE first_name = 'Bob'. I think the Java application uses client side prepared statements but was looking at switching this to the server, and in the example mentioned the value for first_name will vary depending on what the user enters). I would like to speed up performance on the select query and was wondering if I should switch to Prepared Statements or Stored Procedures. Is there a general rule of thumb of what is quicker/less resource intensive (or if a combination of both is better)

  Read the article

• MySQL query optimization.

  - by PiKey

  I'm so bad in making good MySQL queries. I've created this one: http://pastebin.com/GtDfgky8 products Table have about 17k rows, allegro Table have about 3k of rows. The query Idea is select all products, where stock_quanity 3, where is photo, and where is no product id in allegro table. Now query takes about 10 seconds... I have no idea how I can optimize this query. Please help my, I'll be thankfully! :) & Sorry for my bad English also

  Read the article

• How to use Mysql variables with Hibernate ?

  - by Jerome C.

  Hello, I need to use a native sql query in Hibernate with use of variable. But hibernate throws an error saying: Space is not allowed after parameter prefix So there is a conflict with the := mysql variable assignment and hibernate variable assignment. Here is my sql query: SET @rank:=0; UPDATE Rank SET rank_Level=@rank:=@rank+1 ORDER BY Level; I can't use a stored procedure because my sql query is dynamically generated ('Level' can be 'int' or 'force'...) How can I do this ? thanks

  Read the article

• Django ORM dealing with MySQL BIT(1) field

  - by Carles Barrobés

  In a Django application, I'm trying to access an existing MySQL database created with Hibernate (a Java ORM). I reverse engineered the model using: $ manage.py inspectdb > models.py This created a nice models file from the Database and many things were quite fine. But I can't find how to properly access boolean fields, which were mapped by Hibernate as columns of type BIT(1). The inspectdb script by default creates these fields in the model as TextField and adds a comment saying that it couldn't reliably obtain the field type. I changed these to BooleanField but it doesn't work (the model objects always fetch a value of true for these fields). Using IntegerField won't work as well (e.g. in the admin these fields show strange non-ascii characters). Any hints of doing this without changing the database? (I need the existing Hibernate mappings and Java application to still work with the database).

  Read the article

• How do you get SQLAlchemy to override MySQL "on update CURRENT_TIMESTAMP"

  - by nocola

  I've inherited an older database that was setup with a "on update CURRENT_TIMESTAMP" put on a field that should only describe an item's creation. With PHP I have been using "timestamp=timestamp" on UPDATE clauses, but in SQLAlchemy I can't seem to force the system to use the set timestamp. Do I have no choice and need to update the MySQL table (millions of rows)? foo = session.query(f).get(int(1)) ts = foo.timestamp setattr(foo, 'timestamp', ts) setattr(foo, 'bar', bar) www_model.www_Session.commit() I have also tried: foo = session.query(f).get(int(1)) setattr(foo, 'timestamp', foo.timestamp) setattr(foo, 'bar', bar) www_model.www_Session.commit()

  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

• Zend Database Adapter - Complex MySQL Query

  - by Sonny

  I have defined a function in my Navigation model that executes a query, and I was wondering if there's a more "Zendy" way of generating/executing the query. The query I'm using was proposed by Bill Karwin on another thread here for setting arbitrary record order. I tried using a prepared statement, but the values in the SIGN() function got quoted. I'm using the PDO adapter for MySQL. /** * */ public function setPosition($parentId, $oldPosition, $newPosition) { $parentId = intval($parentId); $oldPosition = intval($oldPosition); $newPosition = intval($newPosition); $this->getAdapter()->query(" UPDATE `navigation` SET `position` = CASE `position` WHEN $oldPosition THEN $newPosition ELSE `position` + SIGN($oldPosition - $newPosition) END WHERE `parent_id` = $parentId AND `position` BETWEEN LEAST($oldPosition, $newPosition) AND GREATEST($oldPosition, $newPosition) "); return $this; }

  Read the article

• mysql stored procedures using php

  - by neo skosana

  I have a stored procedure: delimiter // create procedure userlogin(in eml varchar(50)) begin select * from users where email = eml; end// delimiter ; And the php: $db = new mysqli("localhost","root","","houseDB"); $eml = "[email protected]"; $sql = $db-query("CALL userlogin('$eml')"); $result = $sql-fetch_array(); The error that I get from the browser when I run the php script: Fatal error: Call to a member function fetch_array() on a non-object... I am using phpmyadmin version 3.2.4 and mysql client version 5.1.41. Please help. Thank you.

  Read the article

• mysql_real_escape_string and search data in mySql DB

  - by ryrysz

  I have problem with php function : mysql_real_escape_string My test string: @,&!#$%^*()_+' "\/ I add this data to mySql database, like that (in short): $str = mysql_real_escape_string($str); $sql = "INSERT INTO table(company) VALUES('".$str. "')"; In DB is stored as: @,&!#$%^*()_+\' \"\\/ But problem is with find this data by SELECT statement. I want find, company where name is like ' " My SELECT's: SELECT company FROM table WHERE company LIKE '%\' "%'; SELECT company FROM table WHERE company LIKE '%\\' \\"%'; ; not working. This works: SELECT `company` FROM `table` WHERE `company` LIKE '%\\\' \\\\"%'; and SELECT `company` FROM `table` WHERE `company` LIKE '%\\\\\\\' \\\\\\\"%' But I dont know why this work :(. My questions are: why must add so many slashes ? how I can make correct query in PHP: $query = '\' "'; '%'.mysql_real_escape_string($query).'%' result is : '%\' \"%' '%'.mysql_real_escape_string(mysql_real_escape_string($query)).'%' result is : '%\\\' \\\"%' '%'.mysql_real_escape_string(mysql_real_escape_string(mysql_real_escape_string($query))).'%' result is : '%\\\\\\\' \\\\\\\"%' Only last one works good.

  Read the article

• Improving MySQL Update Query Efficiency

  - by Russell C.

  In our database tables we keep a number of counting columns to help reduce the number of simple lookup queries. For example, in our users table we have columns for the number of reviews written, photos uploaded, friends, followers, etc. To help make sure these stay in sync we have a script that runs periodically to check and update these counting columns. The problem is that now that our database has grown significantly the queries we have been using are taking forever to run since they are totally inefficient. I would appreciate someone with more MySQL knowledge than myself to recommend how we can improve it's efficiency: update users set photos=(select count(*) from photos where photos.status="A" AND photos.user_id=users.id) where users.status="A"; If this were a select statement I would just use a join but I'm not sure if that is possible with update. Thanks in advance for your help!

  Read the article

• Mysql Master Slave Replication on Large Database table (how to sync initial data)

  - by Brian Lovett

  We have a production server and a dev server. We have found that backups are nearly impossible on the production server because of the query volume we experience. So, we're looking at setting up replication with our dev server being the slave. This is ideal because we can afford to lock the tables on that server and additionally it will be nice to have up to date data for the developers. Now, the issues. The production server can't really be taken down or locked at this point, at least not easily. We have a high query volume and fairly large 30+ GB innodb tables. Both servers are running all innodb and are also both on mysql 5.1. What can we do to sync the data initially to get replication started? I've tried a few options, but so far, none have worked.

  Read the article

• wrong data type in mysql for grails byte[] property

  - by srkiNZ84

  Hi, I have an application which is trying to save a photo to the database. I've created a grails domain class with a byte[] property and this was working well when using HSQLDB (the default in grails). However, when I changed the database to MySQL I ended up getting the following error: Data truncation: Data too long for column 'photo' at row 1 I then had a look at the schema and found that the byte[] was being created as a TINYBLOB field, which was causing the error. How can I specify that this property should correspond to a BLOB/LONGBLOB type in the database?

  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

• 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

• 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

• mysql reference result from subquery

  - by iamrohitbanga

  this is what i am doing update t1 set x=a,y=b where a and b are obtained from (select query here) i know the select query the select query returns multiple results which are the same when i use group by or distinct query execution slows down considerably a and b are forward references so mysql reports an error i want to set a equal to the value obtained in the first row and b equal to the value obtained in the first row for the respective columns, to avoid group by. i don't know how to refer to the first result from the select query. how can i achieve all this?

  Read the article

• MySQL update error when special characters are used

  - by Katy

  Hi All, I was wondering if anyone had come across this one before. I have a customer who uses special characters in their product description field. Updating to a MySQL database works fine if we use their HTML equivalents but it fails if the character itself is used (copied from either character map or Word I would assume). Has anyone seen this behaviour before? The character in question in this case is ø - and we can't seem to do a replace on it (in ASP at least) as the character comes though to the SQL string as a "?". Any suggestions much appreciated - thanks!

  Read the article

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