mysql

Experiences with phpMyVersion

Hi! I've been looking for an easy and automated way to detect changes on my database structure so I can check them into subversion and be able to roll back to older versions etc. Now I found phpMyVersion, which seems to do exactly that job. I didn't have time to look into it in great detail, but does anyone of you have some experiences...

MySQL: UPDATE with SUM and JOIN

I'm trying to do a SUM and store it in another table. The SUM is simple : SELECT award.alias_id, SUM(award.points) AS points FROM award INNER JOIN achiever ON award.id = achiever.award_id I now want to store that. I figured out how to do it on a row-by-row basis : UPDATE aliaspoint SET points = (SELECT SUM(award.points)...

MySQL date field contains no value - having problem importing

I'm migrating some data into MySQL and the dump from the other database has left a particular date field blank. The table looks like this: +------------------+-------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+------------...

How to work with database from eclipse

I have a MySQL database server installed on my local machine. I have connected it with eclipse data source explorer. can you please point me to tutorials on effectively working with the database from eclipse itself without using command line MySQL client ? thanks ...

SQL foreign keys with multiple tables

If I have three tables: music_genres ----------- music_type_id genres [other unique fields] minerals -------- mineral_id mineral [other unique fields] verbs ----- verb_id verbs [other unique fields] and these are populated with: rock jazz funk punk rock boulder stone shale rock wobble shake vibrate Now let's say I was displayin...

merging two tables w/ same structure

Trying to merge two tables, but I can't really get my head around it. The structure is the exact same for both tables. However, every query I've researched seems to write one table over the other. I'm guessing this has to do with the fact that both tables share the exact same id. As they share unique ids I'd like to have a new id assign...

How to store data in MySql through multiple threads?

I have started five threads from main() function. I have written three funtions as follows: getConnectionToDatabase(); saveToDataBase(); closeConnection(); Now I want that main function should establish a connection to the database before starting those five threads by calling getConnectionToDataBase() so that each thread doesn't hav...

Help me understand the logic of complex SQL queries in MySQL and relational algebra...

Hey, I'm working on a simple project to create tables and insert data into them, and finally execute some queries. Please note: I'm using MySQL command client. My schema is easy to implement; it's no problem. Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pct_time: integer) ...

Doing a count MySQL query?

I have the following table: UID | ID | Type 1 | 1 | product 1 | 2 | product 1 | 3 | service 1 | 4 | product 1 | 5 | product 2 | 6 | service 1 | 7 | order 2 | 8 | invoice 2 | 9 | product I want to end up with: UID | product | service | invoice | order 1 | 4 | 1 | 0 | 1 2 | 1...

one to many queries

Alright, I've just finished normalizing a table. What I didn't really consider when I was doing it is that I now have a one to many relationship... being that my sql knowledge is very, very limited I'm not really sure how to approach this. I tried grouping by the foreign key, but it only returns the last matching result and I want to re...

I would really like to use PHP and MySQL persistent connections, but how?

Apache/PHP/MySQL persistent connections have such a bad reputation because Apache handles each request as a child php process, each having 1 persistent connection. When visitors scale, MySQL reachs max connections limit from all the apache/php child processes, each with 1 persistent connection. There's also the issues of temporary tables...

MySQL - Using UNION with LIMIT

i noticed that (SELECT title, relavency, 'search1' as source FROM search1 ORDER BY relavency DESC LIMIT 10) UNION (SELECT title, relavency, 'search2' as source FROM search2 ORDER BY relavency DESC LIMIT 10) ORDER BY relavency DESC LIMIT 10 the last LIMIT 10 does not work. why? the error was "Error code 1064: ... to use near 'LIM...

SQL Three Table Join and sort

So, I have countries, regions and groups. countries |cid|name| regions |rid|name|cid| groups |gid|name|phone|time|rid| So how can I select each group with its country and region in order: eg. |cname|rname|gname|phone|time| |Australia|nsw|test|1111|whatever| |Australia|nsw|test2|110|whatever| |Australia|vic|test3|100|whatever| |Engla...

mysql_connect() works fine on localhost but not when accessing seperate ip...

Howdy everyone! I'm working with my University's Systems Administrators to get a LAMP stack setup for me. I'll need to access this server from several websites that I'm working on and I'm having some issues. So to keep thing's clear: LAMP Server URL = https://mysqlserver.edu School URL = https://schoolsite.edu When I run mysql_conn...

Why MySQLdb for Mac has to have MySQL installed to install?

Hi, I been working on finding out how to install MySQLdb module for Python on Mac. And all pathes finally come cross to have MySQL installed since there is a mysql_config needed for the module. But I don't understand why it has to be needed? MySQLdb suppose to be a client module for the client who wants to connect to the server. But n...

why can not add this foreign key?

I have this schema: CREATE TABLE `lotto`.`combinaciones` ( `indice` mediumint(8) unsigned NOT NULL, `binario` int(10) unsigned NOT NULL, PRIMARY KEY USING BTREE (`indice`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `lotto`.`sorteo` ( `numeroSorteo` int(11) NOT NULL, `fechaSorteo` date NOT NULL, `precioCarton` doub...

Is this true? performance question

I heard somewhere that you should have your int type fields before all other types in your tables. They said the query runs faster or something. Is this true? For example - id int(10) time int(11) user_id int(10) title varchar(128) text text ...instead of: id int(10) title varchar(128) text text time int(11) user_id int(10) ...

HTML/CSS to display members of a Social Community/Group

Hi, I am using MySql and PHP. I have a table groupXmembers that holds members of a group. I know a simple sql query will give me the members in resultset. But I want to display these members in grid in my sidebar. I am looking for a copy-paste HTML CSS using which I can display users along with their pictures. I know how to display pic...

MySQL Views - When to use & when not to

the mysql certification guide suggests that views can be used for: creating a summary that may involve calculations selecting a set of rows with a WHERE clause, hide irrelevant information result of a join or union allow for changes made to base table via a view that preserve the schema of original table to accommodate other applicati...

datetime function in php

I want to store the data and time in my MYSQL db.I have a datetime field in my db I want to store current datatime in my db How shold i get the current date time?How to pass it to db via a sql query How can i then retriev an print it in correct yyyy--dd--mm format or any other format What wil be format of time? wil it be 23 hrs etc? H...