mysql

MYSQL delete from tasks where project.find(project_id) is null

Hi all the title of this question is a very rough version of the query that I'd like to execute. I've deleted project tasks without a user id, and I now want to delete tasks related to the projects that I've just deleted. Of course I probably should have just done it all with one query ... will take any suggestions for that query als...

MySQL not updating single field in database while updating others.

I am using Zend Framework 1.10.8 and MySQL Server 5.1.X. I create my data array to update the record but only one field updates. <?php $where = "`character_id` = '5'"; $healthGained = 5; $data = array(); if ($healthGained > 0) { $data['character_current_health'] = $character['character_max_health'] + $healthGai...

PHP - MySQL error "Error: Query was empty" when someone tries to sign up with a username that is already taken

Hi, I've got a signup/login system on my site, and I've made it so that if someone tries to sign up with a username that is already in use, it posts an error underneath the form. However, instead of posting the error message that I created, it returns a MySQL error "Error: Query was empty". Here is the code that I am trying to use to do...

What's wrong with my sql query ?

INSERT INTO jos_race_calendrier_regional (id, evt_code, evt_date_comp, evt_station, evt_activite, evt_comite, evt_club_num, evt_club_nom, evt_nom, evt_dt, org_nom, org_adresse, org_adresse2, org_adresse3, org_ville, org_site, cont_nom, cont_tel, cont_gsm, cont_fax, cont_mail, epr_codex, epr_date, epr_sexe, epr_regroupement, epr_grille_ca...

mySQL Create Procedure error + general mysql help

Hi I'm very very new at MySQL and was wondering if anyone could help me out. I'm trying to build a procedure for my database and am using the following code. CREATE PROCEDURE `createuser`(username VARCHAR(100), password VARCHAR(100), email VARCHAR(100)) BEGIN DECLare returnValue int; IF EXISTS(SELECT 'True' FROM User_Table WHERE(User...

ERD diagram and SQL relationships linking user, project and dataset tables.

Hi guys, I have several tables in my ERD which which I would like to combine in a relational manner. I have several use cases, but I completely lost track of what kind of relations to use between the tables. Every user can work on multiple projects. Every user has one specific role per project (Manager, Contributor, User) Every proje...

SubQueries - using only one Where statement.

I have the below query, everytime i change it i need to change the stockclientid. I would like it where i only have one where statement to apply to all the sub queries. Is there also a way to apply the same code to return multiple rows? I would like to have the results display for all stockclientIDs rather than just 1. Select (Select...

Check for integrity constraint violation in SQL before deletion

My question is sort of similar to this one I want to check if a DELETE query will fail because of a constraint violation. I'd like to do this on database level because I think letting it fail and catching the error is ugly. Another option is "manually" checking for it using SELECT queries to see if there are constraints, but this is ra...

PHP/MySQL convert 'while' statements to 'foreach'

Hi, Following on from a previous question, shown here, I was wondering how I would go about changing the code below into 'foreach' statements? Any help greatly appreciated, S. <div id="banner-wrap"> <div id="banner" class="gallery"> <ul class="galleryBar"> <?php ...

What's the best way to connect iPhone app (iOS SDK) to php/MySql backend

Hey guys, New to iPhone dev here. Just wondering what's the best way to connect an iPhone app frontend (based on Cocoa) to a php/MySql based backend? The iPhone application will frequently access and change data stored on our server. Many thanks ...

AJAX database access in PHP. The simplest way.

I'm looking for the simplest possible solution to get mysql database records as XML output using PHP. That means no third party APIs. Just pure php code and as little of it as possible. I posted a similar article for c# in my blog and im looking for an equivalent that will work on any hosting company. Here is the link (i couldn't add ...

Why does a multi-word Doctrine search cause MySQL to use up to 300% CPU?

I have a table using Doctrine's Searchable behaviour, which works well for single word queries. However I've noticed that when a query includes a space, the page hangs and I can't get any response out of refreshing the page etc. If I open Activity Monitor on my Mac, or SSH then 'top' in Ububntu, MySQL is running a process at up to 300% ...

mysql index disk space

anyone know how to determine the size of an index in mysql(5.1)???? thanks in advance ...

Is table alias ambiguity in SQL prohibited in standard or blocked by implementations?

Yesterday I ran into an ambiguity issue in SQL for the first time. I got used to SQL errors in case of table ambiguity and yesterday did not even try to think about the problem in that way. Here is a simplified query: SELECT val1, ( SELECT SUM(val2) as val2_sum FROM ( SELECT 1 as id, 10 as val2 UNION ALL S...

How to change MySQL date format for database?

We are using a MySQL database with FileMaker. It appears that when FileMaker is reading the MySQL tables, it will only accept dates in the format of m/d/y. Is there any way I can get our MySQL database to change its default format to be m/d/y instead of YYYY-MM-DD? I know I can use the DATE_FORMAT() function on individual SELECT queri...

Joining a subselect to a table in sql

Is it possible to join the results of a SELECT with another table. Like this: SELECT * FROM table1 LEFT OUTER JOIN (SELECT * FROM table 2) I know I need to link the column but am not sure how. Is this possible? ...

how to delete the records from a table after a limit.

I have a table like this: CREATE TABLE vhist ( id int(10) unsigned NOT NULL auto_increment, userId varchar(45) NOT NULL, mktCode int(10) unsigned NOT NULL, insertDate datetime NOT NULL, default NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; A user can have more than one record. I need an SQL statemen...

guid as primary key?

Hello, I have a mysql database which has 3 tables that have to be joined together. I receive smaller databases that must feed this mysql database, appending the new data as I get it. The problem I have is the smaller dbs that I get are generated by an outside application and are not really meant to be used all together. Therefore, whe...

maintain session/connection between php and java app

A) If I have a java application which starts up and waits for socket connections on a specified port. Is there a way i could maintain a session connection in php after a user validates, i.e a persistent connection? B) I mean, I'm trying to understand how its done with mysql and php. How does mysql or php know what the last opened connec...

Mysql show processlist lists many processes sleep and info = null?

Hi, I'm injecting a stress test into my web app that connects to a mysql server and I'm monitoring the show processlist of mysql. When the load is high (high swap i/o) I get many processes like that: | 97535 | db| localhost | userA | Sleep | 515 | | NULL | 97536 | db| localhost | userA | Sleep | 516 | | NULL | ...