I understand the differences between CHAR and VARCHAR, one being fixed-length and the other variable-length, and that if all fields in a row are fixed-length, a table will generally perform better. However, something that is unclear to me is that if I give VARCHAR a length, such as VARCHAR(500), does this retain the row as fixed-length?...
I am having problems using stored procedures. Making call to a stored procedure is not working for me. So I replaced the stored procedure by an sql statement. But now, I have some stored procedures which receive parameters. for example CREATE PROCEDURE get_category_details_proc(IN incat_id INT) BEGIN SELECT name, desc FROM category W...
Hi all, MY PLATFORM: PHP & mySQL WHAT I HAVE HERE: I have 4 tables, namely, 'books', 'book_type', 'book_categories', 'all_categories'. WHAT I AM TRYING TO DO: In simple words, I want to display all the books that are in stock i.e. in_stock = 'y', with all the book related information from all the tables, only once without repeati...
Basically I'm looking for something like SELECT ordinal(my_number) FROM my_table which would return 1st 11th 1071st ... etc but preferrably without the use of a stored procedure ...
Hello all, I've got a somewhat complicated question for you cakephp experts. Basically, I have created a db table called "locations". Every month I will get this table sent to me in csv format from a client. Unfortunately, instead of updating this table, I will have to empty it and reimport all of the records. Unfortunately, I cannot al...
Hi :) I have a query: SELECT COUNT(*) as votes, a.member_id FROM ballots a WHERE ballot_id = 1 GROUP BY a.member_id which yields something like: votes member_id 1 paul 5 mike 3 noynoy 10 andy 2 noel I'd like to be able to get the row "andy" because he got the highest "votes". How do I change my query to ...
Hello. For entities in my application, I am planning to log common meta-data like DateCreated, DateModified, IPAddress, etc. Does it make sense to add these columns in entity tables or is it better to have a single table for logging meta-data where each row has link back to the item that it corresponds to? Later for purpose of reporting ...
I am confused about how to copy a column from one table to another table using where. I wrote SQL query but it says transaction lock time exceeded or query returns more than one row. using mysql Basically, I have: Table 1: Results BuildID platform_to_insert Table 2: build BuildID correct_platform update results set results.platfo...
I have four MYSQL 5.0 tables: userdb, filterdb, filterlinkdb, mandatoryfilterdb. All tables are simple. * = pri userdb: id* name filterdb: id* name filterlinkdb: user_id* filter_id* mandatoryfilterdb filter_id* How can I check filterlinkdb to make sure that all mandatoryfilterdb records exist for every user_id using a singl...
Hi, I'm developing a web application with ASP.net & C# and it needs to connect to mysql database. I'm not sure if I should use linq or OleDB to connect to mysql!!! can you give me some advices about that? ...
I'm converting a flat list into a tree in my CakePHP app and found that there is an existing behavior that has this functionality. My table is not one giant tree, but consists of many user-generated trees: basically, each user can create their own folder structure. However it seems that the Tree Behavior would only keep track of lft/rght...
Can anyone of you help me understand the behavior I get when visiting this page? <html> <body> <?php $liendb = mysql_connect("localhost","",""); mysql_select_db ("test"); for($i = 0; $i < 418; ++$i) { echo("Any old text<br />"); } $resultatSQL = mysql_query("SELECT...
$userid = $_SESSION[userid] $sqll = mysql_query("SELECT count(id) FROM entries WHERE userid='$userid'"); Is using count(id) efficient? Or is there a better way? ...
Is there a way this hand coded query could become dynamic? SELECT master.id, (select count(0) as score1 from scores where scores.id = master.id AND scores.category = '1'), (select count(0) as score2 from scores where scores.id = master.id AND scores.category = '2'), (select count(0) as score3 from scores where scores.id = master.id AND ...
I have seen mention of Java and Python. I need something that can be installed on a users desktop without them having to also install Java or anything else. User simplicity is a must. This widget will log into an online php based calendar that accesses mySQL. Any pointers on what I should be reading up on? Python? Thanks! Joel Up...
I am loading a CSV file into MySQL (5.1) using CREATE TABLE and LOAD DATA. I have a number of columns which are textual types (categories) but contain numerical values. When I create the table I assign these columns as VARCHAR(254) but the JDBC driver throws a SQLException "Data truncated for column AgeGroup at row 1". My data looks l...
Hi All, Have a question that I can't seem to find an answer for. I am trying to connect to a remote database. I type in the following to my Ubuntu shell: mysql -u test -h mysql.domain.com -p mysql asks for my password and then outputs the following: ERROR 1045 (28000): Access denied for user 'test'@'externalit.domain.com' (using p...
Is there a reason for or against setting some fields as NULL or NOT NULL in a mysql table, apart from primary/foreign key fields? ...
I have a MySQL table with 10 fields. For this particular query, I only care about 4: Title, Variables, Location, Date. I would like to take the distinct values of these four groups and then group by Title, Variables. However, When I use the following query Select DISTINCT Title, Variables, Location, Da...
I recently tried to import a bunch of blog posts from an old blog (SharePoint) to my current blog (WordPress). When the import completed, a lot of nasty <div> tags and other HTML made it in to the content of the post, which screwed up the way my site was rendering. I'm able to view the offending rows in the MySQL database and want to k...