mysql

Empty files generated from running `mysqldump` using PHP

I keep getting empty files generated from running $command = 'mysqldump --opt -h localhost -u username -p \'password\' dbname > \'backup 2009-04-15 09-57-13.sql\''; command($command); Anyone know what might be causing this? My password has strange characters in it, but works fine with connecting to the db. I've ran exec($command, $r...

How do I sanitize user input for proper content-encoding before I save it?

I've got an application where users input text into forms. The data is saved into a MySQL database (collation: utf8_general_ci) and then output as XML (encoding: UTF-8). The problem is that people tend to cut and paste their information from other sources, for instance, Microsoft Word documents or PDFs for instance. This input text of...

MySQL Join Where Not Exists

I have a MySQL query that joins two tables Voters Households they join on voters.household_id and household.id Now what i need to do is to modify it where the voter table is joined to a third table called elimination, along voter.id and elimination.voter_id, how ever the catch is that i want to exclude any records in the voter table ...

MySQL and SQLite differences in SQL

I'm writing java application that is using both SQLite and MySQL using JDBC. Are there any differences in SQL for those databases? Can I use same queries for both SQLite and MySQL, or is there any db specific stuff, that doesn't work on the other one? As far I've worked only with MySQL, so I don't really know much about SQLite. ...

multidimensional PHP array or multiple queries to mysql DB

Hi there I'm using the PHPExcel lib which it seems to do great jobs, but in my approach it doesn't seem to be so fast. I need to generate a excel file with allot of sheets, data, formulas and styles(bold, color, border) and it takes me allot of computing resources and time. I think that my approach is not so good. Which has to be the ri...

POSTing an arbitrary number of records from an HTML form

Hi I'm making a web page in which I have a list of products, along with a field next to each product in which a customer is meant to enter the quantity they want to order. The list of products is generated from a database and therefore the number of products isn't known. Is there a way of POSTing the quantity of each product ordered alo...

User defined MySQL function not accessible with PHP PDO connection

I've got a trivial MySQL function: DELIMITER $$ DROP FUNCTION IF EXISTS `mydb`.`CALC` $$ CREATE FUNCTION `mydb`.`CALC_IT`(Flag VARCHAR(1), One FLOAT, Other FLOAT) RETURNS FLOAT BEGIN IF One IS NULL THEN RETURN 0; END IF; IF Other IS NULL THEN RETURN 0; END IF; IF Flag = 'Y' THEN RETURN Other - One; ELSE ...

Hibernate: update to sum from other table

I'm trying to replicate this query from MySQL to HQL: *UPDATE users u, mines m SET u.mana = u.mana + COALESCE((SELECT SUM(m.mana_rate) FROM mines m WHERE m.user_id = u.id), 0)* Simply doing this in HQL: update User u set u.mana = u.mana + (select coalesce(sum(m.manaRate), 0) from Mine m where m.userId = u.id) Gives following error: Exc...

PHP Query Fail

<?php if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0) { $dbhost = 'localhost'; $dbuser = 'zuk1_boo'; $dbpass = 'lols'; $dbname = 'zuk1_boo'; $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql'); $name = $_POST['name']; $iq = $_POST['iq']; $nuname = str_replace(" ", "-", $name); $nuname =...

Cannot insert string into MySQL text column

For some reason, my queries screw up when I write to a column of type "text". Here is an example: Describe messages; Format is: Field Type Null Key Default id int(11) NO PRI NULL auto_increment title varchar(255) YES NULL body text YES NULL to text YES NULL content_type varchar(255) YES NULL is_sms tinyint(1) YES ...

Using Visio to generate MySQL DDL

I have a database model diagram created in MS Visio which I would like to export to DDL file to create a MySQL database. I've already installed the MySQL ODBC driver, which I can successfully use to generate DDL file, but I have some problems anyway. Visio puts quotation marks around the table names which are also reserved words (like u...

Visual Studio and MySQL

I have been using Microsoft Visual Web Developer 2008 Express Edition. Originally, I made a website with a MySQL back-end. Then I discovered the .Net web authentication, and moved to MS Access as I could, with some modification, make the web authentication work (but not all the features). For a future release of my site, I moved to MS...

mysql stored procedure syntax error

Ok, this is only the second stored procedure I've written. I think you'll get the idea, I'm trying to close a credit line, and all invoices, charges, notes, etc with it. But I get a syntax error. The goal is to CALL close_account_proc(398985994) DELIMITER $$ CREATE PROCEDURE `cc`.`close_account_proc`(cid INT) @uid_usr := uid_...

server configuration questions...

Please pardon my non-understanding here. I have a local mysql server and I need to be able to access that data over an encrypted channel from a java web application running on a web host. Can anyone recommend the best way to do this? Thank you! Joshua ...

Conditional prepared statement not switching

I have the following PHP code, which is meant to insert data in the event that the username field is empty for a given username, or update data if a username exists. At the moment, the insert was previously working fine, but it would never switch to the update clause. Now however, the insert clause fails to recognise my test variable, f...

List top 5(most collected) species from given data?

List top 5 species(spID, common_name, number_collected) found at 'Karkato'(this is a location_name) The following tables are given: species(spID, genus, species, common_name) Field_location(locID, location_name, latitude, type) specimen(mID, spID, locID, date) primary keys are bold foreign keys are italics ...

MySQL: How do I find out which tables reference a specific table?

I want to drop a table but it is referenced by one or more other tables. How can I find out which tables are referencing this table without having to look at each of the tables in the database one by one? ...

Finding and displaying duplicates

I have a table. I'd like to search within this table for duplicate titles. Lets say my data is as follows: title1, title2, title3, title1, title4, title2, title9 I'd like for my query to search this table and output only the duplicates. So the output would look like: title1 title1 title2 title2 I know how I can find the duplic...

sql limiting for unique records

I have a table that may have about 100 records or so, with a user name field. There may be many records with the same username. How would I ensure that only one record for each username is returned, regardless of which record it is? e.g. for the following table users username item firstname lastname -------------------------------...

sql - updating without overriding existing information

I have the following conditional code, which will insert a new record if a record with the appropriate username does not exist, and update the record if it does exist. This works fine. However, at the moment, if I insert a new record, and only insert the firstname and lastname, and maybe address details with say phone information being ...