Shantanu's Blog

Database Consultant

September 19, 2005


MySQL Case Study - 62

Possible to combine the contents of two tables?

I am trying to combine these tables for test purposes...mysql and dbf. Ideally I would like the last two records in dbf to be added to mysql and at the same time keep the original records in mysql so they are not overwritten by those from dbf with the same id.

Here are my dumps from the tables if someone has a chance to test things...

-- Table structure for table `dbf`

`id` int(11) NOT NULL default '0',
`name` varchar(50) default NULL,
`surname` varchar(50) default NULL,
`country` varchar(50) default NULL,

-- Dumping data for table `dbf`

INSERT INTO `dbf` VALUES (1, 'Sarah', 'Smith', 'UK');
INSERT INTO `dbf` VALUES (2, 'Jackie', 'Collins', 'USA');
INSERT INTO `dbf` VALUES (3, 'Sara', 'Roserio', 'Brazil');
INSERT INTO `dbf` VALUES (4, 'John', 'Smith', 'UK');
INSERT INTO `dbf` VALUES (5, 'Sally', 'Perkins', 'USA');

-- Table structure for table `mysql`

CREATE TABLE `mysql` (
`id` int(11) NOT NULL default '0',
`name` varchar(50) default NULL,
`surname` varchar(50) default NULL,
`country` varchar(50) default NULL,

-- Dumping data for table `mysql`

INSERT INTO `mysql` VALUES (1, 'Sarah', 'Schulz', 'UK');
INSERT INTO `mysql` VALUES (2, 'Jackie', 'Dolan', 'USA');
INSERT INTO `mysql` VALUES (3, 'Sara', 'Smith', 'UK');

This is the result that I would like...

1 Sarah Schulz UK
2 Jackie Dolan USA
3 Sara Smith UK
4 John Smith UK
5 Sally Perkins USA

So far I have tried

(SELECT DISTINCT id, name, surname FROM mysql)
(SELECT DISTINCT id, name, surname FROM dbf)

and I get

| id | name | surname |
| 1 | Sarah | Schulz |
| 2 | Jackie | Dolan |
| 3 | Sara | Smith |
| 1 | Sarah | Smith |
| 2 | Jackie | Collins |
| 3 | Sara | Roserio |
| 4 | John | Smith |
| 5 | Sally | Perkins |

However when I remove name from the query I get the distinct values I want. Is there a way to union based on an index/key?
I want to keep my values from the mysql database and add on the extra ones from dbf.

September 18, 2005


MySQL Case Study - 61

Current date data?

Its a small ad system I'm designing, This page is called ad.php
It is requested through a iframe. I only want some ads to be showen around sertion date, this is why I have 'sdate'(start date) and 'edate'(end date)
eg. I christmas as for one month around Christmas so the 'sdate' in the datebase would be '2005-12-01' and 'edate' would be '2005-12-31'

What Im trying to do is select ad that are currently running.


MySQL Case Study - 60

Problem Selecting Maximum Average

I have a MYSQL table for bowling scores that goes something like this:

Date Name Score
yyyy-mm-dd Kev 150
yyyy-mm-dd Kev 100
yyyy-mm-dd Brendan 89
yyyy-mm-dd Kev 40

I want to select the person with the largest average, and know what that average is, but I am unsure how to do this. Is it possible to use a max function on a function selecting the averages?


MySQL Case Study - 59

Selecting text range

I am trying to find a way to select a range of names in a table from a to c. All of the range posts and examples I see pertain to numbers, but no text.
At the moment, I am doing it this way -

SELECT stage_name FROM stage_names
WHERE stage_name LIKE 'a%'
OR stage_name LIKE 'b%'
OR stage_name LIKE 'c%'
ORDER BY stage_name

Is there a better way?

September 10, 2005


Reading buffers: ob_get_contents() - Practical PHP Programming

Reading buffers: ob_get_contents() - Practical PHP Programming: "Output buffers are two-way affairs, which means you can read from them as well as write to them. You've seen that writing to an output buffer is simply a matter of opening a buffer and outputting text as normal - reading that data back is done by just using the ob_get_contents() function."


Powerpoint Tips

Create an automatic agenda slide: Use the Summary Slide feature to create a list of your slideshow's slides. Select the slides to include in the Slide Sorter view, then click Summary Slide on the Outlining toolbar. Change the title of the new slide to Agenda and move it to the beginning of the presentation. From this slide, you can hyperlink each of the other slides' names to their corresponding slides, then use this Agenda slide to navigate to topics the audience wants to discuss.

Create a slide list: Display the Outline pane. (In PowerPoint 2002, display the Outline tab of the Outline pane.) Press ALT+SHIFT+1 to collapse the entire outline to show only slide titles. (This shortcut toggles between collapsing and expanding the outline.) Choose FILE » PRINT. From the Print What drop-down list, choose Outline View. Click OK.

More about Charts...

Widen the bars in a bar chart for readability: To make your bar chart stand out more clearly, widen the bars. Double-click on the chart to activate it, then double-click on the bars to select them all. In the Format Data Series dialog box, click the Options tab and reduce the Gap Width. Click OK.

Animate charts: Display your charts bar by bar or year by year. Select the chart and choose SLIDE SHOW » CUSTOM ANIMATION. In PowerPoint 2000, use the Custom Animation dialog box to choose the type of animation. In PowerPoint 2002, click Add Effect in the Custom Animation task pane to add an effect. Then choose the animation from the task pane listing, choose Effect Options, and specify how you want to break down the chart's animation.

Save a chart's properties: After you've spent hours formatting a chart, you can save its formatting for use in another presentation. Double-click on the chart, then right-click and choose Chart Type. Click the Custom Types tab of the Chart Type dialog box and choose User Defined. Click Add, name the chart type, and click OK twice to get back to your presentation.


September 08, 2005


Google Send to Phone for Firefox

Google Send to Phone for Firefox: "Google Send to Phone for Firefox is an extension that enables you to send short text messages of web page content to your mobile phone. For example, you might text message yourself a phone number, an address, or directions that you find on the Web."

September 05, 2005


MySQL Case Study - 58

Query by column comparison:
I'm trying to pull a row by comparing that the numerical value of one column is greater than the numerical value of other columns. The manual is quite confusing as to how to phrase such a thing, and I haven't done much more than simple queries up until now.

MySQL version: 4.0.22

What I started with that doesn't work:

select * from someTable where col1 > col2 and col1 > col3 and col1 > col4 order by someThing limit 1

I'm basically trying to grab one row where the value of one column is greater than the values of the remaining similar columns.

Is this possible?

Help would be greatly appreciated!!


MySQL Case Study - 57

select daily item orders & totals for a given month

I've got 2 tables:
product table with a name & id
order table with prod_id, order_date, qty

I'd like to select the daily totals for a given month for each item, can I do this with 1 sql query? How do I extract a single day as a column, and do that for each day in the month?


MySQL Case Study - 56

Union Question
this query works great:

(SELECT id_homes FROM homes WHERE id_homes < 100 ORDER BY id_homes DESC LIMIT 1) UNION ALL (SELECT id_homes FROM homes WHERE id_homes > 100 LIMIT 1);
| id_homes |
| 99 |
| 101 |
2 rows in set (0.00 sec)

but is it possible to split my result on two columns like:

PHP Code:
| prev | next |
| 99 | 101 |

September 03, 2005


Nokia - Nokia 770

Nokia - Nokia 770: Nokia 770 Internet Tablet
* Easy, broadband access over Wi-Fi.
* A truly portable, elegantly-sized tablet designed for effortless surfing.
* Impressive hi-resolution widescreen display and intuitive interface are optimized for online browsing.


June 2001   July 2001   January 2003   May 2003   September 2003   October 2003   December 2003   January 2004   February 2004   March 2004   April 2004   May 2004   June 2004   July 2004   August 2004   September 2004   October 2004   November 2004   December 2004   January 2005   February 2005   March 2005   April 2005   May 2005   June 2005   July 2005   August 2005   September 2005   October 2005   November 2005   December 2005   January 2006   February 2006   March 2006   April 2006   May 2006   June 2006   July 2006   August 2006   September 2006   October 2006   November 2006   December 2006   January 2007   February 2007   March 2007   April 2007   June 2007   July 2007   August 2007   September 2007   October 2007   November 2007   December 2007   January 2008   February 2008   March 2008   April 2008   July 2008   August 2008   September 2008   October 2008   November 2008   December 2008   January 2009   February 2009   March 2009   April 2009   May 2009   June 2009   July 2009   August 2009   September 2009   October 2009   November 2009   December 2009   January 2010   February 2010   March 2010   April 2010   May 2010   June 2010   July 2010   August 2010   September 2010   October 2010   November 2010   December 2010   January 2011   February 2011   March 2011   April 2011   May 2011   June 2011   July 2011   August 2011   September 2011   October 2011   November 2011   December 2011   January 2012   February 2012   March 2012   April 2012   May 2012   June 2012   July 2012   August 2012   October 2012   November 2012   December 2012   January 2013   February 2013   March 2013   April 2013   May 2013   June 2013   July 2013   September 2013   October 2013   January 2014   March 2014   April 2014   May 2014   July 2014   August 2014   September 2014   October 2014   November 2014   December 2014   January 2015   February 2015   March 2015   April 2015   May 2015   June 2015   July 2015   August 2015   September 2015   January 2016   February 2016   March 2016   April 2016   May 2016   June 2016   July 2016   August 2016   September 2016   October 2016   November 2016   December 2016   January 2017   February 2017   April 2017   May 2017   June 2017   July 2017   August 2017   September 2017   October 2017   November 2017   December 2017   February 2018   March 2018   April 2018   May 2018   June 2018   July 2018   August 2018   September 2018   October 2018   November 2018   December 2018   January 2019   February 2019   March 2019   April 2019   May 2019   July 2019   August 2019   September 2019   October 2019   November 2019   December 2019   January 2020   February 2020   March 2020   April 2020   May 2020   July 2020   August 2020   September 2020   October 2020   December 2020   January 2021   April 2021   May 2021   July 2021   September 2021   March 2022   October 2022   November 2022   March 2023   April 2023   July 2023   September 2023   October 2023   November 2023   April 2024   May 2024   June 2024  

This page is powered by Blogger. Isn't yours?