I am trying to add a self relation in an existing Innodb table here is table structure Table person person_id int (10) primary key not null auto increment, parent_id int (10) NULL default null, name varchar(30) When I use this command ALTER TABLE `person` ADD FOREIGN KEY ( `parent_id` ) REFERENCES `person` (`person_id`) ...
Hello, How do you sort data which was stored in a mysql database depending on the days of the week in which the data was submited ?? I basically want to create a diary which outputs information in each day of the week depending on what day it was posted by dates so, Mon - Data in order of date Tue - Wed - e.t.c Any code examples and ...
I have two tables, arrc_PurchActivity and arrc_Voucher. The purchase activity table contains multiple records per purchase, tied together by the credit card authorization. I need to return a single row per purchase, plus pull in a field from the voucher table. If I just grab a few fields from the purchase activity table, like this: SEL...
I am having some issue posting data to a specific URL when testing payment transactions on the paypal platform. Although much more technically understood, I chose not to use their API and NVP platform because of the requirement to FIRST create a transaction, then come back and have to create a recurring profile (as opposed to all at once...
I am working with MySQL and using stored procedures. I have a profiling tool that I am using to profile the code that communicates with MySQL through the stored procedures and I was wondering if there was a tool or capability within MySQL client to profile stored procedure executions. What I have in mind is something that's similar to ru...
Basically, I got the same problem as this guy but I also need relevancy: http://stackoverflow.com/questions/1878899/php-smart-search-engine-to-search-mysql-tables-advice But, I can't use Sphinx or Lucene (shared hosting)... I can use LIKE but that has not relevancy so does anyone know a good PHP class that does create relevancy? So I ...
I have an SQL query that uses GROUP_CONCAT to get all people attached to a certain order. Is there a way I can search inside the GROUP_CONCAT field? SELECT orders.orderID, GROUP_CONCAT(contacts.firstName, " ", contacts.lastName) AS attachedContacts FROM (orders) JOIN contacts ON orders.contactID=contacts.contactID GROUP BY orders.orde...
There is a CKEditor field on our website which saves it's HTML to a text field in a MySQL database. What I'm wondering is, if there is a function (regex for example?) that could strip out HTML tags when doing a LIKE match, e.g. so that searching for: like '%this is a test%' would find 'this<strong>is</strong>a test I get the feel...
I am trying to retrieve all records where the start_date is greater than the current timestamp. Any ideas why this might not be working: WHERE (UNIX_TIMESTAMP(event_dates.start_date)) > DATE(NOW()) and this is the example start_date column 2010-10-30 ...
Hello, I am trying to manage the retrieval of a node in a nested set model table, not through the unique ID, but through the name (a string), and other nodes within the tree under different parents may be called the same way. As far as now I used an unique ID to get nodes inside the nested sets: SELECT node.name, node.lft, node.r...
If I write a sql query for mysql and I just specify JOIN (no outer, inner, left, etc), what type of join does this default to? For example: SELECT count(*) FROM Students p JOIN Classes c ON p.studentId = c.studentId ...
I have the following MySql Table: -------------------------------------------- Table 'category' -------------------------------------------- category_id name parent_id 1 animal NULL 2 vegetable NULL 3 mineral NULL 4 dog 1 6 cat 1 7 ...
I am trying to create a stored procedure by using navicat premium. I clicked functions button then choosed stored procedure choice. on the advanced tab it wants me to write begin end part of teh sql code. I wrote this *BEGIN UPDATE TABLE trl_profile SET trl_profile.type = type WHERE trl_profile.profile_id =profile_id; END;* in the sql ...
Hello! My problem is: I had created a new user:GRANT ALL PRIVILAGES ON sampdb.* TO 'monty'@'' IDENTIFIED BY 'pass' This user had appiared in two databases: mysql.user(with no privilages at all) and mysql.db(with nearly all privilages except GRANT one). OK. After that I tried to access the sampdb database by this new user:mysql -u mont...
Hello, I have table with position atribute 'posit' with unknown values (in my example '0') and I want to UPDATE it to 1,2,3, ... BEFORE: _______________ | title | posit | |---------------| | test | 0 | |-------|-------| | test | 0 | |-------|-------| | test | 0 | |-------|-------| | test | 0 | '---------------' ...
This is my code that is meant to connect to a MySQL server and fetch some info: using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; namespace test_MLDropCopy { class Program { static void Main(string[] args) { string connectionString = "server=xxxxx;uid=xxx...
Essentially what I need to do is pull someone's age from the database which is stored as an integer and then perform a check as to whether they are over 18 or not. The simple select of the row returns their age. The goal is to have the Row return either a "yes" or "no" if they are over the age of 18. Normally I'd do this logic when the...
I have the following query in mysql 5.1.41: select distinct table_schema from information_schema.tables where table_schema like '%dog%'; I want to take the output of that command: +-------------------+ | table_schema | +-------------------+ | dog_a | | dog_b | +-------------------+ and then use the da...
I have a voting system with two models: Item(id, name) and Vote(id, item_id, user_id). Here's the code I have so far: class Item < ActiveRecord::Base has_many :votes def self.most_popular items = Item.all #where can I optimize here? items.sort {|x,y| x.votes.length <=> y.votes.length}.first #so I don't need to do anything ...
I have a table with two fields, TAG_ID and TAG_DESC my primary is tag_id. I'm looking for a query that will display each TAG_DESC and the amount of times it occurs next to it. Thanks, ...