mysql

How Do I Solve a Locking Issue in MySQL?

I suppose this issue applies to deadlocks, live-locks, or just lock wait timeouts. I'm trying to figure out what query is causing a lock that is preventing another query from executing. Oracle has (if memory serves) a LOCK table that you can join onto itself to determine which queries are locking others. I need a way to accomplish t...

tags in mysql + php

Is there a common way of dealing with tags in databases? I'm thinking of using tinytext with pipes. I think adding another table and using IDs might make it more complicated for little gain. What's your preferred way of doing this? and what is the right way of doing queries in a table to find results matching multiple or single tags...

MySQL command to search CSV (or similar array)

I'm trying to write an SQL query that would search within a CSV (or similar) array in a column. Here's an example: insert into properties set bedrooms = 1,2,3 (or 1-3) title = nice property price = 500 I'd like to then search where bedrooms = 2+. Is this even possible? ...

PHP and MySQL Loop help?

Let's say I have a user that entered 12 links into the database but when they are displayed I want to break up the links into groups of 3 and repeat the groups of three until all 12 kinks are displayed for example. <div> <p><a href="">Link 1</a></p> <p><a href="">Link 2</a></p> <p><a href="">Link 3</a></p> </div> Here is part of th...

reducing mysql statements that differ only in ORDER BY field name

I'm trying to sort data by different fields ascending and descending. But I have different mysql pdo statements for the 4 fields I have (8 queries total): $stmt1 = $po->prepare("SELECT * FROM tabname WHERE categ=:categ ORDER BY field1 DESC"); $stmt2 = $po->prepare("SELECT * FROM tabname WHERE categ=:categ ORDER BY field1 ASC"); $stmt3 =...

Formatting a timestamp

Hello, The variable below formats as "2009-11-05 22:51:26" when it is printed. How could I format it to "22:51 New York Time 5 Nov 2009 Thursday"? (I think the timestamp in my database is United States eastern time zone). createddatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP Thanks in advance, John ...

Database design question (conditional mandatory fields)

Hi, Just looking for some opinions/ideas on how best to do this. I'm building an extranet system where users are added by the administrators manually (there's no ability for someone to "register" themselves). When they're added only a small amount of mandatory data is entered (name, email, password), they are then emailed their login, a...

pdo binding asc/desc order dynamically

Let's say I have 2 pdo statements that differ only in order (asc vs. desc) $stmt1 = $po->prepare("SELECT * FROM tabname WHERE categ=:categ ORDER BY field1 DESC"); $stmt2 = $po->prepare("SELECT * FROM tabname WHERE categ=:categ ORDER BY field1 ASC"); Is there a way I can bind ASC/DESC dynamically so I can have only 1 stmt $order = "AS...

Update Database record using jQuery/PHP and a link and not a button

I am attempting to make a registration-like page for a small forum. To register for an event, I simply want the users to click a 'link'. (It doesn't have to be a link, but I don't want it to look like a button, so fancy CSS is acceptable). On this page there will be a limited number of openings per event. (Simple database query to popul...

How to display a piece of code only once when the rest is looped using PHP and MySQL?

I'm trying to display the following code only once while the rest of the code loops threw. How should my code look like and where should I put it. As of right now the bottom code will not display but if I put it in the loop it will display multiple times (not what I want). //Display only once. if (!empty($row['category']) && !empty($r...

PHP + MySQL Queue

I need a simple table that acts as a Queue. My MySQL server restriction is I can't use InnoDB tables, only MyISAM. Clients/workers will work at the same time and they will need to receive differents jobs each time. My idea is to do the following (pseudo-code): $job <- SELECT * FROM queue ORDER BY last_pop ASC LIMIT 1; UPDATE queue SET...

Complicated MySQL Query

I'm creating a site in wordpress which holds information on television programs. I'm using custom fields to select each post. The table looks something like this +----+---------+----------+------------+ | id | post_id | meta_key | meta_value | +----+---------+----------+------------+ | 1 | 1 | name | Smallville | | 2 | 1...

MYSQL update clause comparing the WHERE from 2 tables.

$produpd = "UPDATE tblnavpc SET tblnavpc.ChildName = tblnav.NavName " . "FROM tblnav WHERE tblnavpc.CID = tblnav.NavID"; This is the error I get" 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 tblnav WHERE tblnavpc.CID = tblnav....

MySQL: Get all subcategories, descendants

I have mysql tables defined as: categories: category_id, category_name, parent_category_id I'm looking for a nice sql query that would retrieve all the DESCENDANTS of a given category_id. That means, its children, and its children's children. If that helps, we can assume a maximum number of levels (3). This query could be sent at any ...

Optimize Join sentence with foreign keys, and show records with nulls

Hi guys I have the following structure SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; CREATE TABLE IF NOT EXISTS `sis_param_tax` ( `id` int(5) NOT NULL auto_increment, `description` varchar(50) NOT NULL, `code` varchar(5) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7; CREATE TABLE IF NOT EXISTS ...

How to store NULL values in datetime fields in MySQL?

I have a "bill_date" field that I want to be blank (NULL) until it's been billed, at which point the date will be entered. I see that MySQL does not like NULL values in datetime fields. Do any of you have a simple way to handle this, or am I forced to use the min date as a "NULL equivalent" and then check for that date? Thanks. EDITED...

How do you count the number of times any given $variable appears in a column in a MySql table

How do you count the number of times any given $variable appears in a MySQL Table The database name is STUDENTS The table is called PROJECTS The column is called ESSAYS The variable for each essay is what im trying to count. It is called &essay and inserts into PROJECTS ...

Can I add an entry to mysql and return a column if it's not present in one query?

Can I have one query to check if an entry is in a mysql table, and if it is present, return a column, but if it's not present, insert it and return a value? I have a users table with the following layout: userID - int, auto-increment userName - varchar(100) active - tinyint(1) I want to do something like select userID from users wh...

PHP + MySQL Cycle Queue

I just accepted a similar question (http://stackoverflow.com/questions/1690748/php-mysql-queue), but I realized that it wasn't the correct question for my problem, but was the correct answer for my question :) I have a MySQL (MyISAM type) table of sites to be scraped by workers. CREATE TABLE `site` ( `id` int(11) NOT NULL auto_increm...

mysql - Able to enter multiple values even when new value equals an existing primary key

I have a table defined as follows: CREATE TABLE `mydb`.`users` ( `userID` int(10) unsigned NOT NULL AUTO_INCREMENT, `userName` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `createdDate` datetime NOT NULL, `active` tinyint(1) NOT NULL, `lastUpdatedDate` datetime NOT NULL, PRIMARY KEY (`userID`,`userName`) USING BTREE ) H...