When I select all...

bncplix · February 15, 2009

I will start by showing some of my code:

for ($i = 0; $i <= $74; $i += 1) {
$request = "SELECT friendid FROM friends WHERE userid = '".$loggedid."' AND worldid='".$i."'";
$result = mysql_query($request);
$data = mysql_fetch_array($result, MYSQL_ASSOC);
$friendid = $data['friendid'];


$request = "SELECT friendname FROM friendnames WHERE friendid = '".$friendid."'";
$result = mysql_query($request);
$data = mysql_fetch_array($result, MYSQL_ASSOC);
$friendname = $data['friendname'];

$request = "SELECT worldname FROM worlds WHERE worldid = '".$i."'";
$result = mysql_query($request);
$data = mysql_fetch_array($result, MYSQL_ASSOC);
$worldname = $data['worldname'];

}

when io make i output friendname it will be the latest result, how can i make it go through each result found to print?

As you can see I am selecting sets of data bassed on certain things

Now I know how to get individual data out of it, but how do i get the multiple sets of data each query is selecting?

Phoenix~Fire · February 15, 2009

$i++; is faster than $i += 1;

$i < 75 is faster than $i <= 74

$data = mysql_fetch_array($result, MYSQL_ASSOC); //faster you do: $data = mysql_fetch_row($result);

$friendid = $data['friendid']; // and: $friendid = $data[0];

It's a bit faster this way. In fact... it's probably also a better idea to use 3 queries instead of... 222 seperate queries.

