mysql

How can I configure a Servlet in Eclipse to use a JPA project?

I am using Eclipse Galileo and I wanted to deploy a simple application, using JPA, GlassFish 2.1 and MySQL 5. Unfortunately, I could not find any tutorials for GlassFish 2.1 (just for 3.0, but I cannot use it). I created a JPA project, added a MySQL5 connection and generated an Entity from the database. The generate JPA class is: pac...

MySQL - Select Concat an entire row

How can I select and concat every field in a row? I want to do something similar to this: SELECT concat(SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl_name') as single FROM tbl_name ..but obviously the above doesn't work. Any suggestions? ...

Expert opinions on a very large number of columns in a mySQL table?

I am creating a tool with which to edit web pages within a CMS. The main goal of the tool is total flexibility for the user. Therefore, a great number of properties can be edited in it - properties like these (snippet): langbutton_menu_border_color_left langbutton_menu_border_width_left langbutton_menu_border_style_left langbutton_men...

MySql error 121 while restoring truncated database from dump

Hi! I need to create a copy of PROD database in my DEV environment. I've made a MySQL dump, truncated old database to remove all structure & data. But during import I get such error: http://pastebin.com/m1ff0e920. After googling, I've discovered, that this problem is for duplicated names of the foreign keys. But 1) There is no FK in this...

Database Structure Advice Needed

Hey Guys, Im currently working on a site which will contain a products catalog. I am a little new to database design so I'm looking for advice on how best to do this. I am familiar with relational database design so I understand "many to many" or "one to many" etc (took a good db class in college). Here is an example of what an ite...

MySQL transaction query help

This problem is baffling me: BEGIN; INSERT INTO sub_users(user_id, email) SELECT user_id FROM users WHERE email='[email protected]', '$email'; COMMIT; Normally, I have multiple statements in that transactions, but I've removed those for clarity. I get this error: #1064 - You have an error in your SQL syntax; check the manual that...

Confirm Password?

New to all this so forgive my ignorance. I am trying to figure out how to add a "confirm your password" field to my form. Using PHP and mySQL. Is this entered in the html form code, and how can you set it to auto check that the password and confirm password fields match. ...

MySQL: Join Query Across Multiple Databases Located on Different Servers

In SQL Server there is a way to join tables from multiple sql servers by using link tables. I wonder whether is it possible to do the same? I am using PHP, does PHP provides this kind of facilities? ...

Using a MySQL trigger to update all fields matching a condition

Hi, What I am trying to do is to set the column of each insert query to the latest value of $i I've never used a trigger in MySQL before but I do believe that's the easiest way to do this. I would be open to other ideas and suggestions. Right now I have: $i = 1; foreach($_FILES["upload_project_images"]["name"] as $key => $name) { ...

Simple MySQL query question

How would I do this (the part in square brackets): $name = 'Bob'; mysql_query("INSERT INTO table(field1,field2) [ select value of field1 from another table ], '$name'"); ...

how to edit/manage multi tiered conditional data in php

I have 4 tables in a mysql database that i need to add/edit/manage. The difficulty, is that each one, is dependent on the one before it. The way i have this setup on the user end, is you select an option from table 1. You are then presented with the options in table 2, that have the first option's ID in their row. Once you select optio...

Workaround to allow a TEXT column in mysql MEMORY/HEAP table

I want to use a temporary MEMORY table to store some intermediate data, but I need/want it to support TEXT columns. I had found a workaround involving casting the TEXT to a VARCHAR or something, but like an idiot I didn't write down the URL anywhere I can find now. Does anyone know how to, for example, copy a table x into a memory table...

MySQL Database Permission Question

Hi. I've become accustom to mysql user permissions along the lines of GRANT ALL PRIVILEGES ON db_base.phonebook TO db_user @'%' IDENTIFIED BY 'db_passwd'; and other things that affect how a certain user interacts with mysql. I have a database that was created by a program I ran and it has its own permissions. I can't figure out how t...

Displaying MySQL data into HTML table (clearly explained)

Ok, I have an app as described in this post: http://stackoverflow.com/questions/1623105/good-database-structure-for-a-new-web-app I've prepared a scenario to make my question as clear as possible: Users table: +----------+-----------------+ | user_id | email | +----------+-----------------+ | user_1 | user1...

how to insert/retrieve a userDefined Object in DB-MySql?

I want to insert and retrieve a a user defined Object in DB,am using Mysql5.1. 1)What should me the data type for the column(is Blob is the correct answer for this question) I am using EntityClass to Insert/Get values from the DB. 2)but to how to insert Object in database? ...

Query is slow while doing a NOT IN on a nested SELECT from another table

SELECT problems . * , users.id AS new_user_id, users.nick AS nick FROM problems, users WHERE problems.deleted =0 AND problems.topic_id =1 AND problems.user_id = users.id AND problems.id NOT IN ( SELECT DISTINCT (problem_id) FROM problems_attempted WHERE user_id =1 AND total_questions = ( attempted_right + attempted_wrong + skippe...

mysql set a flag for each match

I have the following query: SELECT users_extra.first_name, users_extra.last_name FROM (branches, users_extra) WHERE ((branches.manager_id = users_extra.userid) OR (branches.sales_manager_id = users_extra.userid) OR (branches.admin_manager_id = users_extra.userid) OR (branches.ops_manager_id = users_extra.userid) OR (branches.export_mana...

MySQL Column Unification, any performance improvements?

I'm designing a MySQL table for an authentication system for a high-traffic personal website. Every time a user comment, article, etc is displayed the following fields will be needed: login User Display User Bio ( A little signature ) Website Account YouTube Account Twitter Account Facebook Account Lastfm Account So everything is in ...

Definer of Stored Procedure

Hi, Most of the stored procedures has been created as a root at % or 'someUser' @ localhost When I copy the procedure to another server, it does not work since the user 'someUser' is not there. The relevant line in dump file is shown below. /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 PROCEDURE `SP_Name`(in dt varchar(10)) ...

How to show and search for hidden characters (line feeds, etc) in SQL

I have a large MySQL database with lots of text (like '[new line]Normal') that I want to find & replace. However, I can't find it with any of the following searches, because I'm not sure what characters are used where the [new line] occurs - hence my question. SELECT * FROM table WHERE field LIKE "%<!--[if gte mso 9]><xml>\nNormal%"; S...