Is there a better way to relate a web page to a row in a database?


We currently use a CMS we developed in-house. It's not a full CMS, as it just contains things like the contact name for a given web page, page title, and some other details, but not the content.

As it is now, each page is "linked" to its row in the database by the page URL (minus the doman name). Since this is a text column and not a numeric column, I know it's not ideal, but I can't think of any other way to do it. If I use an ID number, then it seems like I would have to have an ID number on each page (and there are around 60,000 of them) so that would be hard to maintain.

So far, the current method works, BUT our main table is starting to get pretty big, and the server people have asked us to try to reduce the sizes of our databases (we use Access, so each database is an MDB file). I was thinking that it would save space (and improve performance) to somehow make this a numeric field, but I can't think of a way to do it, especially when each page is still its own individual file.

Anyone have any advice
Posted On: Monday 31st of December 2012 01:45:22 AM Total Views:  368
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Is there anything wrong with my create table statement?

Hi , I have an sql query which mysql is giving error: Code: CREATE TABLE '42011-10-04 08:10:37'(`id` INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(`id`),`url_string` VARCHAR(100),`loadtime` float(10),`comment` VARCHAR(160),`urlflag` INT NOT NULL DEFAULT '0') Is there anything wrong with query, its giving the error as below: Code: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' 42011-10-04 08:10:37'(`id` INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(`id`),`url_' at line 1 Please help.
VIEWS ON THIS POST

283

Posted on:

Thursday 25th October 2012
View Replies!

Is there any problem with having an idex of every column ?

, i have a database with many thousands of rows and 50 odd columns. Its quite conceivable that my users may wish to search on ANY of those 50 columns, so to increase search speeds I was wondering what cost to my system (be it in performance or resources) would there be if i created an index on each of the 50 columns. I assume there is a downside ps - I have plenty of disk/memory and cpu (some would say my box was massively overspecced, but im not telling anyone about it )
VIEWS ON THIS POST

184

Posted on:

Thursday 25th October 2012
View Replies!

Is there a way to generate sql insert statement from directory of files?

I'm trying to make something like this: Code: (NULL, 'SeaBattle', 'gameboy', 'Gameboy', '22', 'TheSmartass', '37', 'Unsorted', '13', '1', '0', '480', '432', '', '2009-03-29 19:30:38', 'English', '', '', 'SeaBattle'), all the file names are like SeaBattle.zip or something.... They are just sitting in a directory. Can something generate this statement for me inserting all the file names I mean I am ok with doing mass find and replace to get rid of .zip and if i must I will put space into sql statement... But so far the only way I have found to do this is with "send to clipboard as name" and then tons of mass find and replace as well as manual data entry on each line. Seeing how I was stupid and wasn't remote backing up my database since getting new hard drive installed a month ago I now have countless hours of data entry ahead unless there is solution.
VIEWS ON THIS POST

208

Posted on:

Thursday 25th October 2012
View Replies!

Wanting to learn php and mysql, is there one I should learn before the other?

As you are likely to come across sql queries being used in php, it would be best to have a read of an sql book first. But if you were not going to use a database, it wouldn't matter if you didn't know some sql. Most php tutorials however use very simple sql in their examples. Rudy is an SQL god, and the free intro chapters you can get at from his book look okay for beginners to start with. Haven't seen the later chapters though. But that might change, of course.
VIEWS ON THIS POST

279

Posted on:

Thursday 25th October 2012
View Replies!

is there any advantage to this

I have a table containing about 150,000 rows, and I most often order the rows for display using a DATETIME column. I notice that if I drop the order by altogether, the query executes about 15x faster than with the order by. Typically ordering these results using an INT field takes about 0.01 seconds vs 0.5 seconds using a DATETIME or VARCHAR column. I was wondering if there would be any advantage to running a cron job every 10 minutes and adding a numerical rank to each row according to the date, then ordering by the integer rank column instead - would this make any difference The only keys on the table are id (primary), and FULLTEXT title, description. I previously had index keys on the date fields however found this was even slower. Here is a typical query: Code MySQL: SELECT a.id, a.ends, a.category, a.title, a.url_title, a.pic_id, a.start_bid, a.cur_bid, a.buy_now, a.num_bids, a.has_video, u.nick, f.filepath, f.filepath_thumb, f.filename, f.description AS pic_description, weight_high.value AS weight_value, body_tone_n_high.value AS body_tone_n_value FROM auctions a INNER JOIN users u ON a.seller = u.id LEFT JOIN files f ON a.pic_id = f.id LEFT JOIN variant_values weight_low ON weight_low.item_id = a.id AND weight_low.variant_id = 1 LEFT JOIN variant_values weight_high ON weight_high.item_id = a.id AND weight_high.variant_id = 1 LEFT JOIN variant_values body_tone_n_low ON body_tone_n_low.item_id = a.id AND body_tone_n_low.variant_id = 12 LEFT JOIN variant_values body_tone_n_high ON body_tone_n_high.item_id = a.id AND body_tone_n_high.variant_id = 12 WHERE a.suspended = '0' AND a.category IN(249, 326) AND a.ends > '2009-02-15 03:03:08' AND a.starts
VIEWS ON THIS POST

140

Posted on:

Thursday 25th October 2012
View Replies!

Is there a program for continously seeing what MySQL processes are running

Greetings, Is there a program like Linux Top that allows one to see on a continuous, that is self refreshing basis, the MySQL Processes that are running and how much CPU each process is using and for how long it is running and thus inform one as to the MySQL processes that are taking too long so that one can kill them Just to be clear a command like this: mysqladmin -u root -h localhost -p processlist will not do the job, since the above command gives you a snap shot of when it was issued and does not run on a continuous basis as for example Linux Top does. To be even further clear, I am looking for a PC based desktop software that like Putty has a MySQL equivalent Top function that provides to one on a continuous basis information about what MySQL processes are running on ones server.
VIEWS ON THIS POST

188

Posted on:

Thursday 25th October 2012
View Replies!

Trying to figure out how to secure the db better. Starting with:Limit user access.

I created a user and login under security for the database. In the web.cfg I have: in the codebehind file: string connectionString = ConfigurationManager.ConnectionStrings["ReadOnlyConnectionString"].ConnectionString; SqlConnection conn = new SqlConnection(connectionString); errors at the line: conn.Open(); The Error: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
VIEWS ON THIS POST

126

Posted on:

Thursday 25th October 2012
View Replies!

SQL error telling me there's an unknown column that IS in the table.

this has me very confused... The error says this: Unknown column 'hotels.hotel_id' in 'on clause' Here's the query: Code: SELECT hotels.hotel_name,hotels.hotel_idnum,hotels.hotel_id FROM hotels,event_hotels LEFT JOIN hotel_reg ON hotel_reg.hotel_fk=hotels.hotel_id AND er_fk=4751 WHERE hotel_reg.hotel_fk IS NULL AND eh_available!='Hide' AND event_hotels.hotel_fk=hotels.hotel_id AND event_hotels.event_fk=18 ORDER BY hotels.hotel_name ASC; My DB table: hotels Field Type Null Default hotel_id mediumint(8) No hotel_idnum varchar(200) Yes NULL hotel_name varchar(200) Yes NULL hotel_add1 varchar(200) Yes NULL hotel_add2 varchar(200) Yes NULL hotel_city varchar(150) Yes NULL hotel_state varchar(50) Yes NULL hotel_zip varchar(10) Yes NULL hotel_cn varchar(200) Yes NULL hotel_pos varchar(255) Yes NULL hotel_phone varchar(25) Yes NULL hotel_fax varchar(25) Yes NULL hotel_fd varchar(25) Yes NULL hotel_url varchar(255) Yes NULL hotel_map varchar(255) Yes NULL hotel_login varchar(100) Yes NULL hotel_pw tinyblob Yes NULL hotel_email varchar(200) Yes NULL hotel_desc text Yes NULL hotel_rating varchar(10) Yes NULL hotel_checkin varchar(100) Yes NULL hotel_checkout varchar(100) Yes NULL hotel_am1 varchar(50) Yes NULL hotel_am2 varchar(50) Yes NULL hotel_am3 varchar(50) Yes NULL hotel_am4 varchar(50) Yes NULL hotel_am5 varchar(50) Yes NULL hotel_am6 varchar(50) Yes NULL hotel_am7 varchar(50) Yes NULL hotel_am8 varchar(50) Yes NULL hotel_am9 varchar(50) Yes NULL hotel_am10 varchar(50) Yes NULL hotel_am11 varchar(50) Yes NULL hotel_am12 varchar(50) Yes NULL hotel_am13 varchar(50) Yes NULL hotel_am14 varchar(50) Yes NULL hotel_am15 varchar(50) Yes NULL hotel_am16 varchar(50) Yes NULL hotel_am17 varchar(50) Yes NULL hotel_am18 varchar(50) Yes NULL hotel_am19 varchar(50) Yes NULL hotel_am20 varchar(50) Yes NULL hotel_am21 varchar(50) Yes NULL hotel_am22 varchar(50) Yes NULL hotel_am23 varchar(50) Yes NULL hotel_am24 varchar(50) Yes NULL hotel_am25 varchar(50) Yes NULL hotel_am26 varchar(50) Yes NULL hotel_am27 varchar(50) Yes NULL hotel_am28 varchar(50) Yes NULL hotel_am29 varchar(50) Yes NULL hotel_am30 varchar(50) Yes NULL hotel_am31 varchar(50) Yes NULL hotel_fee8 varchar(20) Yes NULL hotel_fee11 varchar(20) Yes NULL hotel_fee15 varchar(20) Yes NULL hotel_fee17 varchar(20) Yes NULL hotel_fee25 varchar(20) Yes NULL hotel_fee29 varchar(20) Yes NULL hotel_fee30 varchar(20) Yes NULL hotel_fee31 varchar(20) Yes NULL hotel_amenities text Yes NULL hotel_attractions text Yes NULL hotel_entrance varchar(50) Yes NULL hotel_totalrooms varchar(4) Yes NULL hotel_pets varchar(10) Yes NULL hotel_cn2 varchar(255) Yes NULL hotel_pos2 varchar(255) Yes NULL hotel_email2 varchar(55) Yes NULL hotel_phone2 varchar(25) Yes NULL hotel_cn3 varchar(255) Yes NULL hotel_pos3 varchar(255) Yes NULL hotel_email3 varchar(55) Yes NULL hotel_phone3 varchar(25) Yes NULL hotel_cn4 varchar(255) Yes NULL hotel_pos4 varchar(255) Yes NULL hotel_email4 varchar(55) Yes NULL hotel_phone4 varchar(25) Yes NULL hotel_pic1 varchar(150) Yes NULL hotel_pic2 varchar(150) Yes NULL hotel_pic3 varchar(150) Yes NULL hotel_pic4 varchar(150) Yes NULL hotel_pic5 varchar(150) Yes NULL wtf
VIEWS ON THIS POST

134

Posted on:

Thursday 25th October 2012
View Replies!

Is there a better way to do this?

Is there a better way to do this, maybe with just a mysql query It checks if a row exists, if it does it adds +1 to a field, otherwise it creates a new row. PHP Code: $sql=mysql_query("SELECT*FROMwhateverWHEREusername='$username'"); $result=mysql_num_rows($sql); if($result=="0"){ \tmysql_query("INSERTINTOwhatever(username,views)VALUES('$username','1')")ordie(mysql_error()); } else{ \t\t \tmysql_query("UPDATEwhateverSETviews=views+1\tWHEREusername='$username')")ordie(mysql_error()); \t\t }
VIEWS ON THIS POST

92

Posted on:

Thursday 25th October 2012
View Replies!

Outer join where there is no relationship?

Is an Outer join where there is no relationship between the two tables being joined possible I am using Access and have 3 tables. I have a query as follows :- SELECT F1, F2 FROM Tbl_A WHERE Tbl_A.ID = (SELECT Tbl_B.TblA_ID FROM Tbl_B WHERE Tbl_B.F1 = 'xxxxxx') I want to join on another table, Tbl_C and get the value of a field, Tbl_C.F1, however there is no relationship between Tbl_C and any other table, and I want to get the Tbl_C.F1 value even if there is no value returned from the other part of the query. So I basically want one row that consists of Tbl_A.F1,Tbl_A.F2,Tbl_C.F1 Is what I want to achieve possible
VIEWS ON THIS POST

133

Posted on:

Thursday 25th October 2012
View Replies!

Is there a difference between these queries?

Code: SELECT * FROM table1 WHERE col1 = $value Code: SELECT col1, col2, col3, col4, ... FROM table1 WHERE col1 = $value The 2nd query basically just lists all the columns in the table, same as the first. Is there any difference in running either query Any performance gains from using the 2nd query I'm just trying to clean up some old code. Most of the queries are calling to display all the data in the table anyways, hense using the *.
VIEWS ON THIS POST

112

Posted on:

Thursday 25th October 2012
View Replies!

Updating DB prices between 2 DB...

i have an Xcart set up and i have a program that uploads my Access(POS file) file to Mysql. Now My access files has all my prices and inventory up to date.So it keeps the web site up to date.. now my issue is if the product is not in my Db(Access file)it put the prices to 0.00$... So my question is how can i update the prices without affecting the products not in my file\t this is my script PHP Code: ///1Productcodestomatch $sql2="UPDATETbItemInventoryp LEFTJOINxcart_productspp ONp.Name=pp.productcode SETp.XcartID=pp.productid WHEREp.Name=\tpp.productcode"; $retval=mysql_query($sql2); if(!$retval) { \tdie('Couldnotupdatedata:'.mysql_error()); } ///2\tQTY $sql3="UPDATExcart_productsp LEFTJOINTbItemInventorypp ONp.productid=pp.XcartID SETp.avail=pp.QuantityOnHand WHEREp.productid=\tpp.XcartID"; $retval1=mysql_query($sql3); if(!$retval1) { \tdie('Couldnotupdatedata:'.mysql_error()); } ///5Pricingpermembership $sql4="UPDATExcart_pricingp LEFTJOINTbItemInventorypp ONp.productid\t=pp.XcartID SETp.price=pp.SalesPrice WHEREp.membershipid=\t1"; $retval4=mysql_query($sql4); if(!$retval4) { \tdie('Couldnotupdatedata:'.mysql_error()); } ////6 $sql5="UPDATExcart_pricingp LEFTJOINTbItemInventorypp ONp.productid\t=pp.XcartID SETp.price=pp.SalesPrice2 WHEREp.membershipid=\t5"; $retval5=mysql_query($sql5); if(!$retval5) { \tdie('Couldnotupdatedata:'.mysql_error()); } echo"pricesUpdated\tsuccessfully"; echo"";
VIEWS ON THIS POST

116

Posted on:

Monday 29th October 2012
View Replies!

Combining results of two GROUP BY queries

I have a table named deposit, with columns deposit_id, client_id, client_name, deposit_amount and I have a table named appointment which has columns appointment_id, client_id, client_name, appointment_date. Clients pay a $100 deposit, then gets docked $10 for each session they come to. I have created the following query to count the total deposits paid by each client: SELECT d.client_id, d.client_name, sum(d.deposit_amount) AS 'total paid' FROM deposit AS d GROUP BY d.client_id I can also create a query to show the total number of appointments for each client: SELECT a.client_id, count(a.appointment_id) AS 'number of appointments to date' FROM appointment AS a GROUP BY a.client_id What I would like to do is combine these so that I know how many sessions each client has remaining from their deposit. I think I would need a query that returned the following info: client_id client_name ((sum(d.deposit_amount)*10 count(a.appointment_id)) But I do not know the proper syntax for making this query. I'd really appreciate any help anyone could offer, I'm kind of stumped.
VIEWS ON THIS POST

109

Posted on:

Monday 29th October 2012
View Replies!

Problem With MySQL Privileges

i am really fustrated over that issue..Here is the deal .. I log in as root at mysql command prompt ( for password i just hit enter ) , then i create USER A .. Then i create DATABASE A , Then i GRANT ALL PRIVILEGES ON DATABASE A TO USER A ... However when i login to phpmyadmin as USER A it says that i have no database privileges ... There is no way i can fix that i m trying 2 days now ... Can someone plz help me out Plz be detailed as possible. Thank you for your time BELOW IS A PHP FORUM ERROR WHEN TRYING TO INSTALL A fatal and unrecoverable database error has occurred. This may be because the specified user does not have appropriate permissions to CREATE TABLES or INSERT data, etc. Further information may be given below. Please contact your hosting provider in the first instance or the support forums of phpBB for further assistance. install_install.php [ 1193 ] SQL : CREATE TABLE phpbb_attachments ( attach_id mediumint(8) UNSIGNED NOT NULL auto_increment, post_msg_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, topic_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, in_message tinyint(1) UNSIGNED DEFAULT '0' NOT NULL, poster_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, is_orphan tinyint(1) UNSIGNED DEFAULT '1' NOT NULL, physical_filename varchar(255) DEFAULT '' NOT NULL, real_filename varchar(255) DEFAULT '' NOT NULL, download_count mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, attach_comment text NOT NULL, extension varchar(100) DEFAULT '' NOT NULL, mimetype varchar(100) DEFAULT '' NOT NULL, filesize int(20) UNSIGNED DEFAULT '0' NOT NULL, filetime int(11) UNSIGNED DEFAULT '0' NOT NULL, thumbnail tinyint(1) UNSIGNED DEFAULT '0' NOT NULL, PRIMARY KEY (attach_id), KEY filetime (filetime), KEY post_msg_id (post_msg_id), KEY topic_id (topic_id), KEY poster_id (poster_id), KEY is_orphan (is_orphan) ) CHARACTER SET `utf8` COLLATE `utf8_bin` CREATE command denied to user 'administrator'@'localhost' for table 'phpbb_attachments'
VIEWS ON THIS POST

76

Posted on:

Monday 29th October 2012
View Replies!

Wildly varying query execution time

We are currently running MySQL 4.3 (with a plan to upgrade to 5.2 fairly shortly) but we are experiencing queries taking wildly different times to execute. For example: Code: SELECT COUNT(*) AS numPosts FROM forumPosts WHERE isDeleted = '0' AND ID_POSTER = 14018; currently takes a fairly sensible 0.002 seconds to execute but at random times during the day it can take up to 48 seconds as shown in the slow query log. When this happens it seems that nearly (if not) all queries are being added to the slow query log taking between 4 and 89 seconds. Suddenly things will sort themselves out and all will be well for a little while. The MySQL Server is running on RHEL 4.3 in case that makes a difference. The database is being accessed through PHP and has about 60 - 70 active connections at peak times. This may or may not happen at peak times. I am completely stuck so does anybody know what could be causing this
VIEWS ON THIS POST

129

Posted on:

Monday 29th October 2012
View Replies!

Any one gone through this .?

, I need to add a custom paramenter to mysql i.e. --max_connect_errors=999999999. The problem is , MySql starts automatically ( /etc/init.d/mysql) . , so not able to add the parameter. I tried to stop mysql through rc.local & tried to run PHP Code: /usr/sbin/mysqld--basedir=/usr\t--datadir=/var/lib/my--max_connect_errors=999999999 didnt helped me. How to add paramenter in init. script , please correct my assumptions. I assume that this shell script reads the parameters from my.cnf file. if so, i would add the parameter in the file.
VIEWS ON THIS POST

160

Posted on:

Monday 29th October 2012
View Replies!

Date Query Question

Hi Tried doing a google search regarding this though I found no relevant topics. Say you have a table: - id - itemid - todaydate (DATETIME) In a particular day, an item can be used multiple times (determined by a row insert). Is there a way to query this: "get count of an item used for March 2008." I want to use a WHERE clause against todaydate so it gets March rows only.. My example probably does not match want I am actually doing but I would like to know how to use the WHERE clause as above.
VIEWS ON THIS POST

46

Posted on:

Monday 29th October 2012
View Replies!

MYSQL ranking problem

I want to get a query like this - Given a list of names sorted by the letter A, tell me the numeric position of the name "jazajay". (Cheers Requinix ) What I have got is this, so almost there - PHP Code: SELECTNameFROMnamesWHERELEFT(Name,1)='A'ORDERBYName I then run a php for loop to loop through the results and find the right name at the right position. However I know MYSQL can preform this a bit quicker than PHP but I'm not sure how to proceed. Any takers
VIEWS ON THIS POST

78

Posted on:

Monday 29th October 2012
View Replies!

How to find the SQL..

Hard to explain. I'm making a register/login script. (I have one actually) and I was wondering, I use my current script for gameyin.com. I was thinking of just copying the structure of the table over to my new website name so it's just easier. The structure is like this Table structure for table Users Field Type Null Default id int(11) Yes NULL Username text Yes Password varchar(32) Yes Name text Yes Email text Yes Date text Yes IP text Yes Actkey varchar(40) Yes Activated int(1) Yes 0 I exported to Word so you could see. Anyone make the SQL code for me I don't know where to start. I forget how I originally made it.
VIEWS ON THIS POST

100

Posted on:

Monday 29th October 2012
View Replies!

Users dbo. and viss.

Please read this topic about database, need some answers please! http://forums.devshed.com/asp-progr...iss-431459.html
VIEWS ON THIS POST

245

Posted on:

Monday 29th October 2012
View Replies!

I Need help to limit # of music files downloaded per user on my website.

, im trying to make it to where a person must subscribe to my website as a user in order to download a file. I want them to be able to only download 3 of any files from a list on my website and once they have downloaded 3 files, they can not download any more untill the next month. I would also like to charge for each user a small amount for this service; since they will be being offered access to other features that do not pertain to this. But yes, and i would prefer that they are able to preview the song first, before they actually have to download the file. Please help and
VIEWS ON THIS POST

237

Posted on:

Monday 29th October 2012
View Replies!

Page 2 - Multiple records inserted, why?

Thank you, you are right. I need to lock table, delete/insert, unlock. And just in case create index out of three fields to prevent duplication . Double precausion The only problem, how many seconds this lock would take. If in one place they delete-insert 100 records, in the other 100 offices they do the same at the same time, in other places they run reports (does lock locks table for search also), would it create a traffic jam or a gridlock ... or it's just few secs.. Victoria
VIEWS ON THIS POST

125

Posted on:

Monday 29th October 2012
View Replies!

Load data - my data file format not correct ?

I have a data file im trying to load a table with some sample data. its been a while since ive done this, as i can now remember having trouble with this in the past. Im trying to load into a table called job_list with these columns id (identity column) title description date_posted open_status here is the contents of my simple data file Quote: event coordinator 1|manages events and staff|2006-02-04|1| food server 1|waits on guests.|2006-01-04|1| bar tender|serves drinks and arranges for supplies|2006-01-04|1| here is my load statement Code: LOAD DATA INFILE 'job_list_load2.txt' INTO TABLE job_list FIELDS TERMINATED BY '|' After running the above statement, i get some warnings about "out of range values adjusted for column..." The data that gets inserted in the table seems to have skipped column 1, having put the description data in the title fields.
VIEWS ON THIS POST

73

Posted on:

Monday 29th October 2012
View Replies!

MySQL Start Errors

, I installed WebMin to one of my computers. Once it was going I clicked an option there to import the Linux users into WebMin and problems started. I eventually got WebMin going again, but during the process I was prompted to change the Linux root password, so did. Now the DB is locked, looking to the old root password, I assume. Anyway I tried a re-install which failed and tried a password reset, but it will not take as none of the mysql commands take. I'm assuming the only alternative at this time is to remove mysql and start install over fresh. Am I right
VIEWS ON THIS POST

50

Posted on:

Monday 29th October 2012
View Replies!

Database for an online database. How?

, I am trying to develop a very simple online database for my visitors. They will be able to create "tables" with X amount of fields and records. Currently, I am already at a road block and can not think of a logical and efficient enough way to design my MySQL database in order to support this. There are only two possibilities that I can see: OPTION 1: Design a bunch of tables and create relationships using ID's. Something like: ----------------- Database ----------------- databaseID name ----------------- column ----------------- columnID name ----------------- row ----------------- rowID createdOn ----------------- record ----------------- recordID columnID rowID OPTION 2: I would imagine no user would ever create a "table" with over 100 columns, as a result, I can create one monster MySQL table with 100 of these columns, for example: ----------------- Database ----------------- databaseID name ----------------- columns ----------------- columnID databaseID column1 ... column100 Both alternatives have advantages and disadvantages. One particular one that sticks out is hard drive space using option 2 (if the user only need one column, but we're still giving him/her 99 anyway; it's a waste). For option 1, there are so many tables referencing each other that I feel like it will be a lot slower because I'd have to constantly make joins anyway. Is there any other solution to this
VIEWS ON THIS POST

88

Posted on:

Monday 29th October 2012
View Replies!

Query taking 10 minutes - index wrong? all info: desc, explain, etc.

all! I inherit a mysql system and i've been told that the indexes on the tables are all wrong and misplaced. Here are the explain and desc of querys and tables. mysql> desc Incoming; +------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------------+------+-----+---------+----------------+ | idIncoming | int(9) | NO | PRI | NULL | auto_increment | | idOperador | int(10) unsigned | NO | | 0 | | | lenght | int(10) unsigned | NO | | 0 | | | msisdn | bigint(20) | NO | | 0 | | | deliveryDate | datetime | YES | MUL | NULL | | | receiveDate | datetime | YES | MUL | NULL | | | laNumber | bigint(20) | YES | | NULL | | | alias | varchar(50) | YES | MUL | NULL | | | messageReceived | varchar(255) | YES | | NULL | | | idSource | int(10) unsigned | YES | | NULL | | | lida | tinyint(1) | YES | | 0 | | | deleted | tinyint(1) | YES | | 0 | | | audioFile | varchar(40) | YES | | NULL | | | ConnectRadius | varchar(80) | YES | | NULL | | | DisconnectRadius | varchar(80) | YES | | NULL | | | callDuration | bigint(10) | YES | | NULL | | +------------------+------------------+------+-----+---------+----------------+ 16 rows in set (0.04 sec) So this is the table from where i've got to retrieve info. mysql> EXPLAIN SELECT msisdn, idIncoming, receiveDate From Incoming where receiveDate > '2007-12-20 00:00:00' and receiveDate '2007-12-20 00:00:00' and receiveDate
VIEWS ON THIS POST

100

Posted on:

Monday 29th October 2012
View Replies!

AND and ORs - how far?

how 'smart' is mysql when it comes to the following part of a query: ... where condition X and condition Y ... if the query doesn't meet condition X, will it event try condition Y , Yes you are right ish, I was thinking along the lines of the original question having the first condition false.
VIEWS ON THIS POST

42

Posted on:

Monday 29th October 2012
View Replies!

Page 2 - run different versions of Invision power board (was "Head banging in a bad way!")

Quote: Originally Posted by pabloj You have taken me completely wrong, I just pointed out that: 1. The subject you provided is not descriptive, and this is not only my opinion as you can see. 2. Actually I also gave you an idea of a descriptive subject, even if r937 chose another one. 3. Actually I also gave you a possible solution about your ODBC problem don't actually know if you checked that out. 4. The infos about your environment are useful, but unrelated to the MyODBC problem you had, or so I think. 5. If you want to close this thread, close it clicking on the corresponding button. And finally, IMHO you should try to hear others and not just argue with them, it is really pointless. 1. I took note of the possible solution. but again been there and for some reason beyond my present understanding it is creating an issue that prevents me from going forward with my goal as far as setting up and operating IPB on my computer as a test forum/server. 2. pertinent information I gave was to help you to try and help me in this situation. 3. It is not my choice as I stated to argue or cause hate and discontent, but I plainly tried to close the objective argument and tried to get on with the remedies of the issue at hand. But dont worry about it I will help others where I can but I will never ask for help again eiter. I believe in helping people out of their problems not creating petty arguements with them. And finally IMHO people need to read and try to understand anothers frustrations on a matter before calling another stupid over a descriptive title. Quote: even if r937 chose another one. Don't know anything about this person... Have a Great New Year! And for those others
VIEWS ON THIS POST

78

Posted on:

Monday 29th October 2012
View Replies!

SQL Connection using web.config

hi... the attach img is my error.. and following is my code: Code: Dim connection As SqlConnection Dim strCon As String Dim sqlcmd As SqlCommand Dim builder As New StringBuilder builder.Append("INSERT INTO Profile ") builder.Append("(") builder.Append("name,") builder.Append("userid,") builder.Append("password,") builder.Append("gender,") builder.Append("email,") builder.Append("birthDate,") builder.Append("contact") builder.Append(") ") builder.Append("VALUES ") builder.Append("(") builder.Append("'" & name.Text & "', ") builder.Append("'" & username.Text & "',") builder.Append(("'" & pass1.Text & "', ")) builder.Append(("'" & gender.Text & "', ")) builder.Append(("'" & email.Text & "', ")) builder.Append(("'" & birth.Text & "', ")) builder.Append(("'" & contact.Text & "'")) builder.Append(") ") strCon = ConfigurationManager.AppSettings("ConnectionString") connection = New SqlConnection(strCon) sqlcmd = New SqlCommand(builder.ToString, connection) connection.Open() sqlcmd.ExecuteNonQuery() i m trying to get the pre defined connection string that is in my web.config file of my website..
VIEWS ON THIS POST

99

Posted on:

Saturday 10th November 2012
View Replies!

Pulling Results from a check box using sql

OK, bear with me. I am a total novice at this stuff (access, SQL, vb). I am working on creating a form that will allow me to query our project database based on several varying criteria. I get the idea of creating a query on the fly, and I can get this to work for the data we have that is in combo boxes. What I can't get to work is the data that we have in check boxes (yes/no data type). This is the start of my routine Code: Private Sub cmdOK_Click() Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim strSQL As String Set db = CurrentDb Set qdf = db.QueryDefs("qryTest1") strSQL = "SELECT [Mechanical Features].* " & _ "FROM [Mechanical Features] " & _ "WHERE [Mechanical Features].[Fastener Type]='" & Me.cboFastType.Value & "'" & _ "AND [Mechanical Features].[MW Design]='" & Me.cboMWDesign.Value & "'" & _ "AND [Mechanical Features].[Tile Deck]='" & Me.chkTileDeck.Value & "'" & _ "AND [Mechanical Features].[Foldover weirs]='" & Me.chkFOWeir.Value & "'" & _ "ORDER BY [Mechanical features].[Fastener Type];" Debug.Print strSQL End Sub When I pop the SQL string into a query to test it, I get a data type mismatch in criteria expression error. I've narrowed it down to the check boxes (which are the tile deck and foldover weirs fields). When I check the SQL string after running it with criteria, I get this: Code: SELECT [Mechanical Features].* FROM [Mechanical Features] WHERE [Mechanical Features].[Fastener Type]='UNC' And [Mechanical Features].[MW Design]='Oval' And [Mechanical Features].[Tile Deck]='-1' And [Mechanical Features].[Foldover Weirs]='0' ORDER BY [Mechanical features].[Fastener Type]; which looks to be correct to me (on the form I had selected yes for Tile Deck and no for Foldovers). But it won't let me run the query. Any ideas what I'm doing wrong Remember, I'm a total noob when it comes to this stuff, so be gentle!
VIEWS ON THIS POST

78

Posted on:

Saturday 10th November 2012
View Replies!

command text was not set for the command object

Hi all.. i know a very limited bit of ASP however i am currently making a BASIC forum.. No fancy stuff in it. I get this error when i try and Insert some infomation into the database.. "command text was not set for the command object" Here is my section of code that is going wrong... If Request("BUTTON")="Post Text" Then '****************** connecting to the database ************************************ Set oConn = Server.CreateObject("ADODB.Connection") MdbFilePath = Server.MapPath("../db/databasename.mdb") oConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";" '************************************************* ********************************* '****************** Inserting the text from the form into the database ************ Session("email")="my e-mail address" 'this is only here as temp.. '************************************************* ******************************************** If Request.QueryString("type")="reply" and Request.QueryString("mainTopic")="general" Then sSQL = "INSERT INTO data_general (id, author, subject, message, email) VALUES (" & Request.QueryString("id") & ", '" & Session("user") & "', '" & Request("subject") & "', '" & replace(replace(Request("message"),vbcr,""),vb lf,"") & "', '" & Session("email") & "')" ElseIf Request.QueryString("type")="new" and Request.QueryString("mainTopic")="recruit" Then sSQL="SELECT MAX(id) AS max_id FROM data" set RS = oConn.execute(sSQL) 'executing the sSQL stamement var_id = RS("max_id")+1 sSQL = "INSERT INTO data_recruit (id, main, author, subject, message, email) VALUES (" & var_id & ", '" & "yes" & "', '" & Session("user") & "', '" & Request("subject") & "', '" & replace(replace(Request("message"),vbcr,""),vb lf,"") & "', '" & Session("email") & "')" End If '************************************************* ******************************************** oConn.Execute sSQL 'This is the line i get an error on.. '************************************************* ********************************* Please help me...
VIEWS ON THIS POST

124

Posted on:

Saturday 10th November 2012
View Replies!

navigation button in databaseform

Originally posted by : Ingrid (ihei@moerdijk.nl)I have a form to display records from a database. I want to make two navigation buttons to go to the next and the previous record. Can anyone please tell me how to do that
VIEWS ON THIS POST

74

Posted on:

Saturday 10th November 2012
View Replies!

Problems calculating elapsed time

Hi Guys, I work for the IT Department of a Supermarket and have been asked to count the number of complaints which have not been dealt with in the correct time. When an official complaint is received an entry is made in the database which records details such as the name of the complainant, the date the complaint was made etc. Our policy is that all official complaints are dealt with within 40 days, so when the complaint record is created a field called "responsedue" is populated with the date in 40 days time. When the complaint has been dealt with, a field called "actualresponsedate" is populated with the date that the complaint was signed off. I am trying to count the number of complaints that have not been dealt with in the allowed 40 day period. My first stab at the SQL was: Code: Select count(*) Total from TBL_MAIN WHERE COMPLAINT_TYPE1 = 'True' and ACTUALRESPONSEDATE > RESPONSEDUE This is OK in that it counts all of the complaints which were dealt with in more than 40 days, ie. the actual response date was greater than the date that a response was due. My question is (eventually! LOL!!!) how can I adapt the SQL to count those complaints which haven't been dealt with yet - ie. the actualresponsedate doesn't contain a value. I kinda want to say something like: "If the actualresponsedate contains a value then check to see if it is greater than the responsedue date, else, if the actualresponsedate does not contain a value, see if the current date is greater than the response due." Sorry for the long winded post,
VIEWS ON THIS POST

86

Posted on:

Monday 12th November 2012
View Replies!

Remove Hyphens

Is there any way to remove just hyphens or even spaces from data, or better yet remove the hyphens and spaces and put the resulting info in a different column so I could see the before and after Examples: Take this number '-1C3Z-7563-EARM' and make it into '1C3Z7563EARM' Take this number '1F2Z-16045A36-CAA' and make it into '1F2Z16045A36CAA'
VIEWS ON THIS POST

64

Posted on:

Monday 12th November 2012
View Replies!

Converting Data type

I'm using SQL Server 2000. How can I convert an existing column data type from varchar to int, using query The current table consists of data(numbers). So i guess by converting the datatype to int, the data should remain intact right
VIEWS ON THIS POST

43

Posted on:

Tuesday 20th November 2012
View Replies!

distinct 1st field only

I am trying to select rows where only the 1st field is distinct, rather than the entire row being distinct. Any one row with an unique first field will do. I have tried many variations on the following code, but none work: SELECT DISTINCT SUPMAP.SuperMapID, MAP.MapID, MAP.SurveyID, MAP.VariableName FROM SUPMAP INNER JOIN MAP ON SUPMAP.MapID = MAP.MapID WHERE SUPMAP.SuperMapID = ANY(SELECT DISTINCT SUPMAP.SuperMapID FROM SUPMAP); produce something like this: CONBAD CONBAD 300 v0729 CONBAD CONBAD 308 v0730 VOTED CCVOTED 300 CCVOTEDA VOTED CCVOTED 302 CCVOTED etc whereas I want to select only one row with CONBAD, one with VOTED, etc., and it does not matter which one. Any suggestions would be much appreciated. Richard
VIEWS ON THIS POST

100

Posted on:

Tuesday 20th November 2012
View Replies!

SQL-7, varchar, size 8000

Originally posted by : Gregory (gmoldavsky@dsg-us.com)In SQL_7 I create in the table field: Datatype: varchar Length: 8000I trying to put there string (4500 characters) but this field accept only 950 characters:Sample of my string :1234567890 1234567890 1234567890 1234567890 22345678902234567890 1234567890 1234567890 3234567890 12345678903234567890 1234567890 4234567890 1234567890 12345678904234567890 5234567890 1234567890 1234567890 12345678905234567890 1234567890 1234567890 1234567890 72345678906234567890 1234567890 1234567890 8234567890 12345678907234567890 1234567890 9234567890 1234567890 12345678908234567890 0234567890 1234567890 1234567890 12345678909234567890 1234567890 1234567890 1234567890 22345678901034567890 1234567890 1234567890 3234567890 12345678901134567890 1234567890 4234567890 1234567890 12345678901234567890 5234567890 1234567890 1234567890 12345678901334567890 1234567890 1234567890 1234567890 72345678901434567890 1234567890 1234567890 8234567890 12345678901534567890 1234567890 9234567890 1234567890 12345678901634567890 0234567890 1234567890 1234567890 12345678901734567890 1234567890 1234567890 1234567890 12345678901834567890 1234567890 1234567890 1234567890 12345678901934567890 123Help!Thank you,Gregorygmoldavsky@dsg-us.com
VIEWS ON THIS POST

99

Posted on:

Tuesday 20th November 2012
View Replies!