MySQL/PHP help

[Anon]

Firstly, any idea why the following code doesn't work?

It is meant to get a list of scorers for a particular match, and then
go to another table and get the short version of a players name.

if (! isset($news_index) ) {
$news_index = 0;
}
$sql = "SELECT id,player,time FROM
fixtures_scorers WHERE team ='$team3' AND id='$date'";
$result = mysql_query($sql);
$row = mysql_num_rows($result);
$i = 0;
while ($i < $row) {
$i = $i + 1;

	                $myrow = mysql_fetch_array($result);
	                $player =

stripslashes($myrow["player"]);
$time = stripslashes($myrow["time"]);

$result = @("SELECT id FROM fixtures WHERE name='$player'")
or die ("That didn't work :-)");
$playername = @mysql_result($result, 0, "id");

			print "$playername $time, "

;}

For some reason only the first value is printed.

Secondly, last night Tottenham beat Stevenage 8-1 in a football match in England. If I was to get the
above code working, it would look like this:

Poyet 9, Ferguson 21, Poyet 28, Poyet 30, Ferguson 38, Freund 77,
Kamanan 80, Kamanan 84

Would there be any way of getting it to look like this:

Poyet 9,28,30 Ferguson 21,38 Freund 77 Kanaman 80,84

Any help would be greatly appreciated.

[Anon]

Hi Richard,

---

$row = mysql_num_rows($result);
$i = 0;
while ($i < $row) {
$i = $i + 1;

$myrow = mysql_fetch_array($result);
$player =
stripslashes($myrow["player"]);
$time = stripslashes($myrow["time"]);

$result = @("SELECT id FROM fixtures WHERE name='$player'")
or die ("That didn't work :-)");
$playername = @mysql_result($result, 0, "id");
print "$playername $time, "
;}

---

Try replacing the above code with this

$player_arr= array();
$i=0;
while($row=mysql_fetch_array($result))
{
$i++;
$player =
stripslashes($myrow["player"]);
$time = stripslashes($myrow["time"]);
if($i==1){
$player_arr[$player]=$time;
} else {
while ( list($key, $val) == each($player_arr)){
if($key==$player){
$player_arr[$player]=$player_arr[$player].",".$time;
} else {
$player_arr[$player]=$time;
}
} // end while
} // end if
}// end while

while ( list($key, $val) == each($player_arr)){
$res = @("SELECT id FROM fixtures WHERE name='$player'")
or die ("That didn't work :-)");
$playername = @mysql_result($res, 0, "id");

echo "$playername $val";
}

I haven't tested it.Hope this works for you

Cheers
Ajay

[antileon]

Hmm... I would do it like this instead. It would really improve the speed:

$rs = mysql_query("SELECT s.id AS scorers_id,f.id AS fixtures_id, player,s.time FROM
fixtures_scorers s, fixtures f, WHERE s.team ='$team3' AND s.id='$date' AND s.player=f.name ORDER BY f.id");

$i = 0;
while ($row = mysql_fetch_array($rs)) {
$playername = $row['fixtures_id'];
$time = $row['time'];
print "$playername $time, "
$i++;
}

Note: It hasn't been tested. And what I'm doing is to join tables to avoid having to do another query for every line in the first query.

As for the second wish: Compare the 'new' playername with the old one from the previous loop and if it matches, just output the time.

It would look like:

$oldvalue = "";
while ($row = ...) {
$playername = ...
$time = ...;
if ($oldvalue == $playername)
{
print "$time, ";
} else {
print "$playername $time, ";
}
$oldvalue = $playername;
}

And this one is not tested either. But it should work.

I hope you understand 🙂

[Anon]

Thank you for your help.

I understand what you're saying... I think... but I can't get it to work. I'm a complete novice and may have done something very foolish ;-)

I'm using this code now:

$rs = mysql_query("SELECT s.id AS scorers_id,p.id AS playername, player,p.time FROM
fixtures_scorers s, players p, WHERE s.team ='$team3' AND s.id='$date' AND s.player=p.name");

$i = 0;
while ($row = mysql_fetch_array($rs)) {
$playername = $row['playername'];
$time = $row['time'];
print "$playername $time, ";
$i++;
}
$oldvalue = "";
while ($row = ...) {
$playername = ...
$time = ...;
if ($oldvalue == $playername)
{
print "$time, ";
} else {
print "$playername $time, ";
}
$oldvalue = $playername;
}

Why doesn't this work? I get a parse error??!!

[powerlord]

The reason your code fails is because you're using the same variable to store the result identifier from mysql_query in both places you do a query. If you were to use different variables, I'd bet it would work correctly. 🙂