I have a number of rows from a database which I want to insert into another database. Since there's quite a few rows I want to use the INSERT .. SELECT ... construct in mysql. The statement I try to run is as follows: set @rank := 1; set @currpoints := 0; set @currgroupcount := 0; SELECT id, @rank := if( @currpoints = points, @rank ,...
I need to generate a random alpha/numeric to give to users that they come to the site to enter. I dont' know much about random numbers and such, I know there are seeding issues and such, but I'm not sure what they are. So, I used this: select substrING(md5(concat_ws('-',md5(username_usr), MD5(zip_usr), MD5(id_usr), MD5(creat...
Is it possible to set up a mysql trigger that fires back the id number of that record when ever there is an insert into the database AND how do i capture that using php? ...
I am using a macro to export a table in a Microsoft Access database to a csv file in order to import into a mysql database. I ended up using a batchfile that would place a marker in the text file before the exporting took place, and then place everything after the last marker into a new file. This works fine, except for the fact that acc...
Hey all, my site is now going to start allowing two different types of users to sign up... normal people companies the two will be able to do all the same things on the site, but their profiles will look different, and im sure they will want to store different types of information about themselves. my question is: should i sto...
According to the documentation, in MySQL the Max() and Min() aggregate functions accept a DISTINCT keyword: The DISTINCT keyword can be used to find the maximum of the distinct values of expr, however, this produces the same result as omitting DISTINCT. Maybe I'm missing something, but how could this ever be useful? UPDATE: ...
I have two tables: 'movies' and 'users'. There's an n:m relationship between those, describing what movies a user has seen. This is described with a table 'seen' Now i want to find out for a given user, all the movies he has not seen. My current solution is like this: SELECT * FROM movies WHERE movies.id NOT IN ( SELECT seen.movie...
What are the best ways to protect from MySQL injection? What are weaknesses I should look out for? I know what it is, but I really have no idea how vulnerable I might be. Though I have taken (what I think to be) steps toward protecting myself and my database. Is there any sure-fire way of stopping someone? BTW...I write in PHP:) ...
It's time to implement sorting on my blog-like web application. In addition to browsing by creation date, I want users to be able to sort by the number of replies, too. Given that I have two tables blog_posts and replies, and that replies has a field called blog_post_id (a foreign key for the blog_post it's a reply to), how do I write ...
I know there are two approaches: adjacency list and nested tree. It's said that adjacency list can become slow to use on traversal because of numerous queries. But I don't know any realistic figures for this. The site I'm making will have in the region of 200 pages. Is traversal to generate (for example) a sitemap going to take longer th...
SELECT item_name from items WHERE item_id = $var; I tried: $var = 001 || 002 || 003 || 004; $var = 001 OR 002 OR 003 OR 004; $var = 001 or 002 or 003 or 004; But all do not work. Thanks, i try that, but the output only 1 result => 1. What I want is to output all, i.e. 1, 2 , 3 and 4.. Means, I want to select multiple records(rows...
I want to display four (4) items'name from these id: Can I do like this? SELECT item_name from items WHERE item_id IN ('001', '012', '103', '500') or SELECT item_name from items WHERE item_id = '001' or item_id = '012' or item_id = '103' or item_id = '500' IN RESPONSE TO ALL ANSWERS Well, most of the answers said it works, but it do...
Is there any way to update a table within the select_expr part of a mysql select query. Here is an example of what I am trying to achieve: SELECT id, name, (UPDATE tbl2 SET currname = tbl.name WHERE tbl2.id = tbl.id) FROM tbl; This gives me an error in mysql, but I dont see why this shouldn't be possible as long as I am not changing ...
Hi All, I have a MySQL indexing question for you guys. I've got a very large table (~100Million Records) in MySQL that contains information about files. Most of the Queries I do on it involve substring operations on the file path column. Here's the table ddl: CREATE TABLE `filesystem_data`.`$tablename` ( `file_id` INT( 14 ) NOT...
Is there a way to update multiple rows with different values for each row using a single SQL query? I have to update one colum in many rows with different data. Using individual update queries for each row seems excessive so if it's possible I would like to consolidate this process into a single SQL statement or at least reduce the numbe...
I have an app which has tasks in it and you can reorder them. Now I was woundering how to best store them. Should I have a colomn for the ordernumber and recalculate all of them everytime I change one? Please tell me a version which doesn't require me to update all order numbers since that is very time consuming (from the executions poin...
I'm using Amazon EC2 and am switching from one server instance to another. My original instance has an EBS volume (e.g. hard drive) with the mysql data on it. I want to attach that volume to my new server instance. To originally create the volume I executed the following: apt-get update && apt-get upgrade -y apt-get install -y xfspr...
This is the best way I could come up with to convert a MySQL GUID/UUID generated by UUID() to a binary(16): UNHEX(REPLACE(UUID(),'-','')) And then storing it in a BINARY(16) Are there any implications of doing it this way that I should know of? ...
WAMP installed fine, no problems, BUT... When going to phpMyAdmin, I get the error from phpMyAdmin as follows: "Cannot load mysqli extension. Please check your PHP configuration". Also, phpMyAdmin documentation explains this error message as follows: "To connect to a MySQL server, PHP needs a set of MySQL functions called "MySQL exte...
I've been struggling with this for far too long, and I don't see any error. I've got a table that holds a bunch of locations. When I'm adding a location via my php script, I get an error of 'Wrong parameter count for mysql_query()'. When I echo the mysql query, everything looks fine,and I can copy and and paste it into phpmyAdmin , e...