LinuxQuestions.org
Visit the LQ Articles and Editorials section
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 02-23-2006, 12:24 AM   #1
paddyjoy
Member
 
Registered: Apr 2005
Location: Sydney
Distribution: FC5
Posts: 174

Rep: Reputation: 30
MYySQL Query return more rows than exist in table


I'm trying to write a query that will return at least 15 rows from a database table, even if less than 15 rows exist in the table. It doesn't matter if there are duplicates.

The only thing I can think of doing is the following:

Assume there are 4 rows in mytable.

Code:
SELECT 1, * FROM mytable UNION SELECT 2, * FROM mytable UNION SELECT 3, * FROM mytable UNION SELECT 4, * FROM myTable
Then I just ignore the first column.

Does anyone have any better ideas?

Paddy
 
Old 02-23-2006, 03:42 AM   #2
Spudley
Member
 
Registered: Mar 2003
Location: Berkshire, England.
Distribution: SuSE 10.0
Posts: 299

Rep: Reputation: 32
Your solution will only work if the table is guaranteed to have four or more records, of course.

I'm trying to think of a scenario where I'd want a query to do what you're asking, and I honestly can't see one. I guess you must be trying to fill up an array or something in a program? But if I needed to do that, I'd probably write a loop in my program rather than trying to do it in SQL -- it'd just that much easier to be certain you have enough records.

If I really needed to do it in SQL, I might consider joining to another table that I know has sufficient records (but not many more) - eg:
SELECT s.* from smalltable s inner join bigenoughtable b on 1=1 limit 15

That'll get you the first fifteen records of a possible (smalltable * bigenoughtable) records.

But if either table has a lot of data, it probably won't be efficient. If you don't have a convenient bigenoughtable to join to, then your solution would probably work better. Still not ideal, though.
 
Old 02-23-2006, 04:11 AM   #3
paddyjoy
Member
 
Registered: Apr 2005
Location: Sydney
Distribution: FC5
Posts: 174

Original Poster
Rep: Reputation: 30
Thanks for the ideas, I might try joining to a larger table method.

I'll explain why I need to do this just in case anyone has any better ideas!

I have a javascript image scroller that I have written that scrolls 15 images. This piece of code is on a large number of pages 100+ and I have set it up so that the scroller items change automatically and I never have to change the javascript code. I have done this by listing the images in the javascript like this:

Code:
<img src="picture.php?ItemId=1">
<img src="picture.php?ItemId=2">
<img src="picture.php?ItemId=3">
<img src="picture.php?ItemId=3">
<img src="picture.php?ItemId=3">
etc.......
The file picture.php returns a different image based on the contents of a table using a query like this:

Code:
SELECT imagepath FROM scrolleritems where scrollerItemId = $ItemId
The table scrolleritems is updated automatically and can have anywhere from 0 to 100 items.

It works well and everything runs automatically except when there are less than 15 items in the table!

Does this seem like a strange way of doing things or does it even make sense?

Paddy
 
Old 02-23-2006, 06:50 AM   #4
Spudley
Member
 
Registered: Mar 2003
Location: Berkshire, England.
Distribution: SuSE 10.0
Posts: 299

Rep: Reputation: 32
Okay. Interesting one

I think what I would do if there are less than 15 images is serve a blank 1x1 pixel gif into the remaining slots, so they still have an image to show, so it doesn't break the scroller, the extra slots would be invisible. That would be the quick and easy way to deal with it without having to hack around with SQL or make any javascript changes.

Whether that approach would work will depend on the rest of the code, of course, but I don't see why it shouldn't work in principle.

Alternatively, I presume the number 15 is coded somewhere into the program; you could override that when the page loads if there aren't enough images

eg, something approximating this:
PHP Code:
<script>
var min_images=15;   //default number of images.
</script>

...

<?
while(list($imgnum)=mysql_fetch_array($result)) {
   print 
"<img src='picture.php?ItemId=$imgnum' />\n";
}
$rowcount=mysql_num_rows($dbh);
if(
$rowcount<15) {
   print 
"<script>\n";
   print 
"min_images=$rowcount;   //not enough pictures, so override min_images.\n";
   print 
"</script>\n";
}

That's not to say it can't be done in SQL, but I would tend to say that SQL isn't really the best way to do it.


By the way -- one other question to think about... what happens when you've got no image records at all? None of the options discussed so far really deal very well with that (showing blanks would probably work, but would look a bit odd). The trick is to have your program try to cope with whatever SQL gives it, rather than trying to force SQL to conform. It's usually a lot easier that way


All the best with the project.
 
Old 02-27-2006, 11:39 PM   #5
paddyjoy
Member
 
Registered: Apr 2005
Location: Sydney
Distribution: FC5
Posts: 174

Original Poster
Rep: Reputation: 30
Thanks for the ideas, after thinking about it all weekend I have decided to do the following.

I now have two tables, items and scrolleritems, items can have any number of rows while scrolleritems has exactly 15. When I want to update the scroller I loop from 1 to 15 (in the php code) updating each row in scrolleritems table with an itemid from the items table. I'm using the modulus operator so that if there are less than 15 items it will still select an itemid from items. It's a bit hard to explain so I'll just post the code.

Due to the nature of the application, if there are no items in the items table the scroller won't be displayed anywhere so that is not a problem.

Seems to work ok although it probably is a very strange way of doing this ha ha!

Code:
function UpdateScrollerItems()
	{

		$sql = "SELECT ItemId FROM items ORDER BY ItemId";

		$sql_result = mysql_query($sql,$connection) or die ('Could not select data');

		$numofrows = mysql_num_rows($sql_result);


		$ItemId = Array();

		while ($row = mysql_fetch_array($sql_result)) {
			array_push($ItemId, $row);
		}
		

	
		for ($count=0; $count <15; $count++)
		{

		$modnumber = (int)($count % $numofrows);
	
		
		$row = $ItemId[$modnumber];

		$SellingItemId = $row['ItemId'];

		$count2 = $count + 1;

		$sql = "UPDATE scrolleritems set ItemId = $SellingItemId WHERE ScrollerItemId = $count2";

		$sql_result = mysql_query($sql,$connection) or die ('Could not select data');

		}

		mysql_close($connection);

	}
Thanks again,
Paddy
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Routing table query mickboda Linux - Networking 3 11-05-2005 11:11 AM
[Perl] multi table query dsheller Programming 4 11-03-2005 05:32 AM
MySQL :: Table in query adds significant delay lackluster Programming 7 07-10-2004 03:48 AM
Table 'mysql.host' doesn't exist Zac2003 *BSD 5 09-14-2003 12:37 PM
return (about partition table) jamaso Linux - Newbie 1 10-22-2001 07:26 AM


All times are GMT -5. The time now is 05:49 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration