Here's what I'm trying to accomplish: I've got two tables, call them first and second. They each have an ID column. They might have other columns but those aren't important. I have a third table, call it third. It contains two columns, ID and OTHERID. OTHERID references entries that may or may not exist in tables first and second. ...
Task 1 First i have a specific user, I want to see with which user it is connected to. lets say alex in following table. I want to check if alex is connected to another user? In following table it is connected with John and christina, so i select john and christina. Task 2 Now I have another user. Lets say martin, I want to see which ...
How can I get the latest ID in a table? ...
I have the following script to update one of my FTP passwords every 15 days through a cronjob and e-mail the appropriate people after the attempt has been made. It randomly will fail and so I will run it again manually and it will work. I can't seem to find where it's going wrong. The script is connecting to a local mysql database grabb...
Hi guys, right to business. I have an activity feed which gets all different kinds of activity from different parts of my site sorts them all out by means of using UNION and an ORDER BY and then a LIMIT to get the top 25 and then displays the activity. My fellow programmer says that we will run into problems when we have more rows (cur...
Hello, Can anybody describe me the logic that i can use for bus reservation system. I am using MySql free edition. How can i put a facility where closest city name appears automatically to user. Eg. If user searches bus from cityA to cityB and there are no buses between cityA and cityB. Then automatically, cityC which is closest to cit...
How do you do SQL query for the following condition? Suppose you have two tables: table1 and table2, where each entry in table1 can have multiple corresponding entries in table2. The pseudo code for the query that I want is: for each $row in table1 $rows = find all rows in table2 that corresponds to $row with $row.id == table2.foreig...
We ve been having some issues with our MySQL server intermittently. It keeps throwing out a lot of these queries concurrently (thus stacking them up in my processlist). We are using a MyISAM db and connection pooling through Glassfish v3 for a Grails Application. db_user myhost:35143 db Query 39 converting HEAP to MyISAM /* mysql-conn...
This seems very basic but I can't figure it out. I've got a table "item_tags", and I want to select all of the items that match tags 1 and 2 (as in, each item has to have both tags). How would I do this in mysql? Create table is: CREATE TABLE `item_tags` ( `uid_local` int(11) NOT NULL DEFAULT '0', `uid_foreign` int(11) NOT NULL D...
Is there a way to lock a Mysql table only for write, so that another script can still make a SELECT query? I'm using this code to write to a table (executes almost every second): mysql_query("LOCK TABLES table WRITE;"); mysql_query("insert into... mysql_query("UNLOCK TABLES;"); and this to select (this script just freezes, probably b...
Hi, I need some help joining two table. I've got: my_type_table, which has columns: type (VARCHAR) latitude (decimal) longitude (decimal) ...and neighborhood_shapes, which has columns: neighborhoods (VARCHAR) neighborhood_polygons (geometry) I've got a function called myWithin which checks the latitude and longitude to see whet...
I am having problems remotely connecting to my mySQL database in Java. Here is my error message: java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified I am sure my ip address & port I am using work, since I am using the same ip & port for a mySQL client program. My hosting c...
I've just set up a LAMP server using SuSE Server 10. I want to set the root password for MySQL, so I entered the following in the terminal: mysqladmin -u root password MyPassword and the output is: mysqladmin: Can't turn off logging; error: 'Access denied; you need SUPER privilege for this operation' I'm not really certain what S...
Basically I am creating a summary table. The issue is that sometimes the data in the primary table is modified manually. I am using an ON DUPLICATE KEY UPDATE, however I also need something like ON MISSING KEY DELETE. The summary needs to update to the changed data. Is the best solution really to simply delete all summary records and r...
I have two tables players and scores. I want to generate a report that looks something like this: player first score points foo 2010-05-20 19 bar 2010-04-15 29 baz 2010-02-04 13 Right now, my query looks something like this: select p.name player, m...
I am not a programmer, but I have been tasked with making a basic accounting app for a small business. I was looking at pbooks, but I am not sure this is customizable enough for my needs. I need to be able to count each day how many food items are sold, how many drink items, how many guests, and then tie orders of food and drinks to gu...
I've got a table with a field for Merchant's name and field with the Services they provide. The Services field is a comma separated list of integers that relate to another Services table, with the Service id and the Service Name fields. I'm trying to create a single query that joins those two, so I can have a list of Merchants, along wi...
I was wondering what else should I add to my friends table how can i stop a user from adding the same friend twice as well as when the user is online? As well as any suggestions on how i can make my friends table better? Here is my MySQL table. CREATE TABLE friends ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NUL...
Is there a limit to the number of columns I can JOIN two tables on in MySQL? ...
It has been mentioned to me i should separate columns that are used often from columns that are not. Why? Is it because a table with less columns is better for the cache? Is there an optimization for caching all columns rather than selected? Is this for tables frequently read? Should I separate freq write columns into their own table? ...