I am using Spring MVC for an application that involved a multilevel back end for management and a customer/member front end. The project was initially started with no framework and simple native JDBC calls for database access. As the project has grown significantly(as they always do) I have made more significant database calls, sometime...
I've started a new job where I'm working with MySQL instead of Oracle. What are some things that I might have to "unlearn" from using Oracle? What are some things that might make Oracle SQL go faster, but might be bad under MySQL (and vice-versa)? In particular, is it better for MySQL code to commit less frequently (as is the case for...
Im having a little trouble connecting to a database with PEAR on my GoDaddy hosting account. I am able to connect to my database with the standard mysql_connect. I have downloaded the DB package from: http://pear.php.net/package/DB Firstly I have included the package (which works): include 'libs/pear/db/DB.php'; Then I connect with...
I've been tasked to build a system that allows someone in our company to send out an email with a link to a pdf file that will be kept on our webserver. The recipient can follow the link to view a newsletter we normally sell. The idea is we do this for three months, then see if they'd like to continue and pay for the full subscription. ...
I have 3 tables structured like so: activity table: activity_id, user_id, type, date reviews table: review_id, activity_id, fishery_id, review, date updates table: update_id, activity_id, update, date I want to call the all the reviews and updates which are linked to a user by the activity table however this query retu...
I am trying to add foreign keys to my table but receiving this error. Error Code: 1005 Can't create table 'william.#sql-88c_3' (errno: 150) I have 3 tables. employee, client and Contract. employe [employee_no PK] , Client[customer_no PK] contract [contract_no PK] I want to have Foreign keys for contract as contract [contract_no PK, empl...
I have the following table: Table Account{ [...] email varchar(100), [...] } and a corresponding regular expression: /^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$/i How can I use the MySQL model, to link the regular expression to the the data-field "email", so that the regex is accessible to read out through php as well as ...
Is there a php function to get regular expressions able to check if an input fits a certain MySQL data type? In example: $foo = get_regex_for_data_type("int(10) unsigned"); echo $foo; would return something like: /^[0-9]{1,10}$/ ...
Im loading an xml file with tweets and then adding it a to a Mysql database. Up to this point it works fine but id like to do a basic check on the url field to see if its already added in the database. If its in the database already id like to update the mention count else insert it as a new entry. Its a real newbie question but im strug...
I want to insert a row of data into a table with five columns (this table joins members and games); four of the five columns are known, while the fourth, rank, has to be dynamically calculated: wishlists(id (int, pk), memberid (int, FK), gameid(int, FK), rank (int), createdat(timestamp) ) INSERT INTO wishlists (memberid, gameid, rank) ...
I have a table with uuid and system column. I need a query to return only uuid's having system=1 but not the uuids with system= 1 and 2 ...
When I set my pagination to display 10 comments at a time my comments query wont count the comments replies as part of the display count how can I fix this so that my comments replies are counted? My comments replies queries are nested in my main query to display comments. Query for pagination SELECT COUNT(comment_id) FROM comments WHE...
Hi, I work at a fairly big website; we have ~400-500 million page views a month. We use PHP and MySQL. Currently our page flow works like this (semi pseudo code for clarity): mysql_connect(); mysql_select_db('red'); mysql_query('SELECT * FROM apples'); mysql_query('SELECT * FROM cakes'); One of my co-workers suggested that mysql_sele...
Suppose I have the following table house: House: id name cityID Where cityID refers to the id field of table city City: id name stateID where stateID refers to the id field of table state State: id name countryID where countryID refers to the id field of the table country: Country: id name How do I do mysql join statements s...
hello, i was trying to run this query: ( (SELECT * FROM (SELECT * FROM `users` WHERE `access_level` > 0) AS `search_subject` WHERE (BINARY `username` = ?)) UNION (SELECT * FROM (SELECT * FROM `users` WHERE `access_level` > 0) AS `search_subject` WHERE (BINARY `username` = ?)) ) LIMIT 5 but got an error because of the sur...
How would I use a IN table with like? So that I could use % in them? By in I mean: SELECT fields FROM table WHERE age = "50" AND name IN ("tim", "bob", "nancy", "john"); I already tried: SELECT fields FROM table WHERE age = "50" AND name LIKE ("2010-09-17%", "2010-09-16%") But it gave the error "Operand should...
I was wondering how can I number my comments using PHP & MySQL and keep the correct comment number when using pagination? A brief example or tutorial would help. Thanks Example output. COMMENT 1 COMMENT 2 COMMENT 3 ...
I have a table like this Sr Name 1 A 2 B 3 C 4 C 5 C 6 E 7 A 8 A 9 A 10 E 11 B 12 B I need output like this A = 4 Times B = 3 Times C = 3 Times E = 2 Times How can I achieve this? Thanks in advance ...
If Newdata is list of x columns, How would get the number unique columns--number of members of first tuple. (Len is not important.) Change the number of "?" to match columns and insert using the statement below. csr = con.cursor() csr.execute('Truncate table test.data') csr.executemany('INSERT INTO test.data VALUES (?,?,?,?)',...
Hello all I'm trying to optimize this mysql query using EXPLAIN. Can somebody please help me out over here? EXPLAIN SELECT * FROM keyword WHERE keyword LIKE "panasonic%" AND keyword != "panasonic" AND price < 3230 AND price > 3370 ORDER BY price DESC LIMIT 99 Basically I want to find out the keywords which start with "some keyword" b...