Jump to content

asmith

Members
 View Profile  See their activity

  • Posts

    967

  • Joined

  • Last visited

 Content Type 

Posts posted by asmith

    Stuck in optimizing query

    in MySQL Help

    Posted

    Hello,

     

    Using MySQL 5.1,

    I have this games table which is storing info about games played: (100,000+ rows and growing.)

     

    CREATE TABLE games (
  id_game int(10) unsigned NOT NULL auto_increment,
  info1 mediumint(9) NOT NULL,
  info2 tinyint(4) NOT NULL,
  versus_type tinyint(3) unsigned NOT NULL,
  post_time int(10) NOT NULL,
  game_type tinyint(4) NOT NULL,
  num_views mediumint( unsigned NOT NULL default '0',
  downloads mediumint( unsigned NOT NULL default '0',
  PRIMARY KEY  (id_game),
  KEY post_time (post_time),
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

     

    and another table which is storing the players for each game: (+600,000 rows and growing.)

     

    CREATE TABLE games_players (
  id_game int(11) NOT NULL,
  id_player mediumint( NOT NULL,
  `type` tinyint(3) unsigned NOT NULL,
  is_winner tinyint(1) unsigned NOT NULL default '0',
  KEY id_player (id_player),
  KEY id_game (id_game,id_player)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

     

    Each game could have different number of players assigned to it.

    So in a player page I wanna show his games normally. In an overall view and statistics, normally I gotta get all games to count different statistics which is all fine I guess. I use a query similar to this to get players games:

     

    SELECT p2.*, g.rate1, g.rate2, g.post_time
FROM games_players AS p
INNER JOIN games_players AS p2 ON p.id_game = p2.id_game
INNER JOIN games AS g ON p.id_game = g.id_game
WHERE p.id_player = [id_player] AND p.type = 0 AND g.game_type = [x] AND g.position = 0
  ORDER BY post_time ASC, g.id_game ASC

     

    The first join is to the same table to get other players our player played against.

    The second join is to get the game info itself.

     

    This query takes about average -0.05s to most members. But there are some members with like +5,000 games played and the query goes up to +0.3s getting those. This is the best I could get out of current situation.

    I've tried separating games table from players. But then for the second query I have to put "WHERE id_game IN (+5000 ids here)" which kinda results the same.

    I have to get all those rows, cause I gotta count different statistics vs each opponent and so and it is not possible to create another table to record each statistics vs every opponent in a separate record.

     

    I'm willing to redesign my tables, modify my PHP codes, etc...

    Am I getting the best out of it or it can be even better?

     

    Thanks for your time.

    p.s. I removed a few info columns in those tables just to simply reading it.

    Time vs Number

    in MySQL Help

    Posted

    Hello,

     

    I've been reviewing my codes/queries. I found that If I split a query (with 2 left joins)  into 3 queries, the sum of the 3 queries times is actually less than the main query. While learning MySQL I always went with "Less queries, More performance", but this case made me wonder which way to choose. Is it easier for MySQL/CPU/Server to handle 3 queries that takes less time or one query taking a bit more?

     

    Thanks for your time.

    MYSQL DATE FORMAT IN PHP TABLE

    in PHP Coding Help

    Posted

    Wherever you have a date vairable, for example if $post['updated'] is a date there, do this instead:

     

    change this:

    <?php echo $post['updated']?>

     

    to:

     

    <?php echo implode('-', array_reverse(explode('-', $post['updated'])))?>

     

    The only thing it does is changing dd-mm-yyyy to yyyy-mm-dd

    But litebearer's way is better. I'd go with that.

    Soap request example to code

    in PHP Coding Help

    Posted

    Hello,

     

    This is supposed to be a sample soap request. I don't know how to actually send the request with php.

     

    POST /sendSms.asmx HTTP/1.1
Host: host_site.com
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://some_uri.com/sendSms"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <sendSms xmlns="http://some_uri.com/">
      <username>string</username>
      <password>string</password>
      <smsContent>
        <string>string</string>
        <string>string</string>
      </smsContent>
      <cell>
        <string>string</string>
        <string>string</string>
      </cell>
      <from>string</from>
    </sendSms>
  </soap:Body>
</soap:Envelope>

     

    How am I supposed to so send this info? What is soapAction there?

     

    I tried:

     

    $client = new SoapClient("host_site.com/sendSms.asmx");

     

    and I'm getting:

     

    Fatal error: Uncaught SoapFault exception: [WSDL] SOAP-ERROR: Parsing WSDL: Couldn't load from 'host_site.com/sendSms.asmx' : Premature end of data in tag html line 3 in ...

    If the download has finished successfully

    in Application Design

    Posted

    Hello.

     

    I'm using this code to send files for download:

     

        header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename="'.basename($fileLocation).'"');
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Pragma: public');
    header('Content-Length: ' . filesize($fileLocation));
    ob_clean();
    flush();
    readfile($fileLocation);

     

    I wanna know if the user has downloaded the file completely or just canceled the browser download window.

    readfile() returns the bytes it read from the file, I checked the value when I press cancel on the download window, the size is the complete file size.

     

    Is there any way I could know if the download has finished successfully?

    Mysql find best query

    in MySQL Help

    Posted

    Hello :)

     

    I have different kind of transactions on the site and their log is recorded in 3 tables: (with different column names)

     

    log_a

    log_b

    log_c

     

    Lets say each transaction is for paying a product (id_product). So no matter what kind the transaction is, it is finally gonna point to id_product.

    I have another table:

     

    paid_products

     

    which is pointing that each product is done by which type of transaction:

     

    id    transaction_type  transaction_id

    3      log_a                    23

    41    log_c                    912

     

    I want to select all recent id_product which is connected to one of these log tables but have other columns data too.

     

    I'm not sure if there is something like:

    IF transaction_type = 'log_a'  then inner join log_a on ... OR

    IF transaction_type = 'log_b'  then inner join log_b on ... OR

     

    So I thought about converting paid_products table to something similar to this:

    id    id_log_a    id_log_b    id_log_c

     

     

    then run such query:

    select *, COALESCE(different field names on log tables) as option1, COALESCE(different field names on log tables) as option2

    from paid_products

    left join log_a as a on  id_log_a = a.ID_LOG

    left join log_b as b on  id_log_b = b.ID_LOG

    left join log_c as c on  id_log_c = c.ID_LOG

     

    Is there an obvious solution which I'm missing?

    Any better idea?

    Thanks for your time :)

    uksort() help

    in PHP Coding Help

    Posted

    Hello.

     

    I have an array like this and trying to sort it by its keys:

    <?php

$as = array(4 => 'a', 1 => 'd', 'A' => 'f', 10 => 'g');

function natSortMe($a, $b)
{
if ($b == 'A')
	return -1;
if ($a == $b)
	return 0;
else
	return $a > $b? -1 : 1;
}

uksort($as, 'natSortMe');
print_r($as);
?>

     

    I want the letter key comes first, then number from highest to lowest. the code above returns this:

    Array

    (

        [10] => g

        [4] => a

        [1] => d

        [A] => f

    )

     

    any idea?

    Need regex help

    in Regex Help

    Posted

    Thanks for your reply. It works fine. :)

     

    However as nrg_alpha mentioned to, I got it working by this too:    '/ |"([^"]+)"/'

    Is there a special circumstance we're missign here?

     

    Thanks again!

    Need regex help

    in Regex Help

    Posted

    Hello.

     

    I have a string like this:

     

    $content = 'word1 word2 "word3 word4" word5 "word6 word7"';

    I need to have an array of each word like explode(' ', $content), but consider the words in double quote as one:

     

    array('word1', 'word2', 'word3 word4',  'word5', 'word6 word7');

     

    Any idea?

    Thanks for your time

    So far I did something like this:  

    /("((?.+)(?<!"))+?)"| )/ 

    preg_match help

    in Regex Help

    Posted

    Thanks for the replay :)

     

    How do you exclude 01 and 02 03 04?  for example:

    01 68 34 12 0c 0f  02 03 04 43 1f 09 01 04 32 01 45 29 81 02 03 04 ...

     

    your suggestion will give:

    01 04 32 01 45 29 81 02 03 04

     

    while it must give:

    01 45 29 81 02 03 04

    preg_match help

    in Regex Help

    Posted

    Hi,

     

    I have a content looks like this:  (it is the  bin2hex() of a file with a space between each)

     

    01 68 34 12 0c 0f 02 03 04 43 1f 09 04 32 01 45 29 81 02 03 04 ...

     

    I want to get the content starting with 01 and end with 02 03 04. (the red text between bold ones)This phrase may be anywhere in the content and repeating any number.

     

    So far I did: 

    <?php
