mysql

What are the viable database abstraction layers for Python

I'm starting to get involved in an open source project Gramps which is exploring switching their backend from BSDDB to a relational database. Either SQLite or MySQL we haven't fully decided and may even try to do both in some limited capacity. I'm a professional developer but I'm new to python so I'm not that familiar with the current se...

How to make a sql search query more powerful?

I wrote this sql query to search in a table: Select * from TableName where Name Like '%spa%' The table contain these row for example: 1 Space Company. 2 Spa resort. 3 Spa hotel. 4 Spare Parts. 5 WithoutTheKeyword. I want to know how to edit this query so it return the results sorted like this: 2 Spa resort 3 Spa hotel 1 Space Compa...

MySQL Import Database Error because of Extended Inserts

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 ea...

Prepending a string to a column value in MySQL

I need a SQL update statement for updating a particular field of all the rows with a string "test" join in the front of the existing value For example, if the existing value is try it became testtry ...

How can I send an auto email from MySQL records?

I am using a MySQL database and PHP. My MySQL database contains the following records: Name Address Data email Date Joy Fortblair 10 [email protected] 1/22/2009 Bob Atlanta 15 [email protected] 2/22/2009 My intention is to send the email using PHP with the following conditions: The data is 1 d...

php mysql character set: storing html of international content

i'm completely confused by what i've read about character sets. I'm developing an interface to store french text formatted in html inside a mysql database. What i understood was that the safe way to have all french special characters displayed properly would be to store them as utf8. so i've created a mysql database with utf8 specified ...

MySQL: multiple grouping

So I have an example table called items with the following columns: item_id (int) person_id (int) item_name (varchar) item_type (varchar) - examples: "news", "event", "document" item_date (datetime) ...and a table person with the following columns: "person_id", "person_name". I was hoping to display a list of the top 2 submitters (+...

To check values in other databases using trigger

Is it possible to check whether a particular value in a column exists in other databases using trigger? These two databases are located inside the same MYSQL instance. Specifically, what I want to do is this: Before a row is added to a table ( Document_Index_table) inside Database A ( Document_DB). A trigger is fired. This trigger car...

Fulltext search in MySQL and PHP doesn't work

Hello, I'm currently trying to perform a search over 2 fields in my MySQL table (text type) using PHP. SELECT * FROM content_items WHERE MATCH (content,name) AGAINST ('".urldecode($_REQUEST['term'])."' IN BOOLEAN MODE) I'm always getting zero results, no matter what I search for (even tried to make the query static and it still didn...

MySQL - Concurrent SELECTS - one client waits for another?

Hi, I have the following scenario: I have a database with a particular MyISAM table of about 4 million rows. I use stored procedures (MySQL Version 5.1) and one in particular to search through these rows on various criteria. This table has several indexes on it, and the queries through this stored procedure are normally very fast ( <1s...

How do you handle multiple value types in MySQL when inheritance isn't supported?

I need to create a table of attributes where each record is essentially just a name-value pair. The problem is that the value can be a string, integer or decimal and I'm using MySQL which doesn't support table inheritance. So, the question is - should I create a separate table for each value type or should I just create str_value, int_va...

MySQL: Views vs Stored Procedures

Since MySQL started supporting stored procedures, I've never really used them. Partly because I'm not a great query writer, partly because I often work with DBAs who make those choices for me, partly because I'm just comfy with What I Know. In terms of doing data selection, specifically when considering a select that is essentially a d...

Semi-Distinct MySQL Query

I have a MySQL table called items that contains thousands of records. Each record has a user_id field and a created (datetime) field. Trying to put together a query to SELECT 25 rows, passing a string of user ids as a condition and sorted by created DESC. In some cases, there might be just a few user ids, while in other instances, ther...

How to tell selenium to use test database?

I'm using selenium-client to run a few tests, but Selenium seems to be using my development database. How can I point it to use my test DB? ...

selecting the next record from a list ordered by date.

I am using this SQL query to order a list of records by date in a php page. SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME FROM table WHERE upper(ARTICLE_NAME) LIKE % x % ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'); This works fine. In a different php page, I want to be able to delete this record, and show the n...

Is there a simple way in my ASP.NET app to programmatically find out whether the mysql db server is down?

I use MySQL in my ASP.NET app (with the MySQL .NET Connector), is there anyway to programmatically check whether the db server is down? I came across MySQLConnection.Ping() but unfortunately that doesn't work the way it should (the connection has to be opened first using Open(), also it keeps on returning false even if the DB Server beco...

MySQL "in" for twin values

I am aware of the query syntax: SELECT fields FROM table WHERE value in (1,2,3,4) But I have two values (x and y), can I use the "in" syntax for them or will I have to go with what I used to use before in and have a large set of WHERE conditions: SELECT fields FROM table WHERE (x = 1 AND y = 2) OR (x = 3 AND y = 4) ...

How do I check a table to see if a string is already in use?

I am using this code to check my username column (username is primary) in my userdb table to see whether or not the string is already there. If it isn't there then it adds the string entered from a previous form into the username column in my table. But if it is there then it says "(Username) is already in use!". This works when I put a...

Should I use "id" or "unique username"?

I am using PHP, AS3 and mysql. I have a website. A flash(as3) website. The flash website store the members' information in mysql database through php. In "members" table, i have "id" as the primary key and "username" as a unique field. Now my situation is: When flash want to display a member's profile. My questions: Should Flash pas...

How can I auto calculate a column in a table without using a view

For example, I have the following table: CREATE TABLE `test` ( `total_results` int(10) unsigned NOT NULL, `num_results_as_expected` unsigned int(10) NOT NULL, ) ; I would like to add another column to the table, without using a VIEW, to add the percent of : (num_results_as_expected/total_results)*100 Is this possible without us...