mysql

Error 0040: Entity Framework + MySQL

I'am deploy one application that use Entity Framework with MySQL. I've got this error when I access the page. In my developer machine the system works perfectly, but in the server that I deploy the solution It's not work. The scheme specified is not valid. error 0040: The bool type is not qualified with a namespace or alias. Only Primit...

Query Optimization

I have 2 queries. First: SELECT * FROM `table` WHERE col='xyz' LIMIT 100 //Time Taken: 0.0047s Second: SELECT * FROM `table` WHERE col='xyz' ORDER BY Id DESC LIMIT 100 //Time Taken: 1.8208s The second takes a much longer time. I know why that is, it is because first I have to select the whole table, then do the ordering, whereas ...

Which is better: mysql_connect or mysql_pconnect

I'm a PHP newbie working a some scripts to display some news articles from a databse and wanted to find out a couple of things. For opening a connection to a MySQL database, which is a better option mysql_connect or mysql_pconnect? What are the advantages or drawbacks of using a persistent connection to the database? And in what kind ...

How to migrate from Oracle RDB for OpenVMS to MySQL in Windows?

I have a legacy Alpha server with a RDB database. This db is replicated in a MySQL db with many bad php scripts that drops all the tables and takes everything from the Alpha. This works very slow and is becoming unmaintainable. Is there a better way to fix this than programming again the scripts? Anything like MySQL Migration Toolkit? ...

Unique constraint for two columns in Mysql and indexes

Sorry for newbie question - am I understand right that these two operators are identical in Mysql? ALTER TABLE friends ADD CONSTRAINT UNIQUE (`user_id`, `friend_id`); and CREATE UNIQUE INDEX friends_user_friend ON friends (user_id, friend_id); ...

Add To Basket Script - some design help, please

I'm writing a script to let users place items in their baskets. It is very complex so far, and I would like to talk it through with someone to see if they can suggest better design or tidy the current design up. Here is the code I have, which doesn't work very well (i.e. has errors that I haven't yet resolved), with comments to show my i...

mySQL - Ordering table as desired, before Adding Primary Key column

Am using the following notation to add a primary key to a table: ALTER TABLE tablename ADD id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST And this works fine, but it seems to default the order of the table to the original input order of the rows, before adding this primary key. And this is not ideal for the current situation. ...

Is this a logical use of MySQL Stored Procedures?

I have a client with a hosted MySQL database whose developer keeps asking me to add really simple stored procedures. I look at a stored procedure like this, and I don't see any reason why it would be implemented as a stored procedure and not implemented within application code. Am I correct that this is really strange use of stored pro...

How to archive record with records from other connected tables?

I've got 8 tables: users: uid users_removed: uid messages: mid uid FK users (uid) messages_removed: mid uid comments: cid mid FK messages (mid) comments_removed: cid mid files: fid mid FK messages (mid) files_removed: fid mid When I remove record from table "users" I want move it to table users_removed (bef...

PHP/MySQL isolating database access in a class - how to handle multiple row result set in an OOP manner

PHP/MySQLisolating database access in class - how to handle multiple row Selects Here’s a coding question. I isolated all DB access functions in a class <?php class DB { var $conn; function DBClass () { @$this-> conn = mysqli_connect (DB_SERVER, DB_USER, DB_PASS, DB_NAME); } function validateUse...

problem in variables scope in PHP

I wrote this code require('Database.class.php'); function get_info (){ $db = new Database($config['server'], $config['user'], $config['pass'], $config['database'], $config['tablePrefix']); $db->connect(); $sql = $db->query('SELECT * FROM ja_cat'); while ($options = $db->fetch_array($sql)) { $cat[]...

MySQL question about "reverse LIKEs"

Well given I have a value I want to check for potential matches in a database (in one varchar field) so I write something like: SELECT * FROM table WHERE column LIKE "%value%" Which will work if the value is something like "test" and the column has a value of "this is a test" however if it is reversed then I will not get a match I hav...

inserting a array of values

I have a array with a variable amount of values. Is there a more efficient or better way to INSERT them into my DB besides a loop with a query inside it? ...

Query displays first entry in table only - wrong!

$query = "SELECT A.Agent_Name, C.Country_Name, J.Job_Type FROM Line_Items LI, Agents A, Country C, Job J WHERE LI.Agent_ID = 1 AND LI.Agent_ID = A.Agent_ID AND LI.Country_ID = C.Country_ID AND LI.Job_ID = J.Job_ID"; $result = mysql_query($query); while($row = mysql_fetch_query($result)) { echo "Agent:" . $row['A...

PHP, MySQL - Only a DB Class or a Combo Group (DB Class + Results Class)

Hi, my questions pertain to class objects defined for mysql/php interface: Which of the following method is best from a design point and why (relative advantages/disadvantages to each other): To use a single class like ezSQL towards abstracting the mysql/php interface? To use a combination of a DB Connection Class and a Results Class...

Alternatives to traditional relational databases for activity streams

I'm wondering if some other non-relational database would be a good fit for activity streams - sort of like what you see on Facebook, Flickr (http://www.flickr.com/activity), etc. Right now, I'm using MySQL but it's pretty taxing (I have tens of millions of activity records) and since they are basically read-only once written and always ...

Combining two mysql Select statements so I can sort the resulting data

I was wondering if there is a way to simplify this down from two queries to a single one. I'd like to be able to sort the data as I pull it out of the database and this would allow me to do it. The tables are setup like: table: files ------------------------ fileID (INT) | domainID (INT) ------------------------ table: domains ------...

Need to pull last x number of rows based on date.

I have a table with dates in "Aug 23, 2009" format and 5 values, so that it looks like this SELECT * FROM table; Date | Total | V1 | V2 | V3 | V4 Aug 21, 2009 | 41 | 23 | 8 | 8 | 2 Aug 22, 2009 | 39 | 22 | 8 | 7 | 2 Aug 23, 2009 | 35 | 20 | 6 | 7 | 2 Aug 24, 2009 | 34 | 20 | 6 | 6 | 2 Aug 25, 2009 | 32 ...

Getting SUM() on distinct rows in mysql

I have a table ("dump") with transactions, and I want to list the total amount, grouped by category, per month, like: Month | Category | Category ID | SUM. The tables involved looks like this: TABLE dump: id INT date DATE event VARCHAR(100) amount DECIMAL(10, 2) TABLE dump_cat: id INT did INT (id in dump) cid INT (id in categories) TA...

Ruby on rails connection problem

I have a Ruby on Rails project that I was developing on a hosted server but have decided to work on my local windows machine with. To get started I thought I'd make sure that I could just take my models from the old project and put them in a new project then query them in the console. This fails. Edit to reflect more accurate problem:...