If you use the variables inside of for(;{ /* your queries */ --here-- } they'll do what u want. =/ If you use them outside of the loop they will as you say, be the value of the last result.

If you need to use them outside of the loop you would need to use arrays.

$request = "SELECT friendname FROM friendnames WHERE id>=0 and id<74";

$friendresult = mysql_query($request);

/*replacing table and column names with the other ones*/

for($i=0;$i<75;$i++){

$friendid= mysql_fetch_row($friendidresult);

$friendname = mysql_fetch_row($friendnameresult);

$worldname = mysql_fetch_row($worldnameresult);

// could also go list($friendid) = mysql_fetch_row($friendidresult); to use $friendid as your variable... if u wanted

print $friendnames[0].$friendid[0],$worldname[0];

}

corbin · February 15, 2009

$i++; is faster than $i += 1;

Actually, if I had to guess, I would think that $i += 1 might be faster.

The reasoning? Well, $i++ returns $i and then increments it, so a copy is needed. $i += 1 just straight up incremented $i (and would return the value post incrementing).

I know for sure that ++$i is faster than $i++, and logically, $i += 1 seems like short hand for ++$i.

bncplix · February 15, 2009

$i++; is faster than $i += 1;

Actually, if I had to guess, I would think that $i += 1 might be faster.

The reasoning? Well, $i++ returns $i and then increments it, so a copy is needed. $i += 1 just straight up incremented $i (and would return the value post incrementing).

I know for sure that ++$i is faster than $i++, and logically, $i += 1 seems like short hand for ++$i.

Didnt think that really mattered, its not noticably faster anywho

I am having a few problems still so i think im going to rework the way my database handles the data and ill let you guys know if i run into any problems

And i did hav that MYSQL_ASSOC thing on some, but it didnt seem to do anything so i removed it, what excactly does it do that makes everything faster?

bncplix · February 15, 2009

Ok im calling on you guys for some help

I have been trying to get this work for a while but i just cant

So my database has these tables:

friendnames

friends

members

worlds

In friendnames, i have friendid, friendname and worldid

This is where the names are added and the friend id is looked up to find the name

In friends this is where it stores the data for what friends each user has

it has :

userid (members id)

friendid

worldid (needed again for selecting in database)

members has just the id, username and password

worlds has the world id and name (never changed)

Im making it so when a name is passed to the script it will check the database. If the name is nowhere in friendnames, then it will add that and link the id as one the player is friends with. If it is already in the database, it simply links the id

Now I am trying to make it display but I have run into some bumps, herei s the script:

<?


session_start();
$loggedas = $_SESSION['myusername'];
if(!session_is_registered(myusername)){
//echo 'You are not logged in';

?>


<?php



//header("location:main_login.php");
}else{

echo 'You are logged in<p>';


$host = 'localhost'; // Database host usually localhost
$dbuser = ''; // Database Username
$dbpass = 'i'; // Database Password
$dbname = ''; // Database name

// Database Connection - do NOT edit
mysql_connect("$host", "$dbuser", "$dbpass") or die();
mysql_select_db("$dbname") or die();


$query = "SELECT id FROM members WHERE username='".$loggedas."'";
$result = mysql_query($query);
$data = mysql_fetch_array($result, MYSQL_ASSOC);
$loggedid = $data['id'];

for ($i = 0; $i <= 74; $i += 1) {

$request = "SELECT friendid FROM friends WHERE userid = '".$loggedid."' AND worldid='".$i."'";
$result = mysql_query($request);
$data = mysql_fetch_array($result, MYSQL_ASSOC);
$friendid = $data['friendid'];


echo '<p>friend id: '.$friendid;
$request = "SELECT friendname FROM friendnames WHERE friendid = '".$friendid."'";
$result = mysql_query($request);
$data = mysql_fetch_array($result);
$friendname = $data['friendname'];
echo '<p> name: '.$friendname;

$request = "SELECT worldname FROM worlds WHERE worldid = '".$i."'";
$result = mysql_query($request);
$data = mysql_fetch_array($result, MYSQL_ASSOC);
$worldname = $data['worldname'];

echo '<p> world: '.$worldname;

}





} //else (if logged in)
?>

I cant figure out how to get it to display properly each name

Also, as you can see I am using a loop to check every single world to see if there is players from each. Is there a way to select the first world id found when looking, do all the players from that one, then do the next found world id, until all are done, instead of the unneccessary checking?

Help is greatly appreciated and I can reward it somehow too

Phoenix~Fire · February 15, 2009

Well, realistically things are only like a couple CPU ticks faster... Your right, things wouldn't be noticeable. However, If you were to run the queries billions of times over you would be saving time.

MYSQL_ASSOC means mysql_fetch_array() will NOT index the array...

$q = "select * from something";

print_r(mysql_fetch_array($q)); would return

Array(0 => 1, 1 => "User", 2 => "password", id => 1, username => "User", password => "password");

print_r(mysql_fetch_array($q,MYSQL_ASSOC)); would return

Array(id => 1, username => "User", password => "password");

mysql_fetch_assoc() I believe is the same as using mysql_fetch_array(query,MYSQL_ASSOC)

bncplix · February 15, 2009

Well, realistically things are only like a couple CPU ticks faster... Your right, things wouldn't be noticeable. However, If you were to run the queries billions of times over you would be saving time.

MYSQL_ASSOC means mysql_fetch_array() will NOT index the array...

$q = "select * from something";

print_r(mysql_fetch_array($q)); would return

Array(0 => 1, 1 => "User", 2 => "password", id => 1, username => "User", password => "password");

print_r(mysql_fetch_array($q,MYSQL_ASSOC)); would return

Array(id => 1, username => "User", password => "password");

mysql_fetch_assoc() I believe is the same as using mysql_fetch_array(query,MYSQL_ASSOC)

To be honest i dont see a diference, but ill stick with mysql_assoc

read my post above yours and i hope you can help, been at this for a whle and i hit a brick wall ;/

fenway · February 15, 2009

TLDR.. what's wrong now?

bncplix · February 15, 2009

TLDR.. what's wrong now?

TLDR?

corbin · February 15, 2009

TLDR = Too long, didn't read.

As long as you're doing that many queries, it's going to be slow.

Why not pull everything in one query?

bncplix · February 15, 2009

TLDR = Too long, didn't read.

As long as you're doing that many queries, it's going to be slow.

Why not pull everything in one query?

I can do that? That is why I posted cuz i said i know theres a more efficient way but i just cant figure it out

Robbo_89 · February 15, 2009

<?php

// get userid here

// Query the worlds
$result = mysql_query("SELECT * FROM worlds");

// Loop through each world
while($world = mysql_fetch_assoc($result))
{
    // Query the players in this world
    $request = mysql_query("SELECT * FROM friends WHERE worldid = $world[worldid] AND userid = $userid");
    
    // If there are no friends then continue
    if(!mysql_num_rows($request))
        continue;
    
    // Loop through each friend
    while($friend = mysql_fetch_assoc($request))
    {
        // do what you want with the friend here
    }
    
    mysql_free_result($request);
}

mysql_free_result($result);

?>

To lazy to make one query.

Mchl · February 15, 2009

$i++; is faster than $i += 1;

Actually, if I had to guess, I would think that $i += 1 might be faster.

I actually tested it:

$time_start1 = microtime(true);
for ($i=1; $i < 1000000; $i++) {}
$time_end1 = microtime(true);

$time_start2 = microtime(true);
for ($j=1; $j < 1000000; $j+=1) {}
$time_end2 = microtime(true);

$time_start3 = microtime(true);
for ($k=0; $k < 1000000; ++$k) {}
$time_end3 = microtime(true);



$time1 = $time_end1 - $time_start1;
$time2 = $time_end2 - $time_start2;
$time3 = $time_end3 - $time_start3;
echo "$time1 s, $time2 s, $time3 s<br/>\n";
?>

In general it seems that $j+=1 is a few percent faster than $i++, and ++$k another few percent faster, although from time to time I get results that suggest otherwise. Probably OS's influence

Robbo_89 · February 15, 2009

<?php

// get userid here

// Query the worlds
$result = mysql_query("SELECT worldid as id, worldname as name FROM worlds");

// Loop through each world
while($world = mysql_fetch_assoc($result))
{
    // Query the players in this world
    $request = mysql_query("SELECT fn.friendname AS name, fn.friendid AS id FROM friends AS f
                                LEFT JOIN friendnames AS fn ON (f.friendid = fn.friendid AND fn.worldid = $world[worldid]) 
                            WHERE f.worldid = $world[id] AND f.userid = $userid");
    
    // If there are no friends then continue
    if(!mysql_num_rows($request))
        continue;
    
    // Loop through each friend
    while($friend = mysql_fetch_assoc($request))
    {
        // do what you want with the friend here
    }
    
    mysql_free_result($request);
}

mysql_free_result($result);

?>

corbin · February 15, 2009

$i++; is faster than $i += 1;

Actually, if I had to guess, I would think that $i += 1 might be faster.

I actually tested it:
$time_start1 = microtime(true);
for ($i=1; $i < 1000000; $i++) {}
$time_end1 = microtime(true);

$time_start2 = microtime(true);
for ($j=1; $j < 1000000; $j+=1) {}
$time_end2 = microtime(true);

$time_start3 = microtime(true);
for ($k=0; $k < 1000000; ++$k) {}
$time_end3 = microtime(true);



$time1 = $time_end1 - $time_start1;
$time2 = $time_end2 - $time_start2;
$time3 = $time_end3 - $time_start3;
echo "$time1 s, $time2 s, $time3 s<br/>\n";
?>
In general it seems that $j+=1 is a few percent faster than $i++, and ++$k another few percent faster, although from time to time I get results that suggest otherwise. Probably OS's influence

Yeah, I expected $i++ to be the slowest. I would have thought that $i += 1 would have been equal to or super close to ++$i though since they do the same thing. (As far as parsing goes, I'm sure $i += 1 takes longer to parse since the 1 could be any number, whereas with ++$i, it's always 1.)

bncplix · February 15, 2009

Robbo has been such a help in the IRC channel.

I want to say thanks

bncplix · February 18, 2009

Thats weird, yesterday this script worked fine, no problems.

Today i went on the site with it, no changes to the script

For some reason, the "world" while loop goes through twice, so all the data is printed, then the excact same data is printed out again

bncplix · February 18, 2009

Here is the full script

<?


session_start();
if ($_GET['log'] == "true"){
session_destroy();
}
$loggedas = $_SESSION['myusername'];
if(!session_is_registered(myusername)){
//echo 'You are not logged in';

?>
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="yes" name="apple-mobile-web-app-capable" />
<meta content="text/html; charset=iso-8859-1" http-equiv="Content-Type" />
<meta content="minimum-scale=1.0, width=device-width, maximum-scale=0.6667, user-scalable=no" name="viewport" />
<link href="../css/style.css" rel="stylesheet" type="text/css" />
<script src="../javascipt/functions.js" type="text/javascript"></script>
<title> Friend List</title>
<meta content="" name="Keywords" />
<meta content="Description of your site" name="description" />
</head>
<body class="list">

<div id="topbar">
<div id="title">Friend List</div>
<div id="leftnav">
<a href="index.php"><img alt="home" src="../images/home.png"/></a>
</div>
</div>
<div id="content">

<ul>
<li class="title">Please log in</span></li>




<table width="300" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<form name="form1" method="post" action="checklogin.php">
<td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td colspan="3"><strong></strong></td>
</tr>
<tr>
<td width="78">Username</td>
<td width="6">:</td>
<td width="294"><input name="myusername" type="text" id="myusername"></td>
</tr>
<tr>
<td>Password</td>
<td>:</td>
<td><input name="mypassword" type="text" id="mypassword"></td>
</tr>
<tr>
<td> </td>
<td> </td>
<td><input type="submit" name="Submit" value="Login"></td>
</tr>
</table>
</td>
</form>
</tr>
</table>

<?php



//header("location:main_login.php");
}else{


?>
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="yes" name="apple-mobile-web-app-capable" />
<meta content="text/html; charset=iso-8859-1" http-equiv="Content-Type" />
<meta content="minimum-scale=1.0, width=device-width, maximum-scale=0.6667, user-scalable=no" name="viewport" />
<link href="../css/style.css" rel="stylesheet" type="text/css" />
<script src="../javascipt/functions.js" type="text/javascript"></script>
<title> Friend List</title>
<meta content="" name="Keywords" />
<meta content="Description of your site" name="description" />
</head>
<body class="list">

<div id="topbar">
<div id="title">Friend List</div>
<div id="leftnav">
<a href="index.php"><img alt="home" src="../images/home.png"/></a>
</div>
<div id="rightbutton">
<a href="index.php">Refresh</a>
</div>
</div>
<div id="content">

<ul>
<!--<li class="title">Enter a character name</span></li>-->

<?php
//echo 'You are logged in<p>';


$host = 'localhost'; // Database host usually localhost
$dbuser = ''; // Database Username
$dbpass = ''; // Database Password
$dbname = ''; // Database name

// Database Connection - do NOT edit
mysql_connect("$host", "$dbuser", "$dbpass") or die();
mysql_select_db("$dbname") or die();


//$newfriend = $_GET['add'];
$newfriend= str_replace("\'","'",$_GET['add']);
//$newfriend= str_replace("'","\'",$_GET['add']);
$newfriend = htmlentities($newfriend); //prevent xss

if ($newfriend != ""){
//add new friend
//check to see if new character exists

$charSite = file_get_contents('http://www.tibia.com/community/?subtopic=character&name='.str_replace(array(' ','~'),array('+',''),$newfriend));
$splitA = explode('<TR><TD BGCOLOR="#505050" CLASS=white><B>', $charSite);
$splitB = explode('</B></TD></TR>',$splitA[1]);
if($splitB[0] == "Could not find character"){
echo 'The character '.$newfriend.' does not exist.';
}else{
//lets first find what world hes in

$splitA = explode('World:</TD><TD>', $charSite);
$splitB = explode('</TD></TR>',$splitA[1]);
$world = $splitB[0];
//echo '-'.$world.'-';

//fix name up for databasr
//$newfriend = str_replace(" ","+",$newfriend);
$newfriend = strtolower($newfriend);
//check to see if hes already in the database
$newfriend = str_replace("'","\'",$newfriend);
$request = mysql_query("SELECT friendname FROM friendnames WHERE friendname = '".$newfriend."'");



//find world id related to world
$query = "SELECT worldid FROM worlds WHERE worldname = '".$world."'";
$result = mysql_query($query);
//while($data = mysql_fetch_array($result)){
$data = mysql_fetch_array($result);
$worldid = $data['worldid'];
//}

//echo '-'.$worldid.'-';
if (mysql_num_rows($request) > 0){
//already exists
//echo 'This player is already in the database.';
$query = "SELECT id FROM members WHERE username='".$loggedas."'";
$result = mysql_query($query);
$data = mysql_fetch_array($result, MYSQL_ASSOC);
$loggedid = $data['id'];
//echo $loggedid; //user's id number
//lets add the id that is already in the database to the users list

$request = "SELECT friendid FROM friendnames WHERE friendname = '".$newfriend."'";
$result = mysql_query($request);
$data = mysql_fetch_array($result, MYSQL_ASSOC);
$friendid = $data['friendid']; //we now know the friend id and the user id
//echo 'friendid: '.$friendid;

$request = mysql_query("SELECT userid FROM friends WHERE friendid = '".$friendid."' AND userid = '".$loggedid."'");

if (mysql_num_rows($request) > 0){
echo 'You already have '.$newfriend.' on your friend list.';
}else{

//add new to friends, user id, friend id and world id
$query = "INSERT INTO friends(userid, friendid, worldid) 
VALUES ('".$loggedid."', '".$friendid."', '".$worldid."')";
mysql_query($query);


  mysql_free_result($result);
//mysql_query(" ");
echo 'You added '.$newfriend.' to your VIP list.';
}//}else{ for already has
}else{
//echo 'name id not already added';




$query = "SELECT id FROM members WHERE username='".$loggedas."'";
$result = mysql_query($query);
$data = mysql_fetch_array($result, MYSQL_ASSOC);
$loggedid = $data['id'];

//find world id related to world
//$request = mysql_query("SELECT worldid FROM worlds WHERE worldname = '".$world."'");
//$result = mysql_query($request);
//$data = mysql_fetch_array($result, MYSQL_ASSOC);
//$worldid = $data['worldid'];
//$newfriend = str_replace("'","\'",$newfriend);
$query = "INSERT INTO friendnames(friendname, worldid) 
VALUES ('".$newfriend."', '".$worldid."')";
mysql_query($query);

$request = "SELECT friendid FROM friendnames WHERE friendname = '".$newfriend."'";
$result = mysql_query($request);
$data = mysql_fetch_array($result, MYSQL_ASSOC);
$friendid = $data['friendid'];

//check to ensure he dosnt already have the friend added

$request = mysql_query("SELECT userid FROM friends WHERE friendid = '".$friendid."' AND userid = '".$loggedid."'");

if (mysql_num_rows($request) > 0){
echo 'You already have '.$newfriend.' on your friend list.';
}else{

//add new to friends, user id, friend id and world id
$query = "INSERT INTO friends(userid, friendid, worldid) 
VALUES ('".$loggedid."', '".$friendid."', '".$worldid."')";
mysql_query($query);
echo 'You added '.$newfriend.' to your VIP list.';
mysql_free_result($result);
}
} //end if for if it exists, else
}


}//if ($newfriend != ""){




//CHECK ONLINES!!




$query = "SELECT id FROM members WHERE username='".$loggedas."'";
$result = mysql_query($query);
$data = mysql_fetch_array($result, MYSQL_ASSOC);
$userid = $data['id'];


$result = mysql_query("SELECT worldid as id, worldname as name FROM worlds");

// Loop through each world
while($world = mysql_fetch_assoc($result))
{
    // Query the players in this world
$request = mysql_query("SELECT fn.friendname AS name, fn.friendid AS id FROM friends AS f
                                LEFT JOIN friendnames AS fn ON (f.friendid = fn.friendid AND fn.worldid = $world[id]) 
                            WHERE f.worldid = $world[id] AND f.userid = $userid");
    
    // If there are no friends then continue
    if(!mysql_num_rows($request))
        continue;
    
    //Lets get the world list and do some checks
    
    $worldSite = file_get_contents('http://www.tibia.com/community/?subtopic=whoisonline&world='.$world['name']);
$splitA = explode('<TD>Currently ', $worldSite);
$splitB = explode(' players are online.',$splitA[1]);
$onlinecount = $splitB[0];



echo '<li class="title">'.$world['name'].'</span></li>';
    // Loop through each friend
    while($friend = mysql_fetch_assoc($request))
    {
    for ($i = 1; $i <= $onlinecount; $i++) { 
   $splitA = explode('<A HREF="http://www.tibia.com/community/?subtopic=characters&name=', $worldSite);
$splitB = explode('">',$splitA[$i]);
$online[$i] = str_replace("+"," ",$splitB[0]);
$online[$i] = str_replace("%27","'",$online[$i]);

if ( strtolower($online[$i]) == $friend['name']){

echo '<li><span class="name"><a href="http://tibia.mobi/search.php?char='.$friend['name'].'">'.$friend['name'].'</a></span></li>';
    }
   
    }
    }
    mysql_free_result($request);
}
mysql_free_result($result);



mysql_close();


echo '<center>Add a friend:</center>';
echo '<center><form name="add" action="index.php" method="get">

<br><br>
<input type="text" size="15" name="add" style="font-size:20pt;" value="">
<br><input type="submit" value="Submit" style="height:5em; width:18em; font-size:10pt;"><br>
</form></center>';


} //else (if logged in)
?>

fenway · February 23, 2009

What do you expect us to do with that?

Sign In

When I select all...

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information