does mysql ignore null values on unique constraints?
i have an email column that i want it to be unique. but i also want it to accept null values. can my database have 2 null emails that way? ...
mysql
How can i find out if I am at the last row in a data set?
Hi folks, I would appreciate some help on this, I have a result set which I am trying to work with. The result contains 2 fields and any number of rows. In calling fetch_row() how can you tell if the method has got to the last row? I am iterating through the rows using: while(list($className, $classID) = $result->fetch_row()) { ...
Mysql JOIN - Unexpected result in mysql_fetch_array
I am using a complex join statement to get data from my 'items' table where 'column1' is equal to the value of table2.ID This is in a mysql_query() statement, and it should return 3 rows. Now my understanding is that by using $array=mysql_fetch_array($queryresult);, I can then loop through each 'row' using a foreach($array as $output){...
MySQL to return only last date / time record
i have a table that has a serial number, date and time when that serial number was modified. i would like to retrieve the latest time and date when that particular serial number was modified. any suggestions? the dates and times are on different columns. thanks ...
Error on Recordset update after addnew saying a column cannot be null even with a default set to empty string
I've got a program that i've been supporting that recently was installed on a windows 7 machine. Everything has been working however recently this machine has started Receiving the following error when using the recordset.update method after modifying fields i've received the following error. -2147467259 ([MySQL][ODBC 3.51 Driver][...
How can I optimize this MySQL query?
Hello, can someone please explain why the addition of the group by subquery makes this query takes so long (30secs): SELECT * FROM aggregate_songlist AS a INNER JOIN musical_works AS m ON a.musical_work_id = m.id WHERE m.genre='rock' AND m.id NOT IN (SELECT sources.musical_work_id FROM sources GROUP BY sources.musical_work_id HAVING C...
how to make phpMyAdmin import datetime correctly from csv?
I've been provided a csv file which contains an export of a client's database table. Two of the columns are dates, and in the file they're formatted as mm/dd/yyyy. ID | ActivateDate ----------------- 1 | 05/22/2010 2 | 10/01/2010 Our mySQL database that I need to import them into has those columns defined as datetime, with a defaul...
How to account for an unknown return type for a Java Function
I have a function that returns the result of a query. The input arguments are the SQL statement and the field to be retrieved, while the output is the result of the query. As expected, the database contains multiple data types. Is there a generic return type I can specify the function to have? The code below retrieves string...I need to...
MySQL REGEXP for numbers that begin with ..
Hi guys. I need to write a query using MYSQL REGEXP that will find me rows that have a certain column begin with 11 or 12 or etc. I know I can use LIKE or LEFT(####,2) but would like to use the REGEXP option. My data is stored as 110001, 122122, 130013a and etc. EDIT 1: To make it more clear, I would like to express SELECT * FROM ...
Optimize massive MySQL INSERTs
Hi! I've got an application which needs to run a daily script; the daily script consists in downloading a CSV file with 1,000,000 rows, and inserting those rows into a table. I host my application in Dreamhost. I created a while loop that goes through all the CSV's rows and performs an INSERT query for each one. The thing is that I get...
How can you get a sql script of changes from Rails migrations (for MySQL)?
I've seen http://github.com/muness/migration_sql_generator, but it doesn't work properly with MySQL for certain important operations. Is there any other way we can capture the sql generated during a rails migration? The reason I'm asking is that I cannot run the migrations on the production server since it is maintained by technical sup...
Mysql Query Optimization (Group_Concat)
SELECT b.isbn, booktitle, price, description, GROUP_CONCAT(c.contributorid SEPARATOR '|' ) AS contributorids, GROUP_CONCAT(fname, ' ', lname SEPARATOR '|' ) AS contributornames FROM (book AS b LEFT JOIN book_contributor AS bc ON b.isbn = bc.isbn) LEFT JOIN contributor AS c...
Does MySQL Short Circuit the IF() function?
I need to query data from a second table, but only if a rare set of conditions in the primary table is met: SELECT ..., IF(a AND b AND c AND (SELECT 1 FROM tableb ...)) FROM tablea ... a, b, and c conditions are almost always false, so my thinking is the subquery will never execute for most rows in the result set and thus be way faste...
Self referential table and recursive SQL function
Hi All, I have a category table with: categoryID parentCategoryID categoryName and an items table with: itemID categoryID itemName I am using MySQL. i want to write a query that will return a count of Items in a category given a categoryID. The query should return total count for all items in all subcategories of the given categ...
Database schema designing help - Mysql
Ok need some help, I have a system I'm creating that will allow users to purchase site credits and earn them, and with these credits order products, and access features that may be for sale. My issue is creating the orders and transactions tables. I have 2 separate order tables one for site credits (sitecredit_orders) and one for produc...
Displaying BLOB files in PHP script
This is my PHP script which displays a radio station's schedule: <div class="divider"></div> <div class="main" style="width:552px;"> <img src="$image" width=115 height=60> <div class="time">$airtime</div> <div class="show"><h3><a href="$link><b>$presenter</b></a></h3> <p>$showdesc</p></div> ...
How to generate a user role grid
I have the following tables: users (id, username, ... ) roles (id, name) roles_users (user_id, role_id) I am wondering how I can create a nice sort of user-role-grid from that which an admin can use to administer roles to users in a clear way. What I would like is basically a table full of checkboxes sort of like this: ...
CodeIgniter Active Record, basic update give error
Hey, I'm new to CodeIgniter and I get an error I cannot understand. This is the code that give the error: $data = array('adr' => $address); $this->db->where('id', $id); $this->db->update('domains', $data); The error is: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the rig...
Using mysql_insert_id to insert a lot of data
I want to use PHP and MySQL to insert data into a data table and its associated many-to-many table. The auto-incrementing primary ID of the data table is used in the many-to-many table. So, I need to know the ID's of the data table to insert into the many-to-many table. My current thinking is that the best way to approach this is with...
Is it better to pull in an entire dataset if there's going to be a lot of ajax filtering done on the page?
I basically have this web page where you can narrow your search results by adding and removing certain filters on the left hand side. The content being pulled from the database is a list of properties, which can be filtered by: Type ( Apartment, Camp, Ranch, Fishing Camp, Hotel, Lodge ) Activities ( Camping, Kayaking, Hunting ) Locati...