mysql

MySQL function to compare values in a db table against the previous

Iam quite new to functions in SQL and I would like to create a function to compare values in a MySQL table against previous and I am not sure how to do this. For example (iId is the input value) DECLARE pVal INT(20); DECLARE val INT(20); SELECT price INTO pVal FROM products WHERE Id=iId; SELECT price FROM products; IF price == pVal...

update myqsl table

how can i write the query, to update the table videos, and set the value of field name to 'something' where the average is max(), or UPDATE the table, where average has the second value by size!!! i think the query must look like this!!! UPDATE videos SET name = 'something' WHERE average IN (SELECT `average` ...

MySQL Update command

hey guys i need to add a special text to all rows in my mysql table , how to add some text to the end of all rows' content in a table just for one field i used this code : UPDATE `blogs` SET `title`= `title` + 'mytext'; but didnt work for me ...

Minimizing SQL queries using join with one-to-many relationship

So let me preface this by saying that I'm not an SQL wizard by any means. What I want to do is simple as a concept, but has presented me with a small challenge when trying to minimize the amount of database queries I'm performing. Let's say I have a table of departments. Within each department is a list of employees. What is the most e...

Difficulty creating a paging function with MySQL and ColdFusion

I'm trying to create pagination for search results using MySQL and ColdFusion. My intention is to only retrieve the queries that can be displayed on a single page, thus making the process efficient. I tried using two queries in my function, but I could not return two variables to the cfinvoke. The following code does not paginate, but ...

function for converting time to number of seconds

On our site, we have a lot of swimming times that we would like to convert to seconds. i.e. 1:23:33.03 or 58:22.43. Is there a PHP function that can do this? A MySQL function? ...

How to first get different related values from diferent SQL tables (PHP)

I am triig to fill options list. I have 2 tables USERS and STREAMS I vant to get all streams and get names of users assigned to that streams. Users consists of username and id Streams consists of id, userID, streamID I try such code: <?php global $connection; $query = "SELECT * FROM streams "; ...

Is there a way to effect user defined data types in MySQL?

I have a database which stores (among other things), the following pieces of information: Hardware IDs BIGINTs Storage Capacities BIGINTs Hardware Names VARCHARs World Wide Port Names VARCHARs I'd like to be able to capture a more refined definition of these datatypes. For instance, the hardware IDs have no numerical significance, so...

Specific ordering of records based on list of IDs? with MySQL & PHP

I have a 'user' table in MySQL When users login to my app, they can see a list of all other users and via some javascript-drag-drop can arrange which order the other users appear in. We want to save that order. And it is saved to their record (i.e. 'order_pref' = 4,3,5,23... list of user_ids in the order they specified). So that's fin...

Avoiding repeated subqueries when 'WITH' is unavailable

MySQL v5.0.58. Tables, with foreign key constraints etc and other non-relevant details omitted for brevity: CREATE TABLE `fixture` ( `id` int(11) NOT NULL auto_increment, `competition_id` int(11) NOT NULL, `name` varchar(50) NOT NULL, `scheduled` datetime default NULL, `played` datetime default NULL, PRIMARY KEY (`id`) ); ...

Enter ID instead of name on submit (form)

In my users_subjects table, I have a user ID and a subject ID. When a user (of admin level) creates a subject they select from a drop down menu a project. Here is the select query to draw up appropriate values: $sql = "SELECT users_subjects.users_subjectsid, subjects.subjectn FROM subjects JOIN users_subjects on subjects.subjectid = us...

Jetty 7 + MySQL Config [java.lang.ClassNotFoundException: org.mortbay.jetty.webapp.WebAppContext]

I've been trying to get a c3p0 db connection pool configured for Jetty, but I keep getting a ClassNotFoundException: 2010-03-14 19:32:12.028:WARN::Failed startup of context WebAppContext@fccada@fccada/phpMyAdmin,file:/usr/local/jetty/webapps/phpMyAdmin/,file:/usr/local/jetty/webapps/phpMyAdmin/ java.lang.ClassNotFoundException: org.mort...

MySQL: Which is faster — INSTR or LIKE?

If your goal is to test if a string exists in a MySQL column (of type 'varchar', 'text', 'blob', etc) which of the following is faster / more efficient / better to use, and why? Or, is there some other method that tops either of these? INSTR( columnname, 'mystring' ) > 0 vs columnname LIKE '%mystring%' ...

MySql query problem with prev/next selection and mutiple columns sorting

here's my table products_tb for products: --------------------------------------- | product_id | prod_name | cat_id | --------------------------------------- | 112 | Zumar | 3 | | 131 | Xerox | 2 | | 143 | Dan | 1 | | 145 | Alan | 1 | | 195 | ...

MySQL - How to determine if my table is stored in RAM?

I'm running: MySQL v5.0.67 InnoDB engine innodb_buffer_pool_size = 70MB Question: What command can I run to ensure that my entire 50 MB database is stored entirely in RAM? ...

calculating and showing a date as 'secs ago', 'mins ago', 'hours ago' etc

I've got a small custom made blog type thing and want to display the date posted next to comments. I'd like to do it in the format of: posted 23 secs ago posted 43mins ago posted 1hr ago posted 1day ago posted 2 weeks ago ... probably won't get much longer than that because articles older than a month aren't shown. I can stored the a...

Getting random record from database with group by

Hello i have a question on picking random entries from a database. I have 4 tables, products, bids and autobids, and users. Products ------- id 20,21,22,23,24(prime_key) price........... etc........... users ------- id(prim_key) name user1,user2,user3 etc bids ------- product_id user_id created autobids -------- u...

What is wrong with this SQL query? (or perhaps why doesn't MySQL like the sixth field?)

Here is my table: id int(11) name varchar(255) description text highest_bidder int(11) value varchar(255) group int(11) I originally didn't have the group field in there but added it later. Anyway, I go to insert into the database using the following snippet: INSERT INTO ...

Possible to open a text file in a MYSQL stored procedure?

Is it possible to open and read from a text file in a MYSQL stored procedure? I have a text file with a list of about 50k telephone numbers, and want to write a stored procedure that will open the file, read the 50k lines and store it as rows in a table. I cannot load the file directly using LOAD IN FILE as the table has additional colum...

UNIQUE and PRIMARY KEY

Is declaring an attribute of a table as UNIQUE equivalent to declaring it as PRIMARY KEY? thanks a lot! ...