I have a table that has a column which holds the id of a row in another table. However, when table A is being populated, table B may or may not have a row ready for table A. My question is, is it possible to have mysql prevent an invalid value from being entered but be ok with a NULL? or does a foreign key necessitate a valid related va...
I'm having some trouble understanding how to use prepared statements, when you need to match one value against several columns at once. In other words what instead of doing this: $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?"); $stmt->bindParam(1, $name); I wanted to do this: $stmt = $dbh->prepare("SELECT * FROM REGI...
I have a table that I'm querying for value 43 in the second field and I return the value of the third field SELECT t1_field3 FROM table1 WHERE t1_field2=43 this returns 19, 39,73 t1_id t1_field2 t1_field3 ----- --------- --------- 1 43 19//// 2 43 39//// 3 ...
I have this large (and oddly formatted txt file) from the USDA's website. It is the NUT_DATA.txt file. But the problem is that it is almost 27mb! I was successful in importing the a few other smaller files, but my method was using file_get_contents which it makes sense why an error would be thrown if I try to snag 27+ mb of RAM. So how...
I'm trying to grab content where id = 3 OR id = 9 OR id = 100... Keep in mind, I can have a few hundred of these ids. What is the most efficient way to write my query? $sql = "SELECT name FROM artists WHERE (id=3 OR id=9 OR .... id-100)" ...
So I have a database that is setup sort of like this (simplified, and in terms of the tables, all are InnoDBs): Users: contains based user authentication information (uid, username, encrypted password, et cetera) Contacts: contains two rows per relationship that exists between users as (uid1, uid2), (uid2, uid1) to allow fo...
could somebody correct my following query, i am novice to software development realm, i am to a string builder object in comma separated form to my query but it's not producing desired result qyery is as follows and string cmd = "SELECT * FROM [placed_student] WHERE passout_year=@passout AND company_id=@companyId AND course_id=@courseId...
I have a scenario where I'm calculating something in the WHERE clause of my SQL, but I also want to get that calculation - since it's expensive. Is it possible to get the results of something done in the WHERE clause, like this: SELECT `foo` FROM `table` WHERE (foo = LongCalculation(`column`)) Wishful thinking, or possible with MySQL?...
I have a database it is quite larger so I wanted to export it using Command Prompt, but I dont know how to. Please help I am using WAMP ...
Is there a function for MySQL that will count the number of times a string occurs in another string or column? Basically I want: SELECT SUB_COUNT('my word', `my_column`) AS `match_count` FROM `table` Thanks! EDIT: I need to know how many times the string appears in a column for each row in a SELECT. ...
There are parts of my app where a persistent connection is required, in particular the parts where every hour maybe 30,000 select requests are made by many different users, this is causing my mysql server to max out on the 100 connection limit, and i really don't want to increase it since 100 connections already seems like alot. So for t...
Hi I'm receiving the following error on my shared hosting box: SQLSTATE[HY000]: General error: 5 Out of memory (Needed 4194092 bytes) This error is only triggered on a specific page. I guess this indicates that I am reaching the upper limit of the 64MB allocated to me in my current MySQL environment. Does this mean that a single q...
So, here's the problem (it's probably an easy one :P) This is my table structure: CREATE TABLE `users_awards` ( `user_id` int(11) NOT NULL, `award_id` int(11) NOT NULL, `duplicate` int(11) NOT NULL DEFAULT '0', UNIQUE KEY `award_id` (`award_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 So it's for a user awards system. I don't ...
This is a bit lengthy but I have provided sufficient details and kept things very clear. Please see if you can help. I am sure a person experienced with this can surely help or suggest me to decide the tables structure. About the system:- There are tutors who create classes A tags based search approach is being followed Tag relatio...
when I am trying to connect mySql data base from cmd it giving some error like - c:\xampp\mysql\bin>mysqladmin -u root password 123123 mysqladmin: connect to server at localhost failed error:Access denied for user root@localhost (using password : NO) ...
I want to remove the password for user root in localhost. How can I do that? By mistake I have set the password of root user. That's why phpmyadmin is giving an error: #1045 - Access denied for user 'root'@'localhost' (using password: NO) ...
i've a old database where i store the data of the holidays and dates in which they are celebrated.. id country hdate description link 1 Afghanistan 2008-01-19 Ashura ashura 2 Albania 2008-01-01 New Year Day new-year the flaws in the above structure is that, i repeat the data other than...
Hello people, Here's my USER table CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) NOT NULL, `expiry` varchar(6) NOT NULL, `contact_id` int(11) NOT NULL, `email` varchar(255) NOT NULL, `password` varchar(100) NOT NULL, `level` int(3) NOT NULL, `active` tinyint(4) NOT ...
Alright, I'm trying to work on a function for active user counting in an AJAX based application. I want to express the below SQL Query in correct syntax but I'm not sure how to write it. The desired SQL Query is below: SELECT count(*) FROM active WHERE timestamp > time() - 1800 AND nick=(a string that doesn't contain [AFK]) Now, ...
I have seven 1G MySQL binlog files that I have to use to retrieve some "lost" information. I only need to get certain INSERT statements from the log (ex. where the statement starts with "INSERT INTO table SET field1="). If I just run a mysqlbinlog (even if per database and with using --short-form), I get a text file that is several hun...