Shantanu's Blog

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`

CREATE TABLE `dbf` (
`id` int(11) NOT NULL default '0',
`name` varchar(50) default NULL,
`surname` varchar(50) default NULL,
`country` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 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,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 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...
Code:

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)
UNION
(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.

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.

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
etc.

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?

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?

http://forums.devshed.com/t286825/s.html

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."

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.

From:
presentations.com

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."

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!!

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?

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 |
+----------+---------+

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.