mysql

MySQL left join subquery fail

Following query runs well in MySQL 5.x SELECT m_area.id, m_area.cn_areaName, m_area.de_areaName, m_area.en_areaName,m_area.jp_areaName,t_shop.count FROM m_area left join ( select t_shop.areaID, count(areaID) AS count from t_shop group by t_shop.areaID ) t_shop on m_area.id = t_shop.areaID However, when I have to run it in a 4...

Select Mulitple Records based on One record's column value

I have a table which contains related records (multiple revisions of the same record). Each record has a string field that resembles a date (date, time, and microtime). I want to select all records that are older than a specific date. If a record has a related record newer than the specific date, I do not want to select any of those rela...

What is meant by Remapping MySQL?

I would like to know what is meant by the term "remapping" in MySQL, sorry If this is a noob question, It's just the first time ive heard of it. ...

Handling Multiple Images with ColdFusion and MySQL

Hello everyone, This is an architecture question, but its solution lies in ColdFusion and MySQL structure--or at least I believe so. I have a products table in my database, and each product can have any number of screen-shots. My current method to display product screen-shots is the following: I have a single folder where all screen-...

mysql recovery with .frm, ibdata1, ib_logfile0, ib_logfile1

I have several database directory under mysql's "data" dir, and the ibdata1 file. After searching everywhere, I could find solution to recover it. There is a tool called "stellar phoenix database recovery for mysql" seems working, but it's not free. Any one can help? Thanks a lot in advance. ...

I installed Apache and added PHP to it, than installed MySql but PHPMYADMIN isn't working?

I was using XAMP for windows but there were forming a lot of problems with MySql so uninstalled it and installed Apache Web Server. I connected Apache to PHP and it worked fine. I installed MySql again and it worked fine. Now I can not get MySql to work with PHPMyAdmin. I did not test PHP to see if it worked with MySql without PHPMyAdmi...

How should I connect to a MySQL data source from Eclipse?

I have an external MySQL server that's set up and working fine. I created a database connection in Eclipse and can view the database in the Data Source Explorer tab. Now, I have a servlet that needs to access that database. How do I do it? Is there a way to reference that database connection created in the data source explorer, or do...

Database Design for Facebook-like messages

Hello, I am currently planning a new system in PHP/MySQL and want to make sure my database can handle the amount of data that I am planning to store. One of the features of my new project is a "messages" feature like Facebook. I want to make sure I create the best possible experience for the end user. The website will eventually handle 1...

PHP: Passing variables for mysql query without enclosing them in " or '

Hi, There's an open source application that does queries like so: $db->query('SELECT item FROM table WHERE something='.$something); This works fine, mysql runs the query however if I use exactly the same method I get an error, mysql is seeing it as "something = (value of $something)" and it's (rightly) complaining that (value of $som...

MySQL Multiple Counts in Single Query

I am trying to COUNT two columns in a single query, but the results are spitting out the same values for medcount and uploadcount. Any suggestions? $sqllog="SELECT * , COUNT($tbl_list.listname) AS listcount, COUNT($tbl_uploads.id) AS uploadcount FROM $tbl_members LEFT JOIN $tbl_list ON $tbl_member...

Encrypt / Decrypt Primary Key instead of using UID?

Although I always check that someone is allowed to access a record, I normally use a UID in query strings as I feel it discourages the temptation to "poke around" that ?id=1, ?id=2 does. I find though that it makes it a bit convoluted to do lookups across multiple tables as you need to store the UID as well instead of just the record id...

MYSQL Workbench EER, what's different?

Hey guys, I just downloaded Mysql Workbench and noticed they use the term EER Diagram? I thought that Entity Relationship diagram were a conceptual representation of data (Conceptual schema), a bit like this: http://www.ncgia.ucsb.edu/giscc/units/u045/figures/figure1.jpg I mean, Mysql Workbench's Diagrams have tables with foreign keys a...

INTERSECT in MySQL

I have two tables, records and data. records has multiple fields (firstname, lastname, etc.). Each of these fields is a foreign key for the data table where the actual value is stored. I need to search on multiple record fields. Below is an example query using INTERSECT, but I need one that works in MySQL. SELECT records.id FROM reco...

Force drop mysql bypassing foreign key constraint

I'm trying to delete all tables from a database except one, and I end up having the following error: Cannot delete or update a parent row: a foreign key constraint fails Of course I could trial and error to see what those key constraints are and eventually delete all tables but I'd like to know if there is a fast way to force drop al...

Eliminate the Join Table in HasMany Relationships in Rails

I am thinking about ways to create a Role Based Access Control system in Rails. I have seen these great projects too (among others): RoleRequirement ActsAsPermissible RailsAuthorization BoxRoom (rails file management) My question is, is it really necessary to have a join table for everything? If I one of the tables in the relations...

How can I protect against SQL injection attacks using Perl's DBI?

Is there a function i can use in Perl to sanitize input before putting it into a MySQL db? I don't know regex very well so before I make my own function i was wondering if there was already one made. ...

Problem with CROSS JOIN and referencing, field list error in MySQL query.

I'm trying to run the following MySQL query: mysql> SELECT num.value, agro.mean, agro.dev -> FROM randomNumbers num -> CROSS JOIN ( -> SELECT AVG(value) AS mean, STDDEV(value) AS dev -> FROM randomNumbers -> ) agro -> ORDER BY num.value; the example came from here http://www.sitecrafting.co...

GoDaddy's MySQL dump isn't compatible with server (error 1251)

I've a problem when trying to run mysqldump using the cron manager in GoDaddy's hosting interface. It spew out: Got error: 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client when trying to connect Anyone ever have the same problem? Is there a solution that doesn't involve updatin...

Pull first X words (not just characters) from mySQL

I'd like to be able to pull the first X words from a database field for use in a preview. Basically if a field 's content was "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Mauris malesuada." I would like to echo "Lorem ipsum dolor sit amet... see more" What's the best way to do this? The only thing I know to do is p...

How to do a parameterized query

[ Status: Learner ] I am attempting to implement a parameterized query but I am having problems. Jonathan Sampson recently hinted at how this could be done (#2286115), but I'm not following his suggestion correctly. Here is my script $cGrade = "grade" ; include_once ( "db_login.php" ) ; $sql = "SELECT last_name AS last_name ...