mysql

SQL question about counting...

I want to make a query so that I can grab only Locations that have at least 50 Places. I have a table of Locations: Id, City, Country 1, Austin, USA 2, Paris, France And a table of Places connected to Locations by Location_id Id, Name, Details, Location_id 1, The Zoo, blah, 2 2, Big Park, blah, 2 I can join them like so: SELECT p...

PHP MYSQL FORMS

Hi, I want to use a hyperlink on one page to load details into another form page, and then update a database table using the form field values. I have 2 php pages. One returning the bet_id of the last 10 records of a MYSQL query and another returning all field values for a specific record into a form, giving the end user the opportuni...

Search MySQL table for a kind of results

Hello, How can I search within a MySQL table for results ending in anything except ".jpg"? Thanks. ...

Is it possible to drop all foreign key constraints on a table at once in mySQL 5?

In mySQL 5, is there a way to drop all foreign key constraints on a table with one SQL statement without referring to them by name? I'm writing a DB update script, and unfortunately some of the sites had constraints created with "wrong" names. I'm trying to avoid going in and getting the actual constraint names from the DB and insertin...

Database problem: Fetching a default translation from a table

Good day, I have a MySQL table descriptions, which holds fields such as: lang_id, label, short_description, long_description and is_default. In my application, product descriptions are fetched from the database according to the current language. Everything works fine for now, however I'd like to add a default descriptions for each pro...

xampp mysql error

Hi , I get this error: mysql service not started [-1] when i try to open the mysql admin . I had xampp 1.7.2 installed before and it worked fine , now i installed 1.7 instead and it has this problem . how can i fix this ? Thanks ...

MySQL Table primary keys

Greetings, I have some mysql tables that are currently using an md5 hash as a primary key. I normally generate the hash with the value of a column. For instante, let's imagine I have a table called "Artists" with the fields id, name, num_members, year. I tend to make a md5($name) and use it has an ID. I would like to know what are the ...

Can't insert byte[] into MySQL using java

Following is the code I have used: byte[] bkey = key.getEncoded(); String query = "INSERT INTO keytable (name, key) VALUES (?,?)"; PreparedStatement pstmt = (PreparedStatement) connection.prepareStatement(query); pstmt.setString(1, "test"); pstmt.setBytes(2, bkey); pstmt.execute(); And following is an error I got: com.mysql.jdbc.ex...

Why does this syntax work in MySQL (matching column-pairs against column-pairs)?

Just now I needed to do something like the following query, and was surprised that it actually worked as intented. However I cannot find any documentation on it so I'm a little afraid about unknown side effects of this. Here's what I wrote: select * from Table1 where (col1, col2) in (select col3, col4 from Table2) This seems to be mat...

C#, MySQL, ADO.NET, delimiter causing syntax error

I have C# code that cycles through .sql files and executes what's inside them to set up a database. One .sql file is basically as follows: DROP PROCEDURE IF EXISTS myProc; DELIMITER $$ CREATE PROCEDURE myProc() BEGIN -- procedure stuff goes here END $$ DELIMITER ; CALL myProc(); When I input this into the MySQL Query Browser's sc...

MySQL from the command line - can I practically use LOCKs?

I'm doing a bash script that interacts with a MySQL datatabase using the mysql command line programme. I want to use table locks in my SQL. Can I do this? mysql -e "LOCK TABLES mytable" # do some bash stuff mysql -u "UNLOCK TABLES" The reason I ask, is because table locks are only kept for the session, so wouldn't the lock be released...

mysql database design??

I have a table in which I store ratings from users for each product.. The table consists of the following fields productid, userid, rating(out of 5)...This table might contain a million rows in the future.... So in order to select the top 5 products I am using the following query::. SELECT productid, avg( rating ) as avg_rating from pro...

Best efficient way to make a fulltext search in MySQL

I have 3 tables, and I want to make a query for a search term textbox. My query currently looks something like this: SELECT Artist.* FROM Artist, Band, Instrument WHERE MATCH (Artist.name) AGAINST ('mysearchterm') OR MATCH (Band.name) AGAINST ('mysearchterm') OR MATCH (Instrument.name, Instrument.description) AGAINST ('mysearchterm'); ...

ibatis check for exist before creating value

I have a property definition table and second one that holds the actual property values: table propdef: id, name, description table props: id, propdefid, userid, value This way i can dynamically create properties for my users. When I want to update a property for a user I have to check the props table if a propdefid/userid row exists...

Zend_Db_Table - Associative Array Instead Of Object

The following line: $select = $table->select(); $select->where('approved = 1'); $result = $table->fetchRow($select); Returns an object. What I would like is to get an associative array instead. I know that Zend_Db has fetchAssoc() method for that but is something similar also in the Zend_Db_Table (I tried fetchAssoc() but it doesn't ...

MySQL Error: Query was empty (1065) on simple INSERT statement

I am trying to run a large script that creates a table, and then inserts almost 15,000 rows into it. The table gets created just fine, and then at the 833 INSERT, I get an error: Error: Query was empty (1065) Here is my 833rd INSERT statement (the one that is failing): INSERT INTO CLASSCODE (CLASS_CODE, CLASS_CODE_NAME, RATE_GROUP, ...

MySQL PHP Verify an advert/post via email before displaying on website???

Working on a web based "buying and selling" application with PHP MySQL where users can post adverts for items and services. Before a new advert is displayed on the system there must be a method of verification to ensure that the user provided email address is correct, and that the advert is legitimate. I want to send the creator of any...

PHP/MySQL security--where to begin?

I'm a PHP/MySQL noob who knows nothing about online security. Could you point me to some resources that will aid in my knowledge? (Beginner level, please!) ...

Using Columns in a RegExp in MySQL

I'm using the following query with regexp: SELECT a.id, a.company, a.name, b.title, b.description, b.t_id FROM a, b WHERE ( b.title REGEXP "[[:<:]]a.company[[:>:]]" OR b.description REGEXP "[[:<:]]a.company[[:>:]]" OR b.title REGEXP "[[:<:]]a.name[[:>:]]" OR b.description REGEXP "[[:<:]]a.name[[:>:]]" ) AND a.company != '' AND a.name ...

PHP theres some Impact using long text in a associative array?

Im doing a database class in PHP and I want to make cache of the result of the querys in a associative array, My idea is to use the sql statment as the index of the cache array, its could be a good idea? or should I use a md5 from the sql? class DB{ const HOST = 'localhost'; //Your Database Host! const USER = 'user'; //Your Database Use...