mysql

PHP/MySQL question [facebook-related]

Okay... I'm having the following problem - in my facebook application I let people add quotes sending them to my DB with their user_id, post_id and date. Then there's a browse page where people see the posts - so far, so good. But then I add a button that lets my users set the said post as their status. Still all good. However when I hit...

mysql insert problem

ive got a really weird problem. i have no clue why its not working. i create an user and get the id and insert a row based on that id in another table. the row gets inserted with that id but the other values however for that row are not inserted! $user_id = mysqli_insert_id($this->connection); $query = "INSERT INTO selections (user...

Strange problem with mysql procedures and PHP database connection

I have two completely separate queries that were transferred over to procedures. When they were ad-hoc queries, they ran great but now that I have stored them, things have gotten strange. PHP calls a class method which returns the values of one of the procs. The second method is then called and fails. When I run the run the proc that PH...

Catch invalid hibernate connection URL error

If I put the wrong connection URL in my hibernate.cfg.xml file, I want to be able to detect it and terminate gracefully, but I can't figure out how; it just hangs indefinitely on buildSessionFactory() during the hibernate initialisation procedure: SessionFactory sf = new AnnotationConfiguration().configure().buildSessionFactory(); It'...

Check query results before running

I would like to run my UPDATE statement on my table and see what the results will be without actually changing the table. For example: UPDATE MyTable SET field1=TRIM(field1); I would like to see the result of that without actually changing the tables content. Is this possible? Specifically I am asking about MySQL. Also, I know I ...

Date Range Query MySQL

I need a query to select data between two dates with today's date as a reference. The database has a datetime field for "start" and a datetime field for "end". $todays_date = date("Y-m-d H:i:s"); $q = "SELECT * FROM news WHERE `end` >= '" . $todays_date . "' AND `start` >= '" . $todays_date . "' ORDER BY id DESC"; The problem is t...

PHP - Doctrine ORM not able to handle bit(1) types correctly?

UPDATE I have filed a bug in Doctrine about this http://www.doctrine-project.org/jira/browse/DC-400 I have the following Doctrine schema: --- TestTable: columns: bitty: bit(1) I have created the database and table for this. I then have the following PHP code: $obj1 = new TestTable(); $obj1['bitty'] = b'0'; $obj1->save();...

Understanding keys in databases

This question is geared towards MySQL, since that is what I'm using -- but I think that it's probably the same or similar for almost every major database implementation. How do keys work in a database? By that I mean, when you set a field to 'primary key', 'unique key' or an 'index' -- what do each of these do, and when should I use eac...

Drop screwed up table in Mysql db

I've managed to corrupt (or something) the 'sessions' table in a mysql db i have (which is called "e_learning_resource_prelive"). This wouldn't be a problem normally as i could just go back to a backup dump of the db. However, the corrupted table seems to be stopping me deleting the database: > mysqladmin -u root drop e_learning_resou...

Mysql. Limit $x1, $x2. Is it possible to do Limit $x2, $x1?

For example, $x1 = 10, $x2 = 25 then it will work Limit $x1, $x2. But I want to make Limit $x2, $x1 and it doesn't work. I want to get from newest to oldest entry from that list. ORDER BY doesn't work edited you can close it, I've figured out by myself. I use ORDER BY a_time DESC LIMIT $x2-$x1 now, so thanks. ...

DELETE data from a table, joining through two tables

I'm working with some rather sensitive data, so I want to be ABSOLUTELY sure I am doing it properly. I am trying to delete the rows in a table that are associated with another table The only way to associate the table is to join through two other tables... here is the exact query: DELETE tt.Transaction_Amount, tt.Transaction_ID FRO...

Counting records of a table with multiple foreign keys (MySQL)

Hello, I have a MySQL database with various tables whose records can be tagged, so I have a tags table, and a tag_associations table that relates tags to other "taggable" tables via multiple foreign keys, and also to an "owning" user -- my fields are something like the following: tag_association_id (primary key) user_id (tag associati...

How can I add a column to the primary key of a MySQL InnoDB table?

I have tables foo and bar: create table foo(a int, b varchar(10), primary key (a)); create table bar(a int, c int, d int, primary key (a,c), foreign key(a) references foo(a)); Now I have a new column e that needs to participate in the primary key of bar. How can I do this? It seems I...

Is there any good reasons that I should not use - (dash) in field names in MySQL?

I have field name category_id in product table. And also I want name field of category id as category-id. Is there any good reasons that I should not use - in a field name? Where can I find which character I should not use in MySQL field and table name? ...

Is field and table name case sensitive in MySQL?

If I have field names called category_id and Category_Id, are they different? And if I have table name called category and Category, are they different? ...

continuous integration with mysql

My entire environment, java, js, and php are set up with our continuous integration server (Hudson). But how do I get out database into the mix? I would like to deploy fresh MySql databases for unit testing, development, and qa. And then I'd like to diff development against production and have an update script that would be used for re...

Why does added RAND() cause MySQL to overload?

OK I have this query which gives me DISTINCT product_series, plus all the other fields in the table: SELECT pi.* FROM ( SELECT DISTINCT product_series FROM cart_product ) pd JOIN cart_product pi ON pi.product_id = ( SELECT product_id FROM cart_product po WHER...

mysql insert random unique 8 chars

hello i'm using PHP , Mysql i have a table with 3 fields (( ID , Username , PID )). i want 'PID field' to contain string of 8 unique chars. my solution is to generate the random string in PHP and check if it's exists if it's exists then it will generate another string . is there any better solution that will save processing time lik...

Stumbleupon type query...

Wow, makes your head spin! I am about to start a project, and although my mySql is OK, I can't get my head around what required for this: I have a table of web addresses. id,url 1,http://www.url1.com 2,http://www.url2.com 3,http://www.url3.com 4,http://www.url4.com I have a table of users. id,name 1,fred bloggs 2,john bloggs 3,amy ...

DELETE data from two tables, based on a third's ID

I'm hesitant to run this query which deletes data (for obvious reasons). I would like to delete the the matching rows from two tables, where the ID = the ID of a third table (which i want to remain unaffected) This is the exact query I would like to run: DELETE FROM ItemTracker_dbo.Transaction t, ItemTracker_dbo.Purchase ...