When I select all...

[bncplix]

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]

$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]

$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]

$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]

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]

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]

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]

TLDR.. what's wrong now?

[bncplix]

TLDR.. what's wrong now?

 

TLDR?

[corbin]

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]

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]

<?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]

$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]

<?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]

$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]

Robbo has been such a help in the IRC channel.

 

I want to say thanks

[bncplix]

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]

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]

What do you expect us to do with that?