mysql-query

proper way to delete album and its media and sub album and its media

what would be proper way to delete albums hierarchical data with its media. should i just start looping and find all the sub album id and delete thous and make query to delete all the media belonging to that album id. that will be loads of query execution if data is alot or albums are alot for a user. what your device is on this. my...

MYSQL Searching by Zip Code

I am having a tough time searching by zip, which wasn't a problem before and will most likely be an easy problem for a MySQL / PHP programmer. I have a database full of house listings that I need to search by various fields. I have no problem searching by city, until I search by zip code (which like I said used to work). Anyways here i...

Using PDO to create a mysql query function, wont insert rows

Trying to get a grasp of using PDO, and I'm using some pre-made functions to make things simpler for when I want to do a query. First one connects, second runs the query. Unfortunately it won't let me INSERT rows using dbquery(). SELECT works fine, just can't seem to get anything else to work. Here's the code: function dbConnect() ...

Does this MySQL Query have any hidden gotchas?

Hello, I hope this isn't a terribly obtuse question. I notice that MySQL will let me refer back to the "Mnth" field in my GROUP and ORDER. This is not the case in every db engine I've worked with. Can anyone confim that this is an OK approach? SELECT DATE_FORMAT(FROM_UNIXTIME(`swauditlogs`.`dateline`),'%Y-%m') AS Mnth, Count(`swauditlo...

How can i get in between rows in mysql query.

I want to fetch intermediate rows from by database. Like for last 10 rows i will use limit : return Doctrine_Query::create() ->select('v.*') ->from('Video v') ->where("v.community_id='$community_id' AND v.user_id='$user_id' AND v.published='$published'") ...

Is this possible to join tables in doctrine ORM without using relations?

Suppose there are two tables. Table X-- Columns: id x_value Table Y-- Columns: id x_id y_value Now I dont want to define relationship in doctrine classes and i want to retrieve some records using these two tables using a query like this: Select x_value from x, y where y.id="variable_z" and x.id=y.x_id; ...

mysql select in multiple fields

hi to all In my search I used autosuggest. The problem now is I have to search the value in multiple fields such as firstname,middlename,lastname,caption etc. How to identify that the match string will belong on specific field name. let say i have table firstname middlename lastname caption james kelly ...

Simple MySQL query question

bins ---- id min max 1 1 20 2 21 40 3 41 60 pictures -------- id 3 11 59 Basically, I want to select the highest picture Id and then select from the bin table the bin id it matches. For example, for a pictures.id = 59 (highest), I want bins.id = 3. Can anyone help me with such a query? ...

MySQL Query in CodeIgniter with Session ID

Hi all, Let's say I have a query: " SELECT * FROM table WHERE donor_id = " .$this->session->userdata('id') ." GROUP BY rating" However, it appears that I get a mysql syntax error here, citing that $this->session->userdata('id') gives me '25' for example, instead of 25. Are there any workarounds here to prevent $thi...

CodeIgniter Concat

Hi all, I've spent a few hours staring at this piece of code. Fresh eyes please! Here is a shortened version of the query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (`requests` c) JOIN `inventory` d ON `d`.`listing_seq_no` = `c' at...

Zend_Db_Select - Joins and Count - Possible Zend Bug?

I'm having an issue getting a COUNT() from a SQL query using Zend_Db_Table_Select, and I think it may be a possible bug because the SQL it should be generating actually works. Here's the Zend Select Query: ($this is a Zend_Db_Table, renamed to table1 in this example) $select = $this->select(); $select->setIntegrityCheck(false);...

Join, Group By and Sum in MySQL

Hello, I have the following three tables in a MySQL database in order to give ratings to comments of users: Users: id name ----------- 1 Smith 2 Brown Comments: id user_id post_id comment ----------------------------------- 1 2 1 Test 1 2 1 1 Test 2 3 1 1 ...

MySQL searching long CHAR column, using smaller (substring) CHAR column as index?

So i have a UNIQUE CHAR(255) column, i want to find a particular row, Would it make sense to create CHAR(10) INDEX to make search more efficient? I know a unique is also an index the engine will scan throught the index to where the 1st letter is J, then JO, then JOH but an index of 255 bytes x 1 million records, is a lot of memory "s...

Grouped/Stratified Reports using PHP and MySQL

I am trying to generate a grouped/stratified report using PHP and MySQL. tbl_items has the following fields: (1) UserName and (2) ItemName A single user can have multiple items listed. I want to generate an output where the data is stratified by each user. For example: *UserName #1 -ItemName 1 -ItemName 2 *UserName #2 -ItemNa...

Use MySQL to determine if today is a user's birthday

I have all users' birthdays stored as a unix timestamp and am wanting to send out e-mails each day to users that have a birthday that day. I need to make a MySQL query that will get all of the rows that contain a birthday on today's date. It seems like this should be fairly simple, but maybe I am just overcomplicating it. ...

MySQL - A way to join all found rows in one column

Hi, I have this table: CREATE TABLE `dummy` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , `name` VARCHAR( 30 ) NOT NULL ) ENGINE = MYISAM ; and running this query: SELECT GROUP_CONCAT(`name` SEPARATOR "||") FROM `dummy` This query joins name column in all rows with || in a single column. BUT, the result is truncated wit...

Zend_Db_Table_Abstract and MySQL COMMENTs

I have built a query tool that enables a non-technical user to query parts of a database. I want to rename all the columns nicely. e.g.: table_id` > "Table ID" forename > "Forename" phone > "Telephone" I have put all the information into the MySQL COMMENT field as in: ALTER TABLE `table` CHANGE `field` `field` INT( 11 ) NOT NULL COM...

Why this is not valid MySQL query?

mysql> ALTER TABLE bdds_arts ADD test VARBINARY; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 something wrong with varbinary type? here is output of mysql --version mysql Ver 14.12 Distrib 5.0.24a, for Win32 (...

Mysql highest from 1 row

Is is poslbe too get the highest value from a row? IE. I have 3 days as columns with a timestamp value, can i get the highest value out? ...

mysql conditional insert - if not exists insert

Is there a query that will just check for the record and if it doesn't exists insert? I don't want to on duplicate update or replace. Looking for a one query solution, looked at other answer but not really what I was hoping for. Table: name|value|id ------------------ phill|person|12345 pseudo query: IF NOT EXISTS(name='phill', valu...