Is it possible to hide a folder from the www directory so that the php files will not be seen if you access it through a web browser? I'm doing this because I'm not yet good enough to secure those files and the mysql database that they are manipulating. Or even a trick that would make the web browser not to be able to access the localho...
mysql> ALTER TABLE category ADD CONSTRAINT category_parent_category_id FOREIGN KEY (parent) REFERENCES category(id); ERROR 1005 (HY000): Can't create table 'sfnews.#sql-244_1' (errno: 150) DDL as follows: Create Table: CREATE TABLE `category` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `parent` bigin...
I have a select like this: SELECT field1, field2, field3 FROM table WHERE field1= 5 AND field_flag =1 GROUP BY field1, field2, field3 limit 1000; I want to update field_flag for the resulting rows. How can I do that in MySQL? ...
Is there a way to use LIKE and IN together? I want to achieve something like this. SELECT * FROM tablename WHERE column IN ('M510%', 'M615%', 'M515%', 'M612%'); So basically I want to be able to match the column with a bunch of different strings. Is there another way to do this with one query or will I have to loop over the array of...
I am trying to create a JSF application using the Eclipse IDE. I am using a remote mySQL server as my database. How do I connect to this remote database for creating tables and accessing them? ...
Hi guys, In MySQL, I've set the log_output to FILE, and general_log to ON, and the queries start rushing in for me to debug. BUT, after 1022 bytes, each query is abruptly truncated. How can I increase this limit so that I get to debug also the long queries? Cheers Nik ...
Create Table: CREATE TABLE `category` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `parent` bigint(20) unsigned DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `parent_idx` (`parent`), CONSTRAINT `category_parent_category_id` FOREIGN KEY (`parent`) REFERENCES `category`...
I have a simple tree which takes the shape below ROOT /\ A B / \ A1 B1 \ B11 This is stored in a DB table CLASSES that is self referencing. ID | CLASS_ID | PARENT_ID --------------------------- 1 | ROOT | 2 | A | ROOT 3 | A1 | A 4 | B | ROO...
I'm working on building a tree structure in MySQL and have been experimenting with different ways of representing the data. However, no matter how I slice it, there are shortcomings. The nested sets model allows me to easily select entire branches of the tree - which I need to do. However it is not so easy to select immediate childr...
I'm working with a tree structure in MySQL that is respresented using the nested sets model. I'm hoping some of you sql experts can help me with building a SELECT query. I would like to be able to match a set of nodes using LIKE. For each node that is matched, I also need a comma-delimmited list of the ancestors of that node, and a co...
I have a large table with a multi-part index. I need to run several queries to update a field that is part of the index. Basically every row will be updated. Would it be faster to drop the index, do all the updates and then re-add it? EDIT: Added question, why does it take so freaking long to DROP an index? Am I missing something or ...
I have practiced SQL in w3schools, which is good but doesn’t allow to try with DDL commands, which is certainly not satisfying. Please suggest me such tutorial(s), which allow to practice SQL or MySQL online, allowing to use all the commands, if you have ever observed. ...
Hi I have 2 tables Table 1 ID Status 1 D 2 F 3 D Table 2 SID ID Approve 1 1 N 2 1 Y 3 1 Y 4 2 Y I need a query to joins 2 tables on ID and finds records that don not have N in their Approve column. Does anyone have any clue how to implement this? ...
Hi I'm creating hashed passwords using salted sha1 in PHP. My question is: In MySQL what is the proper character encoding, field type & length to store the result? Is there anything else in MySQL to consider for password security? Finally are SHA256 or SHA512 practical hashing choices? ...
I am dealing with a project which uses cross-schema views heavily. The problem I am looking to solve is importing these schemas in simple manner, without errors. I'll explain this further: Let's say the schemas are A and B. In schema A there is a view which points to schema B and the other way around. When importing the A mysqldump fil...
I have two tables, TableA and TableB. I need to select one count value from TableA, based on a where condition. I need to select two values from TableB. I'd like all the values in one result set. There will never be more than one row in the result set. Here's what I have now: SELECT count(id) FROM TableA WHERE ($some_where_statement)...
Hello, I have the following code in mysql. create table employee( e_id int(10) not null auto_increment, user_id int(10), usertype_id default 1, name varchar(50), primary key (e_id) ); create table relation( r_id int(10) not null auto_increment, user_id int(10) not null, usertype_id int(10) not null, ...
For a browser game, I have a database table fights: fightID fromID toID simulated (1=true, 0=false) Whenever a user requests a page, I select all fights which still have to be simulated: SELECT fightID, fromID, toID FROM fights WHERE simulated = 0 These outstanding fights are then simulated in the PHP script and, finally, the figh...
Hi I cant figure out any good way to get a list of tag.names and lang which isn't already inserted in combination inside images_urls. My database looks something like this. tags name user_id +-------+---------+ |hi | 1 | |friend | 1 | |friend | 2 | |people | 2 | +-------+---------+ users id lang...
I'm trying to construct a select query where it will take all the columns of 4 tables and then order and display the results by the column 'name' (the same in all tables). I'm still learning the ropes of MySQL. I'm finding that because the columns share the name 'name', only the results from the last table are displayed. Is there a wa...