mysql

mysql delimiter error

Modifed. DROP FUNCTION IF EXISTS PersonName; DELIMITER |; CREATE FUNCTION PersonName( personID SMALLINT ) RETURNS CHAR(20) BEGIN DECLARE pname CHAR(20) DEFAULT ''; SELECT name INTO pname FROM family WHERE ID=personID; RETURN pname; END; | DELIMITER ; whats wrong with this code? i get following error with it. There seem...

MySQL DATE_ADD - date is wrong?

So I've got a simple query in MySQL that sets a new member's expiration date once they pay their dues: UPDATE members SET joined=CURDATE(), expires=DATE_ADD(CURDATE(), INTERVAL 1 YEAR), active='1' WHERE id=1000 this query has run 200+ times, normally with the correct result - the current date is put in the joined field, and a year fro...

mysql join statement

Hello, I have a category page in which i am displaying all images coming under a particular category...I also have a dropdown using which i can sort the images based on number of stories written for that image... To accomplish this i am using JOIN statement coz media and story are two different tables... media table has mediaid,catid,u...

Boolean Fields broken in Ext or Ext_scaffold?

I'm experimenting with using ext_scaffold to generate UI elements for a Rails-backed web application. However, I'm experiencing issues with the behavior of boolean fields. For instance, when I do ./script/generate ext_scaffold product name:string description:text sku:integer available:boolean It builds the migration properly and gene...

Mysql join on similar columns

I want to join table1 with table2 on column 'Name', but table2.Name has an 'e' in front of all the names (if table1.name=ABC,table2.name=eABC). How am I supposed to use a join for those two? I tried FROM table1 join table2 on 'e'+table1.name = table2.name, but it doesn't work... ...

MySQL, better to insert NULL or empty string?

I have a form on a website which has a lot of different fields. Some of the fields are optional while some are mandatory. In my DB I have a table which holds all these values, is it better practice to insert a NULL value or an empty string into the DB columns where the user didn't put any data? ...

cron jobs or PHP scheduler

Hi, I am using MYSQL as my database and PHP as my programming language.I wanted to run a cron job which would run until the current system date matches the "deadline(date)" column in my database table called "PROJECT".Once the dates are same an update query has to run which would change the status(field of project table) from "open" to ...

Save edited cell to mysql - How can i send an Identifier with the edited cell data?

Hi, i have the problem that i can't send any identifier for the edited content to the edit.php file. it sends automaticaly an id=1 parameter for the first row in the grid for example...but this is not the same value as in mysql table column "id". the correct id is shown in the grid..it says id 3 in the first row, but when i edit data and...

Mysql only imports a single row from CSV

Development machine is a Mac. I'm having some trouble importing more than a single line from a CSV into Mysql. Here is my SQL statement: LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE students FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (pita, dob, name, grd, asst, loc); It runs fine, but only one record is imported. Any idea whe...

SQL statement with several joins

I must be honest and tell you that I am not good at database queries and this question is probably quite simple. I have three tables Post ID entry Category ID name CategoryBinding ID postID categoryID My normal query is to get all posts with the categories it is put into SELECT * FROM `Post` AS `p` LEFT ...

Can I query other databases from within Wordpress using PHP/mySQL?

I have a site that is a mix of Wordpress and other PHP pages. Can I query the other database and have the results show within Wordpress? ...

How do I handle multiple datasources?

I am working on a web application (PHP + Doctrine + MySQL) to sell. The problem is that there is information that the clients will need from my central data source and they will have information that they don't want me to see (financial and such). My Question, what is the best way to get that information to the customer application? My...

can mysql find the year value of a unix timestamp?

im storing the dates that entries were posted on in the db using a standard unix timestamp. is it possible, using only a mysql query (no php logic), to select entries that were posted in a certain year? id like to avoid retrieving ALL entries and then using php to filter on year value. i could store the year in a separate field of cou...

How can I optimize this MySQL query that involves two left joins?

I cannot figure out why my query slows down. What it boils down to are four tables: team, player, equipment, and metadata. Records in player and equipment have a FK to team, making team a parent of player and equipment. And all three of those tables' rows each have a record in metadata which stores things like creation date, creator ...

What would be considered average or high load for a website using mysql on the backend?

I'm roughly 10% into my website project and I'm trying to figure out what sort of load I'm putting on the database. When a user is logged in, I have many functions that trigger every minute, and every time someone visits the page, a number of elements, such as area code lists, states and countries are pulled to build a registration page...

Custom URL rewrite in wordpress

Hello all, I am trying to achieve a custom URL structure with Wordpress. Basically, my site functions as my blog and my portfolio. I want to have an "Articles" section and a "Portfolio" section. I want the articles to display on the Articles page, and the portfolio on the Portfolio page. Should be easy, except on top of that I want cust...

MysqlCommand switching connection unexpectedtly

I have a little problem with my MysqlConnection objets. I have those two connection strings in my app.config : <connectionStrings> <add name="bdpvcLocalhost" connectionString="Persist Security Info=False;server=localhost;Uid=root;Password=***;database=bdpvc" providerName="Mysql.Data.MySqlClient"/> <add name="bdpvcProduction" c...

Convert a MySql database to MS SQL Server 2005 with data?

I'm trying to create a completely new database from an existing MySQL database, bringing over both data and schema, but so far the only way I've been able to do this is to first import the MySQL database into MS Access, and then into SQL Server 2005? Crazy right? Surely, there is a way that doesn't involve a tedious, custom time-consumin...

Multiple counts within a single SQL query

I'm trying to get the count of documents within 4 specific sections using the following code: SELECT category.id , category.title , count(ts1.section_id) AS doc1 , count(ts2.section_id) AS doc2 , count(ts3.section_id) AS doc3 , count(ts4.section_id) AS doc4 FROM category LEFT JOIN category_link_section A...

Importing space-delimited data into MySQL

I have file in the following format: `e00` `e01` `e02` `e03` `e10` `e11` `e12` `e13 Trying to import the data with LOAD DATA INFILE 'file' INTO TABLE 'foo' FIELDS TERMINATED BY ' ' ENCLOSED BY '`' only seems to get the first 3 fields of each line. Is there a way to load the data without altering the file format? ...