Lowest possible result that no one has

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
NiGHTFiRE
Forum Contributor
Posts: 156
Joined: Sun May 14, 2006 10:36 am
Location: Sweden

Lowest possible result that no one has

Post by NiGHTFiRE »

Hey,
I've got a table called bud where people place bets. And i need to know howto get data out of that table where no one has placed a bet. Meaning:
person 1 has bet 1$
person 2 has bet 2$
person 3 has bet 4$
person 4 has bet 5$
person 5 has bet 7$
person 6 has bet 8$
person 7 has bet 10$

Then I need a query to select to lowest possible number but no one should have beted that number and it should be limited to 2 results. So there the result should be $3 and 6$.
I don't really know how I would do like that.
Any knows how I would do like that?
Thanks for your help
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post by xpgeek »

Code: Select all

$sql = "select bet from bud";
$res = mysql_query($sql);
$c = 0;
$min = array();
$num = mysql_num_rows($res);
for ($i = 1; $i < $num; $i++)
{
$row = mysql_fetch_assoc($res);
if ($i != $row['bet'])
{
$c++;
$min[] = $i;
}
if ($c == 2) break;
}
NiGHTFiRE
Forum Contributor
Posts: 156
Joined: Sun May 14, 2006 10:36 am
Location: Sweden

Post by NiGHTFiRE »

Pimptastic | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Okey, so i've got this code:

Code: Select all

$sql = "SELECT bud FROM $MYSQL_TABLE"; 
$res = mysql_query($sql); 
$c = 0; 
$min = array(); 
$num = mysql_num_rows($res); 
for ($i = 1; $i < $num; $i++) 
{ 
$row = mysql_fetch_assoc($res); 
if ($i != $row['bet']) 
{ 
$c++; 
$min[] = $i; 
} 
if ($c == 2) {
echo "De talen som inte har fått något bud är:". $c;
break; 
}

That will give me the results I wish?
Thanks


Pimptastic | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

NiGHTFiRE wrote: That will give me the results I wish?
Try it
NiGHTFiRE
Forum Contributor
Posts: 156
Joined: Sun May 14, 2006 10:36 am
Location: Sweden

Post by NiGHTFiRE »

It's an SMS service so I can try it if i dont want to loose money,
but does everything look correct at this code:

Code: Select all

$stora = array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "Å", "Ä", "Ö");
$sma = array("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", "å", "ä", "ö"); 
$smaword2 = str_replace($stora, $sma, $word[2]);
$sql = "SELECT bud FROM $MYSQL_TABLE WHERE item='$smaword2'"; 
$res = mysql_query($sql); 
$c = 0; 
$min = array(); 
$num = mysql_num_rows($res); 
for ($i = 1; $i < $num; $i++) 
{ 
$row = mysql_fetch_assoc($res); 
if ($i != $row['bud']) 
{ 
$c++; 
$min[] = $i; 
} 
if ($c == 2) {
echo "De talen som inte har fått något bud är:". $c;
break; 
} else {
echo "ERROR";
}
}
?>
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

That code won't work. You'll need to sort the bids to fix it.

And even when you do that it won't scale well. If you have 1,000,000 bids in your database, and the lowest missing one is 999,999 you'll be waiting for a very long time while the script runs.
NiGHTFiRE
Forum Contributor
Posts: 156
Joined: Sun May 14, 2006 10:36 am
Location: Sweden

Post by NiGHTFiRE »

Well since i took SELECT bud FROM $MYSQL_TABLE WHERE item='$smaword2' it won't be so many. Cause when you make an auction you add the maxvalue of buds people can select and it wont be so many. But how would I do so it would work?
Thanks
NiGHTFiRE
Forum Contributor
Posts: 156
Joined: Sun May 14, 2006 10:36 am
Location: Sweden

Post by NiGHTFiRE »

I've got this code now:

Code: Select all

<?php
$nr = $_REQUEST["nr"]; 
$sms = @urldecode($_GET["sms"]);
$word = explode(" ", $sms);

//Databas variablar
$MYSQL_HOST = ''; 
$MYSQL_USER = '';
$MYSQL_PASSWORD = '';
$MYSQL_DATABASE = '';
$MYSQL_TABLE = 'bud';
$MYSQL_TABLE2 = 'auction';
//Ansluta till databasen
mysql_connect($MYSQL_HOST, $MYSQL_USER, $MYSQL_PASSWORD) or die(mysql_error());
mysql_select_db($MYSQL_DATABASE);
$datum = date("d/m/y"); 
$stora = array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "Å", "Ä", "Ö", " ");
$sma = array("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", "å", "ä", "ö", "+"); 
$smaword2 = str_replace($stora, $sma, $word[2]);
$sql = "SELECT bud FROM $MYSQL_TABLE WHERE item='$smaword2'"; 
$res = mysql_query($sql); 
$c = 0; 
$min = array(); 
$num = mysql_num_rows($res); 
for ($i = 1; $i < $num; $i++) 
{ 
$row = mysql_fetch_assoc($res); 
if ($i != $row['bud']) 
{ 
$c++; 
$min[] = $i; 
} 
if ($c == 2) {
$tal = explode(" ", $c);
echo "De talen som inte har fått något bud är: ". $tal['1'];
echo " och ". $tal['2'];
break; 
} else {
echo "ERROR";
}
}
?>
Do you see anything that's wrong or that I should change?
Thanks.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

It still won't work if the bids aren't in perfect ascending order in the database. Nor will it work if there's a duplicate bid.
NiGHTFiRE
Forum Contributor
Posts: 156
Joined: Sun May 14, 2006 10:36 am
Location: Sweden

Post by NiGHTFiRE »

onion2k: How would i solve it?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Do you have a table that contains the 'sequence' with allowed bets?

Eg: you have a table with allowed that contains values (0, 1, 2, ..., 8, 9, 10).

(untested queries)

Code: Select all

SELECT val
FROM allowed
WHERE val NOT IN (SELECT val FROM bets)
ORDER BY val ASC
LIMIT 2
Or a bit optimised:

Code: Select all

SELECT val
FROM allowed
LEFT OUTER JOIN bets ON allowed.val = bets.val
WHERE bets.val IS NULL
ORDER BY val ASC
LIMIT 2
NiGHTFiRE
Forum Contributor
Posts: 156
Joined: Sun May 14, 2006 10:36 am
Location: Sweden

Post by NiGHTFiRE »

Okey, could you explain those queries?
I'm not the best at MySQL.
Post Reply