Page 1 of 1
Lowest possible result that no one has
Posted: Tue May 23, 2006 7:30 am
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
Posted: Tue May 23, 2006 7:46 am
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;
}
Posted: Tue May 23, 2006 8:03 am
by NiGHTFiRE
Pimptastic | Please use 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
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]
Posted: Tue May 23, 2006 8:09 am
by JayBird
NiGHTFiRE wrote:
That will give me the results I wish?
Try it
Posted: Tue May 23, 2006 8:43 am
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";
}
}
?>
Posted: Tue May 23, 2006 10:03 am
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.
Posted: Tue May 23, 2006 11:06 am
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
Posted: Fri May 26, 2006 10:37 am
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.
Posted: Fri May 26, 2006 12:45 pm
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.
Posted: Fri May 26, 2006 2:25 pm
by NiGHTFiRE
onion2k: How would i solve it?
Posted: Fri May 26, 2006 4:45 pm
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
Posted: Fri May 26, 2006 4:54 pm
by NiGHTFiRE
Okey, could you explain those queries?
I'm not the best at MySQL.