Jump to content

awjudd

Staff Alumni
 View Profile  See their activity

  • Posts

    422

  • Joined

  • Last visited

  • Days Won

    1

 Content Type 

Posts posted by awjudd

    Help with next position

    in MySQL Help

    Posted

    Just an extension on what I said.  If the current position is 8, you will get the following:

     

    Query Part 1:

    SELECT SlotID AS SlotRank, SlotID FROM UserSeats WHERE SlotID > 8

     

    Returns an empty table because there are no seats greater than 8 (that is the maximum number of seats)

     

    Query Part 2:

    SELECT SlotID + 8 AS SlotRank, SlotID FROM UserSeats WHERE SlotID <= 8

     

    Returns:

    SlotRank | SlotID

    -----------------------

          12    |    4

          14    |    6

          9      |    1

          15    |    7

     

    So, when we UNION these two sets, we will get (same as the results from the second part of the query because we are at the maximum value):

     

    SlotRank | SlotID

    -----------------------

          12    |    4

          14    |    6

          9      |    1

          15    |    7

     

    Then using that as the basis, we can order those results based on the SlotRank to see which should come first.  For example:

     

    SELECT SlotID FROM (
SELECT SlotID AS SlotRank, SlotID FROM UserSeats WHERE slot > :current_slot
UNION
SELECT SlotID + 8 AS SlotRank, SlotID FROM UserSeats WHERE slot <= :current_slot )
ORDER BY SlotRank

     

    This will return the following result set (given that the current slot is 8):

    SlotID

    -----------

        1

        4

        6

        7

     

    Which would mean that the next used seat is seat 1 ... which satisfies your requirement?  It wouldn't choose 4 first because you said you wanted the next used seat since if we are at 8 would be 1 ...

     

    Does this make sense?

     

    ~judda

    multiple AND and OR clauses

    in MySQL Help

    Posted

    Your quotes for the surname are incorrect:

     

    From:

    $gmapSql2 .= " AND Surname = ''{$surname}";

     

    To:

    $gmapSql2 .= " AND Surname = '{$surname}'";

     

    I also noticed that the $langSpoken thing will also have a problem because your if statement is missing curly braces.  i.e.

     

    From:

     

    	
if($langSpoken !="All Languages")
$gmapSql2 .= " WHERE LanguageSpoken ='{$langSpoken}'  OR LanguageSpoken2 ='{$langSpoken}' OR LanguageSpoken3 ='{$langSpoken}'";
$gmapSql2 .= " OR LanguageSpoken4 ='{$langSpoken}' OR LanguageSpoken5 ='{$langSpoken}'";

     

    To:

     

    	
if($langSpoken !="All Languages") {
$gmapSql2 .= " WHERE LanguageSpoken ='{$langSpoken}'  OR LanguageSpoken2 ='{$langSpoken}' OR LanguageSpoken3 ='{$langSpoken}'";
$gmapSql2 .= " OR LanguageSpoken4 ='{$langSpoken}' OR LanguageSpoken5 ='{$langSpoken}'";
}

     

    This should make the query valid.

     

    ~judda

    Help with next position

    in MySQL Help

    Posted

    The actual query really depends on your implementation ... however, something like this may work:

     

    SELECT slot AS SlotRank, slot FROM slots WHERE slot > :current_slot ORDER BY slot 
