Hi, I have to insert approximately 96,000 rows in a table based on certain calculations. The business requirements is to have this operation in a single transaction, i.e. either all the entries are made successful or none. I am using MySQL and I could see the operation takes much more time than expected.. I shut down the operation afte...
Hi i need help with a query. I have 4 tables articles - id, category_id, user_id categories - id article_category - article_id, category_id users - id The category_id in the article table is used determine the main category Now I want to get all articles so i uses the query below. Article can have more than one category. I want to s...
I have two tables, one for bookings and one for room allocations. Booking table has an id and room allocations table has an id and room_id (id refers to the booking id). I want to find a list of all of the bookings who do not have an entry in the room allocations table. I made the following query: $sql = "SELECT booking.id, booking.use...
I have a report which will give me the time field as $time='Wed, 25 Aug 2010 13:43:38 +0000'. I am currently storing this into my MySql DB exactly like this and generating a custom report with the same time. I am in EST time so i want to know how i can change the $time field to show it according to 'Wed, 25 Aug 2010 09:43:38 '. i want t...
I have installed php and mysql on windows xp with iis 5.1 as my web server. But when i run the following code it just shows me a blank page. Can anybody let me know where i am wrong. Thanks in advance. <?php $con = mysql_connect("localhost","root","xxxxxx"); if (!$con) { die('Could not connect: ' . mysql_error()); } ?> ...
Hello All, This is probably something easy to accomplish. I have some dates in my MySQL database, (and I am using PHP). Some are stored as such 2010-08-25 11:00:00, while others are stored as 2010-08-25T08:00:00 My question is: I am selecting the dates from the database, and then using ORDER BY start_date However, I have noticed tha...
Hello, I'm created table with one MEDIUMBLOB column and writing every second 2 images (16 or 32 KB) to this table. But random times i get Lost connection to MySQL server during query. Maybe i need to tune some mysqld server params or do something different ? I tested with local and remote mysqld server and i get same error. My code t...
Using MySQL 5.1.37 This isn't really a critical issue, one of our biz-dev people just asked about it and I didn't have a good answer. In our users table we have an auto-increment primary key. From time to time, it skips numbers and seems to be doing so at an increasing rate. My original thought was that when two concurrent signups oc...
Query issues, for the life of me i cant figure out what is wrong with this query HAVING distance ='10' GROUP BY c.ancient, c.ant_name, p.upper, p.name, ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY...
I am trying to make a web application using Netbeans 6.5, MySQL 5.1, JSF, hibernate 3.2 and GlassFish. I want to insert the contents of a form in the database; it works fine when the table doesn't have foreign keys but it doesn't work when they do have them. I tried using Hibernate, and also using an SQL query directly, but it can't get ...
If I understand correctly, the pre MySQL 5.0.3 interpretation of the BIT data type meant it could be used as a series of flags? If this is the case I can see practical uses for it in MySQL but imagine it would not have been as efficient as using SET. Even if the above is not the case, I have great difficulty in understanding how the cur...
i get the following exception (missing primary key) in the line of using Find() method "Table doesn't have a primary key." I've rechecked the Database and all Primary Key columns are set correctly. my code: DataTable dt = p.GetAllPhotos(int.Parse(Id)); DataTable temp = new DataTable(); temp = dt....
I have a website that picks an entry from a database at random and then displays it to the user. I would like to add a "top rated" page on which the best entries as chosen by an aggregate of user opinions are displayed. I want up votes and down votes as seen here on stackoverflow and on many other websites. I am not very experienced wi...
I have a table that records a history of address updates, called transaction. The schema is something like row_id (int, PK), user_id (int), address1 (varchar), transdate (timestamp). I want to query the table and have a single row returned for a user showing what is the latest row (i.e. greatest timestamp), but if there is data in the ...
Hi, I have a simple question here (I know it is so simple but I just got stuck...) I have a table tag_id, search_id 1,1 2,1 4,2 2,2 5,2 I would like to get the search_id by giving the tag_ids example: tag_id 1,2 would return search_id 1 tag_id 4,2,5 would return search_id 2 tag_id 2,6 would return no result. I should have pay more at...
Spent hours on this now and could use some help! Our website queries our db - table columns are set to Latin1 collation, website has set names to UTF8 for queries. The data is French and when we do a search for a string including accented characters we get the "*Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_c...
Hi guys, I have a mysql database tabel populated with non english data. When I view the data in Navicat MySQL browser the data appears fine. However when I run a php script to seelct and display the data on a web page it displays question marks instead. The page encoding is set to utf8 and even the mysql collation is set to utf8 - someth...
Whats the best way to print from mysql using php when you know theres only going to be a single record. My Sql is "select user from users where user = 'norman'; This will return only a single record. So what's the best way to print it? I currently do: while ($info=mysql_fetch_assoc($data)) etc etc But thats ok for more than one record...
After download the Sakila (sample), I have try to forward engineering this Schema into the server but only part of the table was engineered. Later synchronize also fall. Please take a look the report below and tell me is what wrong. I dont have programming background. If someone have successfully forward engineer Sakila fully, let me kn...
I would like to know if it's possible to find a value in a select statement and use it in a where clause like: SELECT col1, MAX(col2) - COUNT(DISTINCT col3) as variable FROM table WHERE col1 > variable "table" is pretty big, and I want to narrow down the records the query has to look at as quickly as possible. I know this isn't bit ...