preg_match_all('/01 (^(02 03 04)+[0-9a-z]{2} )+02 03 04 /', $content, $result, PREG_OFFSET_CAPTURE);
print_r($result);
?>

    Can't get the unicode character

    in PHP Coding Help

    Posted

    Hey guys

     

    I'm trying to get some text from various parts of a file. I have converted the file to hex (bin2hex) and I've got this:

     

    64 72 e6 62 65 72  (without spaces)

     

    I'm converting that back by:

     

    <?php
function hex2bin($h)
{
if (!is_string($h)) return null;
$r='';
for ($a=0; $a<strlen($h); $a+=2)
	$r .= chr(hexdec($h{$a}.$h{($a+1)}));

return $r;
}
?>

     

    It works fine, except for unicode characters. For example, the above hex is giving me:

     

    dr�ber

     

    While it must be:

     

    dræber

     

    How can I get the correct word?

    Thanks a lot

    HEADER ERROR

    in PHP Coding Help

    Posted

    As cags mentioned too, you have html data at the very beginning of your code getting echoed. So when you are redirecting your user, there are html on the top of the page.

     

    Try this: (and look how I edited your code)

     

    <?php

error_reporting(E_ALL & ~E_NOTICE);

include "phpfunctions/sitewidefunctions.php";
include "phpfunctions/uniquevisitorcounter.php";