UNION
SELECT slot + 8 AS SlotRank, slot FROM slots WHERE slot <= :current_slot ORDER BY slot

     

    Where :current_slot is the current slot that you are in.

     

    First query: Grab anything that is after our current slot

    Second query: Grab anything that is less than or is our current slot offsetting it by the maximum number of elements in your group (i.e. 8) so that they have higher slot numbers.

     

    That make sense?

     

    ~judda

    More data from MySql ("complex query")

    in MySQL Help

    Posted

    I believe that the syntax error you just provided us with is because you are missing the comma between losses and SUM ... i.e. 

    SELECT *, 
    case when (kills = 0) then 0 
    when (deaths = 0) then 1000 
    else ((kills*1.0)/(deaths*1.0)) end as killdeathratio, 
    ($scoreFormula) as totalscore 
     FROM ( 
     SELECT 
     gp.name as name, 
     bans.name as banname, 
     avg(dp.courierkills) as courierkills, 
     avg(dp.raxkills) as raxkills,
     avg(dp.towerkills) as towerkills, 
     avg(dp.assists) as assists, 
     avg(dp.creepdenies) as creepdenies, 
     avg(dp.creepkills) as creepkills,
     avg(dp.neutralkills) as neutralkills, 
     avg(dp.deaths) as deaths, 
     avg(dp.kills) as kills, 
     COUNT(*) as totgames, 
     case when (kills = 0) then 0 
     when (deaths = 0) then 1000 
     else ((kills*1.0)/(deaths*1.0)) 
     end as killdeathratio,
     SUM(case when(((dg.winner = 1 and dp.newcolour < 6) 
     or (dg.winner = 2 and dp.newcolour > 6)) 
     AND gp.`left`/ga.duration >= $minPlayedRatio) then 1 
     else 0 end) as wins, 
     SUM(case when(((dg.winner = 2 and dp.newcolour < 6) 
     or (dg.winner = 1 and dp.newcolour > 6)) 
     AND gp.`left`/ga.duration >= $minPlayedRatio) 
     then 1 else 0 end) as losses
          
     , SUM(case 
     when(
     (gp.`leftreason` LIKE ('%ECONNRESET%')) 
     OR 
     (gp.`leftreason` LIKE ('%was dropped%'))
     )
     then 'DISC' ) as disc
     

     FROM gameplayers as gp 
     LEFT JOIN dotagames as dg ON gp.gameid = dg.gameid 
     LEFT JOIN dotaplayers as dp ON dg.gameid = dp.gameid 
     AND gp.colour = dp.colour 
     AND dp.newcolour <> 12 
     AND dp.newcolour <> 6
     LEFT JOIN games as ga ON dp.gameid = ga.id 
     LEFT JOIN bans on bans.name = gp.name 
     WHERE dg.winner <> 0 $_sql
     GROUP BY gp.name having totgames >= $games) as i 
     ORDER BY $order $sortdb, name $sortdb 
     LIMIT $offset, $rowsperpage

    javascript & php confirmation

    in Javascript Help

    Posted

    Assuming it just associated with a button ... you could do something like this ...

     

    <script type = 'text/javascript'>
    function checkDelete ( id )
    {
         if ( confirm ( "Are you sure you want to delete " + id + "?" ) )
         {
              // relocate to another page
              document.location.href = 'deleteurl.php?id=' + id;
         }
         else
         {
               alert ( "Stop the presses!" );
         }
     }
</script>

     

    Then in your actual page you would have something like:

    <input type = 'button' onclick = 'checkDelete("<?php echo $id; ?>")' />

     

    ~juddster

    Wont MD5?

    in PHP Coding Help

    Posted

    == is NOT an assignment.  Therefore if you are trying to assign the value you are going about it the wrong way ... especially since it is in an if statement ...

     

    ~juddster

    MySQL Results Display

    in MySQL Help

    Posted

    Another thing is, in query you have $keyword whereas whenever you reference it outside of the query it is $search_term (and you never assign $search_term to $keyword).  So the query won't ever return what you want it to :P

     

    Single row expected:

    $arr = mysql_fetch_array ( $query );
print_r ( $arr ); // Display all information returned
echo $arr [ 'Note_ID' ]; // Echo only the 'Note_ID' which is returned

     

    Multiple rows returned:

    while ( $arr = mysql_fetch_array ( $query ) )
{
print_r ( $arr ); // Display all information returned
echo $arr [ 'Note_ID' ]; // Echo only the 'Note_ID' which is returned
}

     

    Hope this helps.

     

    ~juddster

    List option in sql query

    in Microsoft SQL - MSSQL

    Posted

    If the selection returns a value from a select box or something similar, if you just send back an id and then map those ids to a particular value to add to your query, you should be  safe.  Or as Brian said ... use parameters.

     

    $query = 'SELECT * FROM `details` ';
switch ( $listoption )
{
    case 1:
        $query .= 'WHERE otype = \'bugger\' ';
        break;
    case 2:
        $query .= 'WHERE otype = \'booger\' ';
        break;
    default:
      /* Do nothing (ALL) */
}

/* Execute $query */

     

    ~juddster

    need help to contruct multi-level array

    in Applications

    Posted

    How about something like this?

     

    $arr = array ( 'contactusform' => array ( 
          'field1' => array ( 
                     'fieldid' => 'nameField', 
                     'fieldtext' => 'Name'
           ), 
           'field2' => array ( 
                      'fieldid' => 'nextField', 
                       'fieldtext' => 'stuff here' 
             )
);

     

    ~juddster

    Conditional to complete mysql query

    in PHP Coding Help

    Posted

    You never actually run the first query against the database ...

     

    <?php
session_start();
include('../database.php'); 
$userid = $_SESSION['user']; 

$userxp = mysql_fetch_array ( mysql_query ( "SELECT xp FROM user WHERE username = '$userid'" ) );
$amount = 19999999;
if ($userxp [ 'xp' ] > $amount){
$query = "UPDATE `user` SET `xp`=(`xp`-20000000) WHERE `username` = '$userid' LIMIT 1";
$result = mysql_query($query) or die('Query: ' . $query . '<br />Failed with: ' . mysql_error());
echo "You have purchased this item";
}
else
{
echo "You do not have enough xp!";
}
?>

     

    Would be a hacked out version of your code so that it should work.

     

    ~juddster

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.