using two different tables

[dezkit]

Sup guys, I need help.

Ok here is my current code:

 

<?php
$steamid = $_GET["steamid"];


if($steamid){
$query1 = "SELECT * FROM achiev_unlocks WHERE authid='$steamid'"; 	 
$result1 = mysql_query($query1) or die(mysql_error());
$row1 = mysql_fetch_array($result1) or die(mysql_error());
$query2 = "SELECT * FROM achiev_list"; 	 
$result2 = mysql_query($query2) or die(mysql_error());
$row2 = mysql_fetch_array($result2) or die(mysql_error());
$save = $row2['save'];


echo "Stats for: {$row1['authid']}<br>Last seen as: {$row1['name']}<br><br><br>";
echo "<table border=0>";

while($row2 = mysql_fetch_array($result2)){
  $query3 = "SELECT * FROM achiev_unlocks WHERE {$row2['save']}='1'";
  $result3 = mysql_query($query1) or die(mysql_error());
  $row3 = mysql_fetch_array($result1) or die(mysql_error());
  echo $row2["save"];
}
echo "</table>";
} else {
echo "<form action=\"\" method=\"get\">Search: <input type=\"test\" name=\"steamid\"></form>";
}

?>

 

With my database layout like this:

achiev_list
name | info | save
Amazing | Survive 5 rounds as a human | human_amazing

achiev_unlocks
authid | name | human_amazing | human_unbreakable | etc.....
1 | dezkit | 0 | 1 

Here is my logic:

You search for authid: for example "1"

The php while loops the achiev_list

For every loop, the php checks if there is a table in achiev_unlocks with "1" (for example if human_amazing is being checked in the loop, it checks if human_amazing is 1 in achiev_unlocks)

If the table has 1 then echo the Name and Info.

 

Please help me as for my code doesn't work for some reason. Thank you, if any info is needed ill be here.

[dezkit]

I found a way..

<?php
$steamid = $_GET["steamid"];


if($steamid){
$query1 = "SELECT * FROM achiev_unlocks WHERE authid='$steamid'"; 	 
$result1 = mysql_query($query1) or die(mysql_error());
$row1 = mysql_fetch_array($result1) or die(mysql_error());
$query2 = "SELECT * FROM achiev_list"; 	 
$result2 = mysql_query($query2) or die(mysql_error());
$row2 = mysql_fetch_array($result2) or die(mysql_error());
$save = $row2['save'];


echo "Stats for: {$row1['authid']}<br>Last seen as: {$row1['name']}<br><br><br>";
echo "<table border=0>";


while($row2 = mysql_fetch_array($result2)){
  $query3 = "SELECT * FROM achiev_unlocks WHERE {$row2['save']}='1' and authid='$steamid'"; 
////////////////////////////////////////////////////////////// WHEN I PUT save to equal to 0 NOTHING SHOWS UP, there are rows with 0. WHY IS THAT /////////////////////////////////////////////////////
  $result3 = mysql_query($query3) or die(mysql_error());
  $row3 = mysql_fetch_array($result3) or die(mysql_error());
  $query4 = "SELECT * FROM achiev_list WHERE save='".$row2['save']."'";
  $result4 = mysql_query($query4) or die(mysql_error());
  $row4 = mysql_fetch_array($result4) or die(mysql_error());
  echo $row4["name"];
  echo "<br>";
  echo $row4["info"];
  echo "<br><br>";
}


echo "</table>";
} else {
echo "<form action=\"\" method=\"get\">Search: <input type=\"test\" name=\"steamid\"></form>";
}

?>

 

check the comment in the code to help me please.

[Pikachu2000]

Get rid of the single quotes on the ='0' and see if that makes a difference. Integers should not be quoted in a query string.

 

EDIT: Actually, now that I think about it, remove them from any place in the query where you'd expect the value to be an integer.

[Pikachu2000]

Also, since values are coming from a GET request, you should sanitize them before using them in a database query.

 

