I'm trying to create a table in Navicat and immediately add a foreign key relation after that. The syntax however seems to be incorrect... Is this even possible? CREATE TABLE `Bld` ( `id` int(10) NOT NULL PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `Bld` ( CONSTRAINT `fk_Bld_Bld...
ok, say you have two dbs. one you use as a master template which goes through various revisions, let’s say we’re now on revision 3. but the second db is a copy of the template as it was at revision 1. because db 2 is in use, you don’t want to drop any tables, but you do need to update the structure of the entire db to the latest version...
I have this query, which executes in 1 or 2 seconds for a given case: Select Count(*) as qtty From event e Join org o On o.orgID = e.orgID Join venue v On v.venueID = e.venueID Where Match( e.name, e.description ) Against ( $keywords ) And e.site_id = $site_id And e.display <> 0</code> I...
I have a form where the user selects field 1, and then selects field 2, which is populated by their choice on field 1. The problem is that after they are on step 2, field 1 is not showing their choice (even though the selection is still carried in a hidden form field. Im not sure how to do that with the way ive setup the form below. i n...
What is the best way to check if a table exists in MySQL (preferably via PDO in PHP) without throwing an exception. I do not feel like parsing the results of "SHOW TABLES LIKE" et cetera. There must be some sort of boolean query? ...
I want to know that my sql execute count queries in linear time or in log(n) time i think that if query parameter was indexed it can do it by cubing ...
What is the best way to optimize a MYSQL server that has 400-1000 queries across 20 tables running against it from 20-30 users? I have these reports that run against the server and they need anywhere from 400-1000 queries to get all the necessary information. The two issues I am facing are the queries individually are relatively fast bu...
Been looking into using multi-column primary keys and as performance is extremely important with the size of traffic and database I need to know if there is anything to consider before I start throwing out the unique ID method on many of my tables and start using mulit column primary keys. So, what are the performance/optimisation pros/...
I have a mysql table that looks something like this: Row 1: 'visitor_input_id' => int 1 'name' => string 'country' 'value' => string 'Canada' Row 2: 'visitor_input_id' => int 1 'name' => string 'province' 'value' => string 'Alberta' Row 3: 'visitor_input_id' => int 1 'name' => string 'first_name' 'v...
I've not come across an error like this before where Line 0 is referred to. Does it have a specific meaning, or is it simply that PHP was unable to determine the line number before something went wrong? The full warning is: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /xxxxxx/text_editor.php o...
I'm trying to optimize my application by reducing round-trips to my database. As part of that effort, I've been moving some of the tables into memory, storing them as Boost.MultiIndex containers. As a side-effect of this process, I've lost the ability to do wild-card matching on my strings. For example, when the table was stored in My...
Let's say I want to build a gaming website and I have many game sections. They ALL have a lot of data that needs to be stored. Is it better to make one database with a table representing each game or have a database represent each section of the game? I'm pretty much expecting a "depends" kind of answer. ...
Is there a way to grab the columns name of a table in mysql? using php ...
Hi, i have a table with 2 columns tid and nid columns, i need to prepare a report to get common nodes between a tid. i.e table is like (tid, nid) (mysql,2) (mysql,3) (ajax,1) (ajax, 2) now i need to get what what are the common nodes between ajax and mysql which is node2. i am looking for a query or some logic with database query in...
When executing: BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 1; END; DECLARE EXIT HANDLER FOR SQLWARNING BEGIN ROLLBACK; SELECT 1; END; -- delete all users in the main profile table that are in the MaineU18 by email address DELETE FROM ap_form_1 WHERE element_5 ...
I wasn't sure if it recounted the rows, or if after it retrieved the whole result set, it only grabbed the total post query? ...
I'm trying to select all dates that haven't pass the current date. So for instance, I want to display every date after 2009-10-06 and NOT dates prior 2009-10-06. ...
I would like to create a view that has a single column. If my two tables looked like the following: Table 1: ID | Name 1 | Joe Table 2: ID | Address 1 | 123 BlueBerry St. The view would look like: View: newcolumn | Joe 123 Blueberry St. The reason I do this is because I have an autocomplete text box that searchs a single colum...
I am going to be making a small user system but I have questions. If I were to make a registration table (mysql), what is wrong with just storing the password and username into the database without encryption? I am trying to think of how to build the admin portion. Should I just check a column in the database to see if user is admin or...
I was trying to execute this mysql query it is not giving me any results if i set the query_limit to more than 100, can somebody point me what i can do to get this working for fetching more data. $query_limit = 190; $query1 = "SELECT N.nid ,N.tid FROM term_node N JOIN term_data S ON S.tid = N.tid AND S.vid =1"; $query_result = db_quer...