mysql

count of a value in a column in mysql database

i have a large mysql database table in which one column contains values ranging from 1 to 9 what query can i give so that i can get the count of 1s and 2s and 3s ... 9s in that column in the database ? ...

select from one table and insert into another

I've got two tables. Table_A (nid, vid, type, title, uid) Table_B (id, questiontext) I need to insert records from Table_B into Table_A. I tried this: INSERT INTO Table_A (nid, vid, type, title, uid) VALUES ('', '', multichoice', (SELECT questiontext from Table_B), '1') but it's throwing an error. What should be the correc...

MySQL getting MAX of previous WHERE condition

I have a table: id | score | date bob | 40 | 2010-1-1 bob | 70 | 2010-1-15 sue | 55 | 2010-1-1 sue | 80 | 2010-2-1 I want to query for either the score for a user on a specific date OR, if no score exists for that user on that date,return the score from the most recent date for that user. Is there a way to do this w...

PK for web, autoincrement vs UUID style, wich is better and why?

What is the real diference between each one? I think auto inc is easy to hack vs uuid Uuid is slow than autoincrement in a query with to many records, but is there a huge difference in it? ...

MySQL: how to make multiple table fulltext search

Hello, I want to integrate the MySQL fulltext search function in my PHP site. I have the following problem now. SELECT * FROM testtable t1, testtable2 t2 WHERE MATCH ( t1.firstName, t1.lastName, t1.details, t2.firstName, t2.lastName, t2.details ) AGAINST ( 'founder' ); And i have the error code: #1210 - Incorrect arguments to MATCH...

MySQL: how to make the data various?

We have a table in our database (MySQL) that has the following: id, title, and discription. It's a mushup site and much of the data we get from the other site has the same titles and discriptions, though unique ids. We do not wish to pick up data that have the same titles and discriptions as we already have similiar (same) data in our ...

MySQL - Recursing a tree structure

I have a database table which link locations together; a location can be in a location, which can be inside another location. location (<id>, ....) location_parent (<location_id>, <parent_id>) Here's the MySQL/PHP to go down for a depth of one: $sql = "SELECT id FROM se_locations_services WHERE parent_locationid IN ( SELECT location_...

Mysql problem to store only different data

Sometimes data we are about to store have the same title and description as that we already have in our database. We do not wish to store data if the title and description of incoming data are the same as the title and description of already stored data. How would we do that? ...

Have mysql re-allocate ids in a continuous range

I have an existing database in mysql. One of my tables has discontinuous ids. I would like to modify the ids of the table so that they go from 1 to num-of-rows. This particular tables does not happen to have incoming references, so the ids can be changed without modifying other tables. The reason I want to do that is that I want to pro...

MySQL sorting performance problem

I have a query with subquery but subquery table has approximately 500.000 records and runing this query takes too long. How can I speed up this query? Any suggestions: SELECT ID, VehicleID, Plate, VehicleType, COALESCE( (SELECT EngineState FROM Locations WHERE Locations.VehicleID = Clients.VehicleID ORDER ...

MySQL: Loop through databases and run a stored procedure on it

Hi, I'm very new to MySQL and I have a stored procedure that I'd like to add to several legacy databases. I'm using SQLyog and I would like to loop through each database on the connection and if it matches 'application_%' (databases are called application_clientName, there are dozens) to run the stored procedure. A script I can save a...

creating an image in php using mysql resut set

Is there anyway to create an image outof mysql result set in PHP using GD library? I am ucrrently displaying the resultset in a table format in my php page. ...

MySQL: How to make data unique for several rows in a table?

How would I do the following (interested only in how to do UNIQUE (title, description)) in myphpadmin?: CREATE TABLE `myData` ( id serial primary key, title varchar(255) not null, description varchar(255) not null, UNIQUE (title, description) ); And by the way, so as not to open another question on SO, I would like to ...

Windows7 WAMP 64-bit stack MySQL problems

By using this guide here -> http://www.elxis.org/guides/developers-guides/64bit-apache-php-mysql-windows.html And hacking through the errors and issues that came up, I've got an apache, php, and MySQL stack almost working on Windows 7 - all 64 bit. PHP and apache are working just fine, and phpinfo tells me that mysql support is enabled...

Fetch last record in mysql

Hi I want fetch last record using hibernate criteria Can u help me? ...

mysql_insert_id() issue with MySQL 5.0.67 + Windows + VC++ 2008

Hello! On the manual page; http://dev.mysql.com/doc/refman/5.0/en/mysql-insert-id.html It is said that "Returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement." The problem is that it doesn't really work for me after an update. The real need is that I'm trying to update a row in a table (up...

MYSQL: delete empty cells

I have a table, for example, "mytable". It has several columns: col1, col2, col3. There is some data in it, placed like that: id col1 col2 col3 1 aasdas 2 acxvxc 3 dgdgdff 4 hfgdgh 5 jmjkh 6 dfgdfgd 7 bcbxcb For some reason I need table, f...

Help with a quick and dirty DB sync solution

Hello everyone, I have a problem with a deadline. And that deadline is tomorrow :S Luckily it doesn't have to be the best solution; not even a good one. I only need one part working which I will describe shortly. What I want you to know is that I looking for the quickest and dirtiest solution right so my idea maybe sound really bad. So,...

Which type to use for large alphanumeric content when creating a field in MySQL?

And so that to set UNIQUE for it? ...

How to skip an empty LIKE operator in a multiple LIKE query?

I notice my query doesn't behave correctly if one of the like variables is empty: SELECT name FROM employee WHERE name LIKE '%a%' AND color LIKE '%A%' AND city LIKE '%b%' AND country LIKE '%B%' AND sport LIKE '%c%' AND hobby LIKE '%C%' Now when a and A are not empty it works but when a, A and c are not e...