$steamid = (int) $_GET['steamid']; // if value is expected to be an integer
$steamid = mysql_real_escape_string($_GET['steamid']; // if value is to be a string

[dezkit]

Uhh, ok this is weird, if my first row has the value 0, then it echos the name, but if the 0 is in another row, maybe like at the end, it wont echo... help

[dezkit]

How do I do it so only the ones with "1" are echo'd? What my script is doing is only echoing the first 1's in the loop, if there is a 0 in between the 1's it won't echo the rest, for example

 

hello | 1

sadsd | 1

dksdko | 1

askdpok | 0

asdp | 1

opkas | 1

 

The script will only echo hello, sadsd, dksdko and won't echo asdp and opkas.

 

ALSO, it won't echo the first row for some reason, what the hell is up with my script?

<?php
$steamid = $_GET["steamid"];
$steamid = mysql_real_escape_string($_GET['steamid']);

if($steamid){
$query1 = "SELECT * FROM achiev_unlocks WHERE authid='$steamid'"; 	 
$result1 = mysql_query($query1) or die(mysql_error());
$row1 = mysql_fetch_array($result1) or die(mysql_error());
$query2 = "SELECT * FROM achiev_list"; 	 
$result2 = mysql_query($query2) or die(mysql_error());
$row2 = mysql_fetch_array($result2) or die(mysql_error());
$save = $row2['save'];


echo "Stats for: {$row1['authid']}<br>Last seen as: {$row1['name']}<br><br><br>";
echo "<table border=0>";


while($row2 = mysql_fetch_array($result2)){
  $query3 = "SELECT * FROM achiev_unlocks WHERE {$row2['save']}='1' and authid='$steamid'";
  $result3 = mysql_query($query3) or die(mysql_error());
  $row3 = mysql_fetch_array($result3) or die(mysql_error());
  
  $query4 = "SELECT * FROM achiev_list WHERE save='".$row2['save']."'";
  $result4 = mysql_query($query4) or die(mysql_error());
  $row4 = mysql_fetch_array($result4) or die(mysql_error());
  
  echo $row4["name"];
  echo "<br>";
  echo $row4["info"];
  echo "<br><br>";
}


echo "</table>";
} else {
echo "<form action=\"\" method=\"get\">Search: <input type=\"test\" name=\"steamid\"></form>";
}

?>

[dezkit]

I figured it out,

<?php 
$steamid = $_GET["steamid"];
$steamid = mysql_real_escape_string($_GET['steamid']);

if($steamid){
$query1 = "SELECT * FROM achiev_unlocks WHERE authid='$steamid'"; 	 
$result1 = mysql_query($query1) or die(mysql_error());
$row1 = mysql_fetch_array($result1) or die(mysql_error());

$query2 = "SELECT * FROM achiev_list"; 	 
$result2 = mysql_query($query2) or die(mysql_error());
$row2 = mysql_fetch_array($result2) or die(mysql_error());
$save = $row2['save'];


echo "Stats for: {$row1['authid']}<br>Last seen as: {$row1['name']}<br><br><br>";
echo "<table border=0>";


while($row2 = mysql_fetch_array($result2)){
  $query3 = "SELECT * FROM achiev_unlocks WHERE {$row2['save']}='1' and authid='$steamid'";
  $result3 = mysql_query($query3) or die(mysql_error());
  while($row3 = mysql_fetch_array($result3)){
  $query4 = "SELECT * FROM achiev_list WHERE save='".$row2['save']."'";
  $result4 = mysql_query($query4) or die(mysql_error());
  $row4 = mysql_fetch_array($result4);
  echo $row4["name"];
  echo "<br>";
  echo $row4["info"];
  echo "<br><br>";
  }
}


echo "</table>";
} else {
echo "<form action=\"\" method=\"get\">Search: <input type=\"test\" name=\"steamid\"></form>";
}

?>

 

OK Now my last problem: why doesn't my first row pop up? it skips automatically to the next row, can atleast 1 person help me..?

[ChemicalBliss]

I'll tell you why your last row isnt popping up, but i would suggest hearing me out first:

 

You should have a table that holds all the achievements someone can have. Then another table holding all the unlocks people have, so something like:

[Tablename]
Col1	|	Col2	| ..

[available_achievements]
`id`  |	 `name`	 |	`description`

[player_achievements]
`id`  |	  `player_id`	|	`achievement_id`  |	`date_achieved`

[players]
`id`  |	  `name`  |	etc..

 

This way you could easily grab all the achivements, with their names and descriptions, that the player has, in a single mysql query, with one loop.

 

-----------

 

If you  really want to keep your code you can use mysql_data_seek($result2); before you do the while loop.

 

You need to do this because you already got the first row after you executed the query:

 $row2 = mysql_fetch_array($result2) or die(mysql_error());

 

This is why you need to loop the function mysql_fetch_array(); as it only returns one row at a time, and it remembers which row was last called for the result resource you passed.

 

-note: please change you db structure - it will help you in the long run.

-cb-

[dezkit]

Hey, thanks for the reply, but I had fixed my code, the only problem I am having is that the first row isn't coming up for some reason.....

 

<?php
$steamid = $_GET["steamid"];
$steamid = mysql_real_escape_string($_GET['steamid']);

if($steamid){
$query1 = "SELECT * FROM achiev_unlocks WHERE authid='$steamid'"; 	 
$result1 = mysql_query($query1) or die(mysql_error());
$row1 = mysql_fetch_array($result1) or die(mysql_error());

$query2 = "SELECT * FROM achiev_list"; 	 
$result2 = mysql_query($query2) or die(mysql_error());
$row2 = mysql_fetch_array($result2) or die(mysql_error());
$save = $row2['save'];


echo "Stats for: {$row1['authid']}<br>Last seen as: {$row1['name']}<br><br><br>";
echo "<table border=0>";


while($row2 = mysql_fetch_array($result2)){
  $query3 = "SELECT * FROM achiev_unlocks WHERE {$row2['save']}='1' and authid='$steamid'";
  $result3 = mysql_query($query3) or die(mysql_error());
  while($row3 = mysql_fetch_array($result3)){
$query4 = "SELECT * FROM achiev_list WHERE save='".$row2['save']."'";
$result4 = mysql_query($query4) or die(mysql_error());
while($row4 = mysql_fetch_array($result4)){
	  echo $row4["name"];
	  echo "<br>";
	  echo $row4["info"];
	  echo "<br><br>";
  	}
  }
}

echo "<br><br><br>";
  


echo "</table>";
} else {
echo "<form action=\"\" method=\"get\">Search: <input type=\"test\" name=\"steamid\"></form>";
}

?>

[ChemicalBliss]

Please read (and consider) my post, all the way through thoroughly.

 

-cb-

[dezkit]

OH WOW, I just noticed that I had two " $row2 = mysql_fetch_array($result2) or die(mysql_error());" .

 

I'm stupid, thank's for pointing that out bud. <3333333 u

[dezkit]

ONE LAST THING:

I want to echo all the ones that have 0 right after i have the ones that have 1. here is the code that won't echo the ones with 0.

 

<?php
$steamid = $_GET["steamid"];
$steamid = mysql_real_escape_string($_GET['steamid']);

if($steamid){
$query1 = "SELECT * FROM achiev_unlocks WHERE authid='$steamid'"; 	 
$result1 = mysql_query($query1) or die(mysql_error());
$row1 = mysql_fetch_array($result1) or die(mysql_error());

$query2 = "SELECT * FROM achiev_list"; 	 
$result2 = mysql_query($query2) or die(mysql_error());
$save = $row2['save'];


echo "Stats for: {$row1['authid']}<br>Last seen as: {$row1['name']}<br><br><br>";
echo "<table border=0>";


while($row2 = mysql_fetch_array($result2)){
  $query3 = "SELECT * FROM achiev_unlocks WHERE {$row2['save']}='1' and authid='$steamid'";
  $result3 = mysql_query($query3) or die(mysql_error());
  while($row3 = mysql_fetch_array($result3)){
$query4 = "SELECT * FROM achiev_list WHERE save='".$row2['save']."'";
$result4 = mysql_query($query4) or die(mysql_error());
while($row4 = mysql_fetch_array($result4)){
	  echo $row4["name"];
	  echo "<br>";
	  echo $row4["info"];
	  echo "<br><br>";
  	}
  }
}

echo "<br><br><br>";
  
  while($row2 = mysql_fetch_array($result2)){
  $query3 = "SELECT * FROM achiev_unlocks WHERE {$row2['save']}='0' and authid='$steamid'";
  $result3 = mysql_query($query3) or die(mysql_error());
  while($row3 = mysql_fetch_array($result3)){
$query4 = "SELECT * FROM achiev_list WHERE save='".$row2['save']."'";
$result4 = mysql_query($query4) or die(mysql_error());
while($row4 = mysql_fetch_array($result4)){
	  echo $row4["name"];
	  echo "<br>";
	  echo $row4["info"];
	  echo "<br><br>";
  	}
  }
}

echo "</table>";
} else {
echo "<form action=\"\" method=\"get\">Search: <input type=\"test\" name=\"steamid\"></form>";
}

?>

 

btw if i could change the sql structure i would, but i didn't make it and i gotta work with it ;P

 

thanks

[ChemicalBliss]

Again, lol - mysql_data_seek(); between loops .

 

-cb-