mysql

In MySQL set something similar to a LIMIT based on a percentage

Hi all. I've been using the search function but I think there isn't any related question with the solution for my question (my apologizes if I'm wrong). Let's imagine we have a simple table in MySQL with this very simple schema: (MATERIAL varchar(10) primary key, QUANTITY smallint). And with rows like: (A,10),(B,8),(C,7),(D,4),(E,1),(F,...

Mysql issue with decimal

Hello, I have two fields - amount (decimal (11, 2)) - gift_amount (decimal (11, 2)) When I do an update on either for a value equal to or below 999.99, it saves correctly. However, if I go over that, then it drops the value right back to down 1 - 10. Is this a known issue or am I going wrong using decimal? Heres some PHP code of wh...

Mysql query are case sensitive?

When i set lower_case_table_names = 1 in mysql i know that it converts every table name to lowecase so "myCoolLowerCaseName" becomes "mycoollowercasename". But the question is: queries with camelcase name works ? with a table called mycoollowercasename the query:SELECT * FROM myCoolLowerCaseName works? Thanks ...

Inserting timestamp value in SQL Server

I am trying to copy data from my MYSQL table to SQL Server using PHP. I have a TimeStamp value that needs to be copied. While I am trying to copy the fields, it gave an error that timestamp value cannot be inserted. Is there any way to insert the timestamp value? Is it is not possible, then declaring the column as nvarchar will inser...

php/mysql - PDO prepared insert, does not work, and no error messages.

I really have NO idea of what to do with this now, i have been staring at it for hours, and reqritten it.. i can't get it to work!. require_once("Abstracts/DBManager.php"); require_once("UI/UI.Package.php"); class BlogDBM extends DBManager { private $table = "blog_records"; function saveRecord($title,$url,$desc,$feedId,$pubDat...

Problem with PHP 'PHP_SELF'

I am having a bit of trouble. It does not seem to be a big deal, but I have created a page that the user can edit a MySQL database. Once they click submit it should process the php within the if statement and echo 1 record updated. The problem is that it does not wait to echo the statement. It just seems to ignore the way I wrote my if a...

MySQL - mysqldump --routines to only export 1 stored procedure (by name) and not every routine

So we have a lot of routines that come out from exporting. We often need to get these out in CLI, make changes and bring them back in. Yes some of these are managed by different folks and a better change control is required but for now this is the situation. If I do mysqldump --routines --no-create-info --no-data --no-create-db then g...

MySQL - storing characters in different language or special symbols

I need to create a table in MySQL which stores the different currency symbols of the different countries in the world. These symbols are in different languages and are not getting inserted into the database and it throws errors like incorrect string value xd8\x8b Sample data is: insert into country ( country_name, currency_name, cur...

What is the proper query to get all the children in a tree?

Lets say I have the following MySQL structure: CREATE TABLE `domains` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `domain` CHAR(50) NOT NULL, `parent` INT(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MYISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 insert into `domains`(`id`,`domain`,`parent`) values (1,'.com',0); insert into `d...

PHP/MySQL database connection priority?

Hello, I have a production database where usage statistics reside. This database is responsible for many other things (not just statistics calcs). I use php to periodically roll up different resolutions (day, week, month, year) of interesting statistics in buckets dictated by the resolution. The php application I've written "completes"...

How do I display/compare a dynamic value of a mysql row in a if statement?

I have a checkboxes on my site that when unchecked, update their row in in the db as unchecked, and if checked,update their row in the db as checked. I am creating an ifstatement that will commence with its command if checked, and not if unchecked. I have echoed the variable and it is holding the proper value (checked or unchecked) but n...

Search implementation dilemma: full text vs. plain SQL

I have a MySQL/Rails app that needs search. Here's some info about the data: Users search within their own data only, so searches are narrowed down by user_id to begin with. Each user will have up to about five thousand records (they accumulate over time). I wrote out a typical user's records to a text file. The file size is 2.9 MB. Se...

MySQL database - backup problem

Hi I need to backup MySQL database and then deploy it on another MySQL server. The problem is, I need it backup without data , just script which creates database, tables, procedures, users, resets autoincrements etc. ... I tried MySQL administrator tool (Windows) and UNchecked "complete inserts check box", but it still created it ... T...

Perl DBI execute not maintaining MySQL stored procedure results

I'm having a problem with executing a stored procedure from Perl (using the DBI Module). If I execute a simple SELECT * FROM table there are no problems. The SQL code is: DROP FUNCTION IF EXISTS update_current_stock_price; DELIMITER | CREATE FUNCTION update_current_stock_price (symbolIN VARCHAR(20), nameIN VARCHAR(150), currentP...

Why does this SQL code give error 1066 (Not unique table/alias: 'customer')?

Why does the MySQL query below give error 1066 (Not unique table/alias: 'customer')? SELECT customer.id, customer.firstName, account.id FROM customer, account INNER JOIN customer ON customer.id = account.customerId ORDER BY customer.id ...

Stored Procedure with ALTER TABLE

I have a need to sync auto_increment fields between two tables in different databases on the same MySQL server. The hope was to create a stored procedure where the permissions of the admin would let the web user run ALTER TABLE [db1].[table] AUTO_INCREMENT = [num]; without giving it permissions (That just smells of SQL injection). My pr...

Should I include user_id in multiple tables?

I'm at the planning stages of a multi-user application where each user will only have access their own data. There'll be a few tables that relate to each other, so I could use JOINs to ensure they're accessing only their data, but should I include user_id in each table? Would this be faster? It would certainly make some of the queries ea...

Dealing with huge SQL resultset

I am working with a rather large mysql database (several million rows) with a column storing blob images. The application attempts to grab a subset of the images and runs some processing algorithms on them. The problem I'm running into is that, due to the rather large dataset that I have, the dataset that my query is returning is too l...

MySql - Get row number on select

Can I run a select statement and get the row number if the items are sorted? I have a table like this: mysql> describe orders; +-------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+----...

Will MySQL InnoDB ON UPDATE CASCADE constraints update properly during a MySQL REPLACE command?

If I have table B with foreign key references to table A (set to ON UPDATE CASCADE) and I run a LOAD DATA INFILE file.txt REPLACE INTO TABLE A; command, will the references update properly? Please note that I'm not talking about ON DELETE CASCADE; I know a REPLACE command will delete records in table B if I have that set. ...