mysql

How to make a nested query?

Have a table users and there is a field invited_by_id showing user id of the person who invited this user. Need to make a MySQL query returning rows with all the fields from users plus a invites_count field showing how many people were invited by each user. Something like this: SELECT User.*, Count.count FROM users AS User, ...

spring + ibatis + mysql result in com.mysql.jdbc.exceptions.jdbc4.CommunicationsException

Hi all, this is a very famous error, however after searching and broswing the web I didn't manage to find a stable and proper solution. We are running an application onto this environment: spring + ibatis + mysql all at their latest version. Very randomly we get this error, and then after some time, which can be hours or days, the erro...

Using Mysql native functions with ActiveRecord on development environment

Hi. I need to query my database using mysql native functions such as YEAR(date) or NOW().... This works on staging and on production environments but it won't work in my development environment because of SQLite's lack of support to these functions. Can you help? Thank you. ...

mysql - search between dates where all dates appear

I'm working with some imported data that stores details about whether a "room" is available on a specific day or not. Each room has an individual entry for the date that it is available. | id | date | price | -------------------------------- | 1 | 2010-08-04 | 45.00 | A user can search across a date range and the sea...

image resize while keeping ratio

im listing photos using mysql. like <? $a = mysql_query("select * from x"); ?> <? while ($w=mysql_fetch_array($a)) { ?> <img src="<?=$w[url]?>" alt="<?=$w[name]?>" width="150" height="110" /> <? } ?> for this thing, how i can keep picture RATIO? a picture in width 150 and height 500 goes very very BAD quality.. how can i fix this? ...

comparing values of two arrays in php

i have two arrays which keys are book id( 61, 78 ,...etc) (1) book_width ( [61] => 8.3 [72] => 8286.1 [78] => 6.4 [100] => 8407.0 [102] => 0.7 ) (2) book_height ( [61] => 9.00 [72] => 150 [78] => 8.00 [100] => 150 [102] => 3.00 ) now i want an array whic...

Is it possible to group rows by a day stored within a timestamp?

I'm not sure if this is even within the scope of MySQL to be honest or if some php is necessary here to parse the data. But if it is... some kind of stored procedure is likely necessary. I have a table that stores rows with a timestamp and an amount. My query is dynamic and will be searching based on a user-provided date range. I w...

generate days from date range

I would like to run a query like select ... as days where `date` is between '2010-01-20' and '2010-01-24' And return data like: days ---------- 2010-01-20 2010-01-21 2010-01-22 2010-01-23 2010-01-24 ...

Object is reinserted into database immediately after delete (DbLinq)

I have a MySql database, whose general structure looks like this: Manufacturer <== ProbeDefinition <== ImagingSettings ElementSettings ====^ ^==== ProbeInstance I'm using InnoDB to allow foreign keys, and all foreign keys pointing to a ProbeDefinition have set ON DELETE CASCADE. The issue I'm having is when I delete a ProbeDefinit...

Displaying a single rank in MySQL table

I have a table called 'highscores' that looks like this. id udid name score 1 1111 Mike 200 2 3333 Joe 300 3 4444 Billy 50 4 0000 Loser 10 5 DDDD Face 400 Given a specific udid, I want to return the rank of that row by their score...

How can I get phpmail to send to the right user and also limit the ammount of emails for certain users? Part 2.

Thanks to Anthony's advice I've tried to simplify my logic and syntax of the goal I am trying to achieve which is when a blog is written, the AUTHOR gets notified by php mail that someone other than himself comments. When the AUTHOR comments everyone else who commented except him gets a different email. When another user comments, the AU...

Need MySQL Queries to delete WordPress Posts and Post Meta more than X Days Old

I run a blog where the community can post time-sensitive community links (sports scores and such). After a certain time, those posts are no longer useful, so I want to delete them in batch via a MySQL query, but I don't know how. I imagine that getting rid of those posts entirely is more than just deleting from the wp_posts table, right?...

Database development question

Hi all. I am a junior year computer science student. I have learned some database stuff (MySql) in my spare time. This quater, me and three fellow student took up an volunteer work for the University, to develop some websites for them. Our team consists of experienced PHP+MySql developers, and beginners like me. During our last meeting...

MySQL find nearest matching row

Hi there, I thought that this would be simple enough, but I just can't get my head around it. I have a mapping/lookup table with two columns. Col1 is used to lookup values from Col2. Col1 is a straight INT column with values in it incrementing in counts of 20, and starting from 500. So, it has values like 500, 520, 540 and so on. Each...

how to select two record from two different table with one query in mysql

how to select two record from two different table with one query in mysql?? for a search query im using $search = $_GET['gd'] $arama_sonuc = mysql_query("select * from mp3 where baslik like '%$search%'"); well its ok. it shows me results from mp3 table. but with same query i need to search something on HABERLER table too.. how can i ...

When should I use transactions in my queries?

Hi, I'm reading on web, very detailed tutorials on how to use transactions with database types and database engines, but I haven't found some guide that teach me when and why I should use them. I know transactions are usually used for home bankings, so when we work with money data, but I can immagine they are used in many other ways. T...

MySQL / SQL Server... working together as a total solution

Hi, I want to user sql server for analysis services, and a few other small apps. The majority of our systems are in MySQL. Things like user info, customer info, sales data I need to get into SQL Server. Obviously, there is much to debate here. What I was attempting to do is to dump a read only copy into SQL Server so I can user report...

SET NAMES utf8 in MySQL?

I often see something similar to this below in PHP scripts using MySQL query("SET NAMES utf8"); I have never had to do this for any project yet so I have a couple basic questions about it. Is this something that is done with PDO only? If it is not a PDO specific thing, then what is the purpose of doing it? I realize it is sett...

Is php the right language to program for a large json api such as mochi?

So I am currently using django and the site goes down very often... However, because of that i was thinking if a simpler solution of php is introduced in slurping in the games over to my db and s3 using the following api then i would not have to keep up with the django issues: url: http://www.mochimedia.com/feeds/games/xxxxxx/all?format=...

SELECT to pick users who both viewed a page

I have a table that logs page views of each user: +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | view_id | int(11) | NO | PRI | NULL | auto_increment | ...