if(loggedin())
{
   header("Location: home.php");
   exit();
}
getmainlinksfade();
getsubtitlefade('Books');
getbooksfade('copyright');

$username = $_POST['username'];
$password = $_POST['password'];
$rememberme = $_POST['rememberme'];

if($username&&$password)
{
   $qry = mysql_query("SELECT * FROM users WHERE username='$username'");
   if(mysql_num_rows($qry)==0) echo "<div id='index_errorlogin'> No user found <br/></div>";
   else
   {
      while($row = mysql_fetch_assoc($qry))
      {
         $db_password = $row['password'];
         if(md5($password)==$db_password) 
         {         
         $loginok=TRUE;
         mysql_query("UPDATE users SET isOnline=1 WHERE username='$username'");         
         }         
         else
         {
            $loginok=FALSE;
            echo "<div id='index_errorlogin'>$username found! - Incorrect password <br/></div>";
            break;
         }
      if($loginok==TRUE)
         {
            if($rememberme=='on')
            {
            setcookie("username", $username, time() + 7200);
            $_SESSION['username']=$username;
            }
            else $_SESSION['username']=$username;
            header("Location: home.php");
            exit();
         }
         else die("");
      }
   }
}
else  $loginPlease = true;

echo "
<html>
<head>
<title>TITLE</title>
<link rel='stylesheet' type='text/css' href='CSS/global.css'/>
<script type='text/javascript' src='JS/util-functions.js'></script>
<script type='text/javascript' src='JS/clear-default-text.js'></script>
</head>
<body>

<div id='wrap'>
<div id='content'>
<div class='maintitle'>TITLE</div>";

if ($loginPlease)
     echo "<div id='index_errorlogin'>To view the entire library - Please log into the system below<br/></div>";

echo"
<div id='index_form'>
<form  action='index.php' method='post'>
   <input type='text' name='username' value='username' class='cleardefault'><br/>
   <input type='password' name='password'  class='cleardefault'><br/>
   <input type='checkbox' name='rememberme'><span id='index_rememberme'>Remember Me</span>
   <input type='submit' name='login' value='Login' id='index_login'>
</form>
</div>
<br/><br/>";

getindexstats();
getfooter();
echo"
</div>
</div>
</body>
</html>";
?>

×
×
  • 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.